提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
Spring Boot上Excel的导入、导出与模版的下载
前言
项目编写的过程上，要实现对excel文件内容上的数据，进行插入到相对应的数据库上，同时还要对导入的excel某些字段进行校验，校验不通过进行返回前段异常数据与所在异常的位置
提示：以下是本篇文章正文内容，下面案例可供参考
一、引入EasyExcel的Maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>二、excel文件的上传并插入到数据库
对于上传的excel文件，在传输过程中存在一些问题需要我们注意：
1、数据的插入的时候，采用批量插入方式，减少网络开销，提升插入效率。
2、文件上传过程中，需要进行校验上传文件的类型，只允许上传指定类型的文件，以提高安全性。
3、文件上传较大的excel数据，需要分批进行处理操作，避免全部进行加载占用过多的内存，避免发生OOM。
4、当excel文件上传的数据发生异常时，要进行数据的回滚操作，避免产生一些不必要的麻烦。
基于以上的声明，弄了一个小案例，废话不多说，如下所示：
三、文件的导入
3.1、pojo
3.1.1、user
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TableName("user")
@ApiModel(value = "user", description = "学生信息表")
public class user implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键id")
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
@ApiModelProperty(value = "姓名")
@TableField("user_name")
private String userName;
@ApiModelProperty(value = "密码")
@TableField("password")
private String password;
@ApiModelProperty(value = "创建时间")
@TableField("create_time")
private LocalDateTime createTime;
@ApiModelProperty(value = "修改时间")
@TableField("update_time")
private LocalDateTime updateTime;
}3.1.2、UserExcelRecordDTO
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.*;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class UserExcelRecordDTO{
@ExcelProperty(value = "ID", index = 0)
private Integer id;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
@ExcelProperty(value = "密码", index = 2)
private String password;
/**
@DateTimeFormat(value = "yyyy-MM-dd")
@ExcelProperty(value = "过期时间", index = 3)
private String expireTime;
**/
//注意在excel存在导入时间的字段的话，要使用上面的样式，要不然会出现时间转换失败
//表示第几行
private Integer rows;
}3.1.3、UserExcelRecordDTO
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserExcelRecordDTO {
@ExcelProperty(value = "ID", index = 0)
private Integer Id;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
@ExcelProperty(value = "密码", index = 2)
private String password;
//表示第几行
private Integer rows;
}3.2、Controller
/**
* easyExcel上传文件
* 参数必须为file
*/
@PostMapping("/upload")
@ApiOperation("学生数据的导入")
public ResponseResult upload(MultipartFile file) throws Exception {
return userService.upload(file);
}3.3、service & serviceImpl
3.3.1、service
ResponseResult upload(MultipartFile file) throws Exception;3.3.2、serviceImpl
/**
* 文件的导入
* @param file
* @return
*/
public ResponseResult upload(MultipartFile file) throws Exception {
if (file == null) {
log.error("学生信息导入操作，上传文件为空");
//AppHttpCodeEnum.UPLOAD_NOT_PRESENCE只是代表返回异常的数据，自己可以定义，问题不大
return ResponseResult.errorResult(AppHttpCodeEnum.UPLOAD_NOT_PRESENCE);
}
//获取文件名
String fileName = file.getOriginalFilename();
//验证文件名是否合格(xlsx, xls, xlsm,xlt)
//文件后缀名校验
if (!(fileName.endsWith("xls") || fileName.endsWith("xlsx") || fileName.endsWith("xlsm") || fileName.endsWith("xlt"))) {
log.error("学生信息文件的导入，文件上传格式错误");
return ResponseResult.errorResult(AppHttpCodeEnum.UPLOAD_DATA_FORMAT_ERROR);
}
Map returnMap = new HashMap<>();
//验证导入工时的标题头是否合法
Workbook wb = null;
String originalFilename = file.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
if (!"xls".equals(suffix) && !"xlsx".equals(suffix)) {
//return ResultEx.error(Constant.ERROR_CODE, "上传文件只支持xls和xlsx文件后缀;", "");
log.error("学生数据EXCEL导入，上传文件只支持xls和xlsx文件后缀");
return ResponseResult.errorResult(AppHttpCodeEnum.UPLOAD_DATA_SUFFIX);
}
InputStream fin = file.getInputStream();
if ("xls".equals(suffix)) {
wb = new HSSFWorkbook(fin);
} else if ("xlsx".equals(suffix)) {
wb = new XSSFWorkbook(fin);
}
String[] columnName =
{"ID", "姓名", "密码"};
//校验表头是否正确
boolean resultVali = verificationStudentExcelHeadLine(wb, columnName);
if (!resultVali) {
log.error("学生文件的导入数据错误，导入Excel表头与模板不一致，请核对文件");
return ResponseResult.errorResult(AppHttpCodeEnum.UPLOAD_TABLE_DATA_ERROR);
}
EasyExcel.read(file.getInputStream(), UserExcelRecordDTO.class, new UserDataListener(userService)).sheet().doRead();
return null;
}
/**
* excel表格上数据的校验
* 校验表头是否正确
*
* @param wb
* @param columnName
* @return
* @throws Exception
*/
public static boolean verificationStudentExcelHeadLine(Workbook wb, String[] columnName) throws Exception {
String result = null;
try {
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0);
//6
System.err.println("columnName.length:" + columnName.length);
if (row != null && row.getLastCellNum() == columnName.length) {
int lastCellNum = row.getLastCellNum();
System.err.println("lastCellNum:" + lastCellNum);
for (int idx = 0; idx < lastCellNum; idx++) {
String value = getCellValue(row.getCell(idx));
if (idx < 11) {
if (org.apache.commons.lang3.StringUtils.isBlank(value) || !columnName[idx].equals(value)) {
result = "标题行第" + (idx + 1) + "列名称错误！";
return false;
}
}
}
} else {
result = "上传文件首行不能为空或与工时导入表格表头不一致！";
return false;
}
} catch (Exception ex) {
return false;
}
return true;
}3.3.3、UserDataListener
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
@Slf4j
public class UserDataListener extends AnalysisEventListener<UserExcelRecordDTO> {
private final UserService userService;
public UserDataListener(UserService userService) {
this.userService= userService;
}
/**
* 每隔1000条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
List<UserExcelRecordDTO> list = new ArrayList<UserExcelRecordDTO>();
@Override
public void invoke(UserExcelRecordDTO data, AnalysisContext context) {
log.info("invoke---开始校验表格数据");
validChoiceInfo(data, context);
log.info("invoke---开始解析表格数据");
log.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
private void validChoiceInfo(UserExcelRecordDTO data, AnalysisContext context) {
if (data.getId() == null){
log.info("上传失败:第{}行ID信息为空",context.readRowHolder().getRowIndex());
throw new UploadExcelException("上传失败:第" + (context.readRowHolder().getRowIndex() + 1) +"行ID信息为空");
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
@Transactional
private ResponseResult saveData() {
//获取数据库上所有的学生数据
List<UserDevice> allUserList = userService.getAllUser();
log.info("{}条数据，开始存储数据库！", list.size());
List<UserExcelRecordDTO> userList = new ArrayList<>(); //创建需要保存的实体类User集合
for (int i = 0; i < list.size(); i++) {
try {
UserExcelRecordDTO user = UserExcelRecordDTO
.builder()
//ID
.id(list.get(i).getId())
//姓名
.userName(list.get(i).getUserName())
//密码
.password(list.get(i).getPassword())
.build();
//添加到集合
userList.add(user);
} catch (Exception e) {
log.error(e.getMessage());
return ResponseResult.errorResult(AppHttpCodeEnum.PARAM_REQUIRE);
}
}
/**
* 数据库上的所有学生数据
*/
//收集集合上的所有的学生ID数据信息
List<Integer> collectUserIdSet = allUserList.stream().map(User::getId).collect(Collectors.toList());
/**
* 进行excel导入的所有学生ID
*/
//收集所有的要进行导入的ID，为了校验一致性
List<Integer> SetId = userList.stream().map(UserExcelRecordDTO::getId).collect(Collectors.toList());
/**
* 校验导入的学生ID数据是否存在
*/
if (collectUserIdSet.containsAll(SetId)) {
log.error("导入的excel上存在重复的学生ID，请检查！！！");
return ResponseResult.errorResult(AppHttpCodeEnum.UPLOAD_INSERT_DECODE);
}
if (!CollectionUtils.isEmpty(userList)) {
userService.saveUser(userList); //集合不为空之后存储用户
}else{
log.error("数据存储失败，请检查数据格式！！！");
return ResponseResult.errorResult(AppHttpCodeEnum.UPLOAD_DATA_ERROR);
}
log.info("存储数据库成功！");
return ResponseResult.okResult(AppHttpCodeEnum.SUCCESS);
}
}四、数据的导出
4.1、UserExcel
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("UserExcel")
public class UserExcel implements Serializable {
/**
* id
*/
@Excel(name = "id", width = 35, orderNum = "1")
private Integer id;
/**
* 姓名
*/
@Excel(name = "姓名", width = 35, orderNum = "2")
private String userName;
/**
* 设备名称
*/
@Excel(name = "密码", width = 35, orderNum = "3")
private String password;
}4.2、controller
@GetMapping("/export/{Id}")
@ApiOperation("学生数据的导出操作")
public void exportUserDate(HttpServletResponse response, @PathVariable Integer Id) {
log.info("当前传递的项目ID：{}", Id);
userService.exportUserDate(response, Id);
}4.3、service & serviceImpl
4.3.1、service
void exportUserDate(HttpServletResponse response,Integer Id);4.3.2、serviceImpl
public void exportUserDate(HttpServletResponse response, Integer Id) {
// 从数据库获取需要导出的数据
LambdaQueryWrapper<user> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(user::getId, Id);
List<user> userList = userMapper.selectList(wrapper);
List<userExcel> userExcelList = BeanUtil.copyList(userList, userExcel.class);
//导出操作
OfficeExportUtil.exportExcel(OfficeExportUtil.getWorkbook("学生数据表", "Sheet", userExcel.class, userExcelList), "学生数据", response);
}4.4、utils
4.4.1、OfficeExportUtil
/**
* Office导出工具类
**/
@Slf4j
public class OfficeExportUtil {
/**
* 允许导出的最大条数
*/
private static final Integer EXPORT_EXCEL_MAX_NUM = 10000;
/**
* 获取导出的 Workbook对象
*
* @param title 大标题
* @param sheetName 页签名
* @param object 导出实体
* @param list 数据集合
* @return Workbook
*/
public static Workbook getWorkbook(String title, String sheetName, Class<?> object, List<?> list) {
//判断导出数据是否为空
if (list == null) {
list = new ArrayList<>();
}
//判断导出数据数量是否超过限定值
if (list.size() > EXPORT_EXCEL_MAX_NUM) {
title = "导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加导出条件!";
list = new ArrayList<>();
}
//获取导出参数
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
//设置导出样式
exportParams.setStyle(ExcelStyleUtil.class);
//设置行高
exportParams.setHeight((short) 8);
//输出Workbook流
return ExcelExportUtil.exportExcel(exportParams, object, list);
}
/**
* 获取导出的 Workbook对象
*
* @param path 模板路径
* @param map 导出内容map
* @return Workbook
*/
public static Workbook getWorkbook(String path, Map<String, Object> map) {
//获取导出模板
TemplateExportParams params = new TemplateExportParams(path);
//设置导出样式
params.setStyle(ExcelStyleUtil.class);
//输出Workbook流
return ExcelExportUtil.exportExcel(params, map);
}
/**
* 导出Excel
*
* @param workbook workbook流
* @param fileName 文件名
* @param response 响应
*/
public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) {
//给文件名拼接上日期
fileName = fileName + StrUtil.UNDERLINE + DateUtil.today();
//输出文件
try (OutputStream out = response.getOutputStream()) {
//获取文件名并转码
String name = URLEncoder.encode(fileName, "UTF-8");
//编码
// 设置强制下载不打开
response.setContentType("application/force-download");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xlsx");
//输出表格
workbook.write(out);
} catch (IOException e) {
log.error("文件导出异常,详情如下:", e);
} finally {
try {
if (workbook != null) {
//关闭输出流
workbook.close();
}
} catch (IOException e) {
log.error("文件导出异常,详情如下:", e);
}
}
}
}五、excel模版下载
5.1、controller
@GetMapping(value = "/download")
public void download(HttpServletResponse response) throws FileNotFoundException {
userService.downloadExcel(response);
}5.2、service & serviceImpl
5.2.1、service
void downloadExcel(HttpServletResponse response);5.2.2、serviceImpl
public void downloadExcel(HttpServletResponse response) {
//下载模板
//方法一：直接下载路径下的文件模板（这种方式貌似在SpringCloud和Springboot中，打包成JAR包时，无法读取到指定路径下面的文件，不知道记错没，你们可以自己尝试下！！！）
try {
//文件名称
String fileName = "学生数据表.xlsx";
//设置要下载的文件的名称
response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
//通知客服文件的MIME类型
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//获取文件的路径
String filePath = this.getClass().getResource("/template/" + fileName).getPath();
FileInputStream input = new FileInputStream(filePath);
OutputStream out = response.getOutputStream();
byte[] b = new byte[2048];
int len;
while ((len = input.read(b)) != -1) {
out.write(b, 0, len);
}
//修正 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"
response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
input.close();
} catch (Exception ex) {
log.error("下载模版失败！！！");
ex.printStackTrace();
}
}excel模版所放置的位置
六、全局异常处理
6.1、GlobalExceptionHandler
@RestControllerAdvice
@Slf4j
public class GlobalExceptionHandler {
/**
* 捕获业务异常
* @param ex
* @return
*/
@ExceptionHandler
public Result exceptionHandler(BaseException ex){
log.error("异常信息：{}", ex.getMessage());
return Result.error(ex.getMessage());
}
}6.2、BaseException
/**
* 业务异常
*/
public class BaseException extends RuntimeException {
public BaseException() {
}
public BaseException(String msg) {
super(msg);
}
}6.3、UploadExcelException
public class UploadExcelException extends BaseException {
public UploadExcelException(String msg) {
super(msg);
}
}总结
本文利用EasyExcel实现了Excel文件的导入功能。本文的主要目的是，从Controller层到Dao层全流程演示Excel文件的导入导出以及excel模版的下载，以后遇到相似的业务需求，复制即可运行。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
