一、场景复现
碰到了导出大量数据的需求场景:从数据读取数据大约50W,然后再前端导出给用户,整个过程希望能较快的完成。如果不能较快完成,可以给与友好的提示。
大量数据的导出耗时的主要地方:
1、从数据库获取大量数据。如果一般百万级别左右的,走索引的查询,一般5秒左右可以把数据查出来。
2、把查出来的数据,通过NPOI组装成excel。这个过程一般耗时,且消耗资源,很容易出现OOM。
二、代码案例
1、普调的写了一个基于NPOI用户模式,关于XLSX格式的导出。
这里有一个坑点就是: wb.Write(ms);写完之后会关闭,所以一般扩展一个自定义流来代替内存流来实现。
/// <summary> /// 导出Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list">导入的数据源</param> /// <param name="filename">文件名,不含扩展名</param> /// <param name="resources">多语言资源文件</param> /// <returns></returns> public static byte[] Export<T>(List<T> list, string filename) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet(filename); SetColumnTitle<T>(sheet, list[0]); int index = 1; foreach (var model in list) { Type type = model.GetType(); var properties = type.GetProperties(); IRow row = sheet.CreateRow(index++); int j = 0; for (int i = 0; i < properties.Length; i++) { var p = properties[i]; object obj = p.GetValue(model, null); if (obj != null) { row.CreateCell(i).SetCellValue(obj.ToString()); } } } byte[] buffer; using (NpoiMemoryStream ms = new NpoiMemoryStream()) { ms.AllowClose = false; wb.Write(ms); ms.Flush(); buffer = new byte[ms.Length]; ms.Position = 0; ms.Read(buffer, 0, buffer.Length); ms.AllowClose = true; } return buffer; } /// <summary> /// 设置列标题 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sheet"></param> /// <param name="t"></param> private static void SetColumnTitle<T>(ISheet sheet, T t) { Type type = t.GetType(); var properties = type.GetProperties(); IRow row = sheet.CreateRow(0); int j = 0; for (int i = 0; i < properties.Length; i++) { var p = properties[i]; row.CreateCell(j).SetCellValue(p.Name); sheet.SetColumnWidth(j, 5000); j++; } }
用上面这种方法导出数据,很有可能导致OOM,因为在你点导出的时候,占用的内存和CPU都很高。
所以特地去查POI的文档,他们提供了一个流式的XSSFWorkbook版本,这种允许写入非常大的文件而不耗尽内存,因为在任何时候,只有可配置的行部分被保存在内存中,并且还可以自己定义导出的数据的模板。
用XSSFWorkbook 就不要做太多的Excel式的操作,比如合并区域等等,因为这些操作还是在内存中搞。
那如何使用XSSFWorkbook呢。
这个1000的意思是:内存中只放1000行记录,如果超过1000行,就把数据写到磁盘中去,这样就避免内存溢出了。
IWorkbook wb = new SXSSFWorkbook(1000);
上面的代码,需要使用IWorkbook wb = new SXSSFWorkbook(1000); 只需要改改这个就好了。
我前面的这种做法是,先从数据库里面查出来全部数据,然后丢到内存里面,只是生成excel的时候,按照1000行,搞完就放到磁盘中去。
如果你的内存实在是有限,你可以按照下面的这种方式,边分页去查,边生成excel,这个过程不一定会比上面快,因为你多次分页,就要去数据库查多次,这些耗时也要考虑在里面。
下面这个是分页的使用方法【不一定效率好,根据具体情况来使用】
/// <summary> /// 导出Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list">导入的数据源</param> /// <param name="filename">文件名,不含扩展名</param> /// <param name="resources">多语言资源文件</param> /// <returns></returns> public static byte[] ExportStreamAsPage(string filename,int pageSize) { IWorkbook wb = new SXSSFWorkbook(pageSize); ISheet sheet = wb.CreateSheet(filename); ItsmProvider itsmProvider= new ItsmProvider(); //设置标题 SetColumnTitle<ITSM_MAIN_INFO>(sheet,new ITSM_MAIN_INFO()); var type=new ITSM_MAIN_INFO().GetType(); int pageIndex = 1; Boolean hasNext = true; //记录循环次数 while (hasNext) { var itsms=itsmProvider.GetItsmsByPage(pageIndex,pageSize); setRowContent<ITSM_MAIN_INFO>(type, sheet, pageIndex, pageSize, itsms); //不包含任何数据的时候,就退出 if (!itsms.Any()) { break; } //说明已经到了最后一页。 if(itsms.Count()<pageSize) { hasNext = false; } pageIndex++; } byte[] buffer; using (NpoiMemoryStream ms = new NpoiMemoryStream()) { ms.AllowClose = false; wb.Write(ms); ms.Flush(); buffer = new byte[ms.Length]; ms.Position = 0; ms.Read(buffer, 0, buffer.Length); ms.AllowClose = true; } return buffer; }
三、总结
大数据量导出防止OOM方法就是:SXSSFWorkbook