EasyExcel复杂表头读写，合并表头读写

 

JAVA版本：1.8.0_321，所需的maven依赖：

        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>

        <!-- lombok 非必要 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
            <scope>provided</scope>
        </dependency>

        <!-- fastjson非必要 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.70</version>
        </dependency>

 

 

1，读取复杂表头的Excel内容：

最简单的方法是，通过@ExcelProperty(index = 0) 指定实体类字段对应的列序号，读取的时候通过 .headRowNumber(2)设置需要跳过的表头行数。

Excel示例：

 

实体类：

import com.alibaba.excel.annotation.ExcelProperty;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelDTOStudent {

    @ExcelProperty(index = 0)
    private String name;

    @ExcelProperty(index = 1)
    private String gener;

    @ExcelProperty(index = 2)
    private String scoreChinese;

    @ExcelProperty(index = 3)
    private String scoreMath;

    @ExcelProperty(index = 4)
    private String levelChinese;

    @ExcelProperty(index = 5)
    private String levelMath;

}

EasyExcel监听器：

import java.util.List;
import java.util.Map;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import lombok.AllArgsConstructor;

@AllArgsConstructor
public class EasyExcelStudentListener extends AnalysisEventListener<ExcelDTOStudent> {

    private List<ExcelDTOStudent> dataList;

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {

        // 需要处理表头的可以在这里处理
        for (Integer key : headMap.keySet()) {
            System.out.print(key + ":" + headMap.get(key) + "\t");
        }
        System.out.println();
    }

    @Override
    public void invoke(ExcelDTOStudent data, AnalysisContext context) {
        dataList.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

}

读取Excel测试类：

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

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;

public class EasyExcelTestObj {

    public static void main(String[] args) {
        testStudentRead("C:/Users/Chao/Desktop/work/easyExcel/student4Read.xlsx");
    }

    public static void testStudentRead(String targetFilePath) {

        List<ExcelDTOStudent> dataList = new ArrayList<>();

        // 读取excel
        EasyExcel.read(targetFilePath, ExcelDTOStudent.class, new EasyExcelStudentListener(dataList)) //
                .sheet() //
                .headRowNumber(2) // 跳过表头
                .doRead(); //

        // 输出读到的内容
        for (ExcelDTOStudent dto : dataList) {
            System.out.println(JSON.toJSONString(dto));
        }
    }

}

 

监听器打印的表头，和读取的数据：

 

 

 

 

 

2，写复杂表头

方法1，写入excel时单独调用 .head()方法传入表头，EasyExcel会自动合并相同的内容的单元格。

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

import com.alibaba.excel.EasyExcel;

public class EasyExcelTestObj {

    public static void main(String[] args) {
        testStudentWrite("C:/Users/Chao/Desktop/work/easyExcel/student4Write.xlsx");
    }

    public static void testStudentWrite(String targetFilePath) {

        List<ExcelDTOStudent> dataList = new ArrayList<>();
        dataList.add(new ExcelDTOStudent("张三", "男", "90", "75", "优", "良"));
        dataList.add(new ExcelDTOStudent("李四", "女", "60", "45", "中", "差"));

        EasyExcel.write(targetFilePath, ExcelDTOStudent.class) //
                .sheet() // 
                .head(generateHead()) // 单独设置表头
                .doWrite(dataList); //

    }

    public static List<List<String>> generateHead() {

        List<List<String>> headList2D = new ArrayList<>();

        headList2D.add(Arrays.asList("姓名", "姓名")); // 一列
        headList2D.add(Arrays.asList("年龄", "年龄"));
        headList2D.add(Arrays.asList("分数", "语文"));
        headList2D.add(Arrays.asList("分数", "数学"));
        headList2D.add(Arrays.asList("评级", "语文"));
        headList2D.add(Arrays.asList("评级", "数学"));

        return headList2D;
    }

}

 

方法2，通过@ExcelProperty(value = { "姓名", "姓名" })指定实体类字段对应的表头，EasyExcel会自动合并相同的内容的单元格。

修改后的实体类：

import com.alibaba.excel.annotation.ExcelProperty;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelDTOStudent {

    @ExcelProperty(value = { "姓名", "姓名" }, index = 0)
    private String name;

    @ExcelProperty(value = { "性别", "性别" }, index = 1)
    private String gener;

    @ExcelProperty(value = { "分数", "语文" }, index = 2)
    private String scoreChinese;

    @ExcelProperty(value = { "分数", "数学" }, index = 3)
    private String scoreMath;

    @ExcelProperty(value = { "评级", "语文" }, index = 4)
    private String levelChinese;

    @ExcelProperty(value = { "评级", "数学" }, index = 5)
    private String levelMath;

}

测试类：

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

import com.alibaba.excel.EasyExcel;

public class EasyExcelTestObj {

    public static void main(String[] args) {
        testStudentWrite("C:/Users/Chao/Desktop/work/easyExcel/student4Write.xlsx");
    }

    public static void testStudentWrite(String targetFilePath) {

        List<ExcelDTOStudent> dataList = new ArrayList<>();
        dataList.add(new ExcelDTOStudent("张三", "男", "90", "75", "优", "良"));
        dataList.add(new ExcelDTOStudent("李四", "女", "60", "45", "中", "差"));

        EasyExcel.write(targetFilePath, ExcelDTOStudent.class) //
                .sheet() // 
//                .head(generateHead()) // 单独设置表头
                .doWrite(dataList); //

    }

}

测试结果：