最近有一个需要生成一个稍微复杂一点的Excel文档的需求：
- 列宽
- 单元格合并
- 加粗
- 字号
我之前通常只需要生成一个比较简单的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();
}
}
跑一下：
OK，一毛一样