需求
一部分泵站格式固定,一部分泵站支持用户配置。导出格式一致。
思路
- 首先用悟耘信息的 easypoi 的模板导出工具将整个固定的部分生成好。
- 获取生成好的sheet进行扩展。
- 先将二级表头和三级表头生成好,在特定位置生成单元格,格式复制对应已经生成好的单元格。
- 循环填充具体数据。
- 标题,备注,负责人等单元格合并,以完整格式。
效果
难点
1. 业务层面
动态列的数据组装,数据结构和数据处理等逻辑。
2. 合并单元格后边框问题
合并没有边框的单元格会导致失去边框。
解决办法:
为区域添加边框
CellRangeAddress brodercell = new CellRangeAddress(30,35, 1, 7);
RegionUtil.setBorderBottom(BorderStyle.MEDIUM, brodercell, sheet);
RegionUtil.setBorderLeft(BorderStyle.MEDIUM, brodercell, sheet);
RegionUtil.setBorderRight(BorderStyle.MEDIUM, brodercell, sheet);
RegionUtil.setBorderTop(BorderStyle.MEDIUM, brodercell, sheet);
为单元格设置公式,列号为字母ABC
row.getCell(7).setCellFormula("SUM(H6:H29)");
设置单元格公式,需要字母列号。
解决办法:
将列索引转为字母
/**
* 获取一列对应的字母。例如:ColumnNum=1,则返回值为A 列号转字母
*/
public static String getColumnName(int columnNum) {
int first;
int last;
String result = "";
if (columnNum > 256){
columnNum = 256;
}
first = columnNum / 27;
last = columnNum - (first * 26);
if (first > 0){
result = String.valueOf((char) (first + 64));
}
if (last > 0){
result = result + (char) (last + 64);
}
return result;
}
合并单元格,无法合并已经被合并的单元格
报错:
POI Cannot add merged region XXX to sheet because it overlaps with an existing merged region
解决办法:
取消某个范围的合并,然后再次合并。
/**
* 取消多个合并单元格
*
* @param sheet
* @param startRow 开始行号
* @param endRow 结束行号
* @param startColumn 开始列号
* @param endColumn 结束列号
*/
public static void removeMerged(Sheet sheet, Integer startRow, Integer endRow, Integer startColumn, Integer endColumn) {
if(startRow==null){
startRow= sheet.getFirstRowNum();
}
if(endRow==null){
endRow= sheet.getLastRowNum();
}
//获取所有的单元格
int sheetMergeCount = sheet.getNumMergedRegions();
//用于保存要移除的那个合并单元格序号
List<Integer> indexList = new ArrayList<>();
for (int i = 0; i < sheetMergeCount; i++) {
//获取第i个单元格
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (startRow <= firstRow && endRow >= lastRow && startColumn <= firstColumn && endColumn >= lastColumn) {
indexList.add(i);
}
}
sheet.removeMergedRegions(indexList);
}
最后,代码写的跟坨屎一样,也不想整理了。抽象出来做个工具也不可能,完全是个定制开发。
private void extensionCell(Sheet sheet, WaterplantsrunrecordVO result) {
if (CollUtil.isEmpty(result.getExtensionStations())) {
return;
}
int extensionSize = result.getData().size();
List<List<String>> extensionData = result.getData().subList(25, extensionSize);
Map<String, List<Waterplantsrunrecord>> groupByStationId = result.getExtensionStations().stream().collect(Collectors.groupingBy(Waterplantsrunrecord::getStationId, LinkedHashMap::new, Collectors.toList()));
// 站点名单元格,用于复制样式
Row stationTitleRow = sheet.getRow(2);
Cell stationCell = stationTitleRow.getCell(20);
// 测点名称行
Row pointTitleRow = sheet.getRow(3);
Cell pointCell = pointTitleRow.getCell(20);
Cell preCell = pointTitleRow.getCell(24);
// 数据行,用于复制格式
Cell dataCellStyle = sheet.getRow(5).getCell(23);
int baseColumn = 25;
for (Map.Entry<String, List<Waterplantsrunrecord>> entry : groupByStationId.entrySet()) {
Cell createStationCell = stationTitleRow.createCell(baseColumn, CellType.STRING);
createStationCell.setCellStyle(stationCell.getCellStyle());
createStationCell.setCellValue(entry.getValue().get(0).getStationName());
int size = entry.getValue().size();
for (int i = 0; i <= size - 1; i++) {
Cell createPointCell = pointTitleRow.createCell(baseColumn + i, CellType.STRING);
createPointCell.setCellStyle(pointCell.getCellStyle());
createPointCell.setCellValue(entry.getValue().get(i).getPointAlias());
CellRangeAddress pointRegion = new CellRangeAddress(3, 4, baseColumn + i, baseColumn + i);
sheet.addMergedRegion(pointRegion);
sheet.setColumnWidth(baseColumn + i, sheet.getColumnWidth(pointCell.getColumnIndex()));
RegionUtil.setBorderBottom(1, pointRegion, sheet);
RegionUtil.setBorderLeft(1, pointRegion, sheet);
RegionUtil.setBorderRight(1, pointRegion, sheet);
RegionUtil.setBorderTop(1, pointRegion, sheet);
// 创建数据单元格
for (int r = 5; r <= 28; r++) {
Row dataRow = sheet.getRow(r);
List<String> data = extensionData.get(0);
Cell createDataCell = dataRow.createCell(baseColumn + i, CellType.NUMERIC);
createDataCell.setCellStyle(dataCellStyle.getCellStyle());
try {
createDataCell.setCellValue(Double.parseDouble(data.get(r - 5)));
} catch (Exception ignore) {
}
}
extensionData = extensionData.subList(1, extensionData.size());
}
// 创建设定压力单元格
Cell createPreCell = pointTitleRow.createCell(baseColumn + size, CellType.STRING);
createPreCell.setCellStyle(preCell.getCellStyle());
createPreCell.setCellValue(preCell.getStringCellValue());
CellRangeAddress preRegion = new CellRangeAddress(3, 4, baseColumn + size, baseColumn + size);
sheet.addMergedRegion(preRegion);
sheet.setColumnWidth(baseColumn + size, sheet.getColumnWidth(preCell.getColumnIndex()));
RegionUtil.setBorderBottom(1, preRegion, sheet);
RegionUtil.setBorderLeft(1, preRegion, sheet);
RegionUtil.setBorderRight(1, preRegion, sheet);
RegionUtil.setBorderTop(1, preRegion, sheet);
// 合并站点名单元格
CellRangeAddress stationRegion = new CellRangeAddress(2, 2, baseColumn, baseColumn + size);
sheet.addMergedRegion(stationRegion);
RegionUtil.setBorderBottom(1, stationRegion, sheet);
RegionUtil.setBorderLeft(1, stationRegion, sheet);
RegionUtil.setBorderRight(1, stationRegion, sheet);
RegionUtil.setBorderTop(1, stationRegion, sheet);
// 创建数据单元格---设定压力
for (int r = 5; r <= 28; r++) {
Row dataRow = sheet.getRow(r);
List<String> data = extensionData.get(0);
Cell createDataCell = dataRow.createCell(baseColumn + size, CellType.NUMERIC);
createDataCell.setCellStyle(dataCellStyle.getCellStyle());
try {
createDataCell.setCellValue(Double.parseDouble(data.get(r - 5)));
} catch (Exception ignore) {
}
}
extensionData = extensionData.subList(1, extensionData.size());
baseColumn = baseColumn + size + 1;
}
// 创建合计单元格
List<Waterplantsrunrecord> tmp = Lists.newArrayList();
List<String> tmpStations = Lists.newArrayList();
result.getExtensionStations().forEach(t -> {
if (CollUtil.isNotEmpty(tmp) && !tmpStations.contains(t.getStationId())) {
// 添加占位对象占据设定压力
tmp.add(new Waterplantsrunrecord());
}
tmp.add(t);
tmpStations.add(t.getStationId());
});
// 添加占位对象占据设定压力
tmp.add(new Waterplantsrunrecord());
Cell sumCellStyle = sheet.getRow(29).getCell(22);
Cell cumCellStyle = sheet.getRow(5).getCell(20);
for (int i = 25; i <= extensionSize - 1; i++) {
Cell cell = sheet.getRow(29).createCell(i, CellType.NUMERIC);
cell.setCellStyle(sumCellStyle.getCellStyle());
if (StrUtil.isNotBlank(tmp.get(i - 25).getFormula()) && tmp.get(i - 25).getFormula().contains("Cum")) {
String columnName = ExportExcelUtil.getColumnName(i + 1);
cell.setCellFormula(String.format("SUM(%s6:%s29)", columnName, columnName));
for (int r = 5; r <= 28; r++) {
sheet.getRow(r).getCell(i).setCellStyle(cumCellStyle.getCellStyle());
}
}
}
// 最后做一些收尾合并单元格
ExportExcelUtil.removeMerged(sheet, 0, 0, 0, 24);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, baseColumn - 1));
ExportExcelUtil.removeMerged(sheet, 1, 1, 0, 24);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, baseColumn - 1));
ExportExcelUtil.removeMerged(sheet, 30, 30, 7, 24);
CellRangeAddress beizhu = new CellRangeAddress(30, 30, 7, baseColumn - 1);
sheet.addMergedRegion(beizhu);
ExportExcelUtil.removeMerged(sheet, 31, 32, 7, 24);
CellRangeAddress remark = new CellRangeAddress(31, 32, 7, baseColumn - 1);
sheet.addMergedRegion(remark);
ExportExcelUtil.removeMerged(sheet, 33, 33, 9, 24);
CellRangeAddress undertakePerson = new CellRangeAddress(33, 33, 9, baseColumn - 1);
sheet.addMergedRegion(undertakePerson);
for (CellRangeAddress range : Arrays.asList(beizhu, remark, undertakePerson)) {
RegionUtil.setBorderBottom(1, range, sheet);
RegionUtil.setBorderLeft(1, range, sheet);
RegionUtil.setBorderRight(1, range, sheet);
RegionUtil.setBorderTop(1, range, sheet);
}
}