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

2019-08-29 09:01:15来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

使用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!

 


原文链接:https://www.cnblogs.com/hxun/p/11419006.html
如有疑问请与原作者联系

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:Struts2 Jsp 动态明细向后台传值

下一篇:java字符串截取