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 > )
{
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 == )
{
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 = ;
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, );
}
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 == )
{
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
}