easyexcel多个sheet导入_EasyExcel2.0 实现模板下载、导入和导出功能-Fun言

之前介绍了一次EasyExcel 1.x 版本的导入导出，网上大部分教程也是基于 1.x 版本。

2.x 版本和1.x版本相差挺大的，用起来也简单很多。

本文以对 User 的导出导出为例。

一、依赖

pom.xml 中添加依赖

com.alibaba

easyexcel

2.1.4

注意：主要确保不要有其他版本的依赖或者其他版本的 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;

/**

* 

*     用户信息(MyBatisPlus)

* 

*

* @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 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 userList = userService.findAll();

List 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 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 userList = userExcelList2UserList(userExcelList);

// 5.入库操作

userService.batchInsertOrUpdate(userList);

}

private void checkImportData(List 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() 

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() 

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() 

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 userExcelList2UserList(List userExcelList) throws ParseException {

Date now = new Date();

List 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 userExcelList = new ArrayList<>();

// 1.excel同步读取数据

try {

ExcelReader excelReader = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).build();

List sheetList = excelReader.excelExecutor().sheetList();

List 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 userList = userExcelList2UserList(userExcelList);

// 5.入库操作

userService.batchInsertOrUpdate(userList);

}