EasyPOI的基本使用

EasyPOI的使用引入依赖

SSM 项目，引入依赖

如果spring的版本是4.x的话引入的easypoi的版本是3.0.1，如果spring是5.x的话引入easypoi的版本是4.0.0

	
            cn.afterturn
            easypoi-base
            4.0.0
        
        
            cn.afterturn
            easypoi-web
            4.0.0
        
        
            cn.afterturn
            easypoi-annotation
            4.0.0

Spring Boot 项目（2.x以上的版本，我demo的版本是2.1.3.RELEASE），引入依赖

	
			cn.afterturn
			easypoi-spring-boot-starter
            4.0.0

需要注意的是由于easypoi的依赖内部依赖原生的poi，所以，引入了easypoi的依赖之后，需要把原生的poi的依赖删掉

注解方式导出Excel 导出测试的demo

 @Test
    public void testExportExcel() throws Exception {

        List 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 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);
        System.out.println(courseEntityList+"11111111111111");
        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("E:/desktop/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("E:/desktop/excel/教师课程学生导出测试.xls");
        workbook.write(fos);
        fos.close();
    }

导出对应的Bean

CourseEntity 类。

package com.mye.hl11easypoi.api.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.util.List;

@Data
@ExcelTarget("courseEntity")
public class CourseEntity implements java.io.Serializable {
    
    private String id;
    
    @Excel(name = "课程名称", orderNum = "0", width = 25, needMerge = true)
    private String name;
    
//    @ExcelEntity(id = "major")
    private TeacherEntity chineseTeacher;
    
    @ExcelEntity(id = "absent")
    private TeacherEntity mathTeacher;

    @ExcelCollection(name = "学生", orderNum = "3")
    private List students;
}

TeacherEntity 类

package com.mye.hl11easypoi.api.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

@Data
public class TeacherEntity {
    
    @Excel(name = "教师姓名", width = 30, orderNum = "1" ,isimportField = "true")
    private String name;
    
    @Excel(name = "教师性别", replace = {"男_1", "女_2"}, suffix = "生", isimportField = "true_st",orderNum = "2")
    private int sex;
}

StudentEntity 类

package com.mye.hl11easypoi.api.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.util.Date;

@Data
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 = "出生日期", 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 importParams参数

属性	类型	默认值	功能
titleRows	int	0	表格标题行数
headRows	int	1	表头行数
startRows	int	0	字段值与列标题之间的距离
keyIndex	int	0	主键
startSheetIndex	int	0	开始读取的sheet位置
sheetNum	int	1	上传表格需要读取的sheet数量
needSave	boolean	false	是否需要保存上传的Excel
needVerfiy	boolean	false	是否需要校验上传的Excel
verifyHanlder	IExcelVerifyHandler	null	校验处理接口自定义校验
importFields	String[]	null	导入时校验数据模板，是不是正确的Excel
saveUrl	String	“upload/excelUpload”	保存上传的Excel目录，默认是如TestEntity这个类保存路径是upload/excelUpload/Test/yyyyMMddHHmmss

需要说明的是
1、titleRows表示的是表格标题行数，如果没有就是0，如果有一个标题就是1，如果是两个标题就2
2. headRows表示的是表头行数，默认是1，如果有两个表头则需要设置2

导入代码

@Test
    public void testimport2() throws Exception {
        // 参数1：导入excel文件流  参数2：导入类型  参数3：导入的配置对象
        importParams importParams = new importParams();
        importParams.setTitleRows(1); // 设置标题列占几行
        importParams.setHeadRows(2);  // 设置字段名称占几行 即header
        importParams.setNeedVerify(true);//开启校验
        importParams.setStartSheetIndex(0);  // 设置从第几张表格开始读取，这里0代表第一张表，默认从第一张表读取
        List courseEntityList = ExcelimportUtil.importExcel(new FileInputStream("E:/desktop/excel/教师课程学生导出测试.xls"), CourseEntity.class, importParams);
        for (CourseEntity courseEntity : courseEntityList) {
            System.out.println(courseEntity);
        }
    }

导入结果

CourseEntity(
id=null, 
name=测试课程, 
chineseTeacher=null, 
mathTeacher=TeacherEntity(name=张老师, sex=1), 
students=[StudentEntity(
		id=null, 
		name=学生1, 
		sex=1, 
		birthday=Wed Aug 03 20:31:23 CST 5881510, 	  	registrationDate=null), 
		StudentEntity(
		id=null, 
		name=学生2, 
		sex=2, 
		birthday=Wed Aug 03 20:31:23 CST 5881510, registrationDate=null)]
)

Excel导入校验

EasyPoi的校验使用也很简单,在导入对象上加上通用的校验规则或者这定义的这个看你用的哪个实现然后params.setNeedVerfiy(true);配置下需要校验就可以了

    @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。

@Test
    public void basetest() {
        try {
            importParams params = new importParams();
            params.setNeedVerfiy(true);
            params.setVerfiyGroup(new Class[]{ViliGroupOne.class});
            ExcelimportResult 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);

EasyPOI的基本使用

Java相关栏目本月热门文章