easyExcel的导入导出举例
官网：https://alibaba-easyexcel.github.io/docs/current/
1、导入
EasyExcel有一个AnalysisEventListener，可以自定义一个Listener继承AnalysisEventListener，里面有一个invoke方法，每条数据都会进入这个方法。我们可以在这里做校验、存储、抛异常等动作，EasyExcel将这些都流程化了，写起代码来非常舒服。当然也有一些点需要注意下，比如自定义Listener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去。
package com.example.springbooteasyexcel.read;
import java.util.Date;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.data.WriteCellData;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@Builder
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class UserData {
private Long userId;
private String userName;
private Integer age;
private String userPhone;
private String userEmail;
private String userAddress;
private Date registerTime;
private String gender;
}
监听类
package com.example.springbooteasyexcel.read;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
/**
* 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
*/
@Slf4j
public class UserExcelReadListener extends AnalysisEventListener<UserData> {
/**
* 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 创建一个Pattern对象，使用正则表达式校验手机号格式
*/
private static final Pattern PHONE_REGEX = Pattern.compile("^1[0-9]{10}$");
/**
* 缓存的数据
*/
private List<UserData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 错误信息列表
*/
private final List<String> errorMsgList = new ArrayList<>(BATCH_COUNT);
@Override
public void invoke(UserData userData, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", userData);
int rowIndex = analysisContext.readRowHolder().getRowIndex();
String name = userData.getUserName();
String phone = userData.getUserPhone();
String gender = userData.getGender();
String email = userData.getUserEmail();
Integer age = userData.getAge();
String address = userData.getUserAddress();
// 只有全部校验通过的对象才能被添加到下一步
if (nameValid(rowIndex, name) && phoneValid(rowIndex, phone) && genderValid(rowIndex, gender) &&
emailValid(rowIndex, email) && ageValid(rowIndex, age) && addressValid(rowIndex, address)) {
cachedDataList.add(userData);
}
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
// to saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("所有数据解析完成！全部校验通过的数据有{}条", cachedDataList.size());
// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData();
// todo saveData();
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (exception instanceof RuntimeException) {
throw exception;
}
int index = context.readRowHolder().getRowIndex() + 1;
errorMsgList.add("第" + index + "行解析错误");
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
int totalRows = context.readSheetHolder().getApproximateTotalRowNumber() - 1;
int maxNum = 2000;
if (totalRows > maxNum) {
errorMsgList.add("数据量过大,单次最多上传2000条");
throw new RuntimeException("数据量过大,单次最多上传2000条");
}
}
public List<String> getErrorMsgList() {
return errorMsgList;
}
/**
* 名称的校验
*
* @param rowIndex 行数
* @param name 名称
*/
private Boolean nameValid(Integer rowIndex, String name) {
if (StringUtils.isBlank(name)) {
errorMsgList.add("第" + rowIndex + "行,'姓名'不能为空");
return Boolean.FALSE;
}
return Boolean.TRUE;
}
private Boolean phoneValid(int rowIndex, String phone) {
if (StringUtils.isBlank(phone)) {
errorMsgList.add("第" + rowIndex + "行,'手机号'不能为空");
return Boolean.FALSE;
}
return Boolean.TRUE;
}
/**
* 性别的校验
*
* @param rowIndex 行数
* @param gender 性别
*/
private Boolean genderValid(int rowIndex, String gender) {
if (StringUtils.isBlank(gender)) {
errorMsgList.add("第" + rowIndex + "行,'性别'不能为空");
return Boolean.FALSE;
}
return Boolean.TRUE;
}
/**
* 地址校验
*
* @param rowIndex 行数
* @param address 地址
*/
private Boolean addressValid(int rowIndex, String address) {
// 校验地址是否为空
if (StringUtils.isBlank(address)) {
errorMsgList.add("第 " + rowIndex + " 行,'地址'不能为空");
return Boolean.FALSE;
}
return Boolean.TRUE;
}
/**
* 年龄的校验
*
* @param rowIndex 行数
* @param age 年龄
*/
private Boolean ageValid(int rowIndex, Integer age) {
// 校验年龄是否为空
if (Objects.isNull(age)) {
errorMsgList.add("第 " + rowIndex + " 行'年龄'不能为空");
return Boolean.FALSE;
}
return Boolean.TRUE;
}
/**
* 邮箱的校验
*
* @param rowIndex 行数
* @param email 邮箱
*/
private Boolean emailValid(int rowIndex, String email) {
// 校验邮箱是否为空
if (StringUtils.isBlank(email)) {
errorMsgList.add("第 " + rowIndex + " 行'邮箱'不能为空");
return Boolean.FALSE;
}
return Boolean.TRUE;
}
}
测试
package com.example.springbooteasyexcel.read;
import com.alibaba.excel.EasyExcel;
public class ReadExcelTest {
public static void main(String[] args) {
UserExcelReadListener userExcelReadListener = new UserExcelReadListener();
EasyExcel.read("用户信息表.xlsx", UserData.class, userExcelReadListener).sheet().doRead();
System.out.println(userExcelReadListener.getErrorMsgList());
}
}
2、导出
用注解有三个@ExcelProperty、@ColumnWidth、@ExcelIgnore。
- @ExcelProperty 确定表头，还可以合并行
- @ColumnWidth 主要是控制列宽
- @ExcelIgnore忽略不需要输出的字段
（1）单个sheet和表头合并 和 多个sheet导出
实体类
package com.example.springbooteasyexcel.sheet;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.data.WriteCellData;
import lombok.Builder;
import lombok.Data;
import java.util.Date;
@Data
@Builder
public class UserSheet {
@ExcelProperty(value = "用户ID", index = 0)
@ColumnWidth(10)
private Long userId;
@ExcelProperty(value = "用户名称", index = 1)
@ColumnWidth(10)
private String userName;
@ExcelProperty(value = {"基本信息", "手机号码"}, index = 2)
@ColumnWidth(20)
private String userPhone;
@ExcelProperty(value = {"基本信息", "电子邮箱"}, index = 3)
@ColumnWidth(20)
private String userEmail;
@ExcelProperty(value = {"基本信息", "地址"}, index = 4)
@ColumnWidth(20)
private String userAddress;
@ExcelProperty(value = "注册时间", index = 5)
@ColumnWidth(20)
private Date registerTime;
@ExcelProperty(value = "性别，男:红色/女:绿色")
@ColumnWidth(30)
private WriteCellData<String> gender;
/**
* 忽略这个字段
*/
@ExcelIgnore
private Integer age;
}
package com.example.springbooteasyexcel.sheet;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Builder;
import lombok.Data;
@Data
@Builder
public class CitySheet {
@ExcelProperty(value = "城市名称", index = 0)
@ColumnWidth(10)
private String cityName;
@ExcelProperty(value = "城市介绍", index = 1)
@ColumnWidth(60)
private String cityDesc;
}
package com.example.springbooteasyexcel.sheet;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Builder;
import lombok.Data;
@Data
@Builder
public class CompanySheet {
@ExcelProperty(value = "公司名称", index = 0)
@ColumnWidth(10)
private String companyName;
@ExcelProperty(value = "公司创始人", index = 1)
@ColumnWidth(10)
private String companyBoss;
@ExcelProperty(value = "公司总基地", index = 2)
@ColumnWidth(10)
private String companyBase;
@ExcelProperty(value = "公司简介", index = 3)
@ColumnWidth(50)
private String companyDesc;
}
测试类
package com.example.springbooteasyexcel.data;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.RichTextStringData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.example.springbooteasyexcel.sheet.CitySheet;
import com.example.springbooteasyexcel.sheet.CompanySheet;
import com.example.springbooteasyexcel.sheet.UserSheet;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
public class Mock {
public static List<UserSheet> userList() {
List<UserSheet> list = new ArrayList<>(10);
list.add(UserSheet.builder().userId(001L).userName("张三").userPhone("11112223123").userEmail("zhansan@163.com").userAddress("北京朝阳区").gender(buildCellData("男")).registerTime(Calendar.getInstance().getTime()).build());
list.add(UserSheet.builder().userId(002L).userName("李四").userPhone("11112223123").userEmail("lisi@qq.com").userAddress("南京玄武门").gender(buildCellData("女")).registerTime(Calendar.getInstance().getTime()).build());
list.add(UserSheet.builder().userId(003L).userName("王五").userPhone("11112223123").userEmail("wangwu@google.com").userAddress("杭州未来科技城").gender(buildCellData("男")).registerTime(Calendar.getInstance().getTime()).build());
list.add(UserSheet.builder().userId(004L).userName("赵六").userPhone("11112223123").userEmail("zhaoliu@baidu.com").userAddress("上海徐家汇").gender(buildCellData("女")).registerTime(Calendar.getInstance().getTime()).build());
return list;
}
private static WriteCellData<String> buildCellData(String gender) {
// 设置单个单元格多种样式
WriteCellData<String> cellData = new WriteCellData<>();
// 设置单个单元格的填充类型
cellData.setType(CellDataTypeEnum.RICH_TEXT_STRING);
RichTextStringData richTextStringData = new RichTextStringData();
cellData.setRichTextStringDataValue(richTextStringData);
richTextStringData.setTextString(gender);
WriteFont writeFont = new WriteFont();
if ("男".equalsIgnoreCase(gender)) {
//设置颜色为红色
writeFont.setColor(IndexedColors.RED.getIndex());
} else if ("女".equalsIgnoreCase(gender)) {
//设置颜色为绿色
writeFont.setColor(IndexedColors.GREEN.getIndex());
}
//应用颜色字体
richTextStringData.applyFont(writeFont);
return cellData;
}
public static List<CitySheet> cityList() {
List<CitySheet> list = new ArrayList<>(10);
list.add(CitySheet.builder().cityName("杭州市").cityDesc("杭州市一般指杭州。 杭州，简称“杭”，古称临安、钱塘，浙江省辖地级市、省会、副省级市、特大城市、国务院批复确定的浙江省经济、文化、科教中心，长江三角洲中心城市之一，环杭州湾大湾区核心城市、G60科创走廊中心城市。").build());
list.add(CitySheet.builder().cityName("合肥市").cityDesc("合肥市一般指合肥。 合肥，简称“庐”或“合”，古称庐州、庐阳、合淝，安徽省辖地级市、省会，是合肥都市圈中心城市，国务院批复确定的中国长三角城市群副中心城市，全国四大科教基地、现代制造业基地和综合交通枢纽。").build());
list.add(CitySheet.builder().cityName("武汉市").cityDesc("武汉市一般指武汉。 武汉，简称“汉”，别称江城，是湖北省省会，中部六省唯一的副省级市，超大城市，中国中部地区的中心城市，全国重要的工业基地、科教基地和综合交通枢纽，联勤保障部队机关驻地。").build());
list.add(CitySheet.builder().cityName("深圳市").cityDesc("深圳市一般指深圳。 深圳，简称“深”，别称鹏城，广东省辖地级市，是广东省副省级市，国家计划单列市，超大城市，国务院批复确定的中国经济特区、全国性经济中心城市、国际化城市、科技创新中心、区域金融中心、商贸物流中心。").build());
return list;
}
public static List<CompanySheet> companyList() {
List<CompanySheet> list = new ArrayList<>(10);
list.add(CompanySheet.builder().companyName("阿里巴巴").companyBoss("马云").companyBase("杭州市").companyDesc("阿里巴巴集团经营多项业务，另外也从关联公司的业务和服务中取得经营商业生态系统上的支援。业务和关联公司的业务包括：淘宝网、天猫、聚划算、全球速卖通、阿里巴巴国际交易市场、1688、阿里妈妈、阿里云、蚂蚁集团 [408] 、菜鸟网络等。").build());
list.add(CompanySheet.builder().companyName("字节跳动").companyBoss("张一鸣").companyBase("北京市").companyDesc("字节跳动的全球化布局始于2015年 [3] ，“技术出海”是字节跳动全球化发展的核心战略 [4] ，其旗下产品有今日头条、西瓜视频、抖音、头条百科、皮皮虾、懂车帝、悟空问答等。").build());
list.add(CompanySheet.builder().companyName("腾讯").companyBoss("马化腾").companyBase("深圳市").companyDesc("社交和通信服务QQ及微信/WeChat、社交网络平台QQ空间、腾讯游戏旗下QQ游戏平台、门户网站腾讯网、腾讯新闻客户端和网络视频服务腾讯视频等。").build());
list.add(CompanySheet.builder().companyName("百度").companyBoss("李彦宏").companyBase("北京市").companyDesc("百度（Baidu）是拥有强大互联网基础的领先AI公司。百度愿景是：成为最懂用户，并能帮助人们成长的全球顶级高科技公司。").build());
return list;
}
}
package com.example.springbooteasyexcel.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.springbooteasyexcel.data.Mock;
import com.example.springbooteasyexcel.sheet.CitySheet;
import com.example.springbooteasyexcel.sheet.CompanySheet;
import com.example.springbooteasyexcel.sheet.UserSheet;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
@RestController
@RequestMapping("/export")
public class ExportController {
/**
* @param response
* @url <a>http://localhost:8080/export/test1</a>
* 在Excel中写入单个sheet
*/
@RequestMapping("/test1")
public void test1(HttpServletResponse response) {
//从HttpServletResponse中获取OutputStream输出流
try {
// 设置响应类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 设置编码格式
response.setCharacterEncoding("utf-8");
// 设置URLEncoder.encode 防止中文乱码
String fileName = URLEncoder.encode("用户信息表", "UTF-8").replaceAll("\\+", "%20");
// 设置响应头
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 写出Excel
EasyExcel.write(response.getOutputStream(), UserSheet.class).inMemory(true).sheet("用户信息表").doWrite(Mock.userList());
} catch (IOException e) {
throw new RuntimeException("数据或文件损坏，无法下载");
}
}
/**
* 在Excel中写入多个sheet
*
* @url <a>http://localhost:8080/export/test2</a>
*/
@RequestMapping("/test2")
public void test2(HttpServletResponse response) throws Exception {
// 设置响应类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 设置编码格式
response.setCharacterEncoding("utf-8");
// 设置URLEncoder.encode 防止中文乱码
String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
// 设置响应头
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 多个sheet的输出需要使用ExcelWriter类，这里想要下载成功，需要输出到OutputStream中
try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).inMemory(true).build()) {
// 创建用户信息表的sheet，写入用户信息数据，1代表sheet的位置是第一个
WriteSheet userInfoSheet = EasyExcel.writerSheet(0, "用户信息表").head(UserSheet.class).build();
excelWriter.write(Mock.userList(), userInfoSheet);
// 创建城市信息表的sheet，写入城市信息数据，2代表sheet的位置是第二个
WriteSheet cityInfoSheet = EasyExcel.writerSheet(1, "城市信息表").head(CitySheet.class).build();
excelWriter.write(Mock.cityList(), cityInfoSheet);
// 创建公司信息表的sheet，写入公司信息数据，3代表sheet的位置是第三个
WriteSheet companyInfoSheet = EasyExcel.writerSheet(2, "公司信息表").head(CompanySheet.class).build();
excelWriter.write(Mock.companyList(), companyInfoSheet);
}
}
}
（3）模版导出
参考：https://blog.csdn.net/xhmico/article/details/137461979
进行填充的方法为：fill()
在 EasyExcel 中，FillConfig 类用于配置单元格填充相关的设置。
以下是一些 FillConfig 类中常用的配置项：
forceNewRow：是否强制写入到新行。当设置为 true 时，每次写入都会创建新的行，而不是在现有行上进行覆盖
direction：填充方向，可以指定是水平填充还是垂直填充
属性：
WriteDirectionEnum.VERTICAL：垂直填充
WriteDirectionEnum.HORIZONTAL：水平填充
如果有多列组合填充的情况，通过 {前缀.变量} 的形式区分不同的列表，例如
使用 FillWrapper 构建 Excel 写入时的填充模板
excelWriter.fill(new FillWrapper("data1", data()), writeSheet);
excelWriter.fill(new FillWrapper("data2", data()), writeSheet);
excelWriter.fill(new FillWrapper("data3", data()), writeSheet);
案例一：
模版：
实体类：
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class StaffSalaryEntity {
@ApiModelProperty(value = "姓名")
private String name;
@ApiModelProperty(value = "职称")
private String post;
@ApiModelProperty(value = "月薪")
private BigDecimal mouthSalary;
@ApiModelProperty(value = "时薪")
private BigDecimal hourSalary;
@ApiModelProperty(value = "应出勤天数")
private Double shouldAttend;
@ApiModelProperty(value = "出勤天数")
private Double actualAttend;
@ApiModelProperty(value = "平时加班时数")
private Double overtime;
@ApiModelProperty(value = "周末加班时数")
private Double weekOvertime;
@ApiModelProperty(value = "国假天数")
private Double holiday;
@ApiModelProperty(value = "正班薪资")
private BigDecimal normalSalary;
@ApiModelProperty(value = "平时加班薪资")
private BigDecimal overtimeSalary;
@ApiModelProperty(value = "周末加班薪资")
private BigDecimal weekOvertimeSalary;
@ApiModelProperty(value = "国假薪资")
private BigDecimal holidaySalary;
@ApiModelProperty(value = "岗位津贴")
private BigDecimal postSubsidy;
@ApiModelProperty(value = "全勤补贴")
private BigDecimal fullAttendSubsidy;
@ApiModelProperty(value = "全勤奖")
private BigDecimal award;
@ApiModelProperty(value = "事假缺勤扣款")
private BigDecimal deduction;
@ApiModelProperty(value = "社保费用")
private BigDecimal social;
@ApiModelProperty(value = "应得薪资")
private BigDecimal shouldSalary;
@ApiModelProperty(value = "个人扣税")
private BigDecimal selfTax;
@ApiModelProperty(value = "实发薪资")
private BigDecimal actualSalary;
@ApiModelProperty(value = "员工签名")
private BigDecimal sign;
}
导出：
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.mike.server.system.domain.excel.ProductOfferExcelEntity;
import com.mike.server.system.entity.StaffSalaryEntity;
import org.junit.jupiter.api.Test;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
public class TestDemo {
/**
* 案例一：工资表
*/
@Test
public void salaryList() {
// 模板文件路径
String templateFilePath = "D:\\excel-files\\gzb-template.xlsx";
// 输出文件路径
String outFilePath = "D:\\excel-files\\gzb.xlsx";
// 创建 ExcelWriter 实例
ExcelWriter writer = EasyExcel
// 写入到
.write(outFilePath)
// 指定模板
.withTemplate(templateFilePath)
.build();
WriteSheet sheet = EasyExcel.writerSheet().build();
// 获取员工工资数据
List<StaffSalaryEntity> staffSalaryEntities = getStaffSalaryEntities();
FillConfig fillConfig = FillConfig.builder()
// 开启填充换行
.forceNewRow(true)
.build();
// 执行填充操作
writer.fill(staffSalaryEntities, fillConfig, sheet);
// 结束
writer.finish();
}
public List<StaffSalaryEntity> getStaffSalaryEntities() {
List<StaffSalaryEntity> list = new ArrayList<>();
list.add(StaffSalaryEntity.builder()
.name("米大傻")
.post("开发")
.mouthSalary(new BigDecimal(1320))
.hourSalary(new BigDecimal("7.59"))
.shouldAttend(21.0)
.actualAttend(21.0)
.overtime(21.0)
.weekOvertime(8.0)
.holiday(0.0)
.normalSalary(new BigDecimal(1320))
.overtimeSalary(new BigDecimal("238.97"))
.weekOvertimeSalary(new BigDecimal("242.76"))
.holidaySalary(new BigDecimal(0))
.postSubsidy(new BigDecimal(0))
.award(new BigDecimal(20))
.deduction(new BigDecimal(0))
.social(new BigDecimal("113.6"))
.shouldSalary(new BigDecimal("1688.12"))
.selfTax(new BigDecimal(0))
.actualSalary(new BigDecimal("1688.1"))
.build());
return list;
}
}
可能遇到的问题：
（1）驼峰命名导致填充数据失败
如果占位符中的变量采用的是驼峰命名的方式，且组成变量的 “单词” 存在过于简单的时就会出现该状况，比如说：
月薪的占位符 {.mSalary} 中的 mSalary 是由 m 和 salary 组成，m 只有一个字母， {.mSalary} 填充不上，但是如果单独使用 {.m} 或者 {.salary} 却可以填充上去，或者把每个组成的单词写复杂一点，比如写成 {.mouthSalary} 也是可以填充上的
（2）模板单元格样式消失
如果你使用模板导出发现有些单元格的样式消失了，比如：
