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.分批次生成excel
            int  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生成excel
                String 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();
                    //手动清除list
                    list.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;
    }