easyExcel模板 动态单元格合并列

Question

easyExcel 模板填充 动态单元格填充后样式存在问题如下

这是模板文件

寻求有能力的技术解决问题

并且有一些生成的行高很窄不知道为什么？数据也混乱
代码如下

代码如下
1、
public Boolean edit(FurnitureContractRowReq contractRowReq) {
//        contractHeadService.update(new LambdaUpdateWrapper<FurnitureContractHead>()
//                .eq(FurnitureContractHead::getId, contractRowReq.getId())
//                .set(FurnitureContractHead::getTotal, contractRowReq.getTotal())
//                .set(FurnitureContractHead::getConcessionalRate, contractRowReq.getConcessionalRate())
//        );
        this.remove(new LambdaQueryWrapper<FurnitureContractRow>()
                .eq(FurnitureContractRow::getContractHeadId, contractRowReq.getId()));
        for (FurnitureContractRow row : contractRowReq.getRows()) {
            if (row.getType() == 2 && StringUtils.hasText(row.getColumn0())) {
                String[] imgs = row.getColumn0().split(",");
                if (imgs.length > 0 && !ObjectUtils.isEmpty(imgs[0])) row.setColumn0(imgs[0]);
                if (imgs.length > 1 && !ObjectUtils.isEmpty(imgs[1])) row.setColumn1(imgs[1]);
                if (imgs.length > 2 && !ObjectUtils.isEmpty(imgs[2])) row.setColumn2(imgs[2]);
                if (imgs.length > 3 && !ObjectUtils.isEmpty(imgs[3])) row.setColumn3(imgs[3]);
            }
            row.setId(null);
            row.setContractHeadId(contractRowReq.getId());
        }
        boolean b = this.saveBatch(contractRowReq.getRows());
        //生成Excel文件
        FurnitureContractHead byId = contractHeadService.getById(contractRowReq.getId());
        FurnitureSalesOrders salesOrders = ordersService.getById(byId.getSalesOrdersId());
        FurnitureSalesOrdersRemittanceLog ordersRemittanceLog = ordersRemittanceLogService.getOne(new LambdaQueryWrapper<FurnitureSalesOrdersRemittanceLog>()
                .eq(FurnitureSalesOrdersRemittanceLog::getSalesOrdersId, byId.getSalesOrdersId())
        );
        List<FurnitureContractRow> contractProductExcels = contractRowReq.getRows();
        String orderNumber = "";
//        File file = new File("/usr/local/app/temp/" + "contract_" + contractRowReq.getId() + ".xlsx");
        File file = new File("/Users/zhangzhenxing/Downloads/" + "contract_" + contractRowReq.getId() + ".xlsx");
        Set<CellRangeAddress> cellRangeAddresses = new HashSet<>();
        ExcelWriterBuilder write = EasyExcel.write(file);
        int index = 7;
        int index1 = 1;
        List<Map<String, Object>> data = new ArrayList<>();
        for (FurnitureContractRow contractProductExcel : contractProductExcels) {
            if (contractProductExcel.getType() == 0) {
                CellRangeAddress cellAddresses = new CellRangeAddress(index, index, 1, 6);
                cellRangeAddresses.add(cellAddresses);
//                write.registerWriteHandler(new MergeCellStrategyHandler(index, 0, 6, false));
                Map<String, Object> map = CollectionUtil.objectToMap(contractProductExcel);
                data.add(map);
                //图示
            } else if (contractProductExcel.getType() == 2) {
                CellRangeAddress cellAddresses = new CellRangeAddress(index, index, 1, 6);
                cellRangeAddresses.add(cellAddresses);
//                write.registerWriteHandler(new MergeCellStrategyHandler(index, 2, 6, false));
                Map<String, Object> map = new HashMap<>();
                if (StringUtils.hasText(contractProductExcel.getColumn0())) {
                    map.put("column0", imageCells(HttpUtil.createGet(contractProductExcel.getColumn0()).execute().bodyBytes()));
                }
                if (StringUtils.hasText(contractProductExcel.getColumn1())) {
                    map.put("column1", imageCells(HttpUtil.createGet(contractProductExcel.getColumn1()).execute().bodyBytes()));
                }
                if (StringUtils.hasText(contractProductExcel.getColumn2())) {
                    map.put("column2", imageCells(HttpUtil.createGet(contractProductExcel.getColumn2()).execute().bodyBytes()));
                }
                if (StringUtils.hasText(contractProductExcel.getColumn3())) {
                    map.put("column3", imageCells(HttpUtil.createGet(contractProductExcel.getColumn3()).execute().bodyBytes()));
                }
                data.add(map);
            } else {
//                write.registerWriteHandler(new MergeCellStrategyHandler(index, 8, 8, true));
                contractProductExcel.setSort(index1);
                Map<String, Object> map = CollectionUtil.objectToMap(contractProductExcel);
                if (StringUtils.hasText(contractProductExcel.getColumn1())) {
                    map.put("column1", imageCells(HttpUtil.createGet(contractProductExcel.getColumn1()).execute().bodyBytes()));
                }
                data.add(map);
                index1++;
            }
            index++;
        }
        try (ExcelWriter excelWriter = write
                .registerWriteHandler(new AddCellRangeWriteHandler(cellRangeAddresses))
//                .registerWriteHandler(new RowWriteHandler() {
//                    @Override
//                    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
//                        row.setHeight((short) 1600);
//                    }
//                })
                .withTemplate(new ClassPathResource("template/excel/orderContract.xlsx").getInputStream())
                .excelType(ExcelTypeEnum.XLSX)
                .build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet(0)
                    .sheetName("合同")
                    .build();
            List<WriteHandler> customWriteHandlerList = writeSheet.getCustomWriteHandlerList();
            //填充列表
//            Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
            excelWriter.fill(data, FillConfig.builder().forceNewRow(Boolean.TRUE).build(), writeSheet);
            //填充对象
            excelWriter.fill(Map.of("orderNumber", orderNumber,
                    "contacts", byId.getContacts(),
                    "telephone", byId.getPhone()
            ), writeSheet);
            excelWriter.finish();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        try {
            String s = aliYunOssUtil.writeOssBytes(Files.readAllBytes(Paths.get(file.getAbsolutePath())), UUID.randomUUID() + ".xlsx");
            System.out.println("s = " + s);
//            contractHeadService.update(new LambdaUpdateWrapper<FurnitureContractHead>()
//                    .eq(FurnitureContractHead::getId, contractRowReq.getId())
//                    .set(FurnitureContractHead::getFileUrl, s)
//            );
        } catch (Exception e) {
            e.printStackTrace();
        }
//        file.delete();
        return b;
    }
2、
private WriteCellData<Void> imageCells(byte[] bytes) {
        WriteCellData<Void> writeCellData = new WriteCellData<>();
        // 这里可以设置为 EMPTY 则代表不需要其他数据了
//        writeCellData.setType(CellDataTypeEnum.EMPTY);
        // 可以放入多个图片
        List<ImageData> imageDataList = new ArrayList<>();
        writeCellData.setImageDataList(imageDataList);
        ImageData imageData = new ImageData();
        imageDataList.add(imageData);
        imageData.setImage(bytes);
        // 图片类型
        //imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
        // 上 右 下 左边距 类似于 css 的 margin
        imageData.setTop(0);
        imageData.setRight(0);
        imageData.setBottom(0);
        imageData.setLeft(0);
        // relative表示相对于当前的单元格index。first是左上点，last是对角线的右下点，这样确定一个图片的位置和大小。
        // 例：0,0,0,0 表示在当前单元格大小，FirstRow：0,FirstColumn：0,LastRow：0,LastColumn：1 表示图片基于当前单元格，横向占据两个单元格，纵向占据两个单元格
        imageData.setRelativeFirstRowIndex(0);
        imageData.setRelativeFirstColumnIndex(0);
        imageData.setRelativeLastRowIndex(0);
        imageData.setRelativeLastColumnIndex(0);
        return writeCellData;
    }
3、
/**
 * 添加合并区Handler
 */
public class AddCellRangeWriteHandler extends AbstractMergeStrategy {

    private final Set<CellRangeAddress> rangeCellList;

    public AddCellRangeWriteHandler(Set<CellRangeAddress> rangeCellList) {
        this.rangeCellList = (rangeCellList == null) ? new HashSet<>() : rangeCellList;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if (CollectionUtils.isEmpty(rangeCellList)) {
            if (cell.getRowIndex() == 7) {
                for (CellRangeAddress item : rangeCellList) {
                    sheet.addMergedRegionUnsafe(item);
                }
            }
        }
    }
}
4、

Answer 1

️题主您好️，目前由我为您解答👇🏼
🧡参考谷歌智能助手，由吾为汝整理提供🧡

要解决您在使用 EasyExcel 模板时遇到的动态单元格合并和样式问题，我们需要确保以下几点：

1. 正确使用注解：在模型类中使用 @ExcelProperty 注解来指定列名。
2. 动态合并单元格：通过自定义策略来实现动态合并单元格。
3. 设置行高和列宽：确保在生成的 Excel 文件中设置适当的行高和列宽。
4. 数据填充：确保数据正确填充到相应的单元格中。

下面是一个示例代码，展示如何使用 EasyExcel 实现上述功能：

依赖配置

首先，确保您的 Maven 项目中包含 EasyExcel 的依赖：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

模型类

定义一个模型类，并使用 @ExcelProperty 注解来指定列名：

import com.alibaba.excel.annotation.ExcelProperty;

public class DemoData {
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    // Getters and Setters
}

自定义合并策略

创建一个自定义合并策略类：

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

public class CustomMergeStrategy extends AbstractRowWriteHandler {
    @Override
    public void afterRowDispose(Workbook workbook, Sheet sheet, Row row, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            return;
        }
        // Example: Merge cells in the first column for rows with the same value
        String currentValue = row.getCell(0).getStringCellValue();
        int startRow = row.getRowNum();
        int endRow = startRow;
        while (endRow < sheet.getLastRowNum() && sheet.getRow(endRow + 1).getCell(0).getStringCellValue().equals(currentValue)) {
            endRow++;
        }
        if (startRow != endRow) {
            sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, 0, 0));
        }
    }
}

写入 Excel 文件

使用 EasyExcel 写入数据，并应用自定义合并策略：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;

import java.util.ArrayList;
import java.util.List;

public class EasyExcelExample {
    public static void main(String[] args) {
        String fileName = "demo.xlsx";
        List<DemoData> data = new ArrayList<>();
        data.add(new DemoData("Alice", 30));
        data.add(new DemoData("Alice", 25));
        data.add(new DemoData("Bob", 28));
        data.add(new DemoData("Charlie", 35));

        ExcelWriterBuilder writerBuilder = EasyExcel.write(fileName, DemoData.class);
        writerBuilder.registerWriteHandler(new CustomMergeStrategy());
        writerBuilder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()); // Auto-adjust column width
        writerBuilder.sheet("Sheet1").doWrite(data);
    }
}

注意事项

1. 数据一致性：确保数据源中的数据一致，以便正确合并单元格。
2. 样式调整：如果需要更多样式调整，可以进一步自定义 WriteHandler 或使用 WriteCellStyle。
3. 调试：如果遇到样式问题，可以通过调试查看生成的 Excel 文件，检查是否有未预期的合并或样式应用。

以上代码展示了如何使用 EasyExcel 进行动态单元格合并、数据填充以及样式调整。希望这能帮助您解决问题！