poi大数据内存导出Excel表格

1.controller层代码

long t1=System.currentTimeMillis();
        List<Map<String, Object>> list = registrationInformationService.getListMapGSXXInfoExport(param, model);
        long t2=System.currentTimeMillis();
        System.out.println("查询数据用时："+(t2-t1)+"ms");
        String[] sortKey=new String[]{"字段A","字段B"};
        ExportXlsxUtil exportXlsxUtil =new ExportXlsxUtil();
        exportXlsxUtil.setFilename("某某表.xlsx");
        exportXlsxUtil.write(absolutePath+"/某某表..xlsx",list,3, sortKey);
        long t3=System.currentTimeMillis();
        System.out.println("数据行："+list.size()+"数据列"+list.get(0).size());
        System.out.println("写入execl用时："+(t3-t2)+"ms");
        System.out.println("总用时："+t3+"ms");
        System.out.println("success");

2.ExportXlsxUtil代码

package com.wpkj.common.util;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;

public class ExportXlsxUtil {
    

    private String filename;

    public String getFilename() {
        return filename;
    }
    
    public void setFilename(String filename) {
        this.filename = filename;
    }
    
    /**
     *借助临时存储空间生成海量excel数据
     */
    public void write(String filePath, List<Map<String, Object>> list,Integer startRow,String[] sortKey) throws Exception {
        HttpServletResponse  resp= ServletActionContext.getResponse();
        List<List<String>> data=sortListMap(list,sortKey);
        XSSFWorkbook wb1=new XSSFWorkbook(new FileInputStream(new File(filePath)));
        SXSSFWorkbook wb = new SXSSFWorkbook(wb1,-1);
        Sheet sh = wb.getSheetAt(0);
        for(int rownum = startRow; rownum < data.size(); rownum++){
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < data.get(rownum).size(); cellnum++){
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(data.get(rownum).get(cellnum));
            }
            if(rownum % 100 == 0) {
                ((SXSSFSheet)sh).flushRows(100);
            }
        }

//        FileOutputStream out = new FileOutputStream(filePath);
//        wb.write(out);
//        out.close();
//        wb.dispose();
        exportEx(wb, resp);
    }
    

    /**
     * ListMap根据
     * @param data
     */
    private List<List<String>> sortListMap(List<Map<String, Object>> datas,String[] sortKey){
        List<List<String>> _datas=new ArrayList<List<String>>();
        List<String> temp=null;
        for (int i = 0; i < datas.size(); i++) {
            temp=new ArrayList<String>();
            if (datas.get(i).getClass() == HashMap.class) {
                Map<String, Object> map=(Map<String, Object>) datas.get(i);
                for (String sort : sortKey) {
                    if (map.get(sort)!=null && !"".equals(map.get(sort).toString())) {
                        temp.add(map.get(sort).toString());
                    }else{
                        temp.add("");
                    }
                }
                _datas.add(temp);
            }
        }
        return _datas;
    }
    
    /**
     * 导出到excel
     * @param args
     * @throws UnsupportedEncodingException 
     */
    private void exportEx(SXSSFWorkbook wb,HttpServletResponse  resp) throws UnsupportedEncodingException{
        resp.setHeader("content-disposition", "attachment;filename="+new String(filename.getBytes("utf-8"), "iso-8859-1"));
        resp.setContentType("application/msexcel;charset=UTF-8");
        BufferedOutputStream write;
        
        try {
            ServletOutputStream outputStream = resp.getOutputStream();
            write = new BufferedOutputStream(resp.getOutputStream());
            wb.write(write);
            write.flush();
            write.close();
        }  catch (IOException e) {
            e.printStackTrace();
        }
    }

}

这样就可以大数据导出来。不会引起jvm内存溢出的情况。