当前项目中使用最多的几乎是下面的工具生成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的导出
- 先添加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文件会比较耗时,我们可以细化步骤,将进度值写进缓存,然后前端异步调用+轮询可以实时读取进度。