最近有一个需要生成一个稍微复杂一点的Excel文档的需求:
image.png

  • 列宽
  • 单元格合并
  • 加粗
  • 字号

我之前通常只需要生成一个比较简单的Excel,所以只需要使用Hutool里面生成Excel文档的工具类即可,但这个工具无法灵活的去定义样式
所以我就想到了Apache原生的POI,直接用它来实现虽然代码量比较多,但它足够灵活

说明

POI操作EXCEL对象:

  • HSSF:操作Excel 97(.xls)格式
  • XSSF:操作Excel 2007 OOXML (.xlsx)格式,操作EXCEL内存占用高于HSSF
  • SXSSF:从POI3.8 beta3开始支持,基于XSSF,低内存占用

使用POI的HSSF对象,生成Excel 97(.xls)格式,生成的EXCEL不经过压缩直接导出

线上问题:负载服务器转发请求到应用服务器阻塞,以及内存溢出

如果系统存在大数据量报表导出,则考虑使用POI的SXSSF进行EXCEL操作

HSSF生成的Excel 97(.xls)格式本身就有每个sheet页不能超过65536条的限制

XSSF生成Excel 2007 OOXML (.xlsx)格式,条数增加了,但是导出过程中,内存占用率却高于HSSF

SXSSF是自3.8-beta3版本后,基于XSSF提供的低内存占用的操作EXCEL对象。其原理是可以设置或者手动将内存中的EXCEL行写到硬盘中,这样内存中只保存了少量的EXCEL行进行操作

EXCEL的压缩率特别高,能达到80%,12M的文件压缩后才2M左右

如果未经过压缩、不仅会占用用户带宽,且会导致负载服务器(apache)和应用服务器之间,长时间占用连接(二进制流转发),导致负载服务器请求阻塞,不能提供服务

关于ApachePOI导出Excel基本的使用我这里就不详解了,具体参考Apache POI官方网站

代码中的基本概念如下:

  • SXSSFWorkbook: 相当于一个excel文件

  • SXSSFSheet:一张excel表,excel左下角的sheet0,sheet1..

  • SXSSFRow:一张表格中的某一行

  • SXSSFCell:一张表格中的一个单元格

了解上述概念之后对后面写代码很有帮助

具体实现

由于只是展示基础使用,所以我没有对它进行任何包装,只是从上到下的将需求一一实现
引入依赖:

<dependency>
	<groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
</dependency>

具体实现:

public static void main(String[] args) {
        // 创建
        SXSSFWorkbook wb = new SXSSFWorkbook();
        SXSSFSheet sheet = wb.createSheet();

        // 设置宽高
        sheet.setColumnWidth(0, 16 * 256);
        sheet.setColumnWidth(1, 15 * 256);
        sheet.setColumnWidth(2, 16 * 256);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(4, 13 * 256);
        sheet.setColumnWidth(5, 19 * 256);
        sheet.setColumnWidth(6, 21 * 256);
        sheet.setColumnWidth(7, 32 * 256);
        sheet.setColumnWidth(8, 18 * 256);
        sheet.setDefaultRowHeightInPoints(25);
        // 隐藏网格线
        sheet.setDisplayGridlines(false);

        //生成单元格样式
        CellStyle headlineCellStyle = wb.createCellStyle();
        //新建font实体
        Font headlineFont = wb.createFont();
        //设置字体颜色
        headlineFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //字体大小
        headlineFont.setFontHeightInPoints((short) 18);
        headlineFont.setFontName("等线");
        //粗体
        headlineFont.setBold(true);
        headlineCellStyle.setFont(headlineFont);
        // 设置水平左对齐
        headlineCellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        // 设置垂直居中部
        headlineCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框线
        headlineCellStyle.setBorderTop(BorderStyle.THIN);
        headlineCellStyle.setBorderBottom(BorderStyle.THIN);
        headlineCellStyle.setBorderLeft(BorderStyle.THIN);
        headlineCellStyle.setBorderRight(BorderStyle.THIN);
        headlineCellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

        int index = 0;

        int headlineRowNum = index++;
        // 大标题
        SXSSFRow headlineRow = sheet.createRow(headlineRowNum);
        SXSSFCell headlineCell = headlineRow.createCell(0);
        headlineCell.setCellValue(LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日")) + "XXXX充值對賬單");
        headlineCell.setCellStyle(headlineCellStyle);

        for (int i = 1; i < 9; i++) {
            SXSSFCell headlineCells = headlineRow.createCell(i);
            headlineCells.setCellStyle(headlineCellStyle);
        }

        CellRangeAddress headlineCellAddresses = new CellRangeAddress(headlineRowNum, headlineRowNum, 0, 8);
        sheet.addMergedRegion(headlineCellAddresses);

        // 充值通道标题
        //生成单元格样式
        CellStyle rechargeChannelTitleCellStyle = wb.createCellStyle();
        //新建font实体
        Font rechargeChannelTitleFont = wb.createFont();
        //设置字体颜色
        rechargeChannelTitleFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //字体大小
        rechargeChannelTitleFont.setFontHeightInPoints((short) 16);
        rechargeChannelTitleFont.setFontName("等线");
        //粗体
        rechargeChannelTitleFont.setBold(true);
        rechargeChannelTitleCellStyle.setFont(rechargeChannelTitleFont);
        // 设置水平左对齐
        rechargeChannelTitleCellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 设置垂直居中部
        rechargeChannelTitleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        rechargeChannelTitleCellStyle.setBorderTop(BorderStyle.THIN);
        rechargeChannelTitleCellStyle.setBorderBottom(BorderStyle.THIN);
        rechargeChannelTitleCellStyle.setBorderLeft(BorderStyle.THIN);
        rechargeChannelTitleCellStyle.setBorderRight(BorderStyle.THIN);
        rechargeChannelTitleCellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

        int rechargeChannelTitleRowNum = index++;
        SXSSFRow rechargeChannelTitleRow = sheet.createRow(rechargeChannelTitleRowNum);
        SXSSFCell rechargeChannelTitleCell = rechargeChannelTitleRow.createCell(0);
        rechargeChannelTitleCell.setCellValue("充值通道匯總Top up Total Detail");
        rechargeChannelTitleCell.setCellStyle(rechargeChannelTitleCellStyle);
        for (int i = 1; i < 9; i++) {
            SXSSFCell rechargeChannelTitleCells = rechargeChannelTitleRow.createCell(i);
            rechargeChannelTitleCells.setCellStyle(rechargeChannelTitleCellStyle);
        }
        CellRangeAddress rechargeChannelTitleCellAddresses = new CellRangeAddress(rechargeChannelTitleRowNum, rechargeChannelTitleRowNum, 0, 8);
        sheet.addMergedRegion(rechargeChannelTitleCellAddresses);


        // 充值通道项目标题
        //生成单元格样式
        CellStyle titleCellStyle = wb.createCellStyle();
        //新建font实体
        Font titleFont = wb.createFont();
        //设置字体颜色
        titleFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //字体大小
        titleFont.setFontHeightInPoints((short) 11);
        titleFont.setFontName("等线");
        //粗体
        titleFont.setBold(true);
        titleCellStyle.setFont(titleFont);
        // 设置水平左对齐
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中部
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleCellStyle.setBorderTop(BorderStyle.THIN);
        titleCellStyle.setBorderBottom(BorderStyle.THIN);
        titleCellStyle.setBorderLeft(BorderStyle.THIN);
        titleCellStyle.setBorderRight(BorderStyle.THIN);
        titleCellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

        SXSSFRow rechargeChannelItemTitleRow = sheet.createRow(index++);
        SXSSFCell rechargeChannelItemTitleCell1 = rechargeChannelItemTitleRow.createCell(0);
        SXSSFCell rechargeChannelItemTitleCell2 = rechargeChannelItemTitleRow.createCell(1);
        SXSSFCell rechargeChannelItemTitleCell3 = rechargeChannelItemTitleRow.createCell(2);
        SXSSFCell rechargeChannelItemTitleCell4 = rechargeChannelItemTitleRow.createCell(3);
        rechargeChannelItemTitleCell1.setCellValue("充值通道 Method");
        rechargeChannelItemTitleCell1.setCellStyle(titleCellStyle);
        rechargeChannelItemTitleCell2.setCellValue("交易金額 Amount");
        rechargeChannelItemTitleCell2.setCellStyle(titleCellStyle);
        rechargeChannelItemTitleCell3.setCellValue("手續費 Fee");
        rechargeChannelItemTitleCell3.setCellStyle(titleCellStyle);
        rechargeChannelItemTitleCell4.setCellValue("到賬金額 Net Amount");
        rechargeChannelItemTitleCell4.setCellStyle(titleCellStyle);

        // 充值通道项目
        CellStyle dataCellStyle = wb.createCellStyle();
        //新建font实体
        Font dataFont = wb.createFont();
        //设置字体颜色
        dataFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //字体大小
        dataFont.setFontHeightInPoints((short) 11);
        dataFont.setFontName("等线");
        //粗体
        dataFont.setBold(false);
        dataCellStyle.setFont(dataFont);
        // 设置水平左对齐
        dataCellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 设置垂直居中部
        dataCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataCellStyle.setBorderTop(BorderStyle.THIN);
        dataCellStyle.setBorderBottom(BorderStyle.THIN);
        dataCellStyle.setBorderLeft(BorderStyle.THIN);
        dataCellStyle.setBorderRight(BorderStyle.THIN);
        dataCellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

        // TODO: 2020/3/19 数据区域
        SXSSFRow channelRow = sheet.createRow(index++);
        SXSSFCell channelCell1 = channelRow.createCell(0);
        SXSSFCell channelCell2 = channelRow.createCell(1);
        SXSSFCell channelCell3 = channelRow.createCell(2);
        SXSSFCell channelCell4 = channelRow.createCell(3);
        channelCell1.setCellValue("支付寶");
        channelCell1.setCellStyle(dataCellStyle);
        channelCell2.setCellValue("109.00");
        channelCell2.setCellStyle(dataCellStyle);
        channelCell3.setCellValue("0.00");
        channelCell3.setCellStyle(dataCellStyle);
        channelCell4.setCellValue("109.00");
        channelCell4.setCellStyle(dataCellStyle);

        // 通道汇总
        CellStyle summaryCellStyle = wb.createCellStyle();
        //新建font实体
        Font summaryFont = wb.createFont();
        //设置字体颜色
        summaryFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //字体大小
        summaryFont.setFontHeightInPoints((short) 14);
        summaryFont.setFontName("等线");
        //粗体
        summaryFont.setBold(true);
        summaryCellStyle.setFont(summaryFont);
        // 设置水平左对齐
        summaryCellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 设置垂直居中部
        summaryCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        summaryCellStyle.setBorderTop(BorderStyle.THIN);
        summaryCellStyle.setBorderBottom(BorderStyle.THIN);
        summaryCellStyle.setBorderLeft(BorderStyle.THIN);
        summaryCellStyle.setBorderRight(BorderStyle.THIN);
        summaryCellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

        // TODO: 2020/3/19 汇总
        SXSSFRow channelSummaryRow = sheet.createRow(index++);
        SXSSFCell channelSummary1 = channelSummaryRow.createCell(0);
        SXSSFCell channelSummary2 = channelSummaryRow.createCell(1);
        SXSSFCell channelSummary3 = channelSummaryRow.createCell(2);
        SXSSFCell channelSummary4 = channelSummaryRow.createCell(3);
        channelSummary1.setCellValue("匯總Total");
        channelSummary1.setCellStyle(summaryCellStyle);
        channelSummary2.setCellValue("269.22");
        channelSummary2.setCellStyle(summaryCellStyle);
        channelSummary3.setCellValue("0.00");
        channelSummary3.setCellStyle(summaryCellStyle);
        channelSummary4.setCellValue("269.22");
        channelSummary4.setCellStyle(summaryCellStyle);

        SXSSFRow row = sheet.createRow(index++);

        int rechargeRowNum = index++;
        SXSSFRow rechargeRow = sheet.createRow(rechargeRowNum);
        SXSSFCell rechargeCell = rechargeRow.createCell(0);
        rechargeCell.setCellValue("充值明細Top up Detail");
        rechargeCell.setCellStyle(rechargeChannelTitleCellStyle);
        for (int i = 1; i < 9; i++) {
            SXSSFCell rechargeCells = rechargeRow.createCell(i);
            rechargeCells.setCellStyle(rechargeChannelTitleCellStyle);
        }
        sheet.addMergedRegion(new CellRangeAddress(rechargeRowNum, rechargeRowNum, 0, 8));

        SXSSFRow rechargeTitleRow = sheet.createRow(index++);
        SXSSFCell rechargeTitleCell1 = rechargeTitleRow.createCell(0);
        SXSSFCell rechargeTitleCell2 = rechargeTitleRow.createCell(1);
        SXSSFCell rechargeTitleCell3 = rechargeTitleRow.createCell(2);
        SXSSFCell rechargeTitleCell4 = rechargeTitleRow.createCell(3);
        SXSSFCell rechargeTitleCell5 = rechargeTitleRow.createCell(4);
        SXSSFCell rechargeTitleCell6 = rechargeTitleRow.createCell(5);
        SXSSFCell rechargeTitleCell7 = rechargeTitleRow.createCell(6);
        SXSSFCell rechargeTitleCell8 = rechargeTitleRow.createCell(7);
        SXSSFCell rechargeTitleCell9 = rechargeTitleRow.createCell(8);
        rechargeTitleCell1.setCellValue("交易日期 Date");
        rechargeTitleCell1.setCellStyle(titleCellStyle);
        rechargeTitleCell2.setCellValue("交易時間 Time");
        rechargeTitleCell2.setCellStyle(titleCellStyle);
        rechargeTitleCell3.setCellValue("充值通道 Method");
        rechargeTitleCell3.setCellStyle(titleCellStyle);
        rechargeTitleCell4.setCellValue("交易金額 Amount");
        rechargeTitleCell4.setCellStyle(titleCellStyle);
        rechargeTitleCell5.setCellValue("手續費 Fee");
        rechargeTitleCell5.setCellStyle(titleCellStyle);
        rechargeTitleCell6.setCellValue("到賬金額 Net Amount");
        rechargeTitleCell6.setCellStyle(titleCellStyle);
        rechargeTitleCell7.setCellValue("WeMust充值單號 Order");
        rechargeTitleCell7.setCellStyle(titleCellStyle);
        rechargeTitleCell8.setCellValue("BOC通道單號 Order");
        rechargeTitleCell8.setCellStyle(titleCellStyle);
        rechargeTitleCell9.setCellValue("交易狀態 Status");
        rechargeTitleCell9.setCellStyle(titleCellStyle);

        // TODO: 2020/3/19 充值数据
        SXSSFRow rechargeDataRow = sheet.createRow(index++);
        SXSSFCell rechargeDataCell1 = rechargeDataRow.createCell(0);
        rechargeDataCell1.setCellValue("2019-04-04");
        rechargeDataCell1.setCellStyle(dataCellStyle);
        SXSSFCell rechargeDataCell2 = rechargeDataRow.createCell(1);
        rechargeDataCell2.setCellValue("11:01:01");
        rechargeDataCell2.setCellStyle(dataCellStyle);
        SXSSFCell rechargeDataCell3 = rechargeDataRow.createCell(2);
        rechargeDataCell3.setCellValue("支付寶");
        rechargeDataCell3.setCellStyle(dataCellStyle);
        SXSSFCell rechargeDataCell4 = rechargeDataRow.createCell(3);
        rechargeDataCell4.setCellValue("26.00");
        rechargeDataCell4.setCellStyle(dataCellStyle);
        SXSSFCell rechargeDataCell5 = rechargeDataRow.createCell(4);
        rechargeDataCell5.setCellValue("0.00");
        rechargeDataCell5.setCellStyle(dataCellStyle);
        SXSSFCell rechargeDataCell6 = rechargeDataRow.createCell(5);
        rechargeDataCell6.setCellValue("26.00");
        rechargeDataCell6.setCellStyle(dataCellStyle);
        SXSSFCell rechargeDataCell7 = rechargeDataRow.createCell(6);
        rechargeDataCell7.setCellValue("11904041101010001");
        rechargeDataCell7.setCellStyle(dataCellStyle);
        SXSSFCell rechargeDataCell8 = rechargeDataRow.createCell(7);
        rechargeDataCell8.setCellValue("BOCAAS2019040411010157530");
        rechargeDataCell8.setCellStyle(dataCellStyle);
        SXSSFCell rechargeDataCell9 = rechargeDataRow.createCell(8);
        rechargeDataCell9.setCellValue("交易成功");
        rechargeDataCell9.setCellStyle(dataCellStyle);

        // TODO: 2020/3/19 汇总
        SXSSFRow rechargeSummaryRow = sheet.createRow(index++);
        SXSSFCell rechargeSummary1 = rechargeSummaryRow.createCell(0);
        SXSSFCell rechargeSummary2 = rechargeSummaryRow.createCell(1);
        SXSSFCell rechargeSummary3 = rechargeSummaryRow.createCell(2);
        SXSSFCell rechargeSummary4 = rechargeSummaryRow.createCell(3);
        SXSSFCell rechargeSummary5 = rechargeSummaryRow.createCell(4);
        SXSSFCell rechargeSummary6 = rechargeSummaryRow.createCell(5);
        SXSSFCell rechargeSummary7 = rechargeSummaryRow.createCell(6);
        SXSSFCell rechargeSummary8 = rechargeSummaryRow.createCell(7);
        SXSSFCell rechargeSummary9 = rechargeSummaryRow.createCell(8);
        rechargeSummary1.setCellValue("匯總Total");
        rechargeSummary1.setCellStyle(summaryCellStyle);

        rechargeSummary2.setCellValue("");
        rechargeSummary2.setCellStyle(summaryCellStyle);
        rechargeSummary3.setCellValue("");
        rechargeSummary3.setCellStyle(summaryCellStyle);

        rechargeSummary4.setCellValue("0.00");
        rechargeSummary4.setCellStyle(summaryCellStyle);
        rechargeSummary5.setCellValue("0.00");
        rechargeSummary5.setCellStyle(summaryCellStyle);
        rechargeSummary6.setCellValue("269.22");
        rechargeSummary6.setCellStyle(summaryCellStyle);
        rechargeSummary7.setCellValue("");
        rechargeSummary7.setCellStyle(titleCellStyle);
        rechargeSummary8.setCellValue("");
        rechargeSummary8.setCellStyle(titleCellStyle);
        rechargeSummary9.setCellValue("");
        rechargeSummary9.setCellStyle(titleCellStyle);

        FileOutputStream fout = null;
        try {
            fout = new FileOutputStream(System.getProperty("user.dir") + File.separator + "YYYY年MM月DD日XXX充值對賬單.xlsx");
            wb.write(fout);
            fout.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

跑一下:
image.png
OK,一毛一样

上一篇 下一篇