在用NPOI导入excel时,单元格类型是Date,文字如11/13/2023,但读取后返回值是11-NOV-2023
月份变成了英文,采用如下方式解决。
public static DataTable ExcelImport(string strFileName)
{
DataTable dt = new DataTable();
IWorkbook workbook = null;
ISheet sheet = null;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (strFileName.IndexOf(".xlsx") == -1)//2003
{
workbook = new HSSFWorkbook(file);
}
else//2007
{
workbook = new XSSFWorkbook(file);
}
sheet = workbook.GetSheetAt(0);
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)//NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
{
if (HSSFDateUtil.IsCellDateFormatted(cell))
dataRow[j] = cell.DateCellValue.ToString("MM/dd/yyyy");
else
dataRow[j] = cell.NumericCellValue;
}
else if (cell.CellType == CellType.Blank)//空数据类型
{
dataRow[j] = "";
}
else if (cell.CellType == CellType.Formula)//公式类型
{
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
dataRow[j] = eva.Evaluate(cell).StringValue;
}
else //其他类型都按字符串类型来处理
{
dataRow[j] = cell.StringCellValue;
}
}
}
dt.Rows.Add(dataRow);
}
return dt;
}