背景
今天，飞哥一脸不开心地来找我，我这一看又是有事情了，果不其然，飞哥一脸不岔地对我说：“小米，财务部那边说上次做的报销单下载用word方式不想用了，想用excel的方式导出，你觉得可以不？”，我一听，这都是常态了，已经习以为常了，真是“需求虐我千百遍，我待客户如初恋”~
需求
报销单如上图：
- 题目：报销单
- 报销报销人及申报时间
- 费用类型的表格
- 审核情况的表格
创建excel
// 创建一个Workbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
创建sheet
// 在Workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet reimbursementSheet = wb.createSheet("报销单");
设置样式以及字体样式
// 设置样式以及字体样式
HSSFCellStyle titleStyle = createTitleCellStyle(wb);
HSSFCellStyle headerStyle = createHeadCellStyle(wb);
HSSFCellStyle contentStyle = createContentCellStyle(wb);
AtomicInteger rowNum = new AtomicInteger(NumberUtils.INTEGER_ZERO);
创建标题
rowNum.set(this.firstRowHandler(reimbursementSheet,
titleStyle, rowNum.get(), "报销单"));
private int firstRowHandler(HSSFSheet sheet,
HSSFCellStyle titleStyle, int rowNum, String title) {
HSSFRow row0 = sheet.createRow(rowNum++);
// 设置行高
row0.setHeight((short) 800);
// 样式
HSSFCell c00 = row0.createCell(NumberUtils.INTEGER_ZERO);
c00.setCellValue(title);
c00.setCellStyle(titleStyle);
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
sheet.addMergedRegion(new CellRangeAddress(
NumberUtils.INTEGER_ZERO, NumberUtils.INTEGER_ZERO,
NumberUtils.INTEGER_ZERO, 4));//标题合并单元格操作，4为总列数
return rowNum;
}
报销人及申报时间
rowNum.set(this.secondRowHandler(
reimbursementSheet, rowNum.get(), wb));
private int secondRowHandler(HSSFSheet sheet, int rowNum,
HSSFWorkbook wb) {
HSSFRow row1 = sheet.createRow(rowNum++);
String[] row_first = {"报销人：", "", "", "申报时间：", ""};
for (int i = NumberUtils.INTEGER_ZERO; i < row_first.length;
i++) {
HSSFCell tempCell = row1.createCell(i);
if (i == NumberUtils.INTEGER_ZERO) {
tempCell.setCellValue(row_first[i] + "李四");
} else if (i == 3) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
tempCell.setCellStyle(cellStyle);
tempCell.setCellValue(row_first[i]+"2022-03-04 03:43");
} else {
tempCell.setCellValue(row_first[i]);
}
}
// 合并
sheet.addMergedRegion(new CellRangeAddress(
NumberUtils.INTEGER_ONE, NumberUtils.INTEGER_ONE,
NumberUtils.INTEGER_ZERO, 1));
sheet.addMergedRegion(new CellRangeAddress(
NumberUtils.INTEGER_ONE, NumberUtils.INTEGER_ONE,
3, 4));
return rowNum;
}
费用类型处理
this.typeHandler(reimbursementVO, wb, reimbursementSheet,
headerStyle, contentStyle, rowNum);
private void typeHandler(List list, HSSFWorkbook wb,
HSSFSheet sheet, HSSFCellStyle headerStyle,
HSSFCellStyle contentStyle, AtomicInteger rowNum) {
// 费用类型
list.stream().filter(Objects::nonNull)
.forEach(reimbursementDetailVO -> {
//第三行：费用类型
int expenseTypeNum = rowNum.getAndIncrement();
HSSFRow typeTitleRow = sheet.createRow(expenseTypeNum);
typeTitleRow.setHeight((short) 700);
HSSFCell c30 = typeTitleRow.createCell(NumberUtils.INTEGER_ZERO);
c30.setCellValue("费用类型：费用1");
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
sheet.addMergedRegion(new CellRangeAddress(expenseTypeNum, expenseTypeNum, NumberUtils.INTEGER_ZERO, 4));
// 内容
int typeNameNum = rowNum.getAndIncrement();
HSSFRow typeNameNumRow = sheet.createRow(typeNameNum);
typeNameNumRow.setHeight((short) 700);
String[] row_third = {"报销项目", "摘要", "费用金额", "发票数量", "费用说明"};
for (int i = NumberUtils.INTEGER_ZERO; i < row_third.length; i++) {
HSSFCell tempCell = typeNameNumRow.createCell(i);
tempCell.setCellValue(row_third[i]);
tempCell.setCellStyle(headerStyle);
}
int typeNum = rowNum.getAndIncrement();
HSSFRow tempRow = sheet.createRow(typeNum);
tempRow.setHeight((short) 500);
// 循环单元格填入数据
for (int j = NumberUtils.INTEGER_ZERO; j < 4; j++) {
HSSFCell tempCell = tempRow.createCell(j);
tempCell.setCellStyle(contentStyle);
tempCell.setCellValue("内容");
}
// 小计
int subTotalNum = rowNum.getAndIncrement();
HSSFRow subtotalNumRow = sheet.createRow(subTotalNum);
String[] subTotalCellValue = {"小计：2000", "", "", "", ""};
for (int i = NumberUtils.INTEGER_ZERO;
i < subTotalCellValue.length; i++) {
HSSFCell tempCell = subtotalNumRow.createCell(i);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
tempCell.setCellStyle(cellStyle);
tempCell.setCellValue(subTotalCellValue[i]);
}
//标题合并单元格操作，4为总列数
sheet.addMergedRegion(new CellRangeAddress(subTotalNum,
subTotalNum, NumberUtils.INTEGER_ZERO, 4));
});
// 合计
int totalRowNum = rowNum.getAndIncrement();
HSSFRow totalRow = sheet.createRow(totalRowNum);
String[] totalCellValue = {"合计人民币：2000", "", "", "", ""};
for (int i = NumberUtils.INTEGER_ZERO; i <
totalCellValue.length; i++) {
HSSFCell tempCell = totalRow.createCell(i);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
tempCell.setCellStyle(cellStyle);
tempCell.setCellValue(totalCellValue[i]);
}
//标题合并单元格操作，4为总列数
sheet.addMergedRegion(new CellRangeAddress(totalRowNum, totalRowNum, NumberUtils.INTEGER_ZERO, 4));
}
审核情况与费用类型一样，故省略。
表格自适应
为了让表格整体好看一些，不至于拥挤，做了一些表格自适应，代码如下：
setSizeColumn(reimbursementSheet, 5);
private static void setSizeColumn(HSSFSheet sheet,
int columnLength) {
for (int columnNum = 0; columnNum <= columnLength;
columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 200;
for (int rowNum = 0; rowNum < sheet.getLastRowNum();
rowNum++) {
HSSFRow currentRow; // 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
HSSFCell currentCell = currentRow
.getCell(columnNum);
if (currentCell.getCellType() ==
HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.
getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 200);
}
}
excel比word简单点，终于写完了，能交差了，我长长地呼出一口气……
写在最后
好兄弟可以点赞并关注我的公众号“javaAnswer”，全部都是干货。
