NPOI 正确的使用姿势
主要是需要注意公式和日期类型的单元格的读取。
/// <summary>
/// 打开指定 Excel 文件
/// </summary>
/// <param name="fileName">指定要打开的文件名</param>
/// <returns>Excel 文件对应的单元格</returns>
internal ExcelDataGrid Open(string fileName)
{
if (string.IsNullOrWhiteSpace(fileName))
{
throw new ArgumentNullException(nameof(fileName));
}
var sourceFs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
var extensions = new FileInfo(fileName).Extension;
IWorkbook workbook = null;
IFormulaEvaluator evaluator = null;
if (extensions.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
{
workbook = new XSSFWorkbook(sourceFs);
evaluator = new XSSFFormulaEvaluator(workbook);
}
else if (extensions.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
{
workbook = new HSSFWorkbook(sourceFs);
evaluator = new HSSFFormulaEvaluator(workbook);
}
else
{
throw new ArgumentException("不支持的文件扩展名");
}
var sheet = workbook.GetSheetAt(0);
var result = new ExcelDataGrid();
var rowIndex = -1;
var columnIndex = -1;
foreach (var row in GetRows(sheet))
{
rowIndex++;
columnIndex = -1;
foreach (var column in row.Cells)
{
columnIndex++;
result[rowIndex, columnIndex] = GetCellValue(evaluator, column);
}
}
sourceFs.Close();
workbook.Close();
return result;
}
/// <summary>
/// 获取<see cref="ISheet"/>中的行
/// </summary>
/// <param name="sheet">指定获取的<see cref="ISheet"/>实例</param>
/// <returns><see cref="ISheet"/>中的行</returns>
private IEnumerable<IRow> GetRows(ISheet sheet)
{
if (sheet == null)
{
throw new ArgumentNullException(nameof(sheet));
}
var enumerator = sheet.GetEnumerator();
while (enumerator.MoveNext())
{
yield return enumerator.Current as IRow;
}
}
/// <summary>
/// 获取单元格的显示值
/// </summary>
/// <param name="evaluator">单元格公式计算器</param>
/// <param name="cell">单元格</param>
/// <returns>单元格显示的值</returns>
private string GetCellValue(IFormulaEvaluator evaluator, ICell cell)
{
switch (cell.CellType)
{
case CellType.Blank:
return string.Empty;
case CellType.Boolean:
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Formula:
cell = evaluator.EvaluateInCell(cell);
return GetCellValue(evaluator, cell);
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
return cell.DateCellValue.ToString();
else
return cell.NumericCellValue.ToString();
case CellType.String:
return cell.StringCellValue;
case CellType.Unknown:
return "Unknow";
default:
return "";
}
}