java的EasyExcel导出表头不固定、表头宽度自适应、根据某列进行合并行单元格

1、先看效果图

 

2、表头自适应类

根据自己的业务需求来写，下面只是个例子

public class RwhzCustemhandler extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    //the maximum column width in Excel is 255 characters

    public RwhzCustemhandler() {
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            int columnWidth = cell.getStringCellValue().getBytes().length;
            if (columnWidth > MAX_COLUMN_WIDTH) {
                columnWidth = MAX_COLUMN_WIDTH;
            } else {
                if (cell.getColumnIndex() == 1) {
                    columnWidth = columnWidth + 10;
                } else {
                    columnWidth = columnWidth + 3;
                }

            }
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }
}

 

3、根据某些列来合并相同内容的行单元格类

public class RwhzMergeCell implements CellWriteHandler {

    //需要哪些列的行进行合并
    private int[] mergeColumnIndex;
    //从哪一行开始，表头如果是只有一行，数据从第二行开始，即索引1开始
    private int mergeRowIndex;

    public RwhzMergeCell() {
    }

    public RwhzMergeCell(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            for (int columnIndex : mergeColumnIndex) {
                if (curColIndex == columnIndex) {
                    //符合条件，调用自定义合并函数
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行当前列的单元格的值，可能是String类型，也可能是数值
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        //获取当前列的上一行单元格
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        //获取当前列的上一行单元格的值
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        if (preData.equals(curData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }

}

 

4、API接口方法

@GetMapping("/exportExcel")
    public void exportExcel(@RequestParam(value = "ztId") int ztId,
                            @RequestParam(value = "rwszId") int rwszId,
                            @RequestParam(value = "bmId", required = false, defaultValue = "") Integer bmId,
                            @RequestParam(value = "qjszId") int qjszId,
                            @RequestParam(value = "sbzt", required = false, defaultValue = "") Boolean sbzt,
                            @RequestParam(value = "wsjbxs") boolean wsjbxs,
                            @RequestParam(value = "wcqk") boolean wcqk,
                            HttpServletResponse response) throws IOException {

        //获取内容集合
        List<RwhzView> rwhzViews = getRwhzViews(ztId, rwszId, bmId, qjszId, sbzt, wsjbxs, wcqk);
        //获取表头集合
        List<RwhzHead> heads = setRwhzHeads(rwszId);
        response.setHeader("Content-Disposition", "attachment; filename=rwhz.xls");
        // 响应类型,编码
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        //因为我业务的表头是不固定的，而且顺序也是按业务规则排序的，可以随缘改变表头的位置，我要合并的列是“工作内容”表头对应的数据，一下是找到“工作内容”所处的位置index
        int index = 0;
        for (int i = 0; i < heads.size(); i++) {
            if ("工作内容".equals(heads.get(i).getXmmc())) {
                index = i;
                break;
            }
        }
        //因为表头需要额外加上一列序号、任务、期间，那么此时工作内容的位置应该往后移3，工作内容相同的需要也相同，序号也需要合并相同的
        int[] mergeColumnIndex = {0, index + 3};
        //自定义获取需要导出的数据，根据自己的业务需求来
        List<List<Object>> exportData = getExportData(heads, rwhzViews, index);
        EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(new RwhzCustemhandler())
                .registerWriteHandler(new RwhzMergeCell(1, mergeColumnIndex))
                .sheet("任务汇总导出模板").head(getExcelHeader(heads)).doWrite(exportData);
    }

private List<List<String>> getExcelHeader(List<RwhzHead> heads) {//...}

 

5、重要的是那两个类

不确定表头主要是head(List<List<String>>).doWrite( List<List<Object>>)

（1）对于head的List<List<String>>

外面的List的每一个元素List<String>代表每一列表头，里面的List的每一个元素String代表每一行的表头，比如

　　　List<List<String>>heads=new ArrayList<>();
     List<String> list1 = Arrays.asList("AAA", "aaa");
     List<String> list2 = Arrays.asList("AAA", "bbb");
     List<String> list3 = Arrays.asList("AAA", "ccc");
     heads.add(list1 );
     heads.add(list2 );
     heads.add(list3 );

 形成的表头如下图所示

 

图片画的不够标准，大概是那样。

（2）对于数据的List<List<Object>>

外面的List的每一个元素List<Object>代表一行数据，里面List的每一个元素Object代表当前行每一列的数据。

 

合并单元格参考网址：https://blog.csdn.net/weixin_50067580/article/details/111559637