java poi 动态列 复杂导出

需求

一部分泵站格式固定，一部分泵站支持用户配置。导出格式一致。

思路

1. 首先用悟耘信息的 easypoi 的模板导出工具将整个固定的部分生成好。
2. 获取生成好的sheet进行扩展。
3. 先将二级表头和三级表头生成好，在特定位置生成单元格，格式复制对应已经生成好的单元格。
4. 循环填充具体数据。
5. 标题，备注，负责人等单元格合并，以完整格式。

效果

难点

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);
        }
    }