使用POI导出百万级数据到excel的解决方案

1.hssfworkbook 和sxssfworkbook区别

hssfworkbook:是操作excel2003以前（包括2003）的版本，扩展名是.xls，一张表最大支持65536行数据，256列，也就是说一个sheet页，最多导出6w多条数据

xssfworkbook:是操作excel2007-2010的版本，扩展名是.xlsx对于不同版本的excel文档要使用不同的工具类，如果使用错了，
会提示如下错误信息。

org.apache.poi.openxml4j.exceptions.invalidoperationexception    

org.apache.poi.poifs.filesystem.officexmlfileexception

它的一张表最大支持1048576行，16384列，关于两者介绍，对下面导出百万数据很重要，不要使用错了！

 

2.使用sxssfworkbook对象，导出百万数据

sxssfworkbook使用方法和 hssfworkbook差不多，如果你之前和我一样用的hssfworkbook，现在想要修改，则只需要将hssfworkbook改成sxssfworkbook即可，下面有我介绍，具体使用也可参考api。

因项目业务，需要导出百万级数据到excel，在研究了各种方案后，最终确定了用poi的sxssfworkbook。

sxssfworkbook是poi3.8以上新增的，excel2007后每个sheet支持104万行数据。

3.如何将百万数据分成多个sheet页，导出到excel

导出百万数据到excel，很简单，只需要将原来的hssfworkbook修改成sxssfworkbook，或者直接使用sxssfworkbook对象，它是直接用来导出大数据用的，有介绍，但是如果有300w条数据，一下导入一个excel的sheet页中，想想打开excel也需要一段时间吧，慢的话有可能导致程序无法加载，或者直接结束进程的情况发生

4.先看导出后的效果

由于百万数据太长了，这里只截取尾部效果图

 

5.下面是java代码部分

/**
     * 使用 sxssfworkbook 对象实现excel导出
     * (一般是导出百万级数据的excel)
     */
    public void exportbigdataexcel() {
        long  starttime = system.currenttimemillis();    //开始时间
        system.out.println("start execute time: " + starttime);
        
        // 1.创建工作簿
        // 阈值，内存中的对象数量最大值，超过这个值会生成一个临时文件存放到硬盘中
        sxssfworkbook wb = new sxssfworkbook(1000);
        
        //2.在workbook中添加一个sheet,对应excel文件中的sheet
        sheet sheet = wb.createsheet();
        
        //3.设置样式以及字体样式
        cellstyle titlecellstyle = createtitlecellstyle(wb);
        cellstyle headcellstyle = createheadcellstyle(wb);
        cellstyle cellstyle = createcellstyle(wb);
        
        //4.创建标题、表头，内容和合并单元格等操作
        int rownum = 0;// 行号
        // 创建第一行，索引从0开始
        row row0 = sheet.createrow(rownum++);
        row0.setheight((short) 800);// 设置行高
        
        string title = "这里是标题标题标题";
        cell c00 = row0.createcell(0);
        c00.setcellvalue(title);
        c00.setcellstyle(titlecellstyle);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addmergedregion(new cellrangeaddress(0, 0, 0, 6));//标题合并单元格操作，6为总列数
        
        // 第二行
        row row1 = sheet.createrow(rownum++);
        row1.setheight((short) 500);
        string[] row_first = {"填表单位：", "", "", "", "", " xxxx年第x季度 ", ""};
        for (int i = 0; i < row_first.length; i++) {
            cell tempcell = row1.createcell(i);
            tempcell.setcellstyle(headcellstyle);
            if (i == 0) {
                tempcell.setcellvalue(row_first[i] + "测试单位");
            } else if (i == 5) {
                tempcell.setcellstyle(headcellstyle);
                tempcell.setcellvalue(row_first[i]);
            } else {
                tempcell.setcellvalue(row_first[i]);
            }
        }
        
        // 合并
        sheet.addmergedregion(new cellrangeaddress(1, 1, 0, 4));
        sheet.addmergedregion(new cellrangeaddress(1, 1, 5, 6));
        
        //第三行
        row row2 = sheet.createrow(rownum++);
        row2.setheight((short) 700);
        string[] row_second = {"名称", "采集情况", "", "", "登记情况", "", "备注"};
        for (int i = 0; i < row_second.length; i++) {
            cell tempcell = row2.createcell(i);
            tempcell.setcellvalue(row_second[i]);
            tempcell.setcellstyle(headcellstyle);
        }
        
        // 合并
        sheet.addmergedregion(new cellrangeaddress(2, 3, 0, 0));//名称
        sheet.addmergedregion(new cellrangeaddress(2, 2, 1, 3));//人数情况
        sheet.addmergedregion(new cellrangeaddress(2, 2, 4, 5));//登记情况
        sheet.addmergedregion(new cellrangeaddress(2, 3, 6, 6));//备注
        
        //第三行
        row row3 = sheet.createrow(rownum++);
        row3.setheight((short) 700);
        string[] row_third = {"", "登记数(人)", "办证总数(人)", "办证率(%)", "登记户数(户)", "登记时间", ""};
        for (int i = 0; i < row_third.length; i++) {
            cell tempcell = row3.createcell(i);
            tempcell.setcellvalue(row_third[i]);
            tempcell.setcellstyle(headcellstyle);
        }
        
        // 数据处理(创建100万条测试数据)
        list<map<string, object>> datalist = new arraylist<map<string, object>>();
        for (int i = 0; i < 999999; i++) {
            map<string,object> map = new hashmap<string,object>();
            map.put("name", "测试名称" + i);
            map.put("r1", "111");
            map.put("r2", "222");
            map.put("r3", "333");
            map.put("r4", "444");
            map.put("addtime", new datetime());
            map.put("r6", "这里是备注"+i);
            datalist.add(map);
        }
        
        for (map<string, object> exceldata : datalist) {
            row temprow = sheet.createrow(rownum++);
            temprow.setheight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < 7; j++) {
                cell tempcell = temprow.createcell(j);
                tempcell.setcellstyle(cellstyle);
                string tempvalue;
                if (j == 0) {
                    // 乡镇、街道名称
                    tempvalue = exceldata.get("name").tostring();
                } else if (j == 1) {
                    // 登记数（人）
                    tempvalue = exceldata.get("r1").tostring();
                } else if (j == 2) {
                    // 办证总数（人）
                    tempvalue = exceldata.get("r2").tostring();
                } else if (j == 3) {
                    // 办证率（%）
                    tempvalue = exceldata.get("r3").tostring();
                } else if (j == 4) {
                    // 登记户数（户）
                    tempvalue = exceldata.get("r4").tostring();
                } else if (j == 5) {
                    // 登记日期
                    tempvalue = exceldata.get("addtime").tostring();
                } else {
                    // 备注
                    tempvalue = exceldata.get("r6").tostring();
                }
                tempcell.setcellvalue(tempvalue);
               
//                sheet.autosizecolumn(j);// 根据内容自动调整列宽，
            }
        }
        //设置列宽，必须在单元格设值以后进行
        sheet.setcolumnwidth(0, 4000);//名称
        sheet.setcolumnwidth(1, 3000);//登记数(人)
        sheet.setcolumnwidth(2, 3000);//办证总数(人)
        sheet.setcolumnwidth(3, 3000);//办证率(%)
        sheet.setcolumnwidth(4, 3000);//登记户数(户)
        sheet.setcolumnwidth(5, 6000);//登记时间
        sheet.setcolumnwidth(6, 4000);//备注
        
        // 注释行
        row remark = sheet.createrow(rownum++);
        remark.setheight((short) 500);
        string[] row_remark = {"注：表中的“办证率=办证总数÷登记数×100%”", "", "", "", "", "", ""};
        for (int i = 0; i < row_remark.length; i++) {
            cell tempcell = remark.createcell(i);
            if (i == 0) {
                tempcell.setcellstyle(headcellstyle);
            } else {
                tempcell.setcellstyle(cellstyle);
            }
            tempcell.setcellvalue(row_remark[i]);
        }
        int remarkrownum = datalist.size() + 4;
        sheet.addmergedregion(new cellrangeaddress(remarkrownum, remarkrownum, 0, 6));//注释行合并单元格
         
        // 尾行
        row foot = sheet.createrow(rownum++);
        foot.setheight((short) 500);
        string[] row_foot = {"审核人：", "", "填表人：", "", "填表时间：", "", ""};
        for (int i = 0; i < row_foot.length; i++) {
            cell tempcell = foot.createcell(i);
            tempcell.setcellstyle(cellstyle);
            if (i == 0) {
                tempcell.setcellvalue(row_foot[i] + "张三");
            } else if (i == 2) {
                tempcell.setcellvalue(row_foot[i] + "李四");
            } else if (i == 4) {
                tempcell.setcellvalue(row_foot[i] + "xxxx");
            } else {
                tempcell.setcellvalue(row_foot[i]);
            }
        }
        int footrownum = datalist.size() + 5;
        // 注
        sheet.addmergedregion(new cellrangeaddress(footrownum, footrownum, 0, 1));
        sheet.addmergedregion(new cellrangeaddress(footrownum, footrownum, 2, 3));
        sheet.addmergedregion(new cellrangeaddress(footrownum, footrownum, 4, 6));
        
        
        long finishedtime = system.currenttimemillis();    //处理完成时间
        system.out.println("finished execute  time: " + (finishedtime - starttime)/1000 + "m");
        
        //导出
        httpservletresponse response = this.getresponse();
        string filename = "报表文件名称.xlsx";
        try {
            filename = new string(filename.getbytes("utf-8"), "iso-8859-1");
            response.setheader("content-disposition", "attachment;filename=\"" + filename + "\"");
            outputstream stream = response.getoutputstream();
            if (null != wb && null != stream) {
                wb.write(stream);// 将数据写出去  
                wb.close();
                stream.close();
                
                long stoptime = system.currenttimemillis();        //写文件时间
                system.out.println("write xlsx file time: " + (stoptime - starttime)/1000 + "m");
            }
        } catch (exception e) {
            e.printstacktrace();
        }
        
    }
    

 

cellstyle标题，表头，内容样式代码：

private static cellstyle createtitlecellstyle(workbook workbook) {
        cellstyle cellstyle = workbook.createcellstyle();
        cellstyle.setalignment(horizontalalignment.center);
        cellstyle.setverticalalignment(verticalalignment.center);

        cellstyle.setborderbottom(borderstyle.thin);
        cellstyle.setbottombordercolor(indexedcolors.black.index);
        cellstyle.setborderleft(borderstyle.thin);
        cellstyle.setleftbordercolor(indexedcolors.black.index);
        cellstyle.setborderright(borderstyle.thin);
        cellstyle.setrightbordercolor(indexedcolors.black.index);
        cellstyle.setbordertop(borderstyle.thin);
        cellstyle.settopbordercolor(indexedcolors.black.index);

        cellstyle.setfillforegroundcolor(indexedcolors.grey_40_percent.index);
        cellstyle.setfillpattern(fillpatterntype.solid_foreground);

        return cellstyle;
    }

 

private static cellstyle createheadcellstyle(workbook workbook) {
        cellstyle cellstyle = workbook.createcellstyle();
        cellstyle.setalignment(horizontalalignment.center);
        cellstyle.setverticalalignment(verticalalignment.center);

        cellstyle.setborderbottom(borderstyle.thin);
        cellstyle.setbottombordercolor(indexedcolors.black.index);
        cellstyle.setborderleft(borderstyle.thin);
        cellstyle.setleftbordercolor(indexedcolors.black.index);
        cellstyle.setborderright(borderstyle.thin);
        cellstyle.setrightbordercolor(indexedcolors.black.index);
        cellstyle.setbordertop(borderstyle.thin);
        cellstyle.settopbordercolor(indexedcolors.black.index);

        cellstyle.setfillforegroundcolor(indexedcolors.grey_25_percent.index);
        cellstyle.setfillpattern(fillpatterntype.solid_foreground);

        return cellstyle;
    }

 

    private static cellstyle createcellstyle(workbook workbook) {
        cellstyle cellstyle = workbook.createcellstyle();
        cellstyle.setalignment(horizontalalignment.center);
        cellstyle.setverticalalignment(verticalalignment.center);

        cellstyle.setborderbottom(borderstyle.thin);
        cellstyle.setbottombordercolor(indexedcolors.black.index);
        cellstyle.setborderleft(borderstyle.thin);
        cellstyle.setleftbordercolor(indexedcolors.black.index);
        cellstyle.setborderright(borderstyle.thin);
        cellstyle.setrightbordercolor(indexedcolors.black.index);
        cellstyle.setbordertop(borderstyle.thin);
        cellstyle.settopbordercolor(indexedcolors.black.index);

        return cellstyle;
    }
    

 

完毕！good luck！