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

GoodTime

发布于 2024-03-05 16:06:13

1210

发布于 2024-03-05 16:06:13

文章被收录于专栏：GoodTime的全栈开发GoodTime的全栈开发

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 "请上传文件";
}