关于C# 中使用open xml sdk 读取Excel数据笔记

项目用到open xml SDK ，开始的时候一脸懵逼，网上有很多的案例也看好多的案例，还是决定自己总结一下自己的做法：

1、要在项目中用到open xml SDK 得先下载Open XML SDK 2.5 for Office

　　下载路径：https://www.microsoft.com/en-us/download/details.aspx?id=30425

　　下载后文件：OpenXMLSDKV25.msi 安装后就会出现 DocumentFormat.OpenXml.dll

　　可以在官网上看详细的讲解：https://msdn.microsoft.com/zh-cn/library/bb448854.aspx

2、使用open xml 获取Excel数据

　　首先将DocumentFormat.OpenXml.dll 引用到项目下：

　　获取Excel表指定的工作表：

/// <summary>
/// 获取Excel指定工作表数据
/// </summary>
/// <param name="filePath">Excel所在路径</param>
/// <param name="sheetName">工作表名</param>
/// <returns></returns>
public static void GetExcelVlaue(string filePath,string sheetName)
{
　　//打开文件
　　SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false);
 　　WorkbookPart workbook = document.WorkbookPart;
     IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
 　　　　if (sheets.Count() == 0)
           {
              return null;
           }
       WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
　　　　Worksheet worksheet = worksheetPart.Worksheet;

　　　　IEnumerable<Row> rows = worksheet.Descendants<Row>();
　　　　foreach (Row row in rows)//获取行的值
            {
                foreach (Cell cell in row)
                {
                    string columnValue = GetValue(cell, workbook.SharedStringTablePart);
                }
            }

}

 1 /// <summary>
 2         /// 获取单元格信息  这也是官方获取值的方法
 3         /// </summary>
 4         /// <param name="cell"></param>
 5         /// <param name="stringTablePart">stringTablePart就是WorkbookPart.SharedStringTablePart，它存储了所有以SharedStringTable方式存储数据的子元素。</param>
 6         /// <returns></returns>
 7         public static string GetValue(Cell cell, SharedStringTablePart stringTablePart)
 8         {
 9             if (cell.ChildElements.Count == 0)
10                 return null;
11             //get cell value
12             String value = cell.CellValue.InnerText;
13             //Look up real value from shared string table
14             if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
15                 value = stringTablePart.SharedStringTable
16                     .ChildElements[Int32.Parse(value)]
17                     .InnerText;
18             return value;
19         }

获取值就是完了！根据自己要的数据进行整合就好！

如果要获取表格的样式及字体及：

1 IEnumerable<DocumentFormat.OpenXml.Spreadsheet.CellFormat> cellStyles = style.Stylesheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.CellFormat>();//获取单元格样式信息
2 IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Font> font = style.Stylesheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.Font>();//获取字体信息

 1  /// <summary>
 2  /// 创建新的工作表
 3  /// </summary>
 4 /// <param name="filepath">Excel表路径</param>
 5 public static void CreateSpreadsheetWorkbook(string filepath)
 6         {
 7             // 提供文件路径创建一个电子表格文件.
 8             // By default, AutoSave = true, Editable = true, and Type = xlsx.
 9             SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
10                 Create(filepath, SpreadsheetDocumentType.Workbook);
11 
12             // 添加一个workbookpart到文档.
13             WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
14             workbookpart.Workbook = new Workbook();
15 
16             // Add a WorksheetPart to the WorkbookPart.
17             WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
18             worksheetPart.Worksheet = new Worksheet(new SheetData());
19 
20             // Add Sheets to the Workbook.
21             Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
22                 AppendChild<Sheets>(new Sheets());
23 
24             // Append a new worksheet and associate it with the workbook.
25             Sheet sheet = new Sheet()
26             {
27                 Id = spreadsheetDocument.WorkbookPart.
28                     GetIdOfPart(worksheetPart),
29                 SheetId = 1,
30                 Name = "mySheet"
31             };
32             sheets.Append(sheet);
33 
34             workbookpart.Workbook.Save();
35 
36             // Close the document.
37             spreadsheetDocument.Close();
38         }