EasyExcel（环境搭建以及常用写入操作） - BlinkNest-A website that introduces electronic products

文章目录

EasyExcel环境搭建
EasyExcel写

EasyExcel环境搭建

1.创建模块 easyexcel-demo

CleanShot 2024-10-30 at 14.43.41@2x

2.引入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
    </parent>

    <groupId>com.sunxiansheng</groupId>
    <artifactId>easyexcel-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>
</project>

3.启动类创建

package com.sunxiansheng;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * Description: 启动类
 *
 * @Author sun
 * @Create 2024/10/29 11:19
 * @Version 1.0
 */
@SpringBootApplication
public class ExcelApplication {

    public static void main(String[] args) {
        SpringApplication.run(ExcelApplication.class, args);
    }
}

EasyExcel写

1.最简单的写入

1.模板

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @Builder
    public static class Write01 {
        @ExcelProperty(value = "用户编号")
        private Integer userId;
        @ExcelProperty(value = "姓名")
        private String userName;
        @ExcelProperty(value = "性别")
        private String gender;
        @ExcelProperty(value = "工资")
        private Double salary;
        @ExcelProperty(value = "入职时间")
        private Date hireDate;
    }

2.方法

    /**
     * 最简单的写入
     */
    @Test
    public void testWrite01() {
        String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write01.xlsx";
        // 1.构建write，指定写入的文件路径
        ExcelWriter excelWriter = EasyExcel.write(fileName)
                .build();
        // 2.构建sheet，指定sheetName和模板
        WriteSheet writeSheet = EasyExcel.writerSheet()
                .sheetName("用户信息")
                .head(Write01.class)
                .build();
        // 3.构造数据
        List<Write01> list = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Write01 user = Write01.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "女" : "男")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            list.add(user);
        }
        // 4.write向sheet中写入数据
        excelWriter.write(list, writeSheet);
        // 5.关闭流
        excelWriter.finish();
    }

3.结果 Write01.xlsx

CleanShot 2024-10-30 at 14.58.58@2x

2.指定字段不写入Excel

1.模板

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @Builder
    public static class Write02 {
        @ExcelProperty(value = "用户编号")
        private Integer userId;
        @ExcelProperty(value = "姓名")
        private String userName;
        @ExcelProperty(value = "性别")
        private String gender;
        @ExcelProperty(value = "工资")
        private Double salary;
        @ExcelProperty(value = "入职时间")
        private Date hireDate;
    }

2.方法

CleanShot 2024-10-30 at 15.03.32@2x

    /**
     * 指定字段不写入excel
     */
    @Test
    public void testWrite02() {
        String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write02.xlsx";
        // 1.构建write，指定写入的文件路径
        ExcelWriter excelWriter = EasyExcel.write(fileName)
                .build();
        // 2.构建sheet，指定sheetName和模板
        WriteSheet writeSheet = EasyExcel.writerSheet()
                .sheetName("用户信息")
                .head(Write02.class)
                .excludeColumnFieldNames(Arrays.asList("salary", "hireDate")) // 排除字段
                .build();
        // 3.构造数据
        List<Write02> list = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Write02 user = Write02.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "女" : "男")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            list.add(user);
        }
        // 4.write向sheet中写入数据
        excelWriter.write(list, writeSheet);
        // 5.关闭流
        excelWriter.finish();
    }

3.结果 Write02.xlsx

CleanShot 2024-10-30 at 15.04.08@2x

3.指定字段写入excel

1.模板

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @Builder
    public static class Write03 {
        @ExcelProperty(value = "用户编号")
        private Integer userId;
        @ExcelProperty(value = "姓名")
        private String userName;
        @ExcelProperty(value = "性别")
        private String gender;
        @ExcelProperty(value = "工资")
        private Double salary;
        @ExcelProperty(value = "入职时间")
        private Date hireDate;
    }

2.方法

/**
 * 指定字段写入excel
 */
@Test
public void testWrite03() {
    String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write03.xlsx";
    // 1.构建write，指定写入的文件路径
    ExcelWriter excelWriter = EasyExcel.write(fileName)
            .build();
    // 2.构建sheet，指定sheetName和模板
    WriteSheet writeSheet = EasyExcel.writerSheet()
            .sheetName("用户信息")
            .head(Write03.class)
            .includeColumnFieldNames(Arrays.asList("salary", "hireDate")) // 包含字段
            .build();
    // 3.构造数据
    List<Write03> list = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        Write03 user = Write03.builder()
                .userId(i)
                .userName("admin" + i)
                .gender(i % 2 == 0 ? "女" : "男")
                .salary(i * 1000.00)
                .hireDate(new Date())
                .build();
        list.add(user);
    }
    // 4.write向sheet中写入数据
    excelWriter.write(list, writeSheet);
    // 5.关闭流
    excelWriter.finish();
}

3.结果 Write03.xlsx

CleanShot 2024-10-30 at 15.14.07@2x

4.按照index顺序写入excel

1.模板

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @Builder
    public static class Write04 {
        @ExcelProperty(value = "用户编号", index = 0)
        private Integer userId;
        @ExcelProperty(value = "姓名", index = 1)
        private String userName;
        @ExcelProperty(value = "性别", index = 3)
        private String gender;
        @ExcelProperty(value = "工资", index = 4)
        private Double salary;
        @ExcelProperty(value = "入职时间", index = 2)
        private Date hireDate;
    }

2.方法

/**
 * 按照index顺序写入excel
 */
@Test
public void testWrite04() {
    String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write04.xlsx";
    // 1.构建write，指定写入的文件路径
    ExcelWriter excelWriter = EasyExcel.write(fileName)
            .build();
    // 2.构建sheet，指定sheetName和模板
    WriteSheet writeSheet = EasyExcel.writerSheet()
            .sheetName("用户信息")
            .head(Write04.class)
            .build();
    // 3.构造数据
    List<Write04> list = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        Write04 user = Write04.builder()
                .userId(i)
                .userName("admin" + i)
                .gender(i % 2 == 0 ? "女" : "男")
                .salary(i * 1000.00)
                .hireDate(new Date())
                .build();
        list.add(user);
    }
    // 4.write向sheet中写入数据
    excelWriter.write(list, writeSheet);
    // 5.关闭流
    excelWriter.finish();
}

3.结果 Write04.xlsx

CleanShot 2024-10-30 at 15.17.05@2x

5.复杂头数据写入，合并单元格

1.模板

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @Builder
    public static class Write05 {
        @ExcelProperty(value = {"group1", "用户编号"})
        private Integer userId;
        @ExcelProperty(value = {"group1", "姓名"})
        private String userName;
        @ExcelProperty(value = {"group2", "性别"})
        private String gender;
        @ExcelProperty(value = {"group2", "工资"})
        private Double salary;
        @ExcelProperty(value = "入职时间")
        private Date hireDate;
    }

2.方法

/**
 * 复杂头数据写入，合并单元格
 */
@Test
public void testWrite05() {
    String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write05.xlsx";
    // 1.构建write，指定写入的文件路径
    ExcelWriter excelWriter = EasyExcel.write(fileName)
            .build();
    // 2.构建sheet，指定sheetName和模板
    WriteSheet writeSheet = EasyExcel.writerSheet()
            .sheetName("用户信息")
            .head(Write05.class)
            .build();
    // 3.构造数据
    List<Write05> list = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        Write05 user = Write05.builder()
                .userId(i)
                .userName("admin" + i)
                .gender(i % 2 == 0 ? "女" : "男")
                .salary(i * 1000.00)
                .hireDate(new Date())
                .build();
        list.add(user);
    }
    // 4.write向sheet中写入数据
    excelWriter.write(list, writeSheet);
    // 5.关闭流
    excelWriter.finish();
}

3.结果 Write05.xlsx

CleanShot 2024-10-30 at 15.22.53@2x

6.写入数据到不同的sheet中

1.模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public static class Write06 {
    @ExcelProperty(value = "用户编号")
    private Integer userId;
    @ExcelProperty(value = "姓名")
    private String userName;
    @ExcelProperty(value = "性别")
    private String gender;
    @ExcelProperty(value = "工资")
    private Double salary;
    @ExcelProperty(value = "入职时间")
    private Date hireDate;
}

2.方法

/**
 * 写入数据到不同的sheet中
 */
@Test
public void testWrite06() {
    String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write06.xlsx";
    // 1.构建write，指定写入的文件路径
    ExcelWriter excelWriter = EasyExcel.write(fileName)
            .build();
    // 2.循环创建十个sheet，并分别写入数据
    for (int j = 0; j < 10; j++) {
        // 创建sheet
        WriteSheet writeSheet = EasyExcel.writerSheet()
                .sheetName("用户信息" + j)
                .head(Write06.class)
                .build();
        // 3.构造数据
        List<Write06> list = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Write06 user = Write06.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "女" : "男")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            list.add(user);
        }
        // 4.write向sheet中写入数据
        excelWriter.write(list, writeSheet);
    }
    // 5.关闭流
    excelWriter.finish();
}

3.结果 Write06.xlsx

CleanShot 2024-10-30 at 15.30.52@2x

7.日期数字格式化

1.模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public static class Write07 {
    @ExcelProperty(value = "用户编号")
    private Integer userId;
    @ExcelProperty(value = "姓名")
    private String userName;
    @ExcelProperty(value = "性别")
    private String gender;
    @ExcelProperty(value = "工资")
    @ContentStyle(dataFormat = 2) // 保留两位小数
    private Double salary;
    @ExcelProperty(value = "入职时间")
    @DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒") // 日期格式化
    private Date hireDate;
}

2.方法

/**
 * 日期数字格式化
 */
@Test
public void testWrite07() {
    String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write07.xlsx";
    // 1.构建write，指定写入的文件路径
    ExcelWriter excelWriter = EasyExcel.write(fileName)
            .build();
    // 2.构建sheet，指定sheetName和模板
    WriteSheet writeSheet = EasyExcel.writerSheet()
            .sheetName("用户信息")
            .head(Write07.class)
            .build();
    // 3.构造数据
    List<Write07> list = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        Write07 user = Write07.builder()
                .userId(i)
                .userName("admin" + i)
                .gender(i % 2 == 0 ? "女" : "男")
                .salary(i * 1000.213214234)
                .hireDate(new Date())
                .build();
        list.add(user);
    }
    // 4.write向sheet中写入数据
    excelWriter.write(list, writeSheet);
    // 5.关闭流
    excelWriter.finish();
}

3.结果 Write07.xlsx

CleanShot 2024-10-30 at 15.42.52@2x

8.写入图片到Excel

1.模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ContentRowHeight(value = 100) // 内容行高
@ColumnWidth(value = 20) // 列宽
static class Write08 {
    // 使用抽象文件表示一个图片
    @ExcelProperty(value = "File类型")
    private File file;
    // 使用输入流保存一个图片
    @ExcelProperty(value = "InputStream类型")
    private InputStream inputStream;
    // 当使用String类型保存一个图片的时候需要使用StringImageConverter转换器
    @ExcelProperty(value = "String类型", converter = StringImageConverter.class)
    private String str;
    // 使用二进制数据保存为一个图片
    @ExcelProperty(value = "二进制数据(字节)")
    private byte[] byteArr;
    // 使用网络链接保存为一个图片
    @ExcelProperty(value = "网络图片")
    private URL url;
}

2.方法

    /**
     * 写入图片到Excel
     */
    @Test
    public void testWrite08() throws Exception {
        String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write08.xlsx";
        // 本地图片
        String imagePath = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/th.jpeg";
        // 网络图片
        URL url = new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN3246625733_1920x1080.jpg&rf=LaDigue_1920x1080.jpg&pid=hp");
        // 构建writer
        ExcelWriter excelWriter = EasyExcel.write(fileName)
                .build();
        // 构建sheet
        WriteSheet writeSheet = EasyExcel.writerSheet()
                .head(Write08.class)
                .sheetName("图片信息")
                .build();
        // 构建data
        // 将图片读取到byte数组中
        byte[] bytes = new byte[(int) new File(imagePath).length()];
        InputStream inputStream = new FileInputStream(imagePath);
        inputStream.read(bytes, 0, bytes.length);

        List<Write08> imageDataList = new ArrayList<>();

        // 创建数据模板
        Write08 imageData = Write08.builder()
                .file(new File(imagePath)) // File类型
                .inputStream(new FileInputStream(imagePath)) // InputStream类型
                .str(imagePath) // String类型
                .byteArr(bytes) // 二进制数据
                .url(url) // 网络图片
                .build();
        // 添加要写入的图片模型
        imageDataList.add(imageData);
        // 写入数据
        excelWriter.write(imageDataList, writeSheet);
        // 关闭流
        excelWriter.finish();
    }

3.结果 Write08.xlsx

CleanShot 2024-10-30 at 15.51.33@2x

9.通过注解形式设置写入Excel样式

1.模板

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @Builder
    @HeadRowHeight(value = 30) // 头部行高
    @ContentRowHeight(value = 25) // 内容行高
    @ColumnWidth(value = 20) // 列宽
    // 头字体设置成20, 字体默认宋体
    @HeadFontStyle(fontName = "宋体", fontHeightInPoints = 20)
    // 内容字体设置成20, 字体默认宋体
    @ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)
    public static class Write09 {
        @ExcelProperty(value = "用户编号")
        private Integer userId;
        // 字符串的头字体设置成20
        @HeadFontStyle(fontHeightInPoints = 30)
        // 字符串的内容字体设置成20,默认宋体
        @ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)
        @ExcelProperty(value = "姓名")
        private String userName;
        @ExcelProperty(value = "性别")
        private String gender;
        @ExcelProperty(value = "工资")
        private Double salary;
        @ExcelProperty(value = "入职时间")
        private Date hireDate;
    }

2.方法

/**
 * 通过注解形式设置写入Excel样式
 */
@Test
public void testWrite09() {
    String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write09.xlsx";
    // 1.构建write，指定写入的文件路径
    ExcelWriter excelWriter = EasyExcel.write(fileName)
            .build();
    // 2.构建sheet，指定sheetName和模板
    WriteSheet writeSheet = EasyExcel.writerSheet()
            .sheetName("用户信息")
            .head(Write09.class)
            .build();
    // 3.构造数据
    List<Write09> list = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        Write09 user = Write09.builder()
                .userId(i)
                .userName("admin" + i)
                .gender(i % 2 == 0 ? "女" : "男")
                .salary(i * 1000.00)
                .hireDate(new Date())
                .build();
        list.add(user);
    }
    // 4.write向sheet中写入数据
    excelWriter.write(list, writeSheet);
    // 5.关闭流
    excelWriter.finish();
}

3.结果 Write09.xlsx

CleanShot 2024-10-30 at 15.57.32@2x

10.应用自定义转换器

1.模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public static class Write10 {
    @ExcelProperty(value = "用户编号")
    private Integer userId;
    @ExcelProperty(value = "姓名")
    private String userName;
    @ExcelProperty(value = "性别", converter = GenderConverter.class) // 使用自定义转换器
    private Integer gender;
    @ExcelProperty(value = "工资")
    private Double salary;
    @ExcelProperty(value = "入职时间")
    private Date hireDate;
}

2.自定义转换器

/**
 * 自定义转换器，比如数据库中0代表男，1代表女，导出到Excel中需要转换为男和女，注意，需要是public类型的
 */
public static class GenderConverter implements Converter<Integer> {

    private static final String MALE = "男";
    private static final String FEMALE = "女";

    /**
     * Java类型是Integer
     *
     * @return
     */
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    /**
     * Excel类型是String
     *
     * @return
     */
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 写入Excel时，将Integer类型的数据转换为String类型
     *
     * @param value
     * @param contentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public WriteCellData<?> convertToExcelData(Integer value,
                                               ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) throws Exception {
        // 根据整数值返回对应的性别字符串
        String gender;
        switch (value) {
            case 0:
                gender = MALE;
                break;
            case 1:
                gender = FEMALE;
                break;
            default:
                gender = "未知";
        }
        // 返回转换后的 Excel 单元格数据
        return new WriteCellData<>(gender);
    }

    /**
     * 读取Excel时，将String类型的数据转换为Integer类型
     *
     * @param cellData
     * @param contentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 获取Excel的值
        String cellDataStringValue = cellData.getStringValue();
        // 转换为Java的值
        switch (cellDataStringValue) {
            case MALE:
                return 0; // 男，返回0
            case FEMALE:
                return 1; // 女，返回1
            default:
                return -1; // 其他，返回-1
        }
    }
}

3.方法

/**
 * 应用自定义转换器
 */
@Test
public void testWrite10() {
    String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Write10.xlsx";
    // 1.构建write，指定写入的文件路径
    ExcelWriter excelWriter = EasyExcel.write(fileName)
            .build();
    // 2.构建sheet，指定sheetName和模板
    WriteSheet writeSheet = EasyExcel.writerSheet()
            .sheetName("用户信息")
            .head(Write10.class)
            .build();
    // 3.构造数据
    List<Write10> list = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        Write10 user = Write10.builder()
                .userId(i)
                .userName("admin" + i)
                .gender(i % 2 == 0 ? 1 : 0) // 这里插入的是数字，使用自定义转换器转换为男和女
                .salary(i * 1000.00)
                .hireDate(new Date())
                .build();
        list.add(user);
    }
    // 4.write向sheet中写入数据
    excelWriter.write(list, writeSheet);
    // 5.关闭流
    excelWriter.finish();
}

4.结果 Write10.xlsx

CleanShot 2024-10-30 at 16.10.28@2x

文章目录

EasyExcel环境搭建

1.创建模块 easyexcel-demo

2.引入依赖

3.启动类创建

EasyExcel写

1.最简单的写入

1.模板

2.方法

3.结果 Write01.xlsx

2.指定字段不写入Excel

1.模板

2.方法

3.结果 Write02.xlsx

3.指定字段写入excel

1.模板

2.方法

3.结果 Write03.xlsx

4.按照index顺序写入excel

1.模板

2.方法

3.结果 Write04.xlsx

5.复杂头数据写入，合并单元格

1.模板

2.方法

3.结果 Write05.xlsx

6.写入数据到不同的sheet中

1.模板

2.方法

3.结果 Write06.xlsx

7.日期数字格式化

1.模板

2.方法

3.结果 Write07.xlsx

8.写入图片到Excel

1.模板

2.方法

3.结果 Write08.xlsx

9.通过注解形式设置写入Excel样式

1.模板

2.方法

3.结果 Write09.xlsx

10.应用自定义转换器

1.模板

2.自定义转换器

3.方法

4.结果 Write10.xlsx

Latest comments