Java利用EasyExcel实现复杂多级表头构建

无界猴

8个月前更新

037724

使用EasyExcel构建复杂多级表头是一个常见的需求，特别是在处理复杂的Excel报告时。EasyExcel是阿里巴巴开源的一个Java库，用于读写Excel文件，性能高效且易于使用。

下面是一个示例，展示如何使用EasyExcel来构建具有复杂多级表头的Excel文件。

1. 添加依赖

首先，确保你的项目中已经添加了EasyExcel的依赖。如果你使用的是Maven，可以在pom.xml文件中添加以下依赖：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version> <!-- 请根据需要选择合适的版本 -->
</dependency>

2. 创建数据模型

创建一个数据模型类，用于存储表格中的数据。

public class DemoData {
    private String category;
    private String item;
    private Integer quantity;
    private Double price;

    // Getters and Setters
}

3. 创建复杂表头的数据模型

由于EasyExcel通过注解的方式定义表头，对于复杂多级表头，需要自定义Head类。

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.Head;
import lombok.Data;

import java.util.List;

@Data
public class ComplexHead {

    @ExcelProperty({"一级表头1", "二级表头1-1"})
    private String category;

    @ExcelProperty({"一级表头1", "二级表头1-2"})
    private String item;

    @ExcelProperty({"一级表头2", "二级表头2-1"})
    private Integer quantity;

    @ExcelProperty({"一级表头2", "二级表头2-2"})
    private Double price;

    // 自定义Head类，用于多级表头
    public static class ComplexHeadList extends Head {
        private List<List<String>> headList;

        public ComplexHeadList(List<List<String>> headList) {
            this.headList = headList;
        }

        @Override
        public List<List<String>> getHeadList() {
            return headList;
        }
    }
}

4. 自定义写入处理器

为了生成复杂的多级表头，我们需要自定义一个写入处理器。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ComplexHeaderDemo {

    public static void main(String[] args) throws IOException {
        // 定义多级表头
        List<List<String>> headList = new ArrayList<>();
        List<String> head1 = new ArrayList<>();
        head1.add("一级表头1");
        List<String> head1_1 = new ArrayList<>();
        head1_1.add("二级表头1-1");
        List<String> head1_2 = new ArrayList<>();
        head1_2.add("二级表头1-2");

        List<String> head2 = new ArrayList<>();
        head2.add("一级表头2");
        List<String> head2_1 = new ArrayList<>();
        head2_1.add("二级表头2-1");
        List<String> head2_2 = new ArrayList<>();
        head2_2.add("二级表头2-2");

        headList.add(head1);
        headList.add(head1_1);
        headList.add(head1_2);
        headList.add(head2);
        headList.add(head2_1);
        headList.add(head2_2);

        // 数据
        List<DemoData> dataList = new ArrayList<>();
        // 添加一些数据到dataList中...

        // 写入Excel文件
        String fileName = "complex_header_demo.xlsx";
        try (FileOutputStream out = new FileOutputStream(fileName)) {
            EasyExcel.write(out, DemoData.class)
                    .registerWriteHandler(new ComplexHeaderWriteHandler(headList))
                    .sheet("Sheet1")
                    .doWrite(dataList);
        }
    }

    // 自定义写入处理器
    public static class ComplexHeaderWriteHandler extends AbstractCellWriteHandler {
        private final List<List<String>> headList;

        public ComplexHeaderWriteHandler(List<List<String>> headList) {
            this.headList = headList;
        }

        @Override
        public void afterCellDispose(AfterCellDisposeContext context) {
            if (context.getRelativeRowIndex() == 0) { // 只处理第一行表头
                Sheet sheet = context.getWriteSheet().getSheet();
                Row row = sheet.getRow(0);
                if (row == null) {
                    row = sheet.createRow(0);
                }

                for (int i = 0; i < headList.size(); i++) {
                    List<String> head = headList.get(i);
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        cell = row.createCell(i);
                    }
                    StringBuilder sb = new StringBuilder();
                    for (String h : head) {
                        sb.append(h).append("\n");
                    }
                    cell.setCellValue(sb.toString().trim());

                    // 设置单元格样式，使其内容换行显示
                    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                    cellStyle.setWrapText(true);
                    cell.setCellStyle(cellStyle);
                    sheet.autoSizeColumn(i);
                }
            }
        }
    }
}