springboot:整合easypoi

x33g5p2x  于2021-11-22 转载在 Spring  
字(17.4k)|赞(0)|评价(0)|浏览(491)

easypoi的常用注解
easypoi的导入校验
项目源码

springboot:整合easypoi

一、导出

pom文件

  1. <dependencies>
  2. <dependency>
  3. <groupId>cn.afterturn</groupId>
  4. <artifactId>easypoi-spring-boot-starter</artifactId>
  5. <version>4.0.0</version>
  6. </dependency>
  7. <dependency>
  8. <groupId>javax.validation</groupId>
  9. <artifactId>validation-api</artifactId>
  10. <version>2.0.1.Final</version>
  11. </dependency>
  12. <dependency>
  13. <groupId>org.hibernate.validator</groupId>
  14. <artifactId>hibernate-validator</artifactId>
  15. <version>6.0.16.Final</version>
  16. <scope>compile</scope>
  17. </dependency>
  18. <!--这个是我自己定义的一个公共包 -->
  19. <dependency>
  20. <groupId>com.hl</groupId>
  21. <artifactId>springboot-common</artifactId>
  22. <version>0.0.1-SNAPSHOT</version>
  23. </dependency>
  24. <dependency>
  25. <groupId>org.springframework.boot</groupId>
  26. <artifactId>spring-boot-starter-web</artifactId>
  27. </dependency>
  28. </dependencies>

实体类

CourseEntity.java

  1. @Data
  2. @ExcelTarget("courseEntity")
  3. public class CourseEntity {
  4. /** * 主键 */
  5. private String id;
  6. /** * 课程名称 * needMerge 是否需要纵向合并单元格(用于list创建的多个row) */
  7. @Excel(name = "课程名称", orderNum = "0", width = 25, needMerge = true)
  8. private String name;
  9. /** * 老师主键 */
  10. @ExcelEntity(id = "absent")
  11. private TeacherEntity mathTeacher;
  12. @ExcelCollection(name = "学生", orderNum = "2")
  13. private List<StudentEntity> students;
  14. }

StudentEntity.java

  1. @Data
  2. public class StudentEntity implements java.io.Serializable {
  3. /** * id */
  4. private String id;
  5. /** * 学生姓名 */
  6. @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true")
  7. private String name;
  8. /** * 学生性别 */
  9. @Excel(name = "学生性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true")
  10. private int sex;
  11. @Excel(name = "出生日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true", width = 20)
  12. private Date birthday;
  13. }

TeacherEntity.java

  1. @Data
  2. public class TeacherEntity implements java.io.Serializable {
  3. /** * 教师名称 * isImportField 导入Excel时,对Excel中的字段进行校验,如果没有该字段,导入失败 */
  4. @Excel(name = "教师姓名", width = 30, orderNum = "1", isImportField = "true",needMerge = true)
  5. private String name;
  6. /** * 教师性别 * replace 值的替换,`replace = {"男_1", "女_2"} `将值为1的替换为男 * suffix 文字后缀 */
  7. @Excel(name = "教师性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true", orderNum = "2",needMerge = true)
  8. private int sex;
  9. }

工具类

  1. package com.hl.springbooteasypoi.util;
  2. import cn.afterturn.easypoi.excel.ExcelExportUtil;
  3. import cn.afterturn.easypoi.excel.ExcelImportUtil;
  4. import cn.afterturn.easypoi.excel.entity.ExportParams;
  5. import cn.afterturn.easypoi.excel.entity.ImportParams;
  6. import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
  7. import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
  8. import cn.afterturn.easypoi.excel.imports.ExcelImportService;
  9. import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
  10. import org.apache.commons.lang3.StringUtils;
  11. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  12. import org.apache.poi.ss.usermodel.Workbook;
  13. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  14. import org.springframework.web.multipart.MultipartFile;
  15. import javax.servlet.ServletOutputStream;
  16. import javax.servlet.http.HttpServletResponse;
  17. import java.io.File;
  18. import java.io.IOException;
  19. import java.io.InputStream;
  20. import java.net.URLEncoder;
  21. import java.util.HashMap;
  22. import java.util.List;
  23. import java.util.Map;
  24. import java.util.NoSuchElementException;
  25. /** * @ClassName: ExcelUtils * @Description: excle工具类 */
  26. public class ExcelUtils {
  27. /** * excel 导出 * * @param list 数据 * @param title 标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param isCreateHeader 是否创建表头 * @param response */
  28. public static void exportExcel(List<?> list, String title, String sheetName,
  29. Class<?> pojoClass, String fileName,
  30. boolean isCreateHeader, HttpServletResponse response) throws IOException {
  31. ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
  32. exportParams.setCreateHeadRows(isCreateHeader);
  33. defaultExport(list, pojoClass, fileName, response, exportParams);
  34. }
  35. /** * excel 导出 * * @param list 数据 * @param title 标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response */
  36. public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
  37. defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
  38. }
  39. /** * excel 导出 * * @param list 数据 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response * @param exportParams 导出参数 */
  40. public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
  41. defaultExport(list, pojoClass, fileName, response, exportParams);
  42. }
  43. /** * excel 导出 * * @param list 数据 * @param fileName 文件名称 * @param response */
  44. public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
  45. defaultExport(list, fileName, response);
  46. }
  47. /** * 默认的 excel 导出 * * @param list 数据 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response * @param exportParams 导出参数 */
  48. private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
  49. HttpServletResponse response, ExportParams exportParams) throws IOException {
  50. Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
  51. downLoadExcel(fileName, response, workbook);
  52. }
  53. /** * 默认的 excel 导出 * * @param list 数据 * @param fileName 文件名称 * @param response */
  54. private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
  55. Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
  56. downLoadExcel(fileName, response, workbook);
  57. }
  58. /** * 下载 * * @param fileName 文件名称 * @param response * @param workbook excel数据 */
  59. private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
  60. ServletOutputStream out = null;
  61. try {
  62. response.setCharacterEncoding("UTF-8");
  63. response.setHeader("content-Type", "application/vnd.ms-excel");
  64. response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
  65. out = response.getOutputStream();
  66. workbook.write(out);
  67. } catch (Exception e) {
  68. throw new IOException(e.getMessage());
  69. }finally {
  70. if (out != null){
  71. out.close();
  72. }
  73. if (workbook != null){
  74. workbook.close();
  75. }
  76. }
  77. }
  78. /** * excel 导入 * * @param filePath excel文件路径 * @param titleRows 标题行 * @param headerRows 表头行 * @param pojoClass pojo类型 * @param <T> * @return */
  79. public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
  80. if (StringUtils.isBlank(filePath)) {
  81. return null;
  82. }
  83. ImportParams params = new ImportParams();
  84. params.setTitleRows(titleRows);
  85. params.setHeadRows(headerRows);
  86. params.setNeedSave(true);
  87. params.setSaveUrl("/excel/");
  88. try {
  89. return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
  90. } catch (NoSuchElementException e) {
  91. throw new IOException("模板不能为空");
  92. } catch (Exception e) {
  93. throw new IOException(e.getMessage());
  94. }
  95. }
  96. /** * excel 导入 * * @param file excel文件 * @param pojoClass pojo类型 * @param <T> * @return */
  97. public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
  98. return importExcel(file, 1, 1, pojoClass);
  99. }
  100. /** * excel 导入 * * @param file excel文件 * @param titleRows 标题行 * @param headerRows 表头行 * @param pojoClass pojo类型 * @param <T> * @return */
  101. public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
  102. return importExcel(file, titleRows, headerRows, false, pojoClass);
  103. }
  104. /** * excel 导入带错误形象和行数 * @param <T> * @param file excel文件 * @param titleRows 标题行 * @param headerRows 表头行 * @param needVerfiy 是否需要校验 * @param verifyHandler 自定义校验规则 * @param pojoClass pojo类型 * @return * @throws IOException */
  105. public static <T> ExcelImportResult importExcelResult(MultipartFile file, Integer titleRows, Integer headerRows,
  106. boolean needVerfiy, IExcelVerifyHandler<?> verifyHandler,
  107. Class<T> pojoClass) throws IOException {
  108. if (file == null) {
  109. return null;
  110. }
  111. if (checkExcelFormat(file)){
  112. InputStream in = null;
  113. try {
  114. ImportParams importParams = new ImportParams();
  115. importParams.setTitleRows(titleRows); // 设置标题列占几行
  116. importParams.setHeadRows(headerRows); // 设置字段名称占几行 即header
  117. importParams.setNeedVerify(needVerfiy);//开启校验
  118. importParams.setVerifyHandler(verifyHandler);// 这个类是自己创建的
  119. importParams.setStartSheetIndex(0); // 设置从第几张表格开始读取,这里0代表第一张表,默认从第一张表读取
  120. in= file.getInputStream();
  121. return new ExcelImportService().importExcelByIs(in, pojoClass, importParams, true);
  122. } catch (Exception e) {
  123. throw new IOException(e.getMessage());
  124. }finally {
  125. if (in != null){
  126. in.close();
  127. }
  128. }
  129. }
  130. return null;
  131. }
  132. private static Boolean checkExcelFormat(MultipartFile file) throws IOException {
  133. //获取文件名
  134. String fileName = file.getOriginalFilename();
  135. //验证文件名是否合格(xlsx, xls, xlsm,xlt)
  136. //文件后缀名校验
  137. if (!(fileName.endsWith("xls") || fileName.endsWith("xlsx") || fileName.endsWith("xlsm") || fileName.endsWith("xlt"))) {
  138. throw new RuntimeException("上传文件格式不正确,请传入正确的Excel文件");
  139. }
  140. //验证导入工时的标题头是否合法
  141. String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
  142. if (!"xls".equals(suffix) && !"xlsx".equals(suffix)) {
  143. throw new RuntimeException("上传文件只支持xls和xlsx文件后缀");
  144. }
  145. return true;
  146. }
  147. /** * excel 导入 * * @param file 上传的文件 * @param titleRows 标题行 * @param headerRows 表头行 * @param needVerfiy 是否检验excel内容 * @param pojoClass pojo类型 * @param <T> * @return */
  148. public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
  149. if (file == null) {
  150. return null;
  151. }
  152. try {
  153. return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
  154. } catch (Exception e) {
  155. throw new IOException(e.getMessage());
  156. }
  157. }
  158. /** * excel 导入 * * @param inputStream 文件输入流 * @param titleRows 标题行 * @param headerRows 表头行 * @param needVerfiy 是否检验excel内容 * @param pojoClass pojo类型 * @param <T> * @return */
  159. public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
  160. if (inputStream == null) {
  161. return null;
  162. }
  163. ImportParams params = new ImportParams();
  164. params.setTitleRows(titleRows);
  165. params.setHeadRows(headerRows);
  166. params.setSaveUrl("/excel/");
  167. params.setNeedSave(true);
  168. params.setNeedVerify(needVerfiy);
  169. try {
  170. return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
  171. } catch (NoSuchElementException e) {
  172. throw new IOException("excel文件不能为空");
  173. } catch (Exception e) {
  174. throw new IOException(e.getMessage());
  175. }finally {
  176. if (inputStream != null){
  177. inputStream.close();
  178. }
  179. }
  180. }
  181. /** * Excel 类型枚举 */
  182. enum ExcelTypeEnum {
  183. /** * 文件类型 */
  184. XLS("xls"), XLSX("xlsx");
  185. private String value;
  186. ExcelTypeEnum(String value) {
  187. this.value = value;
  188. }
  189. public String getValue() {
  190. return value;
  191. }
  192. public void setValue(String value) {
  193. this.value = value;
  194. }
  195. }
  196. }

service

  1. @Service
  2. public class ExportExcelService {
  3. /** * 导出 */
  4. public HttpResponseTemp<?> exportExcel(HttpServletResponse response) throws IOException {
  5. List<CourseEntity> courseEntityList = new ArrayList<>();
  6. CourseEntity courseEntity = new CourseEntity();
  7. courseEntity.setId("1");
  8. courseEntity.setName("测试课程");
  9. TeacherEntity teacherEntity = new TeacherEntity();
  10. teacherEntity.setName("张老师");
  11. teacherEntity.setSex(1);
  12. courseEntity.setMathTeacher(teacherEntity);
  13. List<StudentEntity> studentEntities = new ArrayList<>();
  14. for (int i = 1; i <= 2; i++) {
  15. StudentEntity studentEntity = new StudentEntity();
  16. studentEntity.setName("学生" + i);
  17. studentEntity.setSex(i);
  18. studentEntity.setBirthday(new Date());
  19. studentEntities.add(studentEntity);
  20. }
  21. courseEntity.setStudents(studentEntities);
  22. courseEntityList.add(courseEntity);
  23. Date start = new Date();
  24. String fileName = "导出文件";
  25. ExcelUtils.exportExcel(courseEntityList,"导出测试","测试",CourseEntity.class,fileName,true,response);
  26. System.out.println(System.currentTimeMillis() - start.getTime());
  27. return ResultStat.OK.wrap(null,"导出成功");
  28. }
  29. }

controller

  1. @Controller
  2. @RequestMapping("/user")
  3. public class ExportExcelController {
  4. @Autowired
  5. private ExportExcelService exportExcelService;
  6. @GetMapping("/export")
  7. public HttpResponseTemp<?> exportExcel(HttpServletResponse response) throws IOException {
  8. return exportExcelService.exportExcel(response);
  9. }
  10. }

导出结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5amdI3eO-1637580252521)(springboot:整合easypoi.assets/image-20211122152259679.png)]

二、导入

普通导入

controller 和service
  1. @Controller
  2. @RequestMapping("/user")
  3. public class ImportExcelController {
  4. @Autowired
  5. private ImportService importService;
  6. @PostMapping("/import")
  7. HttpResponseTemp<?> importExcel(MultipartFile file) throws Exception {
  8. return importService.importExcel(file);
  9. }
  10. }
  11. @Service
  12. public class ImportService {
  13. public HttpResponseTemp<?> importExcel(MultipartFile file) throws Exception {
  14. List<CourseEntity> courseEntityList = ExcelUtils.importExcel(file,1,2,CourseEntity.class);
  15. System.out.println("成功导入:" + JSONUtil.toJsonStr(courseEntityList));
  16. return ResultStat.OK.wrap(courseEntityList,"导入成功");
  17. }
  18. }
导入结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kKcz7GrE-1637580252523)(springboot:整合easypoi.assets/image-20211122162005668.png)]

校验导入

现在产品需要对导入的Excel进行校验,不合法的Excel不允许入库,需要返回具体的错误信息给前端,提示给用户,错误信息中需要包含行号以及对应的错误。

因为 EasyPOI 支持 Hibernate Validator ,所以直接使用就可以了,因为要将错误信息以及错误行号返回,所以需要用到 EasyPOI 的高级用法,实现 IExcelDataModel与 IExcelModel接口,IExcelDataModel负责设置行号,IExcelModel 负责设置错误信息

修改实体类

实现 IExcelDataModel与 IExcelModel接口,并且重写其中的方法,并且自定义errorMsg和rowNum来接受下面重写接口的值

这里需要注意俩点:
1.如果要对这个字段进行校验需要 isImportField = "true"参数

2.如果想要嵌套校验,需要在被嵌套的对象上加入@Valid注解

  1. @Data
  2. @ExcelTarget("courseEntity")
  3. public class CourseEntity implements Serializable,IExcelModel,IExcelDataModel {
  4. /** * 主键 */
  5. private String id;
  6. /** * 课程名称 * needMerge 是否需要纵向合并单元格(用于list创建的多个row) */
  7. @Excel(name = "课程名称", orderNum = "0", width = 25, needMerge = true,isImportField = "true")
  8. @NotBlank(message = "课程名称不能为空")
  9. private String name;
  10. /** * 老师主键 */
  11. @Valid
  12. @ExcelEntity(id = "absent")
  13. private TeacherEntity mathTeacher;
  14. @Valid
  15. @ExcelCollection(name = "学生", orderNum = "2")
  16. private List<StudentEntity> students;
  17. private String errorMsg; //自定义一个errorMsg接受下面重写IExcelModel接口的get和setErrorMsg方法。
  18. private Integer rowNum; //自定义一个rowNum接受下面重写IExcelModel接口的get和setRowNum方法。
  19. @Override
  20. public String getErrorMsg() {
  21. return errorMsg;
  22. }
  23. @Override
  24. public void setErrorMsg(String errorMsg) {
  25. this.errorMsg = errorMsg;
  26. }
  27. @Override
  28. public int getRowNum() {
  29. return rowNum;
  30. }
  31. @Override
  32. public void setRowNum(int rowNum) {
  33. this.rowNum = rowNum;
  34. }
  35. }
  1. @Data
  2. public class StudentEntity implements java.io.Serializable {
  3. /** * id */
  4. private String id;
  5. /** * 学生姓名 */
  6. @Excel(name = "学生姓名",width = 30, isImportField = "true")
  7. @NotBlank(message = "学生姓名不可以为空")
  8. private String name;
  9. /** * 学生性别 */
  10. @Excel(name = "学生性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true")
  11. private int sex;
  12. @Excel(name = "出生日期", exportFormat = "yyyy-MM-dd", format = "yyyy-MM-dd", isImportField = "true", width = 20)
  13. private Date birthday;
  14. }
  1. @Data
  2. public class TeacherEntity implements java.io.Serializable {
  3. /** * 教师名称 * isImportField 导入Excel时,对Excel中的字段进行校验,如果没有该字段,导入失败 */
  4. @Excel(name = "教师姓名", width = 30, orderNum = "1", isImportField = "true",needMerge = true)
  5. @NotBlank(message = "教师姓名不可以为空")
  6. private String name;
  7. /** * 教师性别 * replace 值的替换,`replace = {"男_1", "女_2"} `将值为1的替换为男 * suffix 文字后缀 */
  8. @Excel(name = "教师性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true", orderNum = "2",needMerge = true)
  9. @NotNull(message = "教师性别不可以为空")
  10. private int sex;
  11. }
自定义校验类
  1. package com.hl.springbooteasypoi.verifyHandler;
  2. import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
  3. import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
  4. import com.hl.springbooteasypoi.pojo.CourseEntity;
  5. public class MyVerifyHandler implements IExcelVerifyHandler<CourseEntity> {
  6. @Override
  7. public ExcelVerifyHandlerResult verifyHandler(CourseEntity courseEntity) {
  8. ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
  9. //假设我们要添加用户,
  10. //现在去数据库查询getName,如果存在则表示校验不通过。
  11. //假设现在数据库中有个getName 测试课程
  12. if ("测试课程".equals(courseEntity.getName())) {
  13. result.setMsg("该课程已存在");
  14. result.setSuccess(false);
  15. return result;
  16. }
  17. result.setSuccess(true);
  18. return result;
  19. }
  20. }
controller和service
  1. public HttpResponseTemp<?> checkImportExcel(MultipartFile file) throws IOException {
  2. ExcelImportResult excelImportResult = ExcelUtils.importExcelResult(file, 1, 2, true, new MyVerifyHandler(), CourseEntity.class);
  3. //成功导入
  4. List<CourseEntity> list = excelImportResult.getList();
  5. //失败导入
  6. List<CourseEntity> failList = excelImportResult.getFailList();
  7. HashSet<String> set = new HashSet<>();
  8. for (CourseEntity courseEntity : failList) {
  9. int rowNum = courseEntity.getRowNum();
  10. String errorMsg = courseEntity.getErrorMsg();
  11. String msg = "第" + rowNum + "行的错误是:" + errorMsg;
  12. set.add(msg);
  13. }
  14. System.out.println("导入成功:" + JSONUtil.toJsonStr(list));
  15. System.out.println("导入失败:" + JSONUtil.toJsonStr(failList));
  16. System.out.println("错误信息:" + JSONUtil.toJsonStr(set));
  17. return ResultStat.OK.wrap(null,"导入成功");
  18. }
  1. @Controller
  2. @RequestMapping("/user")
  3. public class ImportExcelController {
  4. @Autowired
  5. private ImportService importService;
  6. @PostMapping("/checkImport")
  7. HttpResponseTemp<?> checkImportExcel(MultipartFile file) throws Exception {
  8. return importService.checkImportExcel(file);
  9. }
  10. }
校验结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5KtmdsaM-1637580252524)(springboot:整合easypoi.assets/image-20211122192307115.png)]

相关文章