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

使用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>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version> <!-- 请根据需要选择合适的版本 -->
</dependency>
<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
}
public class DemoData {
    private String category;
    private String item;
    private Integer quantity;
    private Double price;

    // Getters and Setters
}
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;
        }
    }
}
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;
        }
    }
}
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);
                }
            }
        }
    }
}
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);
                }
            }
        }
    }
}
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);
                }
            }
        }
    }
}

5. 运行代码

运行上述代码，将生成一个名为complex_header_demo.xlsx的Excel文件，其中包含复杂的多级表头。

注意事项

1. 样式处理：在示例中，我们简单地设置了单元格样式以使其内容换行显示。如果需要更复杂的样式，可以进一步定制CellStyle。
2. 数据填充：确保在dataList中添加实际数据，以便在生成的Excel文件中查看完整表格。
3. 性能：对于大数据量的处理，请确保优化代码以提高性能，例如使用分页读取或写入。

通过上述步骤，你可以使用EasyExcel在Java中构建具有复杂多级表头的Excel文件。