Spring Boot上Excel的导入、导出与模版的下载

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

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模版的下载，以后遇到相似的业务需求，复制即可运行。