POI分sheet导出Excel或者zip通用高速方法(万条数据以上)

工作需要写了一个POI的封装类,接受的数据类型是List

 <settings>
        <!-- 当返回值为LinkedHashMap时，表中存储的null值并不会存入Map中，因此还要在mybatis配置 -->
        <setting name="callSettersOnNulls" value="true"/>
    </settings>

xml中写法
接受的数据类型是List<HashMap<String,Object>>,查询的字段顺序决定了列排列的顺序,如果不传入列名,使用List<HashMap<String,Object>>去取得列名,然后生成,

 <select id="getexceldata" resultType="java.util.LinkedHashMap">
      SELECT 
       ReportMonth, ExpenseNumber, Type, TotalActualExpenseAmount, RequesterNameCN, 
       RequesterEmployeeCode, Dept, Area, DeptConfirm, AreaConfirm, StartDate, EndDate, 
       ApprovalDate, AmountMonth, ApprovalNameCN, AccountantDate, AccountantDecision, AccountantNameCN, 
       InvoinceDate, Comments1, SysCreateDate, SysUpdateDate, OriginalId, BigBU
       FROM t_ermonthlyreport
  </select>

/**
 * POI导出方法类
 */
public class POIExcelExport {

    /**
     * 写入数据通用方法(导出多个sheet)
     * @param workbook
     * @param out        : OutputStream对象
     * @param limitNum   : 超过limitNum生成新sheet
     * @param sheetTitle : 设置excel的sheet标题
     * @param headers    : 设置excel的列标题
     * @param columnsize : 设置excel的列宽度
     * @param data       : List<HashMap<String,Object>> 数据
     * @throws IOException
     */
    public void setExcelData(HSSFWorkbook workbook,OutputStream out,int limitNum,String sheetTitle,
            String[] headers,int [] columnsize,List<LinkedHashMap<String,Object>> data) throws IOException{
        int sheetNum = 0;
        HSSFSheet sheet =null;
        HSSFRow row = null; 
        //设置标题样式和单元格样式 
        HSSFCellStyle titlestyle = createTitleCellStyle(workbook);
        HSSFCellStyle bodystyle = createBodyCellStyle(workbook);
        int length = columnsize.length;
        //遍历集合数据,产生数据行
        if(data!=null){
        int rowindex = 0;
            for(HashMap<String,Object> entry:data){
                if (rowindex % limitNum == 0){  
                    if(rowindex != 0 ){
                        sheetNum++;
                    }
                    sheet = workbook.createSheet(sheetTitle+sheetNum);
                    //设置sheet的列宽
                    for(int i=0;i<length;i++){
                        sheet.setColumnWidth(i, (short)columnsize[i]*256);
                    }
                    row = sheet.createRow(0);
                    createHeaders(headers,titlestyle,row);
                    rowindex = 1;
                }
                row = sheet.createRow(rowindex);
                int cellIndex = 0;
                 for (String key : headers){
                     HSSFCell cell = row.createCell(cellIndex);
                        cell.setCellStyle(bodystyle);
                        Object str = entry.get(key);
                        //处理空数据
                        if(str!=null ){
                            cell.setCellValue(str.toString());
                        }else{
                            cell.setCellValue(" ");
                        }
                        cellIndex++;
                   }
                rowindex++;
            }
        }
    }


    /**
     * 写入数据通用方法(导出多个sheet)重载
     * headers 自己从HashMap中取
     * @param workbook
     * @param out        : OutputStream对象
     * @param limitNum   : 超过limitNum生成新sheet
     * @param sheetTitle : 设置excel的sheet标题
     * @param columnsize : 设置excel的列宽度
     * @param data       : List<HashMap<String,Object>> 数据
     * @throws IOException
     */
    public void setExcelData(HSSFWorkbook workbook,OutputStream out,int limitNum,String sheetTitle,
            int [] columnsize,List<LinkedHashMap<String,Object>> data) throws IOException{
        int sheetNum = 0;
        HSSFSheet sheet =null;
        HSSFRow row = null; 
        //设置标题样式和单元格样式 
        HSSFCellStyle titlestyle = createTitleCellStyle(workbook);
        HSSFCellStyle bodystyle = createBodyCellStyle(workbook);
        int length = columnsize.length;
        String [] headers = null;
        //遍历集合数据,产生数据行
        if(data!=null){
        int rowindex = 0;
            for(HashMap<String,Object> entry:data){
                if (rowindex % limitNum == 0){  
                    if(rowindex != 0 ){
                        sheetNum++;
                    }
                    sheet = workbook.createSheet(sheetTitle+sheetNum);
                    //设置sheet的列宽
                    for(int i=0;i<length;i++){
                        sheet.setColumnWidth(i, (short)columnsize[i]*256);
                    }
                    row = sheet.createRow(0);
                    headers  =createHeaders(data,titlestyle,row);
                    rowindex = 1;
                }
                row = sheet.createRow(rowindex);
                int cellIndex = 0;
                 for (String key : headers){
                     HSSFCell cell = row.createCell(cellIndex);
                        cell.setCellStyle(bodystyle);
                        Object str = entry.get(key);
                        //处理空数据
                        if(str!=null ){
                            cell.setCellValue(str.toString());
                        }else{
                            cell.setCellValue(" ");
                        }
                        cellIndex++;
                   }
                rowindex++;
            }
        }
    }

    /**
     * 导出excel通用方法(导出多个sheet .xls文件)重载
     * @param workbook
     * @param out        : OutputStream对象
     * @param limitNum   : 超过limitNum生成新sheet
     * @param sheetTitle : 设置excel的sheet标题
     * @param headers    : 设置excel的列标题
     * @param columnsize : 设置excel的列宽度
     * @param data       : List<HashMap<String,Object>> 数据
     * @throws IOException
     */
    public void exprotExcelManySheet(HSSFWorkbook workbook,OutputStream out,int limitNum,String sheetTitle,
            String[] headers,int [] columnsize,List<LinkedHashMap<String,Object>> data) throws IOException{
        setExcelData(workbook, out, limitNum, sheetTitle, headers, columnsize, data);
        workbook.write(out);
        out.flush();
        out.close();
    }

    /**
     * 导出excel通用方法(导出多个sheet .xls文件)
     * @param workbook
     * @param out        : OutputStream对象
     * @param limitNum   : 超过limitNum生成新sheet
     * @param sheetTitle : 设置excel的sheet标题
     * @param columnsize : 设置excel的列宽度
     * @param data       : List<HashMap<String,Object>> 数据
     * @throws IOException
     */
    public void exprotExcelManySheet(HSSFWorkbook workbook,OutputStream out,int limitNum,
            String sheetTitle,int [] columnsize,List<LinkedHashMap<String,Object>> data) throws IOException{
        setExcelData(workbook, out, limitNum, sheetTitle, columnsize, data);
        workbook.write(out);
        out.flush();
        out.close();
    }



    /**
     * 导出excel压缩包通用方法(导出多个sheet .zip文件)
     * @param workbook
     * @param limitNum   : 限制每个sheet最多多少条数据
     * @param sheetTitle : 每个sheet的name
     * @param headers    : sheet标题
     * @param result     : 传入的数据
     * @param out
     * @throws IOException
     */
    public void exprotExcelManySheetZip(HSSFWorkbook workbook,OutputStream out,int limitNum,String sheetTitle,
            String[] headers,int [] columnsize,List<LinkedHashMap<String,Object>> data) throws IOException{
         setExcelData(workbook, out, limitNum, sheetTitle, headers, columnsize, data);
         ZipOutputStream zip = new ZipOutputStream(out,Charset.forName("GBK"));
         ZipEntry entry = new ZipEntry(sheetTitle+".xls");//设置压缩包中文件的名字
         zip.putNextEntry(entry);
         workbook.write(zip);
         zip.flush();
         zip.close();
    }


    /**
     * 创建标题头
     * @param headers
     * @param titlestyle
     * @param row
     */
    public void createHeaders(String[] headers,HSSFCellStyle titlestyle,HSSFRow row){
        int headerslength = headers.length;
        for(int i =0;i<headerslength;i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellStyle(titlestyle);
            cell.setCellValue(text.toString());
        }
    }

    /**
     * 创建标题头
     * @param data
     * @param titlestyle
     * @param row
     */
    public String [] createHeaders(List<LinkedHashMap<String,Object>> data,HSSFCellStyle titlestyle,HSSFRow row){
        int size = data.get(0).size(); 
        String [] headers = new String[size];
        Set<String> keys = data.get(0).keySet();
        Iterator iter = keys.iterator();
        int count = 0;
        while(iter.hasNext()){
            String str = (String) iter.next();
            headers[count] = str;
            HSSFCell cell = row.createCell(count);
            HSSFRichTextString text = new HSSFRichTextString(str);
            cell.setCellStyle(titlestyle);
            cell.setCellValue(text.toString());
            count++;
        }
        return headers;
    }

    /**
     * 设置正文单元格格式
     * @param workbook
     * @return
     */
    public  HSSFCellStyle createBodyCellStyle(HSSFWorkbook workbook){
        HSSFCellStyle cellstyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)10);
        font.setFontName(HSSFFont.FONT_ARIAL);
        cellstyle.setFont(font);
        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return cellstyle;

    }

    /**
     * 设置标题单元样式
     * @param workbook
     * @return
     */
    public   HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook){
        HSSFCellStyle  cellstyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short)10);
        font.setFontName(HSSFFont.FONT_ARIAL);
        cellstyle.setFont(font);
        cellstyle.setFillForegroundColor(HSSFColor.SEA_GREEN.index);
        cellstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return cellstyle;
    }


    /**
     * excel 或者 zip 文件下载(表单提交,ajax请求不可以)
     * @param request
     * @param response
     * @param filepath
     * @param filename
     * @param out
     * @throws IOException
     */
    public void download(HttpServletRequest request,HttpServletResponse response,HSSFWorkbook workbook,String sheetTitle,String filename) throws IOException {
        // 以下主要实现 Excel文件下载
        // 设置响应头和下载保存的文件名
        response.reset();
        response.setContentType("APPLICATION/OCTET-STREAM");
        String xlsName ="ExpressCount.zip";
        String excelName = URLEncoder.encode(xlsName, "UTF-8");
         response.setHeader("Content-Disposition", "attachment; filename=\"" + excelName + "\"");
        String path = request.getSession().getServletContext().getRealPath("upload/excelexport/");
        String zipname ="ExpressCount";
        FileInputStream fis = new FileInputStream(path+zipname+".zip");   
        // 写出流信息   
        int i = 0;   
        while ((i=fis.read()) != -1) {   
             response.getOutputStream().write(i);   
        }
         fis.close();
         response.getOutputStream().flush();   
         response.getOutputStream().close();
    }



        /**
         *  excel 或者 zip 文件流下载(表单提交,ajax请求不可以)
         * @param path
         * @param response
         */
    public void downloadbyte(String path, HttpServletResponse response) {  
        try {  
            // path是指欲下载的文件的路径。  
            File file = new File(path);  
            // 取得文件名。  
            String filename = file.getName();  
            // 以流的形式下载文件。  
            InputStream fis = new BufferedInputStream(new FileInputStream(path));  
            byte[] buffer = new byte[fis.available()];  
            fis.read(buffer);  
            fis.close();  
            // 清空response  
            response.reset();  
            // 设置response的Header  
            response.addHeader("Content-Disposition", "attachment;filename="  
                    + new String(filename.getBytes()));  
            response.addHeader("Content-Length", "" + file.length());  
            OutputStream toClient = new BufferedOutputStream(  
                    response.getOutputStream());  
            response.setContentType("application/vnd.ms-excel;charset=gb2312");  
            toClient.write(buffer);  
            toClient.flush();  
            toClient.close();  
        } catch (IOException ex) {  
            ex.printStackTrace();  
        }  
    }  
}

/**
 * POIExcelUtils工具类
 */
public  class POIExcelUtils {

    /**
     * 导出多个sheet的excel(.xls文件)
     * @param request
     * @param result        : List<HashMap<String,Object>> 数据
     * @param filename      : 导出excel的名字
     * @param limitNum      : 超过limitNum生成新sheet
     * @param sheetTitle    : 设置excel的sheet标题
     * @param headers       : 设置excel的列标题
     * @param columnsize    : 设置excel的列宽度
     * @return
     * @throws IOException
     */
    public static String POIExcelExport(HttpServletRequest request,List<LinkedHashMap<String,Object>> result,String filename
            ,int limitNum,String sheetTitle,String [] headers,int[] columnsize) throws IOException{
        POIExcelExport poiexport = new POIExcelExport();
        HSSFWorkbook workbook = new HSSFWorkbook();
        String path = request.getSession().getServletContext().getRealPath("upload/excelexport/");
        //不存在就创建文件夹
        newFolder(path);
        OutputStream out = new FileOutputStream(path+filename);
        poiexport.exprotExcelManySheet(workbook, out, limitNum, sheetTitle, headers, columnsize, result);
        String downloadpath = "upload/excelexport/"+filename;
        return downloadpath;
    }

    /**
     * 导出多个sheet的excel(.xls文件)重载
     * @param request
     * @param result        : List<HashMap<String,Object>> 数据
     * @param filename      : 导出excel的名字
     * @param limitNum      : 超过limitNum生成新sheet
     * @param sheetTitle    : 设置excel的sheet标题
     * @param columnsize    : 设置excel的列宽度
     * @return
     * @throws IOException
     */
    public static String POIExcelExport(HttpServletRequest request,List<LinkedHashMap<String,Object>> result,
            String filename,int limitNum,String sheetTitle,int[] columnsize) throws IOException{
        POIExcelExport poiexport = new POIExcelExport();
        HSSFWorkbook workbook = new HSSFWorkbook();
        String path = request.getSession().getServletContext().getRealPath("upload/excelexport/");
        //不存在就创建文件夹
        newFolder(path);
        OutputStream out = new FileOutputStream(path+"/"+filename);
        poiexport.exprotExcelManySheet(workbook, out, limitNum, sheetTitle, columnsize, result);
        String downloadpath = "upload/excelexport/"+filename;
        return downloadpath;
    }


    /**
     * 导出多个sheet的excel压缩文件(.zip)
     * @param request
     * @param result        : List<HashMap<String,Object>> 数据
     * @param filename      : 导出excel的名字
     * @param limitNum      : 超过limitNum生成新sheet
     * @param sheetTitle    : 设置excel的sheet标题
     * @param headers       : 设置excel的列标题
     * @param columnsize    : 设置excel的列宽度
     * @throws IOException
     */
    public static String POIExcelExportZip(HttpServletRequest request,List<LinkedHashMap<String,Object>> result,String filename
            ,int limitNum,String sheetTitle,String [] headers,int[] columnsize) throws IOException{
        POIExcelExport poiexport = new POIExcelExport();
        HSSFWorkbook workbook = new HSSFWorkbook();
        String path = request.getSession().getServletContext().getRealPath("upload/excelexport/");
        //不存在就创建文件夹
        newFolder(path);
        OutputStream out = new FileOutputStream(path+filename);
        poiexport.exprotExcelManySheetZip(workbook, out, limitNum, sheetTitle, headers, columnsize, result);
        String zipdownloadpath = "upload/excelexport/"+filename;
        return zipdownloadpath;
    }


    /**
     * 传入的path不存在就创建文件夹
     * @param path
     * @return
     */
    public static boolean newFolder(String path){
        if(path == null || path==""){
            return false;
        }
        File file = new File(path);
        if(!file.exists()){
            file.mkdirs();
        }
        return true;
    }


    /**
     * 数据转换方法
     * 暂没用到
     * @param oldData
     * @param keys
     * @return
     */
    public static List<List<Object>> dataTransfer(List<HashMap<String,Object>> oldData, String[] keys){
        List<List<Object>> newData = new ArrayList<List<Object>>();
        for (HashMap<String,Object> object : oldData) {
            List<Object> row = new ArrayList<Object>();
            for (String key : keys){
                row.add(object.get(key));
            }
            newData.add(row);
        }
        return newData;
    }

    /**
     * 从hashMap中拿到key的数组(暂时没用到)
     * @param data
     * @return
     */
    public static String[] gethashMapKey(HashMap<String,Object> data){
        int size = data.size(); 
        String [] headers = new String[size];
        Set<String> keys = data.keySet();
        Iterator iter = keys.iterator();
        int count = 0;
        while(iter.hasNext()){
            String str = (String) iter.next();
            headers[count] = str;
            count++;
        }
        return headers;
    }
}   

我在页面上用的Ajax请求,然后后台返回一个excel生成的地址,跳转就可以下载,如果想要通过流的方式,那么不可以用ajax,因为ajax无法解析流.
甚至可以传入List<List<String,Object>>类型

导出6万条数据时间15秒左右,如果不设置列的宽度,还要更快.我试过用poi自带的自适应方法,发现效率变低很多,但是不设置宽度导出的excel很难看,所以我手动传入一个列宽度的数组,在降低点效率的同时又做到了美观..