public class OpenXmlHelper { /// <summary> /// 读取Excel数据到DataSet中,默认读取所有Sheet中的数据 /// </summary> /// <param name="filePath">Excel文件路径</param> /// <param name="sheetNames">Sheet名称列表,默认为null查询所有Sheet中的数据</param> /// <returns></returns> public static DataSet ReadExcel(string filePath, params string[] sheetNames) { try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, true)) { IEnumerable<Sheet> sheets = doc.WorkbookPart.Workbook.Descendants<Sheet>(); if (sheetNames != null && sheetNames.Length > 0) { sheets = sheets.Where(s => sheetNames.ToList().Contains(s.Name)); } DataSet ds = new DataSet(); SharedStringTable stringTable = doc.WorkbookPart.SharedStringTablePart.SharedStringTable; foreach (Sheet sheet in sheets) { WorksheetPart sheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id); IEnumerable<Row> rows = sheetPart.Worksheet.Descendants<Row>(); DataTable dt = new DataTable(sheet.Name); foreach (Row row in rows) { if (row.RowIndex == 1) { GetDataColumn(row, stringTable, dt); } GetDataRow(row, stringTable, dt); } ds.Tables.Add(dt); } return ds; } } catch (Exception ex) { //MessageBox.Show(ex.Message); return null; } }
#region help methods /// <summary> /// 把Spreadsheet行中的数据导出到DataTable中。 /// </summary> /// <param name="row">Spreadsheet行</param> /// <param name="stringTable">共享字符串表</param> /// <param name="dt">DataTable</param> private static void GetDataRow(Row row, SharedStringTable stringTable, DataTable dt) { DataRow dr = dt.NewRow(); string cellValue = string.Empty; int i = 0; int nullCellCount = i; foreach (Cell cell in row) { cellValue = GetCellValue(cell, stringTable); if (cellValue == string.Empty) nullCellCount++; dr[i] = cellValue; i++; } //如果一整行数据都没有数据,则不添加此行到DataTable中 if (nullCellCount != i) dt.Rows.Add(dr); } /// <summary> /// 从Spreadsheet行中读取表头信息 /// </summary> /// <param name="row">Spreadsheet行</param> /// <param name="stringTable">共享字符串表</param> /// <param name="dt">DataTable</param> private static void GetDataColumn(Row row, SharedStringTable stringTable, DataTable dt) { DataColumn col = new DataColumn(); Dictionary<string, int> columnCountDict = new Dictionary<string, int>(); foreach (Cell cell in row) { string cellValue = GetCellValue(cell, stringTable); col = new DataColumn(cellValue); //由于Excel中的数据表列标题可以重复,而DataTable中不允许重复,因此在重复的列标题后追加递增数字 if (dt != null && dt.Columns.Contains(cellValue)) { if (!columnCountDict.ContainsKey(cellValue)) { columnCountDict.Add(cellValue, 0); } col.ColumnName = cellValue + (columnCountDict[cellValue]++); } dt.Columns.Add(col); } } /// <summary> /// 获取Spreadsheet单元格的值 /// </summary> /// <param name="cell">Spreadsheet单元格</param> /// <param name="stringTable">共享字符串表</param> /// <returns>Spreadsheet单元格的值</returns> private static string GetCellValue(Cell cell, SharedStringTable stringTable) { string value = string.Empty; try { if (cell.ChildElements.Count == 0) { return value; } value = cell.CellValue.InnerText; if (cell.DataType != null && cell.DataType == CellValues.SharedString) { value = stringTable.ChildElements[int.Parse(value)].InnerText; } } catch (Exception) { value = "N/A"; } return value; }
#endregion }