.NET NPOI导出时间、公式等格式化

1、业务背景

做导入某业务模块的Excel表格文件时，利用NPOI组件导入，

① 导入的日期错乱（如XX-X月-2022），关于此种情况之前没做格式化做了单独处理，可以查看文章.net NPOI Excel导入：时间格式2022/5/26导入变成26-5月-2022，做了格式化就快捷方便多了

② Excel表中某列通过公式计算好的列导入后获取到的是公式（如D1*E1），不是具体的值，

此时就需要在工作表生成DataTable之前做格式化处理操作，下面直接上代码

2、解决方案

1）写一个判断单元格列类型格式化的公共方法

/// <summary>
/// 判断单元格列的类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static object GetValueType(ICell cell)
{
    bool flag = cell == null;
    object result = null;
    if(flag) result = null;
    switch(cell.CellType)
    {
        case CellType.Boolean:
            result = cell.BooleanCellValue;
            break;
        case CellType.Error:
            result = cell.ErrorCellValue;
            break;
        case CellType.Formula:
            switch(cell.CachedFormulaResultType)
            {
                case CellType.Boolean:
                    result = cell.BooleanCellValue;
                    break;
                case CellType.Error:
                    result = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                case CellType.Numeric:
                    if(DateUtil.IsCellDateFormatted(cell))
                    {
                        result = cell.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                    }
                    else
                    {
                        result = cell.NumericCellValue;
                    }
                    break;
                case CellType.String:
                    string str = cell.StringCellValue;
                    if(!string.IsNullOrEmpty(str))
                    {
                        result = str.ToString();
                    }
                    else
                    {
                        result = null;
                    }
                    break;
                case CellType.Unknown:
                case CellType.Blank:
                default:
                    result = string.Empty;
                    break;
            }
            break;
        case CellType.Numeric:
            if(DateUtil.IsCellDateFormatted(cell))
            {
                result = cell.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
            }
            else
            {
                result = cell.NumericCellValue;
            }
            break;
        case CellType.String:
            string strValue = cell.StringCellValue;
            if(!string.IsNullOrEmpty(strValue))
            {
                result = strValue.ToString();
            }
            else
            {
                result = null;
            }
            break;
        case CellType.Unknown:
        case CellType.Blank:
        default:
            result = string.Empty;
            break;
    }
    return result;
}

2）在工作表生成DataTable时调用上述的公共方法

/// <summary>
/// 从工作表中生成DataTable
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRowIndex"></param>
/// <returns></returns>
public static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
{
    DataTable table = new DataTable();
    IRow headerRow = sheet.GetRow(headerRowIndex);
    int cellCount = headerRow.LastCellNum;
    for(int i = headerRow.FirstCellNum; i < cellCount; i++)
    {
        if(headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
        {
            // 如果遇到第一个空列，则不再继续向后读取
            cellCount = i;
            break;
        }
        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
        table.Columns.Add(column);
    }
    for(int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
    {
        IRow row = sheet.GetRow(i);
        //如果遇到某行的第一个单元格的值为空，则不再继续向下读取
        if(row != null && row.GetCell(0) != null && !string.IsNullOrEmpty(row.GetCell(0).ToString()))
        {
            DataRow dataRow = table.NewRow();
            for(int j = row.FirstCellNum; j < cellCount; j++)
            {
                ICell cell = row.GetCell(j);
                object valueType = GetValueType(cell);
                if(valueType == null || valueType.ToString() == string.Empty)
                {
                    dataRow[j] = string.Empty;
                }
                else
                {
                    dataRow[j] = valueType.ToString();
                }
            }
            table.Rows.Add(dataRow);
        }
    }
    return table;
}

3）在Excel导入方法中调用工作表生成DataTable

/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
    IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
    ISheet sheet = null;
    int sheetIndex = -1;
    if(int.TryParse(sheetName, out sheetIndex))
    {
        sheet = workbook.GetSheetAt(sheetIndex);
    }
    else
    {
        sheet = workbook.GetSheet(sheetName);
    }
    DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
    excelFileStream.Close();
    workbook = null;
    sheet = null;
    return table;
}

4）文件处理

// 文件处理核心代码
if(files != null && files.Files.Count > 0)
{
    try
    {
        var file = files.Files[0];
        if(file == null)
        {
            return "文件不能为空";
        }
        var fileName = file.FileName;
        // 判断文件大小
        if(file.Length > 10 * 1024 * 1024)
        {
            return "文件大小不能大于10M";
        }
        string fileExtension = file.FileName.Substring(file.FileName.LastIndexOf(".")); // 获取文件名称后缀 
        bool GetIsCompatible = new []
        {
            ".xls", ".xlt"
        }.Count(e => e.Equals(fileExtension, StringComparison.OrdinalIgnoreCase)) > 0;
        // 判断文件格式
        if(!(fileExtension.ToLower() == ".xls" || fileExtension.ToLower() == ".xlsx"))
        {
            return "文件扩展名应该是.xls或.xlsx";
        }
        var stream = file.OpenReadStream();
        var dt = ExcelInHelper.ToDataTable(stream, "Sheet1", 0, GetIsCompatible);
        if(dt != null && dt.Rows.Count > 0)
        {
            // 处理数据
            // ......
        }
    }
    catch(Exception ex)
    {
        return $ "读取文件发生错误，错误原因：{ex.Message}";
    }
}
else
{
    return "请上传文件";
}

3、总结

通过格式化处理，Excel工作表中的各种类型数据都可以提前格式化成我们导入需要的数据形式，当然上面的格式化方法也只是列出了部分类型，还有很多类型欢迎补充，以此本文简单快速的解决了关于NPOI导入的格式化问题。

若本文有帮助到阅读本文的同学，欢迎点赞、关注、收藏，互相学习交流。