通过NPOI解析Excel,将数据保存到DataTable中。
#region excel解析
public DataTable ImportExcelFile(string filePath)
{
string extension = System.IO.Path.GetExtension(filePath); //excel获取文件后缀
DataTable table = new DataTable();
using (FileStream fs = File.OpenRead(filePath))
{
IWorkbook wk = null;
if (extension.Equals(".xls")) //根据文件版本实例化对象
{
wk = new HSSFWorkbook(fs);
}
else
{
wk = new XSSFWorkbook(fs);
}
ISheet sheet = wk.GetSheetAt(0); //获取excel的第一个sheet
IRow row = sheet.GetRow(0); //获取sheet的首行
int cellCount = row.LastCellNum;//一行最后一个方格的编号 即总的列数
for (int i = row.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(row.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum; //最后一列的标号,即总的行数
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow rows = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = rows.FirstCellNum; j < cellCount; j++)
{
//if (rows.GetCell(j) != null)
// {
// if (rows.GetCell(j).CellType == CellType.Numeric)
//dataRow[j] = rows.GetCell(j).RichStringCellValue;
dataRow[j] = rows.GetCell(j).ToString();
// }
}
table.Rows.Add(dataRow);
}
}
return table;
}
#endregion