c# 通过npoi操作excel汇总

1:新建工程,使用nuget添加引用

2:读取文件时,需要判断后缀

static DataTable ReadExcel(string filename)
{
DataTable dtTable = new DataTable();
List<string> rowList = new List<string>();
ISheet sheet;
string fileExt = Path.GetExtension(filename).ToLower();
IWorkbook workbook;

using (var fs = new FileStream(filename, FileMode.Open))
{
if (fileExt == “.xlsx”) { workbook = new XSSFWorkbook(fs); } else if (fileExt == “.xls”) { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { return null; }

sheet = workbook.GetSheetAt(0);
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
{
dtTable.Columns.Add(cell.ToString());
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null) continue;
if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
{
rowList.Add(row.GetCell(j).ToString());
}
}
}
if (rowList.Count > 0)
dtTable.Rows.Add(rowList.ToArray());
rowList.Clear();
}
}
// return JsonConvert.SerializeObject(dtTable);
return dtTable;
}