Excel文件导出前后台整体逻辑代码
//导出excel -js代码 $scope.exportToExcel=function(s,e){ // ex: '#my-table' if((s === "" || s === undefined) && (e ==="" || s === undefined)){ $scope.submitted = true; }else { var postData = { parm1:val01, parm2:val02, parm3:val03 } $http({ url:'/ncr/excel/export', method: 'POST', responseType: 'arraybuffer', data: JSON.stringify(postData), headers: { 'Content-type': 'application/json;charset=UTF-8' } }).success(function(data){ if(data.byteLength ==0){ vm.text = "导出条件不满足！"; $scope.ngTip = ngTip; ngTip.tip(vm.text); }else{ var blob = new Blob([data], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}); var filename="Excel清单_" + formatDate(new Date()) + ".xlsx"; if (window.navigator.msSaveOrOpenBlob) {// For IE blowser navigator.msSaveBlob(blob, filename); }else{//For other blowser var objectUrl = URL.createObjectURL(blob); var a = document.createElement('a'); document.body.appendChild(a); a.setAttribute('style', 'display:none'); a.setAttribute('href', objectUrl); a.setAttribute('download', filename); a.click(); URL.revokeObjectURL(objectUrl); } } }).error(function(){ alert("error"); }); } } //日期格式化YYYY-MM-DD function formatDate(d) { var D=['00','01','02','03','04','05','06','07','08','09']; with (d || new Date) return [ [getFullYear(), D[getMonth()+1]||getMonth()+1, D[getDate()]||getDate()].join('-') //,[D[getHours()]||getHours(), D[getMinutes()]||getMinutes(), D[getSeconds()]||getSeconds()].join(':') ].join(' '); }
//列名 private static String columnNames[]={"客户姓名","手机号码","性别","生日"]; //map中的key private static String keys[] = {"custName","mobile","gender","birthDay"]; @PostMapping("/export") public Result<?> exportCustIntroduce(@RequestBody Map<String, Object> map,HttpServletResponse response) throws IOException{ //查询需要导出的数据 List<TestVO> introdList = 从数据库查询出的list; String exportFile = "Excel清单"+(new SimpleDateFormat("YYYY-MM-DD")).format(new Date());//导出文件名 List<Map<String,Object>> list=null; list=createExcelRecord(introdList); ByteArrayOutputStream os = new ByteArrayOutputStream(); UtilTools.createWorkBook(list,keys,columnNames,"Excel清单").write(os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename="+ new String((exportFile + ".xlsx").getBytes(), "iso-8859-1")); ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (IOException e) { throw e; } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } return ResultUtil.sucess(ResultEnum.SUCCESS); } //将记录列表放入Map列表中 private List<Map<String, Object>> createExcelRecord(List<TestVO> testList) { List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>(); TestVO testVo=null; for (int j = 0; j < testList.size(); j++) { testVo=testList.get(j); Map<String, Object> mapValue = new HashMap<String, Object>(); mapValue.put(keys[0], testVo.getName());//姓名 mapValue.put(keys[1], testVo.getMobile());//手机 mapValue.put(keys[2], testVo.getGender());//性别 mapValue.put(keys[3], UtilTool.DATE.dateToString(testVo.getBirthday(), "yyyy-MM-dd"));//生日 listmap.add(mapValue); } return listmap; } /** * * @Title: createWorkBook * @Description: 创建Excel * @param list 数据列表 * @param keys Map列表中的key * @param columnNames Excel的标题 * @param sheetEx Sheet名的前缀 * @return * @return Workbook 返回类型 * @throws */ public static Workbook createWorkBook(List<Map<String, Object>> list,String keys[],String[] columnNames,String sheetEx) { // 创建excel工作簿 Workbook wb = new XSSFWorkbook(); //数据总数 int listSize = list.size(); int cntPerSheet = 65000;//每个sheet数据量 int SheetCnt =0; if(listSize !=0){ SheetCnt = listSize/cntPerSheet;//sheet个数 if(listSize%cntPerSheet!=0){ SheetCnt = SheetCnt+1; } }else{ SheetCnt =1; } short num = 0;//List计数器 for(int s=0;s<SheetCnt;s++){ Sheet sheet = wb.createSheet(sheetEx+"-"+s);//创建sheet // 创建第一个sheet（页），并命名 // Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。 for(int i=0;i<keys.length;i++){ sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // 创建第一行 Row row = sheet.createRow((short) 0); // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont(); // 创建第一种字体样式（用于列名） f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBold(true); // 创建第二种字体样式（用于值） f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // 设置第一种单元格的样式（用于列名） cs.setFont(f); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置第二种单元格的样式（用于值） cs2.setFont(f2); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置列名（标题） for(int i=0;i<columnNames.length;i++){ Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //设置每行每列的值（数据） for (int r=1; num < list.size(); r++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行，在页sheet上 Row row1 = sheet.createRow((short) r); // 在row行上创建一个方格 for(short j=0;j<keys.length;j++){ Cell cell = row1.createCell(j); cell.setCellValue(list.get(num).get(keys[j]) == null?" ": list.get(num).get(keys[j]).toString()); cell.setCellStyle(cs2); } if((num+1) % cntPerSheet==0){//当每个sheet读满cntPerSheet条数据时换sheet; num++; break; } num++; } } return wb; }
备注：基于angularjs1.0、spring-boot、maven环境
jar包-poi-3.13.jar、poi-ooxml-3.13.jar、poi-ooxml-schemas-3.13.jar
pom:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version> </dependency>
问题：针对以上的Excel文件导出，最近用户反映导出的数据比较慢，有时候甚至导不出来数据。
问题调查：通过查看后台日志发现在导出数据的时候，如果数据量比较大则会出现了内存溢出；本地代码debug也发现sql数据查询效率还可以，在生成Excel时速度很慢。
解决方案：修改后台代码Workbook wb = new XSSFWorkbook(); ==》 SXSSFWorkbook wb = new SXSSFWorkbook(100); 就可以解决该问题。
后记分析：HSSFworkbook,XSSFworkbook,SXSSFworkbook区别 -
（摘自：https://blog.csdn.net/qq_34869143/article/details/76512289）
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls； XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx； 对于不同版本的EXCEL文档要使用不同的工具类，如果使用错了，会提示如下错误信息。 org.apache.poi.openxml4j.exceptions.InvalidOperationException org.apache.poi.poifs.filesystem.OfficeXmlFileException 从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的API----SXSSF 当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook 注意点： 针对 SXSSF Beta 3.8下，会有临时文件产生，比如： poi-sxssf-sheet4654655121378979321.xml 文件位置：java.io.tmpdir这个环境变量下的位置 Windows 7下是C:\Users\xxxxxAppData\Local\Temp Linux下是 /var/tmp/ 要根据实际情况，看是否删除这些临时文件 与XSSF的对比 在一个时间点上，只可以访问一定数量的数据 不再支持Sheet.clone() 不再支持公式的求值 在使用Excel模板下载数据时将不能动态改变表头，因为这种方式已经提前把excel写到硬盘的了就不能再改了
