JAVA实现大数据量导出excel

JAVA 实现大数据量导出操作时，如果采用POI直接导出，会出现内存溢出的情况。再者EXCEL的SHEET也存在行数的限制，Excel2003版最大行数是655536行、Excel2007版最大行数是1048576行、Excel2010版最大行数是1048576行。

有人说，可以通过分Sheet存放数据啊，可即便分多个sheet也不能解决内容溢出的问题，数据仍然是在一次请求的内存中。

 

解决方案：

1.      先统计总的记录数，如果大于一定数目则采用压缩文件方式导出

2.      采用分页方式从数据库中获取数据，并生成多个Excel临时文件

3.      将临时文件Excel压缩导出

4.      删除服务器上的临时文件

代码案例：

   /** * 文件导出 * @param params * @param response * @throws Exception */public void excelDownLoad(Map params, HttpServletResponse response)throws Exception {int allRowNumbers = 0;int rowMaxCount = 60000;//查询记录数allRowNumbers = appDao.queryCount("testCommonInfo.infoMangerForAllCount", params); //是否大数据量（超过6W）if(allRowNumbers > rowMaxCount){            List list = new ArrayList();    List downlist = new ArrayList();//1.设置相应头String filename = "导出TEST.zip";filename = new String(filename.getBytes("GBK"), "iso-8859-1");response.reset();response.setContentType("application/octet-stream;charset=UTF-8");response.setHeader("Content-Disposition", "attachment;filename="+filename);response.addHeader("pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");//2.设置批次文件名String fileSuff = "";SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");fileSuff = sdf.format(new Date());String fileName = "导出TEST"+fileSuff;List<String> fileNames = new ArrayList<String>();  //存放生成的文件名称String filePath = "D:/excel/";      //上线后切换成linux服务器地址if(!new File(filePath).exists()){new File(filePath).mkdirs();}File zip = new File(filePath+fileName+".zip");  //压缩文件路径//3.分批次生成excelint  tempsize = (allRowNumbers%rowMaxCount)==0?allRowNumbers/rowMaxCount:allRowNumbers/rowMaxCount+1;for (int i = 0; i < tempsize; i++) {if(i == (allRowNumbers/rowMaxCount)){params.put("startNum", i*rowMaxCount);params.put("endNum", allRowNumbers);}else{params.put("startNum", i*rowMaxCount);params.put("endNum", (i+1)*rowMaxCount);}list = appDao.queryCount("testCommonInfo.commonInfoForPC", params); //3.2生成excelString tempExcelFile = filePath+fileName+"["+(i+1)+"].xlsx"; fileNames.add(tempExcelFile);FileOutputStream fos = new FileOutputStream(tempExcelFile);int rowMemory = 100;SXSSFWorkbook wb = new SXSSFWorkbook(rowMemory);try{wb = exportDataToExcelXLSX(wb, downlist);wb.write(fos);fos.flush();fos.close();}catch(RuntimeException runMsg){throw new Exception("查询数据信息异常 ");}finally{fos.flush();fos.close();//手动清除listlist.clear();downlist.clear();} }//4.导出zip压缩文件exportZip(response, fileNames, zip); }else{……     }}   /** * 文件压缩并导出到客户端 * @param outPut * @param fileNames * @param zip * @throws FileNotFoundException * @throws IOException */private void exportZip(HttpServletResponse response, List<String> fileNames, File zip)throws FileNotFoundException, IOException {OutputStream  outPut = response.getOutputStream();//1.压缩文件File srcFile[] = new File[fileNames.size()];for (int i = 0; i < fileNames.size(); i++) {srcFile[i] = new File(fileNames.get(i));}byte[] byt = new byte[1024];ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zip));out.setEncoding("UTF-8");for (int i = 0; i < srcFile.length; i++) {FileInputStream in = new FileInputStream(srcFile[i]);out.putNextEntry(new ZipEntry(srcFile[i].getName()));int length;while((length=in.read(byt)) > 0){out.write(byt,0,length);}out.closeEntry();in.close();}out.close();//2.删除服务器上的临时文件(excel)for (int i = 0; i < srcFile.length; i++) {File temFile = srcFile[i];if(temFile.exists() && temFile.isFile()){temFile.delete();}}//3.返回客户端压缩文件FileInputStream inStream = new FileInputStream(zip);byte[] buf = new byte[4096];int readLenght;while((readLenght = inStream.read(buf)) != -1 ){outPut.write(buf,0,readLenght);}inStream.close();outPut.close();//4.删除压缩文件if(zip.exists() && zip.isFile()){zip.delete();}}   /** * 设置excel样式和数值 * @param wb * @param listMap * @param companyatr * @return */private static SXSSFWorkbook exportDataToExcelXLSX(SXSSFWorkbook wb, List<Map> listMap){String[] assetHeadTemp = {"系列","代码","名称","凭证号","状态","时间","数量"};String[] assetNameTemp = {"TYPE","CODE","NAME","BILLNO","STATUS","TIME","NUM"};Sheet sheet = null;CellStyle columnHeadStyle = wb.createCellStyle();columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框columnHeadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框columnHeadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框columnHeadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);columnHeadStyle.setWrapText(true);Font f = wb.createFont();// 字体f.setFontHeightInPoints((short) 9);// 字号f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//BOLDWEIGHT_BOLD);// 加粗columnHeadStyle.setFont(f);Row row;Cell cell;sheet = wb.createSheet("sheet");row = sheet.createRow(0);sheet.createFreezePane(0, 1, 0, 1);for(int i=0;i<assetHeadTemp.length;i++){cell = row.createCell(i);cell.setCellStyle(columnHeadStyle);cell.setCellValue(assetHeadTemp[i]);sheet.setColumnWidth(i, (int)7000);}if(listMap != null && listMap.size() > 0){int rowIndex = 1;for(Map map : listMap){row = sheet.createRow(rowIndex++);int index = 0;for(int i=0;i<assetNameTemp.length;i++ ){    cell = row.createCell(index++);cell.setCellValue(map.get(assetNameTemp[i])!=null ?map.get(assetNameTemp[i]).toString():"");}}}return wb;}