个人中心
退出
全网最全最简单使用easypoi导入导出Excel的操作手册
概况
今天做Excel导出时，发现了一款非常好用的POI框架EasyPoi，其 使用起来简洁明了。现在我们就来介绍下EasyPoi,首先感谢EasyPoi 的开发者 Lemur开源
easypoi 简介
easypoi 是为了让开发者快速的实现excel，word,pdf的导入导出，基于Apache poi基础上的一个工具包。
特性
- 基于注解的导入导出，修改注解就可以修改Excel
- 支持常用的样式自定义
- 基于map可以灵活定义的表头字段
- 支持一对多的导出，导入
- 支持模板的导出，一些常见的标签，自定义标签
- 支持HTML/Excel转换
- 支持word的导出，支持图片，Excel
常用注解
@Excel注解
@Excel 注解是作用到Filed 上面，是对Excel一列的一个描述，这个注解是必须要的注解，其部分属性如下：
其使用如下，其中orderNum是指定该字段在Excel中的位置，name与Excel中对应的表头单元格的名称
@Excel(name = "主讲老", orderNum = "1")
private String name;
@ExcelCollection 注解
@ExcelCollection 注解表示一个集合，主要针对一对多的导出
比如一个老师对应多个科目，科目就可以用集合表示，作用在一个类型是List的属性上面，属性如下：
其使用如下所示。
@ExcelCollection(name = "学生", orderNum = "4")
private List<StudentEntity> students;
@ExcelEntity注解
@ExcelEntity注解表示一个继续深入导出的实体，是作用一个类型为实体的属性上面，其属性如下：
其使用如下所示。
@ExcelEntity(id = "major")
private TeacherEntity chineseTeacher;
@ExcelIgnore 注解
@ExcelIgnore 注解修饰的字段，表示在导出的时候补导出，被忽略。
@ExcelTarget 注解
@ExcelTarget注解作用于最外层的对象，描述这个对象的id,以便支持一个对象，可以针对不同导出做出不同处理，其作用在实体类的上，属性如下：
其使用如下：
@ExcelTarget("scoreIssueReqPOJO")
public class ScoreIssueReqPOJO implements java.io.Serializable{}
EasyPOI的使用
1.引入依赖
- SSM 项目，引入依赖
如果spring的版本是4.x的话引入的easypoi的版本是3.0.1，如果spring是5.x的话引入easypoi的版本是4.0.0
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
- Spring Boot 项目（2.x以上的版本，我demo的版本是2.1.3.RELEASE），引入依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
需要注意的是由于easypoi的依赖内部依赖原生的poi，所以，引入了easypoi的依赖之后，需要把原生的poi的依赖删掉
注解方式导出Excel
导出测试的demo
@Test
public void testExportExcel() throws Exception {
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();
Workbook workbook = ExcelExportUtil.exportExcel( new ExportParams("导出测试", null, "测试"),
CourseEntity.class, courseEntityList);
System.out.println(new Date().getTime() - start.getTime());
File savefile = new File("D:/excel/");
if (!savefile.exists()) {
savefile.mkdirs();
}
FileOutputStream fos = new FileOutputStream("D:/excel/教师课程学生导出测试.xls");
workbook.write(fos);
fos.close();
}
导出对应的Bean
- CourseEntity 类。
@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 = "major")
private TeacherEntity chineseTeacher;
/** 老师主键 */
@ExcelEntity(id = "absent")
private TeacherEntity mathTeacher;
@ExcelCollection(name = "学生", orderNum = "4")
private List<StudentEntity> students;
- TeacherEntity 类
@Data
public class TeacherEntity {
/**
* 学生姓名
*/
@Excel(name = "教师姓名", height = 20, width = 30, isImportField = "true_st")
private String name;
/**
* 学生性别
*/
@Excel(name = "教师性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true_st")
private int sex;
}
- StudentEntity 类。
public class StudentEntity implements java.io.Serializable {
/**
* id
*/
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 = "出生日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20)
private Date birthday;
@Excel(name = "进校日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
private Date registrationDate;
导出结果
关于日期格式化的说明
- 如果是导出的实体类(就是说这个实体类是对应导出的Excel的)，那么用
@Excel注解的exportFormat属性来格式化日期。如下所示：
@Excel(name = "出生日期", exportFormat = "yyyy-MM-dd HH:mm:ss", width = 20)
- 如果是导入的实体类（就是说这个实体类是对应导入的Excel的），那么用
@Excel注解的importFormat属性来格式化日期。如下所示：
@Excel(name = "添加时间",importFormat = "yyyy-MM-dd HH:mm:ss",orderNum = "14")
private Date createTime;
@Excel注解的databaseFormat 属性是用于数据库的格式不是日期类型，如datetime时用。
注解方式导入Excel
基于注解的导入导出,配置配置上是一样的，只是方式反过来而已。首先让我们来看看 ImportParams类，这个类主要是设置导入参数，例如表格行数，表头行数等等。
ImportParams参数介绍
需要说明的是
1、titleRows表示的是表格标题行数，如果没有就是0，如果有一个标题就是1，如果是两个标题就2
2. headRows表示的是表头行数，默认是1，如果有两个表头则需要设置2。
导入情形一：有标题有表头
我们有如下格式的Excel需要导入：
这个Excel有一个标题行，有一个表头行，所以我们有了如下设置：
ImportParams params = new ImportParams();
//设置标题的行数，有标题时一定要有
params.setTitleRows(1);
//设置表头的行数
params.setHeadRows(1);
导入的demo
@Test
public void haveTitleTest() {
ImportParams params = new ImportParams();
//设置标题的行数，有标题时一定要有
params.setTitleRows(1);
//设置表头的行数
params.setHeadRows(1);
String file = Thread.currentThread().getContextClassLoader().getResource("haveTitle.xlsx").getFile();
List<ScoreIssueReqPOJO> list = ExcelImportUtil.importExcel(
new File(file),
ScoreIssueReqPOJO.class, params);
System.out.println("解析到的数据长度是：" + list.size());
for (ScoreIssueReqPOJO scoreIssueReqPOJO : list) {
System.out.println("***********有标题有表头导入的数据是=" + scoreIssueReqPOJO.toString());
}
}
导入测试的结果是：
导入情形二：有表头没有标题
只有一个表头没有标题的话，我们ImportParams可以用默认的值。
导入的demo
@Test
public void notTitleTest() {
ImportParams params = new ImportParams();
String file = Thread.currentThread().getContextClassLoader().getResource("notTitle.xlsx").getFile();
List<ScoreIssueReqPOJO> list = ExcelImportUtil.importExcel(
new File(file),
ScoreIssueReqPOJO.class, params);
System.out.println("解析到的数据长度是：" + list.size());
for (ScoreIssueReqPOJO scoreIssueReqPOJO : list) {
System.out.println("***********有表头没有标题导入的数据是=" + scoreIssueReqPOJO.toString());
}
}
导入结果如下：
导入实体Bean配置
public class ScoreIssueReqPOJO implements java.io.Serializable{
/**
* 用户手机号
*/
@Excel(name = "个人用户手机号*",width = 14)
private String mobile;
/**
* 企业用户税号
*/
@Excel(name = "企业用户税号*",width = 20)
private String taxNum;
/**
* 公司名称或者用户名
*/
@Excel(name = "名称",width = 20)
@Length(max =50 ,message = "名称过长")
private String realname;
/**
* 积分数量
* isStatistics 自动统计数据
*/
@Excel(name = "积分数量*")
@NotNull(message = "积分数量不能为空")
private String scoreNum;
/**
* 平台类型
*/
@Excel(name = "业务代码")
@Length(max =2 ,message = "业务代码过长，最长2个字符（必须由诺诺网分配，请勿乱填）")
private String platform;
/**
* 备注
*/
@Excel(name = "备注")
@Length(max =120 ,message = "备注过长,最长120个字符")
private String typeContent;
}
Excel导入校验
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;
使用方式就是在导入时设置needVerfiy属性为true。导入的demo如下所示：
@Test
public void basetest() {
try {
ImportParams params = new ImportParams();
params.setNeedVerfiy(true);
params.setVerfiyGroup(new Class[]{ViliGroupOne.class});
ExcelImportResult<ExcelVerifyEntity> result = ExcelImportUtil.importExcelMore(
new File(PoiPublicUtil.getWebRootPath("import/verfiy.xlsx")),
ExcelVerifyEntity.class, params);
FileOutputStream fos = new FileOutputStream("D:/excel/ExcelVerifyTest.basetest.xlsx");
result.getWorkbook().write(fos);
fos.close();
for (int i = 0; i < result.getList().size(); i++) {
System.out.println(ReflectionToStringBuilder.toString(result.getList().get(i)));
}
Assert.assertTrue(result.getList().size() == 1);
Assert.assertTrue(result.isVerfiyFail());
} catch (Exception e) {
LOGGER.error(e.getMessage(),e);
导入结果ExcelImportResult
导入之后返回一个ExcelImportResult 对象,比我们平时返回的list多了一些元素
/**
* 结果集
*/
private List<T> list;
/**
* 是否存在校验失败
*/
private boolean verfiyFail;
/**
* 数据源
*/
private Workbook workbook;
一个是集合,是一个是是否有校验失败的数据,一个原本的文档,但是在文档后面追加了错误信息
注意,这里的list,有两种返回
一种是只返回正确的数据
一种是返回全部的数据,但是要求这个对象必须实现IExcelModel接口,如下
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);
}
需要对象实现这个接口
每行的错误数据也会填到这个错误信息中,方便用户后面自定义处理
看下代码
@Test
public void baseModetest() {
try {
ImportParams params = new ImportParams();
params.setNeedVerfiy(true);
ExcelImportResult<ExcelVerifyEntityOfMode> result = ExcelImportUtil.importExcelMore(
new FileInputStream(new File(PoiPublicUtil.getWebRootPath("import/verfiy.xlsx"))),
ExcelVerifyEntityOfMode.class, params);
FileOutputStream fos = new FileOutputStream("D:/excel/baseModetest.xlsx");
result.getWorkbook().write(fos);
fos.close();
for (int i = 0; i < result.getList().size(); i++) {
System.out.println(ReflectionToStringBuilder.toString(result.getList().get(i)));
}
Assert.assertTrue(result.getList().size() == 4);
} catch (Exception e) {
LOGGER.error(e.getMessage(),e);
}
}
定制化修改
有时候，我们需要定制化一些信息，比如，导出Excel时，我们需要统一Excel的字体的大小，字型等。我们可以通过实现IExcelExportStyler接口或者继承ExcelExportStylerDefaultImpl类来实现。如下所示：我们定义了一个ExcelStyleUtil工具类继承了ExcelExportStylerDefaultImpl（样式的默认实现类）,并且将列头，标题，单元格的字体都设置为了宋体。
public class ExcelStyleUtil extends ExcelExportStylerDefaultImpl {
public ExcelStyleUtil(Workbook workbook) {
super(workbook);
}
/**
* 标题样式
*/
@Override
public CellStyle getTitleStyle(short color) {
CellStyle cellStyle = super.getTitleStyle(color);
cellStyle.setFont(getFont(workbook, 11, false));
return cellStyle;
}
/**
* 单元格的样式
*/
@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
CellStyle cellStyle = super.stringSeptailStyle(workbook, isWarp);
cellStyle.setFont(getFont(workbook, 11, false));
return cellStyle;
}
/**
* 列表头样式
*/
@Override
public CellStyle getHeaderStyle(short color) {
CellStyle cellStyle = super.getHeaderStyle(color);
cellStyle.setFont(getFont(workbook, 11, false));
return cellStyle;
}
/**
* 单元格的样式
*/
@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
CellStyle cellStyle = super.stringNoneStyle(workbook, isWarp);
cellStyle.setFont(getFont(workbook, 11, false));
return cellStyle;
}
/**
* 字体样式
*
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private Font getFont(Workbook workbook, int size, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints((short) size);
return font;
}
}
然后就是对ExcelExportUtil类进行包装，如下所示：
public class OfficeExportUtil {
/**
*
*/
private static final Integer EXPORT_EXCEL_MAX_NUM = 20000;
/**
* 获取导出的Workbook对象
*
* @param sheetName 页签名
* @param clazz 类对象
* @param list 导出的数据集合
* @return
* @author xiagwei
* @date 2020/2/10 4:45 PM
*/
public static Workbook getWorkbook(String sheetName, Class clazz, List<?> list) {
//判断数据是否为空
if (CollectionUtils.isEmpty(list)) {
log.info("***********导出数据行数为空!");
list = new ArrayList<>();
}
if (list.size() > EXPORT_EXCEL_MAX_NUM) {
log.info("***********导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加导出条件!");
list = new ArrayList<>();
}
log.info("***********"+sheetName+"的导出数据行数为"+list.size()+"");
//获取导出参数
ExportParams exportParams = new ExportParams();
//设置导出样式
exportParams.setStyle(ExcelStyleUtil.class);
//设置sheetName
exportParams.setSheetName(sheetName);
//输出workbook流
return ExcelExportUtil.exportExcel(exportParams, clazz, list);
}
使用的话，我们只需要获取需要导出的数据，然后调用OfficeExportUtil的getWorkbook方法。
总结
本文主要介绍了EasyPOI的使用和相关属性，EasyPOI使用起来还是蛮简单的。但是有个缺点是导入导出大批量数据时性能没那么好。
参考代码
https://gitee.com/lemur/easypoi-test
参考
作者：码农飞哥
微信公众号：码农飞哥
分类导航