EasyExcel2.0 实现模板下载、导入和导出功能 | 言曌博客

安宇雨 - 随手采集
2021-02-23 17:28:03
随手采集
未整理-等待研究

之前介绍了一次EasyExcel 1.x 版本的导入导出，网上大部分教程也是基于 1.x 版本。 2.x 版本和1.x版本相差挺大的，用起来也简单很多。本文以对 User 的导出导出为例。代码地址：https://github.com/saysky/sensboot 官方文档地址：https://alibaba-easyexcel.github.io/

一、依赖

pom.xml 中添加依赖

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>

注意：主要确保不要有其他版本的依赖或者其他版本的 POI，如果有的话升级 POI 版本

二、实体类和DTO

1.User实体类

package com.liuyanzhao.sens.entity;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.validation.constraints.NotBlank;
import java.io.Serializable;
import java.util.Date;
/**
* <pre>
* 用户信息(MyBatisPlus)
* </pre>
*
* @author : saysky
* @date : 2017/11/14
*/
@Data
@TableName("user")
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {
private static final long serialVersionUID = -5144055068797033748L;
/**
* 编号，自增
*/
@TableId(type = IdType.AUTO)
private Long id;
/**
* 用户名
*/
@NotBlank(message = "用户名不能为空")
private String username;
/**
* 显示名称
*/
private String nickname;
/**
* 密码
*/
private String password;
/**
* 邮箱
*/
private String email;
/**
* 头像
*/
private String avatar;
/**
* 0 正常
* 1 禁用
* 2 已删除
*/
private Integer status = 0;
/**
* 创建时间
*/
private Date createdTime;
/**
* 创建人用户名
*/
private String createdBy;
/**
* 更新时间
*/
private Date updatedTime;
/**
* 更新人用户名
*/
private String updatedBy;
public User(String username, String nickname, String password, String email, String avatar, Integer status, Date createdTime, String createdBy, Date updatedTime, String updatedBy) {
this.username = username;
this.nickname = nickname;
this.password = password;
this.email = email;
this.avatar = avatar;
this.status = status;
this.createdTime = createdTime;
this.createdBy = createdBy;
this.updatedTime = updatedTime;
this.updatedBy = updatedBy;
}
}

注意：这里我用的是 lombok 生成 getter/setter 和构造器，用的是 mybatis-plus 作为 ORM 框架 2.UserExcel 用于导入导出的对象 不建议直接在 User 实体类上加 excel 相关注解，所以我们创建了一个专门用于 excel 的类，属性类型这里全部用 String，防止类型不一致无法转换报异常

package com.liuyanzhao.sens.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author 言曌
* @date 2020-01-02 11:39
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@HeadRowHeight(value = 40)
public class UserExcel {
/**
* 用户名
*/
@ExcelProperty(value = "用户名", index = 0)
@ColumnWidth(value = 15)
private String username;
/**
* 显示名称
*/
@ExcelProperty(value = "昵称", index = 1)
@ColumnWidth(value = 15)
private String nickname;
/**
* 密码
*/
@ExcelProperty(value = "密码", index = 2)
@ColumnWidth(value = 20)
private String password;
/**
* 邮箱
*/
@ExcelProperty(value = "邮箱", index = 3)
@ColumnWidth(value = 20)
private String email;
/**
* 头像
*/
@ExcelProperty(value = "头像", index = 4)
@ColumnWidth(value = 20)
private String avatar;
/**
* 0 正常
* 1 禁用
*/
@ExcelProperty(value = "状态\r0正常,1 禁用", index = 5)
@ColumnWidth(value = 20)
private String status;
/**
* 注册时间 yyyy-MM-dd HH:mm:ss格式
*/
@ExcelProperty(value = "注册时间", index = 6)
@ColumnWidth(value = 20)
private String createdTime;
}

三、导出工具类

因为模板下载和导出功能，代码几乎一样，为了减少冗余，我们把导出这部分的代码放到一个新建的工具类里

package com.liuyanzhao.sens.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* @author 言曌
* @date 2020-01-02 11:21
*/
public class ExcelUtil {
/**
* 导出
* @param response
* @param data
* @param fileName
* @param sheetName
* @param clazz
* @throws Exception
*/
public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
return response.getOutputStream();
}
}

四、模板下载

模板下载其实和导出的意思差不多，只不过数据使我们手写的模板数据，而非真实数据

/**
* 下载Excel模板
*/
@GetMapping("/excel/template")
public void downloadTemplate(HttpServletResponse response) {
String fileName = "导入用户模板";
String sheetName = "导入用户模板";
List<UserExcel> userList = new ArrayList<>();
userList.add(new UserExcel("saysky", "言曌", "123456", "847064370@qq.com", "http://xxx.com/xx.jpg", "0", "2017-12-31 12:13:14"));
userList.add(new UserExcel("qiqi", "琪琪", "123456", "666666@qq.com", "http://xxx.com/xx.jpg", "0", "2018-5-20 13:14:00"));
try {
ExcelUtil.writeExcel(response, userList, fileName, sheetName, UserExcel.class);
} catch (Exception e) {
e.printStackTrace();
}
}

五、导出数据

查询所有用户，然后写入 excel，通过输出流给浏览器

/**
* 导出
*/
@GetMapping("/excel/export")
public void exportData(HttpServletResponse response) {
String fileName = "用户列表";
String sheetName = "用户列表";
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<User> userList = userService.findAll();
List<UserExcel> userExcelList = new ArrayList<>();
for (User user : userList) {
UserExcel userExcel = UserExcel.builder()
.username(user.getUsername())
.password(user.getPassword())
.nickname(user.getNickname())
.email(user.getEmail())
.avatar(user.getAvatar())
.status(String.valueOf(user.getStatus()))
.createdTime(dateFormat.format(user.getCreatedTime())).build();
userExcelList.add(userExcel);
}
try {
ExcelUtil.writeExcel(response, userExcelList, fileName, sheetName, UserExcel.class);
} catch (Exception e) {
e.printStackTrace();
}
}

六、导入数据

主要是读取excel数据，然后进行自定义校验格式，然后入库。这里导入都是用同步的，就是全部读取后再做写入操作。如果需要异步那种，导入几条入库几条可以参考官网文档

/**
* 导入：同步读，单sheet
* 注意：这里为了介绍 excel导入导出，代码都写在 controller，实际项目开发中，校验和处理代码建议放到 service
*/
@PostMapping("/excel/import")
public void importData(MultipartFile file) throws ParseException {
List<UserExcel> userExcelList = null;
// 1.excel同步读取数据
try {
userExcelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).sheet().doReadSync();
} catch (Exception e) {
e.printStackTrace();
}
// 2.检查是否大于1000条
if (userExcelList.size() > MAX_USER_IMPORT) {
throw new GlobalException(CodeMsg.OVER_MAX_USER_IMPORT_LIMIT.fillArgs(MAX_USER_IMPORT));
}
// 3.导入校验所有行列格式
checkImportData(userExcelList);
// 4.将 userExcelList 转成 userList
List<User> userList = userExcelList2UserList(userExcelList);
// 5.入库操作
userService.batchInsertOrUpdate(userList);
}
private void checkImportData(List<UserExcel> userExcelList) {
// 行号从第2行开始
int rowNo = 2;
// 遍历校验所有行和列
for (UserExcel userExcel : userExcelList) {
// 1.校验用户名
String username = userExcel.getUsername();
if (StringUtils.isEmpty(username)) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "用户名"));
}
if (username.length() > 20 || username.length() < 4) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "用户名"));
}
// 2.校验密码
String password = userExcel.getPassword();
if (StringUtils.isEmpty(password)) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "密码"));
}
if (password.length() > 100 || password.length() < 6) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "密码"));
}
// 3.校验昵称
String nickname = userExcel.getNickname();
if (StringUtils.isEmpty(nickname)) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "昵称"));
}
if (nickname.length() > 20 || nickname.length() < 2) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "昵称"));
}
// 4.校验Email
String email = userExcel.getEmail();
if (StringUtils.isEmpty(email)) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "邮箱"));
}
if (!EMAIL_PATTERN.matcher(email).matches()) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "邮箱"));
}
// 5.校验状态
String status = userExcel.getStatus();
if (StringUtils.isEmpty(status)) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "状态"));
}
if (!"0".equals(status) && !"1".equals(status)) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "状态"));
}
// 6.校验注册时间
String createdTime = userExcel.getCreatedTime();
if (StringUtils.isEmpty(createdTime)) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "注册时间"));
}
try {
DATE_TIME_FORMAT.parse(createdTime);
} catch (ParseException e) {
throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "注册时间"));
}
}
}
/**
* userExcelList转成userList
*
* @param userExcelList
* @return
*/
private List<User> userExcelList2UserList(List<UserExcel> userExcelList) throws ParseException {
Date now = new Date();
List<User> userList = new ArrayList<>();
for (UserExcel userExcel : userExcelList) {
User user = User.builder()
.username(userExcel.getUsername())
.password(userExcel.getPassword())
.nickname(userExcel.getNickname())
.email(userExcel.getEmail())
.avatar(userExcel.getAvatar())
.status(Integer.valueOf(userExcel.getStatus()))
.createdTime(DATE_TIME_FORMAT.parse(userExcel.getCreatedTime())).build();
user.setCreatedBy("import");
user.setUpdatedBy("import");
user.setUpdatedTime(now);
userList.add(user);
}
return userList;
}

具体 service 层入库代码和其他常量等其他代码这里就不贴了，需要完整代码请访问上面提供的 git 地址

七、多 sheet 导入

有时候导入要求支持多个工作表导入只需要通过 excelReader.excelExecutor().sheetList() 获得 sheet 列表就行，然后分别进行上面的导入操作

@PostMapping("/excel/import")
public void importDataByMoreSheet(MultipartFile file) throws ParseException, IOException {
List<UserExcel> userExcelList = new ArrayList<>();
// 1.excel同步读取数据
try {
ExcelReader excelReader = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).build();
List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();
List<UserExcel> childUserExcelList = new ArrayList<>();
for (ReadSheet sheet : sheetList) {
childUserExcelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).sheet(sheet.getSheetNo()).doReadSync();
}
userExcelList.addAll(childUserExcelList);
} catch (Exception e) {
e.printStackTrace();
}
// 2.检查是否大于1000条
if (userExcelList.size() > MAX_USER_IMPORT) {
throw new GlobalException(CodeMsg.OVER_MAX_USER_IMPORT_LIMIT.fillArgs(MAX_USER_IMPORT));
}
// 3.导入校验所有行列格式
checkImportData(userExcelList);
// 4.将 userExcelList 转成 userList
List<User> userList = userExcelList2UserList(userExcelList);
// 5.入库操作
userService.batchInsertOrUpdate(userList);
}