Spring利用zouzhiy-excel实现自定义表头导出

zouzhiy-excel 是一款 Excel 导入导出的轻量级工具。对 POI 的接口做了一层封装，使导入导出更加简便快捷。

    io.github.zouzhiy
    zouzhiy-excel-boot-starter
    1.1.1

• 首先创建业务对象

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ExcelClass(rowHeadWrite = CustomRowHeadWrite.class)
public class HeadVO {

    private String name;

    private String title;

    @ExcelField(colspan = 3)
    private ItemVo item;

}

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ItemVo {

    private String firstName;

    private String lastName;

    private Integer age;
}

rowHeadWrite 指定自定义标题写入的实现

• 创建ItemVO的CellHandler

@Component
public class ItemCellHandler implements CellHandler {


    @Override
    public Class getJavaType() {
        return ItemVo.class;
    }

    @Override
    public ExcelType getExcelType() {
        return ExcelType.STRING;
    }

    @Override
    public ItemVo read(SheetContext sheetContext, ExcelFieldConfig excelFieldConfig, CellResultSet cellResultSet) {
        if (cellResultSet == null) {
            return null;
        }

        if (cellResultSet.isNone()) {
            return null;
        }
        List cellResultList = cellResultSet.getCellResultListList().get(0);
        CellResult cellResultFirstName = cellResultList.size() > 0 ? cellResultList.get(0) : CellResult.none();
        CellResult cellResultSecondName = cellResultList.size() > 1 ? cellResultList.get(1) : CellResult.none();
        CellResult cellResultAge = cellResultList.size() > 2 ? cellResultList.get(2) : CellResult.none();

        CellHandler stringStringHandler = sheetContext.getConfiguration().getCellHandlerRegistry().getCellHandler(StringStringHandler.class);

        String firstName = stringStringHandler.read(sheetContext, excelFieldConfig, CellResultSet.firstCellResult(cellResultFirstName));
        String secondName = stringStringHandler.read(sheetContext, excelFieldConfig, CellResultSet.firstCellResult(cellResultSecondName));
        CellHandler integerCellHandler = sheetContext.getConfiguration().getCellHandlerRegistry().getCellHandler(IntegerNumberHandler.class);
        Integer age = integerCellHandler.read(sheetContext, excelFieldConfig, CellResultSet.firstCellResult(cellResultAge));

        return ItemVo.builder().firstName(firstName).lastName(secondName).age(age).build();
    }

    @Override
    public void write(RowContext rowContext, Integer columnIndex, ExcelFieldConfig excelFieldConfig, ItemVo value) {
        if (value == null) {
            return;
        }
        Row row = rowContext.getRowList().get(0);
        Cell cellFirstName = row.createCell(columnIndex);
        Cell cellLastName = row.createCell(columnIndex + 1);
        Cell cellAge = row.createCell(columnIndex + 2);
        if (value.getFirstName() != null) {
            cellFirstName.setCellValue(value.getFirstName());
        }
        if (value.getLastName() != null) {
            cellLastName.setCellValue(value.getLastName());
        }
        if (value.getAge() != null) {
            cellAge.setCellValue(value.getAge());
        }

        SheetContext sheetContext = rowContext.getSheetContext();
        CellStyle cellStyle = sheetContext.getDataCellStyle(excelFieldConfig, this.getDefaultExcelFormat());
        cellFirstName.setCellStyle(cellStyle);
        cellLastName.setCellStyle(cellStyle);
        cellAge.setCellStyle(cellStyle);

        int rowspan = rowContext.getRowspan();
        int rowIndex = row.getRowNum();
        RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex + rowspan - 1, columnIndex, columnIndex);
        RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex + rowspan - 1, columnIndex + 1, columnIndex + 1);
        RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex + rowspan - 1, columnIndex + 2, columnIndex + 2);
    }
}

• 创建CustomRowHeadWrite

@Component
public class CustomRowHeadWrite implements RowHeadWrite {

    @Override
    public int write(SheetContext sheetContext, List dataList) {
        ExcelClassConfig excelClassConfig = sheetContext.getExcelClassConfig();
        List itemList = excelClassConfig.getItemList();

        SheetParameter sheetParameter = sheetContext.getSheetParameter();
        int titleRowIndex = sheetParameter.getHeadRowStartIndex();
        int headColumnIndex = sheetParameter.getHeadColumnStartIndex();
        Row row = this.createRow(sheetContext, titleRowIndex);

        RowContext rowContext = new DefaultRowContext(sheetContext, dataList, Collections.singletonList(row), 2);
        CellHandler headHandler = sheetContext.getConfiguration()
                .getCellHandlerRegistry()
                .getCellHandler(HeadStringHandler.class);

        int curColumnIndex = headColumnIndex;
        for (ExcelFieldConfig excelFieldConfig : itemList) {
            String propertyName = excelFieldConfig.getPropertyName();
            if ("item".equals(propertyName)) {
                this.writeItemHead(sheetContext, excelFieldConfig, row, curColumnIndex);
            } else {
                headHandler.write(rowContext, curColumnIndex, excelFieldConfig, excelFieldConfig.getTitle());
            }
            curColumnIndex += excelFieldConfig.getColspan();
        }
        return 1;
    }

    private Row createRow(SheetContext sheetContext, int rowIndex) {
        return sheetContext.getSheet().createRow(rowIndex);
    }

    private void writeItemHead(SheetContext sheetContext, ExcelFieldConfig excelFieldConfig, Row row, int columnIndex) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue("item");

        CellStyle cellStyle = sheetContext.getHeadCellStyle(excelFieldConfig);
        cell.setCellStyle(cellStyle);

        Row rowSecond = this.createRow(sheetContext, row.getRowNum() + 1);
        Cell cell1 = rowSecond.createCell(columnIndex);
        Cell cell2 = rowSecond.createCell(columnIndex + 1);
        Cell cell3 = rowSecond.createCell(columnIndex + 2);

        int colspan = excelFieldConfig.getColspan();
        int rowIndex = row.getRowNum();
        RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex, columnIndex, columnIndex + colspan - 1);

        cell1.setCellValue("firstName");
        cell2.setCellValue("lastName");
        cell3.setCellValue("age");

        cell1.setCellStyle(cellStyle);
        cell2.setCellStyle(cellStyle);
        cell3.setCellStyle(cellStyle);
    }
}

• 接下来我们在 Controller 中添加一个接口，用于导出列表Excel，具体代码如下：

@RestController
@RequestMapping("head-write")
public class HeadWriteExportController {

    @Resource
    private ZouzhiyExcelFactory zouzhiyExcelFactory;

    @GetMapping("list/export")
    public void exportList(HttpServletResponse response) {
        List voList = this.listVo();

        response.addHeader("Content-Disposition"
                , "attachment; filename*=utf-8''" + URLEncoder.encode("列表信息（自定义表头）.xlsx", StandardCharsets.UTF_8.name()));
        zouzhiyExcelFactory
                .write(response.getOutputStream())
                .sheet()
                .title("列表信息（自定义表头）")
                .titleRowStartIndex(0)
                .headRowStartIndex(1)
                .dataRowStartIndex(3)
                .write(voList, HeadVO.class);
    }

    private final Random random = new Random(System.currentTimeMillis());

    private List listVo() {
        List demoVoList = new ArrayList<>(5);
        for (int i = 0; i < 5; i++) {
            HeadVO demoVo = HeadVO.builder()
                    .name("name-" + random.nextInt(900))
                    .title("title-" + random.nextInt(111))
                    .item(getItemVo())
                    .build();
            demoVoList.add(demoVo);
        }
        return demoVoList;
    }

    private ItemVo getItemVo() {
        return ItemVo
                .builder()
                .firstName("firstName-" + random.nextDouble())
                .lastName("lastName-" + random.nextDouble())
                .age(random.nextInt(100))
                .build();
    }
}

• 通过浏览器访问： http://localhost:8080/head-write/list/export。下载附件。导出结果如下：
  

复杂的功能通过简单的几行代码就实现了。

项目源码地址： https://github.com/zouzhiy/zouzhiy-excel
国内镜像地址： https://gitee.com/zouzhiy/zouzhiy-excel