文章目录
springboot：整合easypoi
一、导出
pom文件
<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
@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
@Controller
@RequestMapping("/user")
public class ExportExcelController {
@Autowired
private ExportExcelService exportExcelService;
@GetMapping("/export")
public HttpResponseTemp<?> exportExcel(HttpServletResponse response) throws IOException {
return exportExcelService.exportExcel(response);
}
}
导出结果
二、导入
普通导入
controller 和service
@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,"导入成功");
}
}
导入结果
校验导入
现在产品需要对导入的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;
}
}
controller和service
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);
}
}
校验结果
625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
