避免OOM的高效Excel处理 MiniExcel

当前项目中使用最多的几乎是下面的工具生成Excel:

HSSFWorkbook：一般用于操作Excel2003以前（包括2003）的版本，扩展名是.xls。
XSSFWorkbook：一般用于操作Excel2007及以上的版本，扩展名是.xlsx
SXSSFWorkbook（POI 3.8+版本）：一般用于大数据量的导出。

第一种：HSSFWorkbook

针对EXCEL2003版本，扩展名为.xls，此种的局限就是导出的行数最多为65535行、256列。一般不会出现内存溢出(OOM)的情况

第二种：XSSFWorkbook

针对EXCEL2007+，扩展名为.xlsx ，最多可以导出1048576行,16384列，不过这样就伴随着一个问题–OOM内存溢出。

第三种:SXSSFWorkbook

SXSSFWorkbook可以根据行数将内存中的数据持久化写到文件中，以此逐步写入，避免OOM。

针对于现在项目需求，采用了MiniExcel进行Excel的导出

1. 先添加MiniExcel的引用
   

2.查询数据并调用MiniExcel生成文件

	public class ExcelConstants
    {
        // 一个sheet装100w数据
        public const int PerSheetRowCount = 1000000;
        // 每次查询20w数据，每次写入20w数据
        public const int PerWriteRowCount = 200000;
    }
    
 	public class ExportModel
    {
    
    	/*
    	引用MiniExcel  在生成Excel时会自动引用这里的描述作为列头
    	*/
        [ExcelColumnName("导出列1")]
        public string Column1{ get; set; }

        [ExcelColumnName("导出列2")]
        public string Column2{ get; set; }

        [ExcelColumnName("导出列3")]
        public string Column3{ get; set; }


    }

/// <summary>
/// 
/// </summary>
 public string ExportXeniosReportData()
        {
            try
            {
                /*To Do
                获取查询数据的总数代码
               	*/ 
                // 查询数据总数:实际中需要根据查询条件进行统计
                int totalCount = 1000000;
                // 每一个Sheet存放100w条数据
                int sheetDataRows = ExcelConstants.PerSheetRowCount;
                // 每次写入的数据量20w,每页查询20W
                int writeDataRows = ExcelConstants.PerWriteRowCount;
                // 计算需要的Sheet数量
                int sheetNum = (int)Math.Ceiling(totalCount / (double)sheetDataRows);
                // 计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
                int oneSheetWriteCount = (int)Math.Ceiling(sheetDataRows / (double)writeDataRows);
                // 计算最后一个sheet需要写入的次数
                int lastSheetWriteCount = (int)Math.Ceiling(totalCount % sheetDataRows / (double)writeDataRows);
                if (lastSheetWriteCount == 0)
                {
                    lastSheetWriteCount = oneSheetWriteCount;
                }

                var filepath = AppDomain.CurrentDomain.BaseDirectory + "自定义文件路径";
                if (!Directory.Exists(filepath))
                {
                    Directory.CreateDirectory(filepath);
                }
                string fileName = "自定义文件名称";
                string excelfilepath = $"{filepath}\\{fileName}.xlsx";

                var sheets = new Dictionary<string, object>();
                if (totalCount == 0)
                {
                    MiniExcel.SaveAs(excelfilepath, null, true, "data", ExcelType.XLSX);
                    _cacheManager.SetCache(cachekey, 100, 1);
                    return null;
                }
                
                // 开始分批查询分次写入
                for (int i = 0; i < sheetNum; i++)
                {
                    var onesheetdata = new List<ExportModel>();
                    // 循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                    for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++)
                    {
                    
                        /* To Do
                        分页查询数据赋值 data
                        */
                        onesheetdata.AddRange(data);
                    }
                    sheets.Add($"Sheet{i + 1}", onesheetdata);
                }
                //生成EXCEL
                var config = new OpenXmlConfiguration()
                {
                    TableStyles = TableStyles.Default
                };
                //调用MiniExcel生成文件
                var task = MiniExcel.SaveAsAsync(excelfilepath, sheets, configuration: config);
                return excelfilepath;
            }
            catch (IOException e)
            {
                throw e;
            }
        }

注意

在使用MiniExcel时我们会发现它的MiniExcel.Insert()方法.该方法只支持.csv格式的文件进行操作。
我自己在操作时，发现调用Save() Insert() 时，虽然给了SheetName以及PrintHeader 并没有效果。

if (文件不存在)
{
     MiniExcel.SaveAs(csvfilepath, data, true);

}
else
{
     MiniExcel.Insert(csvfilepath, data);
}

补充
对于上面的生成Excel文件会比较耗时，我们可以细化步骤，将进度值写进缓存，然后前端异步调用+轮询可以实时读取进度。