EasyExcel（环境搭建以及常用写入操作）

EasyExcel环境搭建

1.创建模块 easyexcel-demo

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@Builderpublic 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.方法

    /*** 最简单的写入*/@Testpublic 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

2.指定字段不写入Excel

1.模板

    @NoArgsConstructor@AllArgsConstructor@Data@Builderpublic 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.方法

    /*** 指定字段不写入excel*/@Testpublic 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

3.指定字段写入excel

1.模板

    @NoArgsConstructor@AllArgsConstructor@Data@Builderpublic 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

4.按照index顺序写入excel

1.模板

    @NoArgsConstructor@AllArgsConstructor@Data@Builderpublic 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

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

1.模板

    @NoArgsConstructor@AllArgsConstructor@Data@Builderpublic 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

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++) {// 创建sheetWriteSheet 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

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

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*/@Testpublic 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");// 构建writerExcelWriter excelWriter = EasyExcel.write(fileName).build();// 构建sheetWriteSheet 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

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

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*/@Overridepublic Class<?> supportJavaTypeKey() {return Integer.class;}/*** Excel类型是String** @return*/@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 写入Excel时，将Integer类型的数据转换为String类型** @param value* @param contentProperty* @param globalConfiguration* @return* @throws Exception*/@Overridepublic 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*/@Overridepublic Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {// 获取Excel的值String cellDataStringValue = cellData.getStringValue();// 转换为Java的值switch (cellDataStringValue) {case MALE:return 0; // 男，返回0case FEMALE:return 1; // 女，返回1default: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