文章内容
在开发中导入或者导出excel时，使用jxl或者poi的jar包需要要写一大段代码，而Easypoi对poi进行了封装，在导出的实体类上加入注解即可。
EasyPoi文档：http://easypoi.mydoc.io/
一、依赖引入
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>3.3.0</version>
</dependency>
在application.properties文件中加入：
spring.main.allow-bean-definition-overriding=true
加上上述配置，否则会报错
***************************
APPLICATION FAILED TO START
***************************
Description:
The bean 'beanNameViewResolver', defined in class path resource[cn/afterturn/easypoi/configuration
/EasyPoiAutoConfiguration.class], could not be registered. A bean with that name has already been
defined in class path resource [org/springframework/boot/autoconfigure/web/servlet/error/ErrorMvcAutoConfiguration$WhitelabelErrorViewConfiguration.class]
and overriding is disabled.
Action:
Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
二、Excel导出
1、导出实体类
创建一个people的实体类
@Data
public class People implements Serializable {
@Excel(name = "姓名" ,height = 20, width = 30)
private String name;
@Excel(name = "年龄")
private Integer age;
@Excel(name = "生日",exportFormat = "yyyy-MM-dd")
private String birthday;
}
@Excel 作用到filed上面,是对Excel一列的一个描述。可以设置长宽，日期格式等属性，具体请看文档。此外还有@ExcelCollection @ExcelEntity @ExcelIgnore@ExcelTarget 等注解。
2、导出工具类
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
public class ExcelUtil {
public static void exportExcel(List<?> list, String sheetName, Class<?> pojoClass, String fileName,
HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams();// 导出基本采用ExportParams 这个对象，进行参数配置；
exportParams.setSheetName(sheetName);// sheetName
exportParams.setType(ExcelType.XSSF);// 配置导出excel版本格式 ExcelType.XSSF后缀名为.xlsx ExcelType.HSSF后缀名为.xls
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));// 这里根据上面ExcelType.XSSF配置来配，如果是
// ExcelType.XSSF 对应 .xlsx
// ExcelType.HSSF对应.xls
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
}
3、导出示例
@GetMapping("export")
public void export(HttpServletResponse response){
List<People> peopleList = new ArrayList<>();
People data1 = new People();
People data2 = new People();
data1.setName("隔壁老王");
data1.setAge(30);
data1.setBirthday("1997-10-01 00:00:00");
data2.setName("钻石老王");
data2.setAge(40);
data2.setBirthday("1997-10-01 00:00:00");
peopleList.add(data1);
peopleList.add(data2);
try {
ExcelUtil.exportExcel(peopleList, "个人信息",People.class ,"个人信息" ,response );
} catch (IOException e) {
log.error("导出失败");
}
}
三、Excel导入
1、导入实体类
如果想对导入的数据进行校验，easypoi自带接口ExcelModel 获取错误信息，IExcelDataModel获取错误信息所在的行数。
创一个类去实现这两个接口：
public class ExcelVerifyInfo implements IExcelModel, IExcelDataModel {
private String errorMsg;
private int rowNum;
@Override
public int getRowNum() {
return rowNum;
}
@Override
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
@Override
public String getErrorMsg() {
return errorMsg;
}
@Override
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
}
对导入excel进行校验，对它的实体需要加一些注解
@Data
public class People extends ExcelVerifyInfo implements Serializable {
@Excel(name = "姓名" ,height = 20, width = 30)
@NotNull(message = "姓名不能为空")
private String name;
@Excel(name = "年龄")
@Max(value = 100,message = "年龄 最大值不能超过100" )
@NotNull(message = "年龄不能为空")
//@Pattern(regexp = "[u4E00-u9FA5]*", message = "不是中文")或者正则校验
private Integer age;
@Excel(name = "生日",exportFormat = "yyyy-MM-dd")
private String birthday;
}
@NotNull，@Max，@Min，@Pattern这些注解在message属性上加入你想输出的错误信息。
2、导入工具类
import java.io.IOException;
import org.springframework.web.multipart.MultipartFile;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
public class ExcelUtil {
public static <T> ExcelImportResult<T> importExcel(MultipartFile file, Integer headerRows, boolean needVerfiy,
Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setHeadRows(headerRows); // 头行忽略的行数
params.setNeedVerfiy(needVerfiy); // 是否开启校验
try {
return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
}
ExcelImportResult是一个导入返回的类，里面有很多属性，常用的：
/**
* 结果集
*/private List<T> list;
/**
* 失败数据
*/private List<T> failList;
/**
* 是否存在校验失败
*/private boolean verfiyFail;
3、导入示例
@PostMapping("/import")
public void importExcel(MultipartFile file) {
if (file == null) {
log.info("file 无数据");
return;
}
ExcelImportResult<People> result = null;
try {
result = ExcelUtil.importExcel(file, 1, true, People.class);
} catch (IOException e) {
e.printStackTrace();
}
List<People> failList = result.getFailList();// 获取失败的数据
if (failList.size() > 0) {
for (People people : failList) {
log.info("第{}行,{}", people.getRowNum(), people.getErrorMsg());// 打印失败的行 和失败的信息
}
}
// 如果没有错误，可以存入文件服务系统 或者数据库 ，这里只是将数据打印出来
List<People> list = result.getList();
log.info("成功导入数据 {}", JSON.toJSONString(list));
}