前言
easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法
Excel自适应xls和xlsx两种格式,word只支持docx模式
Easypoi的目标不是替代poi,而是让一个不懂导入导出的快速使用poi完成Excel和word的各种操作,而不是看很多api才可以完成这样工作
注解介绍
@Excel作用到filed上面,是对Excel一列的一个描述
@ExcelCollection表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
@ExcelEntity 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段
@ExcelIgnore和名字一样表示这个字段被忽略跳过这个导导出
@ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理
@Excel注解
| 属性 | 类型 | 默认值 | 功能 |
|---|---|---|---|
| name | String | null | 列名,支持name_id |
| needMerge | boolean | fasle | 是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row) |
| orderNum | String | “0” | 列的排序,支持name_id |
| replace | String[] | {} | 值得替换 导出是{a_id,b_id} 导入反过来 |
| savePath | String | “upload” | 导入文件保存路径,如果是图片可以填写,默认是upload/className/ IconEntity这个类对应的就是upload/Icon/ |
| type | int | 1 | 导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本 |
| width | double | 10 | 列宽 |
| height | double | 10 | 列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意 |
| isStatistics | boolean | fasle | 自动统计数据,在追加一行统计,把所有数据都和输出[这个处理会吞没异常,请注意这一点] |
| isHyperlink | boolean | false | 超链接,如果是需要实现接口返回对象 |
| isImportField | boolean | true | 校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id |
| exportFormat | String | “” | 导出的时间格式,以这个是否为空来判断是否需要格式化日期 |
| importFormat | String | “” | 导入的时间格式,以这个是否为空来判断是否需要格式化日期 |
| format | String | “” | 时间格式,相当于同时设置了exportFormat 和 importFormat |
| databaseFormat | String | “yyyyMMddHHmmss” | 导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出 |
| numFormat | String | “” | 数字格式化,参数是Pattern,使用的对象是DecimalFormat |
| imageType | int | 1 | 导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的 |
| suffix | String | “” | 文字后缀,如% 90 变成90% |
| isWrap | boolean | true | 是否换行 即支持\n |
| mergeRely | int[] | {} | 合并单元格依赖关系,比如第二列合并是基于第一列 则{0}就可以了 |
| mergeVertical | boolean | fasle | 纵向合并内容相同的单元格 |
| fixedIndex | int | -1 | 对应excel的列,忽略名字 |
| isColumnHidden | boolean | false | 导出隐藏列 |
例子
@Excel(name = "日期(格式为yyyy-MM-dd)",isImportField = "true", importFormat = "yyyy-MM-dd", databaseFormat = "yyyy-MM-dd",
exportFormat = "yyyy-MM-dd", width = 30)
private String routeDateStr;
@Excel(name = "状态",width = 20, isColumnHidden = false,replace = {"启用_1","停用_0"}) @Column(name = "ENABLE_STATUS") private String enableStatus;
@Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st") private int sex;
@ExcelEntity和@ExcelCollection的使用
给出一个某个班级选择选择某些课的学生以及对应的老师，一个课程对应一个老师，一个课程对应N个学生
课程实体类
@ExcelTarget("courseEntity") public class CourseEntity implements java.io.Serializable { /** 主键 */ private String id; /** 课程名称 */ @Excel(name = "课程名称", orderNum = "1", width = 25,needMerge="true") private String name; /** 老师主键 */ @ExcelEntity(id = "absent") private TeacherEntity mathTeacher; @ExcelCollection(name = "学生", orderNum = "4") private List<StudentEntity> students; }
学生实体类
public class StudentEntity implements java.io.Serializable { private String id; /** * 学生姓名 */ @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st") private String name; /** * 学生性别 */ @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st") private int sex; @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20) private Date birthday; @Excel(name = "进校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd") private Date registrationDate; }
老师实体类
@ExcelTarget("teacherEntity") public class TeacherEntity implements java.io.Serializable { private String id; /** name */ @Excel(name = "主讲老师_major,代课老师_absent", orderNum = "1", isImportField = "true_major,true_absent",needMerge="true") private String name; }
效果
Excel导入介绍
有导出就有导入,基于注解的导入导出,配置配置上是一样的,只是方式反过来而已,比如类型的替换 导出的时候是1替换成男,2替换成女,导入的时候则反过来,男变成1 ,女变成2,时间也是类似
导出的时候date被格式化成 2017-8-25 ,导入的时候2017-8-25被格式成date类型
基本是写法也很简单,ImportParams 参数介绍下
| 属性 | 类型 | 默认值 | 功能 |
|---|---|---|---|
| titleRows | int | 0 | 表格标题行数,默认0 |
| headRows | int | 1 | 表头行数,默认1 |
| startRows | int | 0 | 字段真正值和列标题之间的距离 默认0 |
| keyIndex | int | 0 | 主键设置,如何这个cell没有值,就跳过 或者认为这个是list的下面的值,这一列必须有值,不然认为这列为无效数据 |
| startSheetIndex | int | 0 | 开始读取的sheet位置,默认为0 |
| sheetNum | int | 1 | 上传表格需要读取的sheet 数量,默认为1 |
| needSave | boolean | false | 是否需要保存上传的Excel |
| needVerfiy | boolean | false | 是否需要校验上传的Excel |
| saveUrl | String | “upload/excelUpload” | 保存上传的Excel目录,默认是 如 TestEntity这个类保存路径就是upload/excelUpload/Test/yyyyMMddHHmss_ 保存名称上传时间_五位随机数 |
| verifyHanlder | IExcelVerifyHandler | null | 校验处理接口,自定义校验 |
| lastOfInvalidRow | int | 0 | 最后的无效行数,不读的行数 |
| readRows | int | 0 | 手动控制读取的行数 |
| importFields | String[] | null | 导入时校验数据模板,是不是正确的Excel |
| keyMark | String | “:” | Key-Value 读取标记,以这个为Key,后面一个Cell 为Value,多个改为ArrayList |
| readSingleCell | boolean | false | 按照Key-Value 规则读取全局扫描Excel,但是跳过List读取范围提升性能,仅仅支持titleRows + headRows + startRows 以及 lastOfInvalidRow |
| dataHanlder | IExcelDataHandler | null | 数据处理接口,以此为主,replace,format都在这后面 |
Excel导入校验
校验,是一个不可或缺的功能,现在java校验主要是JSR 303 规范,实现方式主流的有两种：
- Hibernate Validator
- Apache Commons Validator
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.4.0.Final</version>
</dependency>
EasyPoi的校验使用也很简单,对象上加上通用的校验规则，然后params.setNeedVerfiy(true);配置下需要校验就可以了
/** * Email校验 */ @Excel(name = "Email", width = 25) private String email; /** * 最大 */ @Excel(name = "Max") @Max(value = 15,message = "max 最大值不能超过15" ,groups = {ViliGroupOne.class}) private int max; /** * 最小 */ @Excel(name = "Min") @Min(value = 3, groups = {ViliGroupTwo.class}) private int min; /** * 非空校验 */ @Excel(name = "NotNull") @NotNull private String notNull; /** * 正则校验 */ @Excel(name = "Regex") @Pattern(regexp = "[\u4E00-\u9FA5]*", message = "不是中文") private String regex;
我们会返回一个ExcelImportResult 对象,比我们平时返回的list多了一些元素
public class ExcelImportResult<T> { private List<T> list; // 结果集 private List<T> failList; // 是否存在校验失败 private boolean verfiyFail; private Workbook workbook; // 数据源 private Workbook failWorkbook; private Map<String, Object> map; }
这个对象必须实现IExcelModel接口,如下
public class ExcelVerifyEntityOfMode extends ExcelVerifyEntity implements IExcelModel { private String errorMsg; @Override public String getErrorMsg() { return errorMsg; } @Override public void setErrorMsg(String errorMsg) { this.errorMsg = errorMsg; } }
实现IExcelDataModel接口获取错误数据的行号
public interface IExcelDataModel { /** * 获取行号 * @return */ public int getRowNum(); /** * 设置行号 * @param rowNum */ public void setRowNum(int rowNum); }
使用
1、添加依赖
spring项目依赖
1、easypoi-annotation 基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理
2、easypoi-base 导入导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能
3、easypoi-web 耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
随着spring boot的越来越流行,不可免俗的我们也推出了easypoi-spring-boot-starter,方便大家的引用和依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>3.3.0</version>
</dependency>
2、配置文件中加如下配置
#easypoi启用覆盖
spring.main.allow-bean-definition-overriding=true
3、工具类
package com.ljxx.pts.common.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 org.apache.poi.ss.usermodel.Workbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.InputStream; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; public class ExcelUtils { /** * excel 导出 * * @param list 数据列表 * @param fileName 导出时的excel名称 * @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 fileName 导出时的excel名称 * @param response */ private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException { //把数据添加到excel表格中 Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF); downLoadExcel(fileName, response, workbook); } /** * excel 导出 * * @param list 数据列表 * @param pojoClass pojo类型 * @param fileName 导出时的excel名称 * @param response * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型） */ private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
ExportParams exportParams) throws IOException { //把数据添加到excel表格中 Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list); downLoadExcel(fileName, response, workbook); } /** * excel 导出 * * @param list 数据列表 * @param pojoClass pojo类型 * @param fileName 导出时的excel名称 * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型） * @param response */ 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 title 表格内数据标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 导出时的excel名称 * @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 title 表格内数据标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 导出时的excel名称 * @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 fileName 下载时的文件名称 * @param response * @param workbook excel数据 */ private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException { 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")); workbook.write(response.getOutputStream()); } catch (Exception e) { throw new IOException(e.getMessage()); } } public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException { return importExcel(file.getInputStream(), pojoClass); } /** * excel 导入，有错误信息 * * @param file 上传的文件 * @param pojoClass pojo类型 * @param <T> * @return */ public static <T> ExcelImportResult<T> importExcelMore(MultipartFile file, Class<T> pojoClass) throws IOException { if (file == null) { return null; } try { return importExcelMore(file.getInputStream(), pojoClass); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * excel 导入 * * @param inputStream 文件输入流 * @param pojoClass pojo类型 * @param <T> * @return */ public static <T> List<T> importExcel(InputStream inputStream,Class<T> pojoClass) throws IOException { if (inputStream == null) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(1);//表格内数据标题行 params.setHeadRows(1);//表头行 params.setSaveUrl("/excel/"); params.setNeedSave(true); try { return ExcelImportUtil.importExcel(inputStream, pojoClass, params); } catch (NoSuchElementException e) { throw new IOException("excel文件不能为空"); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * excel 导入 * * @param inputStream 文件输入流 * @param pojoClass pojo类型 * @param <T> * @return */ private static <T> ExcelImportResult<T> importExcelMore(InputStream inputStream, Class<T> pojoClass) throws IOException { if (inputStream == null) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(1);//表格内数据标题行 params.setHeadRows(1);//表头行 params.setSaveUrl("/excel/"); params.setNeedSave(true); params.setNeedVerfiy(true); try { return ExcelImportUtil.importExcelMore(inputStream, pojoClass, params); } catch (NoSuchElementException e) { throw new IOException("excel文件不能为空"); } catch (Exception e) { throw new IOException(e.getMessage()); } } }
导入时注意：工具类中的代码已经设置了标题行和表头行。
4、注意事项
1)、excel表格的表头行名称必须和@Excel的name属性值保持一致，否则读取不到数据。
2)、若导入的字段包含日期类型，那么需要指定导入时的日期的格式importFormat并标明是必导入字段isImportField。
3)、若导出的字段包含日期类型，那么需要指定导出的格式exportFormat。
/** * 日期验证 * 导出时要将R_DATE日期类型的数据取别名放到routeDateStr中，否则无法导出日期 */ @Transient @Excel(name = "日期(格式为yyyy-MM-dd)",isImportField = "true", importFormat = "yyyy-MM-dd", databaseFormat = "yyyy-MM-dd", exportFormat = "yyyy-MM-dd", width = 30) @NotNull(message = "日期为空") @Pattern(regexp = "^\\d{4}-\\d{1,2}-\\d{1,2}$", message = "日期格式必须是yyyy-MM-dd格式，如1970-02-12") private String routeDateStr; /** * 日期 */ @Column(name = "R_DATE") private Date routeDate;
注意：导出时，从数据库中查询数据时，要将R_DATE日期类型字段取别名存入routeDateStr中，否则无法导出日期。此时需要新添加一个String类型的字段routeDateStr，注解@Excel要放到routeDateStr字段上，该字段专门用作导入和导出。我们这里将映射数据库表字段的实体类属性与用于Excel导入导出的实体类属性配置在一个实体类中，注意此时将用于Excel导入导出的实体类属性加上@Transient注解。属性类型也为String。当然，也可以将映射数据库表字段的实体类属性与用于Excel导入导出的实体类属性分开配置，此时，用于Excel导入和导出的实体类的字段类型均为String。
4)、性别或状态字段，使用replace属性用数字代替文字。
@Excel(name = "状态",width = 20, isColumnHidden = false,replace = {"启用_1","停用_0"}) @Column(name = "ENABLE_STATUS") private String enableStatus;
@TableField(value = "sex")
@Excel(name = "性别",replace = {"男_0", "女_1"})
private String sex;
5)、要想导出时显示错误信息列，则实体类必须实现IExcelModel接口，并且实体类中提供errorMsg属性，
@Data @Table(name = "T_ZD_PRI") public class SitePrice implements IExcelModel { 。。。 //错误信息 @Transient @Excel(name = "错误信息", width = 50, isColumnHidden = false) private String errorMsg; }
6）、isColumnHidden属性用来控制导出时是否导出该字段，false为默认值，即导出的意思，如果不想导出该字段，则通过反射将isColumnHidden的属性值改为true
//获取目标对象的属性值 Field field = clazz.getDeclaredField(columnName); //获取注解反射对象 Excel excelAnion = field.getAnnotation(Excel.class); //获取代理 InvocationHandler invocationHandler = Proxy.getInvocationHandler(excelAnion); Field excelField = invocationHandler.getClass().getDeclaredField("memberValues"); excelField.setAccessible(true); Map memberValues = (Map) excelField.get(invocationHandler); Map memberValues2 = getMemberValues2(clazz, columnName); memberValues2.put("isColumnHidden", true);
7）、使用@Pattern注解的regexp属性校验日期和小数
@Transient @Excel(name = "站点经度(小数，小数位2-6位)",width = 30) @NotNull(message = "站点经度为空") @Pattern(regexp = "^([0-9]{1,3})\\.[0-9]{2,6}?$",message = "站点经度必须是小数") private String siteLongitudeStr;
@Transient @Excel(name = "日期(格式为yyyy-MM-dd)",isImportField = "true", importFormat = "yyyy-MM-dd", databaseFormat = "yyyy-MM-dd", exportFormat = "yyyy-MM-dd", width = 30) @NotNull(message = "日期为空") @Pattern(regexp = "^\\d{4}-\\d{1,2}-\\d{1,2}$", message = "日期格式必须是yyyy-MM-dd格式，如1970-02-12") private String routeDateStr;
