biff5文件处理
poi没法处理biff5文件，当解析biff5格式的文件时，会报错：The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)
我使用的解决方案是找同事用Python写了个转xlsx的exe，我用java命令行调用，转个格式再处理。
当时在这个格式上花了2天时间，总结：java目前没有能直接处理biff5的库，apose等付费的没有去尝试，不过貌似也只支持biff7。
以下是失败的尝试：
- jxls库尝试读取，报错
- jython尝试调用python库来处理，jython没法处理mmap等库，失败
另一个可行方案，但是由于应用程序体积原因没考虑：
下载安装libreoffice，使用 ./soffice --headless --convert-to xlsx "D:\biff5.xls" --outdir "D:\out\"来转换biff5到xlsx，因为libreoffice可以绿色化运行的，可以直接打包应用程序的时候带上它。就是仅仅为了转换个文件，却打包了一个600M的软件不太实际😂
导出多种类型的合并单元格的excel
使用模板导出，这样不变的部分就可以节省一部分精力设置单元格格式（我是懒狗），当然直接poi一行一行去写也没问题。
关键代码：
try (
InputStream template = new ClassPathResource("excel/项目表导出模板.xlsx").getInputStream();
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).inMemory(true)
.registerWriteHandler(new MergeHandler(data))
.needHead(false)
.withTemplate(template).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
Map<String, Object> map = BeanUtil.beanToMap(mProject);
if (map.get("executionStartDate") != null) {
map.put("executionStartDate", new SimpleDateFormat("yyyy年MM月dd日").format(map.get("executionStartDate")));
}
excelWriter.fill(map, fillConfig, writeSheet);
excelWriter.fill(data, fillConfig, writeSheet);
List<List<String>> data2 = new ArrayList<>();
data2.add(List.of("月工资支出限额(元)", mProject.getMonthlySalaryLimit().toString()));
data2.add(Arrays.asList("社会保险", "购买人数(人)", null, "缴纳标准(元/人.月)", null, "预算金额(元/月)"));
data2.add(Arrays.asList(null, mProject.getInsuranceBuyersNum().toString(),
null, mProject.getInsurancePayStandard().toString(), null, mProject.getInsuranceBudgetAmount().toString()));
data2.add(Arrays.asList("公司管理费", "人均(元/月)", null, "项目利润合计(元/月)", null));
data2.add(Arrays.asList(null, mProject.getManagementFeePer().toString(), null, mProject.getTotalProjectProfit().toString(), null));
excelWriter.write(data2, writeSheet);
excelWriter.finish();
}
固定模板部分使用excelWriter.fill来填充，模板可以用一个list数据填充，多个list只能是：
- 要么模板上多个list不在一个区域，因为fill没法处理一个list写完后下面行紧跟着另一个list。
- 要么你把多个类型的list都用一个类型的list表示
- 要么在fill完后使用 excelWriter.write 来依次写入list，这个上面方案2是差不多的
我用的是fill完固定部分后excelWriter.write来接着写入剩下的
单元格合并以及样式处理注意点：
- 使用写入拦截器来处理合并和样式设置
WriteHandler有多种，最开始使用CellWriteHandler来处理，可是这个是每写入一个cell才调用，使用起来略显麻烦，而且写入一个单元格数据后再设置边框样式什么的会导致下一行的或下一列的写入数据覆盖掉之前的，导致部分边框缺失。最后选择继承WorkbookWriteHandler来处理，在工作簿所有操作完成后调用afterWorkbookDispose来处理合并和样式设置。 - 注意使用inMemory(true)，因为即使是使用CellWriteHandler也会出现要合并的区域或者要设置格式的单元格数据刷写到磁盘而导致没法处理报错的情况。所以要使用inMemory来全在内存处理。
- 需要注意添加的合并区域不能发生重叠，不然excel打开会报错提示文件样式有问题，自己代码上要注意，同时也要注意代码设置的不能和模板上的合并区域重叠，如果与模板上的合并区域发生重叠，最简单的方式是模板上那个区域取消合并，或者代码里用sheet.removeMergedRegion移除。
- 如果需要删除模板上空数据的行的处理：sheet.removeRow，然后sheet.shiftRows移动下面行整体上移。
- 设置合并单元格的边框的方法 RegionUtil.setBorderLeft,setBorderRight …
处理单元格合并以及样式处理的拦截器示例
public class MergeHandler implements WorkbookWriteHandler {
private final List<MProjectOperatingModeVO> data;
private CellStyle centerStyle;
private Workbook workbook;
private Sheet sheet;
private final int operatingDataLength;
public MergeHandler(List<MProjectOperatingModeVO> data) {
this.data = data;
this.operatingDataLength = data.size();
}
@Override
public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
if (this.workbook == null) {
this.workbook = writeWorkbookHolder.getWorkbook();
this.sheet = writeWorkbookHolder.getWorkbook().getSheetAt(0);
centerStyle = workbook.createCellStyle();
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
}
//合并 执行开始日期
merge(1, 1, 7, 8);
//合并 运营模式
mergeAndSetBorder(7, operatingDataLength + 8, 0, 0);
if (operatingDataLength != 0) {
int mergeRowEnd = 8;
int merge2Row = 8;
for (MProjectOperatingModeVO datum : data) {
if ("职务津贴".equals(datum.getMode())) {
mergeRowEnd++;
}
merge2Row++;
//合并 工资标准
if (merge2Row == 9) {
continue;
}
mergeAndSetBorder(merge2Row, merge2Row, 5, 8);
}
//合并 职务津贴
if (mergeRowEnd > 9) {
mergeAndSetBorder(9, mergeRowEnd, 1, 1);
}
}
//从 月工资支出开始 合并
short rowHeight = 700;
int row = 9 + operatingDataLength;
if (operatingDataLength == 0) {
sheet.removeRow(sheet.getRow(row));
sheet.shiftRows(row + 1, row + 5, -1);
List<CellRangeAddress> list= sheet.getMergedRegions();
//避免重叠合并区域，模板上这里是合并的
for (int i = 0; i < list.size(); i++) {
CellRangeAddress cellAddresses = list.get(i);
if("F10:I10".equals(cellAddresses.formatAsString())){
sheet.removeMergedRegion(i);
break;
}
}
}
sheet.getRow(row).setHeight(rowHeight);
sheet.getRow(row).getCell(0).setCellStyle(centerStyle);
setBorderStyle(new CellRangeAddress(row, row, 0, 0));
mergeAndSetBorder(row, row, 1, 8);
row++;
sheet.getRow(row).setHeight(rowHeight);
mergeAndSetBorder(row, row, 1, 2);
mergeAndSetBorder(row, row, 3, 4);
mergeAndSetBorder(row, row, 5, 8);
row++;
sheet.getRow(row).setHeight(rowHeight);
mergeAndSetBorder(row - 1, row, 0, 0);
mergeAndSetBorder(row, row, 1, 2);
mergeAndSetBorder(row, row, 3, 4);
mergeAndSetBorder(row, row, 5, 8);
row++;
sheet.getRow(row).setHeight(rowHeight);
mergeAndSetBorder(row, row, 1, 2);
mergeAndSetBorder(row, row, 3, 8);
row++;
sheet.getRow(row).setHeight(rowHeight);
mergeAndSetBorder(row - 1, row, 0, 0);
mergeAndSetBorder(row, row, 1, 2);
mergeAndSetBorder(row, row, 3, 8);
}
private void setBorderStyle(CellRangeAddress range) {
RegionUtil.setBorderLeft(BorderStyle.THIN, range, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, range, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, range, sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN, range, sheet);
}
private void merge(int firstRow, int lastRow, int firstCol, int lastCol) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(cellRangeAddress);
}
private void mergeAndSetBorder(int firstRow, int lastRow, int firstCol, int lastCol) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(cellRangeAddress);
sheet.getRow(firstRow).getCell(firstCol).setCellStyle(centerStyle);
setBorderStyle(cellRangeAddress);
}
}
Q.E.D.