easypoi的常用注解
easypoi的导入校验
项目源码
<dependencies>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>javax.validation</groupId>
<artifactId>validation-api</artifactId>
<version>2.0.1.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate.validator</groupId>
<artifactId>hibernate-validator</artifactId>
<version>6.0.16.Final</version>
<scope>compile</scope>
</dependency>
<!--这个是我自己定义的一个公共包 -->
<dependency>
<groupId>com.hl</groupId>
<artifactId>springboot-common</artifactId>
<version>0.0.1-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
</dependencies>
CourseEntity.java
@Data
@ExcelTarget("courseEntity")
public class CourseEntity {
/** * 主键 */
private String id;
/** * 课程名称 * needMerge 是否需要纵向合并单元格(用于list创建的多个row) */
@Excel(name = "课程名称", orderNum = "0", width = 25, needMerge = true)
private String name;
/** * 老师主键 */
@ExcelEntity(id = "absent")
private TeacherEntity mathTeacher;
@ExcelCollection(name = "学生", orderNum = "2")
private List<StudentEntity> students;
}
StudentEntity.java
@Data
public class StudentEntity implements java.io.Serializable {
/** * id */
private String id;
/** * 学生姓名 */
@Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true")
private String name;
/** * 学生性别 */
@Excel(name = "学生性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true")
private int sex;
@Excel(name = "出生日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true", width = 20)
private Date birthday;
}
TeacherEntity.java
@Data
public class TeacherEntity implements java.io.Serializable {
/** * 教师名称 * isImportField 导入Excel时,对Excel中的字段进行校验,如果没有该字段,导入失败 */
@Excel(name = "教师姓名", width = 30, orderNum = "1", isImportField = "true",needMerge = true)
private String name;
/** * 教师性别 * replace 值的替换,`replace = {"男_1", "女_2"} `将值为1的替换为男 * suffix 文字后缀 */
@Excel(name = "教师性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true", orderNum = "2",needMerge = true)
private int sex;
}
package com.hl.springbooteasypoi.util;
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 cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/** * @ClassName: ExcelUtils * @Description: excle工具类 */
public class ExcelUtils {
/** * excel 导出 * * @param list 数据 * @param title 标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param isCreateHeader 是否创建表头 * @param response */
public static void exportExcel(List<?> list, String title, String sheetName,
Class<?> pojoClass, String fileName,
boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/** * excel 导出 * * @param list 数据 * @param title 标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response */
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
/** * excel 导出 * * @param list 数据 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response * @param exportParams 导出参数 */
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/** * excel 导出 * * @param list 数据 * @param fileName 文件名称 * @param response */
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, fileName, response);
}
/** * 默认的 excel 导出 * * @param list 数据 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response * @param exportParams 导出参数 */
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
HttpServletResponse response, ExportParams exportParams) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
/** * 默认的 excel 导出 * * @param list 数据 * @param fileName 文件名称 * @param response */
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
downLoadExcel(fileName, response, workbook);
}
/** * 下载 * * @param fileName 文件名称 * @param response * @param workbook excel数据 */
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
ServletOutputStream out = null;
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
out = response.getOutputStream();
workbook.write(out);
} catch (Exception e) {
throw new IOException(e.getMessage());
}finally {
if (out != null){
out.close();
}
if (workbook != null){
workbook.close();
}
}
}
/** * excel 导入 * * @param filePath excel文件路径 * @param titleRows 标题行 * @param headerRows 表头行 * @param pojoClass pojo类型 * @param <T> * @return */
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedSave(true);
params.setSaveUrl("/excel/");
try {
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("模板不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/** * excel 导入 * * @param file excel文件 * @param pojoClass pojo类型 * @param <T> * @return */
public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
return importExcel(file, 1, 1, pojoClass);
}
/** * excel 导入 * * @param file excel文件 * @param titleRows 标题行 * @param headerRows 表头行 * @param pojoClass pojo类型 * @param <T> * @return */
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
return importExcel(file, titleRows, headerRows, false, pojoClass);
}
/** * excel 导入带错误形象和行数 * @param <T> * @param file excel文件 * @param titleRows 标题行 * @param headerRows 表头行 * @param needVerfiy 是否需要校验 * @param verifyHandler 自定义校验规则 * @param pojoClass pojo类型 * @return * @throws IOException */
public static <T> ExcelImportResult importExcelResult(MultipartFile file, Integer titleRows, Integer headerRows,
boolean needVerfiy, IExcelVerifyHandler<?> verifyHandler,
Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
if (checkExcelFormat(file)){
InputStream in = null;
try {
ImportParams importParams = new ImportParams();
importParams.setTitleRows(titleRows); // 设置标题列占几行
importParams.setHeadRows(headerRows); // 设置字段名称占几行 即header
importParams.setNeedVerify(needVerfiy);//开启校验
importParams.setVerifyHandler(verifyHandler);// 这个类是自己创建的
importParams.setStartSheetIndex(0); // 设置从第几张表格开始读取,这里0代表第一张表,默认从第一张表读取
in= file.getInputStream();
return new ExcelImportService().importExcelByIs(in, pojoClass, importParams, true);
} catch (Exception e) {
throw new IOException(e.getMessage());
}finally {
if (in != null){
in.close();
}
}
}
return null;
}
private static Boolean checkExcelFormat(MultipartFile file) throws IOException {
//获取文件名
String fileName = file.getOriginalFilename();
//验证文件名是否合格(xlsx, xls, xlsm,xlt)
//文件后缀名校验
if (!(fileName.endsWith("xls") || fileName.endsWith("xlsx") || fileName.endsWith("xlsm") || fileName.endsWith("xlt"))) {
throw new RuntimeException("上传文件格式不正确,请传入正确的Excel文件");
}
//验证导入工时的标题头是否合法
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
if (!"xls".equals(suffix) && !"xlsx".equals(suffix)) {
throw new RuntimeException("上传文件只支持xls和xlsx文件后缀");
}
return true;
}
/** * excel 导入 * * @param file 上传的文件 * @param titleRows 标题行 * @param headerRows 表头行 * @param needVerfiy 是否检验excel内容 * @param pojoClass pojo类型 * @param <T> * @return */
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/** * excel 导入 * * @param inputStream 文件输入流 * @param titleRows 标题行 * @param headerRows 表头行 * @param needVerfiy 是否检验excel内容 * @param pojoClass pojo类型 * @param <T> * @return */
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);
params.setNeedVerify(needVerfiy);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}finally {
if (inputStream != null){
inputStream.close();
}
}
}
/** * Excel 类型枚举 */
enum ExcelTypeEnum {
/** * 文件类型 */
XLS("xls"), XLSX("xlsx");
private String value;
ExcelTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
}
@Service
public class ExportExcelService {
/** * 导出 */
public HttpResponseTemp<?> exportExcel(HttpServletResponse response) throws IOException {
List<CourseEntity> courseEntityList = new ArrayList<>();
CourseEntity courseEntity = new CourseEntity();
courseEntity.setId("1");
courseEntity.setName("测试课程");
TeacherEntity teacherEntity = new TeacherEntity();
teacherEntity.setName("张老师");
teacherEntity.setSex(1);
courseEntity.setMathTeacher(teacherEntity);
List<StudentEntity> studentEntities = new ArrayList<>();
for (int i = 1; i <= 2; i++) {
StudentEntity studentEntity = new StudentEntity();
studentEntity.setName("学生" + i);
studentEntity.setSex(i);
studentEntity.setBirthday(new Date());
studentEntities.add(studentEntity);
}
courseEntity.setStudents(studentEntities);
courseEntityList.add(courseEntity);
Date start = new Date();
String fileName = "导出文件";
ExcelUtils.exportExcel(courseEntityList,"导出测试","测试",CourseEntity.class,fileName,true,response);
System.out.println(System.currentTimeMillis() - start.getTime());
return ResultStat.OK.wrap(null,"导出成功");
}
}
@Controller
@RequestMapping("/user")
public class ExportExcelController {
@Autowired
private ExportExcelService exportExcelService;
@GetMapping("/export")
public HttpResponseTemp<?> exportExcel(HttpServletResponse response) throws IOException {
return exportExcelService.exportExcel(response);
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5amdI3eO-1637580252521)(springboot:整合easypoi.assets/image-20211122152259679.png)]
@Controller
@RequestMapping("/user")
public class ImportExcelController {
@Autowired
private ImportService importService;
@PostMapping("/import")
HttpResponseTemp<?> importExcel(MultipartFile file) throws Exception {
return importService.importExcel(file);
}
}
@Service
public class ImportService {
public HttpResponseTemp<?> importExcel(MultipartFile file) throws Exception {
List<CourseEntity> courseEntityList = ExcelUtils.importExcel(file,1,2,CourseEntity.class);
System.out.println("成功导入:" + JSONUtil.toJsonStr(courseEntityList));
return ResultStat.OK.wrap(courseEntityList,"导入成功");
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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
注解
@Data
@ExcelTarget("courseEntity")
public class CourseEntity implements Serializable,IExcelModel,IExcelDataModel {
/** * 主键 */
private String id;
/** * 课程名称 * needMerge 是否需要纵向合并单元格(用于list创建的多个row) */
@Excel(name = "课程名称", orderNum = "0", width = 25, needMerge = true,isImportField = "true")
@NotBlank(message = "课程名称不能为空")
private String name;
/** * 老师主键 */
@Valid
@ExcelEntity(id = "absent")
private TeacherEntity mathTeacher;
@Valid
@ExcelCollection(name = "学生", orderNum = "2")
private List<StudentEntity> students;
private String errorMsg; //自定义一个errorMsg接受下面重写IExcelModel接口的get和setErrorMsg方法。
private Integer rowNum; //自定义一个rowNum接受下面重写IExcelModel接口的get和setRowNum方法。
@Override
public String getErrorMsg() {
return errorMsg;
}
@Override
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
@Override
public int getRowNum() {
return rowNum;
}
@Override
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
}
@Data
public class StudentEntity implements java.io.Serializable {
/** * id */
private String id;
/** * 学生姓名 */
@Excel(name = "学生姓名",width = 30, isImportField = "true")
@NotBlank(message = "学生姓名不可以为空")
private String name;
/** * 学生性别 */
@Excel(name = "学生性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true")
private int sex;
@Excel(name = "出生日期", exportFormat = "yyyy-MM-dd", format = "yyyy-MM-dd", isImportField = "true", width = 20)
private Date birthday;
}
@Data
public class TeacherEntity implements java.io.Serializable {
/** * 教师名称 * isImportField 导入Excel时,对Excel中的字段进行校验,如果没有该字段,导入失败 */
@Excel(name = "教师姓名", width = 30, orderNum = "1", isImportField = "true",needMerge = true)
@NotBlank(message = "教师姓名不可以为空")
private String name;
/** * 教师性别 * replace 值的替换,`replace = {"男_1", "女_2"} `将值为1的替换为男 * suffix 文字后缀 */
@Excel(name = "教师性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true", orderNum = "2",needMerge = true)
@NotNull(message = "教师性别不可以为空")
private int sex;
}
package com.hl.springbooteasypoi.verifyHandler;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.hl.springbooteasypoi.pojo.CourseEntity;
public class MyVerifyHandler implements IExcelVerifyHandler<CourseEntity> {
@Override
public ExcelVerifyHandlerResult verifyHandler(CourseEntity courseEntity) {
ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
//假设我们要添加用户,
//现在去数据库查询getName,如果存在则表示校验不通过。
//假设现在数据库中有个getName 测试课程
if ("测试课程".equals(courseEntity.getName())) {
result.setMsg("该课程已存在");
result.setSuccess(false);
return result;
}
result.setSuccess(true);
return result;
}
}
public HttpResponseTemp<?> checkImportExcel(MultipartFile file) throws IOException {
ExcelImportResult excelImportResult = ExcelUtils.importExcelResult(file, 1, 2, true, new MyVerifyHandler(), CourseEntity.class);
//成功导入
List<CourseEntity> list = excelImportResult.getList();
//失败导入
List<CourseEntity> failList = excelImportResult.getFailList();
HashSet<String> set = new HashSet<>();
for (CourseEntity courseEntity : failList) {
int rowNum = courseEntity.getRowNum();
String errorMsg = courseEntity.getErrorMsg();
String msg = "第" + rowNum + "行的错误是:" + errorMsg;
set.add(msg);
}
System.out.println("导入成功:" + JSONUtil.toJsonStr(list));
System.out.println("导入失败:" + JSONUtil.toJsonStr(failList));
System.out.println("错误信息:" + JSONUtil.toJsonStr(set));
return ResultStat.OK.wrap(null,"导入成功");
}
@Controller
@RequestMapping("/user")
public class ImportExcelController {
@Autowired
private ImportService importService;
@PostMapping("/checkImport")
HttpResponseTemp<?> checkImportExcel(MultipartFile file) throws Exception {
return importService.checkImportExcel(file);
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5KtmdsaM-1637580252524)(springboot:整合easypoi.assets/image-20211122192307115.png)]
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://blog.csdn.net/weixin_43296313/article/details/121478471
内容来源于网络,如有侵权,请联系作者删除!