EasyExcel基本使用

EasyExcel介绍

https://github.com/alibaba/easyexcel

在这里插入图片描述

示例

在这里插入图片描述

pom.xml

<?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><groupId>com.studio</groupId><artifactId>TestEasyExcel</artifactId><version>1.0-SNAPSHOT</version><dependencies><dependency><groupId>ch.qos.logback</groupId><artifactId>logback-classic</artifactId><version>1.2.11</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>1.7.32</version></dependency><dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-core</artifactId><version>2.19.0</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.30</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>4.0.2</version></dependency></dependencies><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><configuration><source>1.8</source><target>1.8</target><encoding>UTF-8</encoding></configuration></plugin><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-surefire-plugin</artifactId><configuration><skip>true</skip></configuration></plugin></plugins></build>
</project>

User.java

package com.studio.pojo;import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;
import java.util.Date;/*** 说明* 1）index：指定列所在的索引，从0开始* 2）@DateTimeFormat 自定义日期格式* 3）@ExcelIgnore 忽略指定表头信息* 4）合并表头* 5）设置单元格大小*/@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@HeadRowHeight(value = 25) // 表头行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 30) // 列宽
public class User implements Serializable {@ExcelProperty(value = {"用户基本信息", "用户名"}, index = 1)private String userName;@ExcelProperty(value = {"用户基本信息", "年龄"}, index = 2)private Integer age;@ExcelProperty(value = {"用户基本信息", "地址"}, index = 4)private String address;@ExcelProperty(value = {"用户基本信息", "生日"}, index = 3)@DateTimeFormat("yyyy-MM-dd HH:mm:ss")private Date birthday;@ExcelIgnore@ExcelProperty(value = {"用户基本信息", "用户ID"}, index = 5)private String userId;
}

Main.java

package com.studio;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.studio.pojo.User;import java.util.ArrayList;
import java.util.Date;public class Main {public static void main(String[] args) {
//        writeExcel();readExcel();}/*** 写入Excel*/public static void writeExcel(){ArrayList<User> users = new ArrayList<>();for (int i = 0; i < 10; i++) {User user = new User();user.setAddress("上海" + i);user.setUserName("张三" + i);user.setBirthday(new Date());user.setAge(10 + i);users.add(user);}EasyExcel.write("E:\\用户.xls", User.class).sheet("用户信息").doWrite(users);}/*** 读取Excel*/public static void readExcel(){ArrayList<User> users = new ArrayList<>();//读取数据EasyExcel.read("E:\\用户.xls", User.class, new AnalysisEventListener<User>() {@Overridepublic void invoke(User user, AnalysisContext analysisContext) {System.out.println("读取一行：" + user);users.add(user);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("所有读取完成");}}).sheet().doRead();System.out.println(users);}
}