easyexcel表格导入

上一次出了一篇关于excel表格导入教程，现在在原来的基础上来进行扩展，增加字段校验与异常处理

1.导入依赖:

首先介绍一下我们需要的pom依赖：

	<!--easypoi依赖，excel导入导出的主要依赖-->
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>3.0.3</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-web</artifactId>
      <version>3.0.3</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
      <version>3.0.3</version>
    </dependency>
    <!-- 阿里 FastJson 依赖 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.57</version>
    </dependency>

2.主要代码:

1.一个导入导出工具类:

package com.ruankao.common.utils.easyexcel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author yangli
 * @description 基于easy-excel的导入导出工具类
 * easy-excel参考网址：http://easypoi.mydoc.io/#text_217704
 * 该工具类来源：https://www.cnblogs.com/better-farther-world2099/articles/11831695.html
 */
public class EasyExcelUtil {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null) {
            ;
        }
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            //throw new NormalException(e.getMessage());
            e.printStackTrace();
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) {
            ;
        }
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            //throw new NormalException("模板不能为空");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
            //throw new NormalException(e.getMessage());
        }
        return list;
    }

    /**
     * excel常规导入(默认只读取第一张sheet工作簿)
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            // throw new NormalException("excel文件不能为空");
            e.printStackTrace();
        } catch (Exception e) {
            //throw new NormalException(e.getMessage());
            e.printStackTrace();
        }
        return list;
    }

    /**
     * excel自定义导入,记得设置titleRows与headRows
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass, ImportParams params){
        if (file == null){
            return null;
        }
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            // throw new NormalException("excel文件不能为空");
            e.printStackTrace();
        } catch (Exception e) {
            //throw new NormalException(e.getMessage());
            e.printStackTrace();
        }
        return list;
    }
}

2.一个导入导出的Vo类：

package com.ruankao.common.utils.easyexcel.model;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.ruankao.common.utils.easyexcel.ExcelModelVaild;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;

import javax.validation.constraints.Max;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

/**
 * @author yangli
 * @description 基础简单选择题-excel导入模板模型
 * @date 2021-02-08
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class BaseEasyChoiceExcel {

    /**
     * value-->表头 index-->列（从0开始）
     */
    @NotBlank(message = "第1列：题目内容不能为空;")
    @Size(max = 255, message = "第1列：最大长度为255;")
    @Excel(name = "题目内容", orderNum = "0")
    private String content;

    @NotBlank(message = "第2列：选项内容不能为空;")
    @Size(max = 255, message = "第2列：最大长度为255;")
    @Excel(name = "选项A", orderNum = "1")
    private String optionA;

    @NotBlank(message = "第3列：选项内容不能为空;")
    @Size(max = 255, message = "第3列：最大长度为255;")
    @Excel(name = "选项B", orderNum = "2")
    private String optionB;

    @NotBlank(message = "第4列：选项内容不能为空;")
    @Size(max = 255, message = "第4列：最大长度为255;")
    @Excel(name = "选项C", orderNum = "3")
    private String optionC;

    @NotBlank(message = "第5列：选项内容不能为空;")
    @Size(max = 255, message = "第5列：最大长度为255;")
    @Excel(name = "选项D", orderNum = "4")
    private String optionD;

    @NotBlank(message = "第6列：题目答案不能为空;")
    @Size(max = 255, message = "第6列：最大长度为255;")
    @Excel(name = "题目答案", orderNum = "5")
    private String answer;

    @NotBlank(message = "第7列：题目解析不能为空;")
    @Size(max = 255, message = "第7列：最大长度为255;")
    @Excel(name = "题目解析", orderNum = "6")
    private String answerAnalysis;

}

3.增加字段校验类

package com.ruankao.common.utils.easyexcel;

import com.ruankao.common.utils.easyexcel.model.BaseEasyChoiceExcel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.validation.ConstraintViolation;
import javax.validation.Validator;
import java.util.*;

/**
 * @author yangli
 * @description: excel导入题目，数据校验类，结合hibernate的检验，返回excel某行某列校验异常信息
 * @date 2021-02-09
 */
@Component
public class ExcelModelVaild {

    @Autowired
    private Validator validator;

    public Map<String, String> vaildBaseEasyChoiceExcelList(List<BaseEasyChoiceExcel> list) {
        if (list == null) {
            return null;
        }
        Map<String, String> result = new LinkedHashMap<>();
        final int[] i = {0};
        list.forEach(p -> {
            i[0] = i[0] +1;
            String name = "第"+ i[0] +"行";
            String vaildResult = vaildBaseEasyChoiceExcel(p).toString();
            if (vaildResult == null || vaildResult.equals("")) {
                return;
            }
            result.put(name,vaildResult);
        });
        if (result.size() == 0) {
            return null;
        }
        return result;
    }

    /**
     * 单个模板实体类数据校验,返回该实体类的所有校验不合格信息-----简单基础选择题模板
     */
    public StringBuilder vaildBaseEasyChoiceExcel(BaseEasyChoiceExcel base) {
        if (base == null) {
            return null;
        }
        StringBuilder vaildResult = new StringBuilder();
        Set<ConstraintViolation<BaseEasyChoiceExcel>> result = validator.validate(base);
        result.stream().forEach(item->{
            vaildResult.append(item.getMessage());
        });
        return vaildResult;
    }
}

4.自定义异常类：

package com.ruankao.common.exception;

/**
 * @author yangli
 * @date 2020-12-24
 * @description 自定义异常类
 */
public class MyBaseException extends RuntimeException {

    private Integer code;
    private String errorMessage;

    public MyBaseException(final Integer code, final String errorMessage) {
        this.code = code;
        this.errorMessage = errorMessage;
    }

    public MyBaseException(final Integer code, final Throwable throwable) {
        super(throwable);
        this.code = code;
    }

    public final Integer getCode() {
        return  this.code;
    }

    public final String getErrorMessage() {
        return this.errorMessage;
    }

}

有了数据校验类，与自定义异常类，我们这里来做一个综合处理，把excel中获取的内容转换为我们需要的实体类，并进行数据校验，抛出自定义异常供前端用户查看异常信息。

来看一下控制层的异常处理：

    /**
     * 通过EXCEL表格批量导入题目
     * @return
     */
    @PostMapping("/uploadExcel")
    public Result<Object> uploadProblemExcel(Integer type, MultipartFile file) {
        // 将excel的内容转换为ProblemVo类，供后续批量提交
        List<ProblemVo> problemVos = new LinkedList<>();
        try {
            problemVos = problemExcelHandle.handleExcelToProblemVo(type,file);
        } catch (MyBaseException e) {
            // 判断是否为数据格式校验异常
            if (e.getCode().equals(ResultEnum.PROBLEM_EXCEL_VAILD_ERROR.getCode())) {
                // excel解析完毕，捕获模板类数据校验异常信息
                Map<String,Object> vaildResult = new HashMap<>();
                Map<String, String> vaildError = JSONObject.parseObject(e.getErrorMessage(),Map.class);
                vaildResult.put("isError", "2");
                vaildResult.put("vaildError", vaildError);
                return new Result<>(ResultEnum.PROBLEM_EXCEL_VAILD_ERROR.getMsg(),vaildResult);
            }
            return new Result<>(e.getCode(),e.getErrorMessage());
        } catch (Exception e) {
            // 其它异常
            return new Result<>(ResultEnum.PROBLEM_EXCEL_PARSING_ERROR.getCode(),ResultEnum.PROBLEM_EXCEL_PARSING_ERROR.getMsg());
        }

        // 保存题目信息到数据库，并收集异常信息返回前端
        try {
            // 返回提交成功的id集合
            Map<String,Object> result = new HashMap<>();
            List<Integer> idList = problemService.addOrUpdateList(problemVos);
            result.put("isError", "0");
            result.put("idList", idList);
            return new Result<>("excel导入成功",result);
        } catch (MyBaseException e) {
            // 判断是否为批量删除或修改错误
            if (e.getCode().equals(ResultEnum.PROBLEM_INSERT_OR_UPDATE_ERROR.getCode())) {
                // 导入失败，捕获并返回提交失败的数据，供用户修正
                Map<String,Object> newResult = new HashMap<>();
                List<ProblemVo> errorList = JSONArray.parseArray(e.getErrorMessage(),ProblemVo.class);
                newResult.put("isError", "1");
                newResult.put("errorList", errorList);
                return new Result<>(ResultEnum.PROBLEM_INSERT_OR_UPDATE_ERROR.getMsg(),newResult);
            }
            return new Result<>(e.getCode(),e.getErrorMessage());
        }
    }

到此就可以实现excel导入时进行数据校验，以及校验失败的异常处理功能了，当然小伙伴们可以根据业务需求增加其它扩展，使程序更加健壮。