NPOI读取Excel（2003或者2010）返回DataTable。支持公式解析，空行处理。
/// <summary>读取excel
/// 默认第一行为表头
/// </summary>
/// <param name="strFileName">excel文档绝对路径</param>
/// <param name="rowIndex">内容行偏移量，第一行为表头，内容行从第二行开始则为1</param>
/// <returns></returns>
public static DataTable Import(string strFileName, int rowIndex)
{
DataTable dt = new DataTable(); IWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = WorkbookFactory.Create(file);
}
ISheet sheet = hssfworkbook.GetSheetAt(); IRow headRow = sheet.GetRow();
if (headRow != null)
{
int colCount = headRow.LastCellNum;
for (int i = ; i < colCount; i++)
{
dt.Columns.Add("COL_" + i);
}
} for (int i = (sheet.FirstRowNum + rowIndex); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
bool emptyRow = true;
object[] itemArray = null; if (row != null)
{
itemArray = new object[row.LastCellNum]; for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{ if (row.GetCell(j) != null)
{ switch (row.GetCell(j).CellType)
{
case CellType.NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
{
itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else//其他数字类型
{
itemArray[j] = row.GetCell(j).NumericCellValue;
}
break;
case CellType.BLANK:
itemArray[j] = string.Empty;
break;
case CellType.FORMULA:
if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx")
{
XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook);
if (eva.Evaluate(row.GetCell(j)).CellType == CellType.NUMERIC)
{
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
{
itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else//其他数字类型
{
itemArray[j] = row.GetCell(j).NumericCellValue;
}
}
else
{
itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
}
}
else
{
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook);
if (eva.Evaluate(row.GetCell(j)).CellType == CellType.NUMERIC)
{
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
{
itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else//其他数字类型
{
itemArray[j] = row.GetCell(j).NumericCellValue;
}
}
else
{
itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
}
}
break;
default:
itemArray[j] = row.GetCell(j).StringCellValue;
break; } if (itemArray[j] != null && !string.IsNullOrEmpty(itemArray[j].ToString().Trim()))
{
emptyRow = false;
}
}
}
} //非空数据行数据添加到DataTable
if (!emptyRow)
{
dt.Rows.Add(itemArray);
}
}
return dt;
}
测试的公式有TODAY()、IF、VLOOKUP。