EasyPOI的基本使用

转x33g5p2x  于2021-09-29 转载在 其他  
字(7.0k)|赞(0)|评价(0)|浏览(659)

引入依赖

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);
        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;
    /** * 课程名称 * needMerge 是否需要纵向合并单元格(用于list创建的多个row) */
    @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<StudentEntity> students;
}

TeacherEntity 类

package com.mye.hl11easypoi.api.pojo;

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

@Data
public class TeacherEntity {
    /** * 教师名称 * isImportField 导入Excel时，对Excel中的字段进行校验，如果没有该字段，导入失败 */
    @Excel(name = "教师姓名", width = 30, orderNum = "1" ,isImportField = "true")
    private String name;
    /** * 教师性别 * replace 值的替换，`replace = {"男_1", "女_2"} `将值为1的替换为男 * suffix 文字后缀 */
    @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 {
    /** * 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

ImportParams参数
属性类型默认值功能
titleRowsint0表格标题行数
headRowsint1表头行数
startRowsint0字段值与列标题之间的距离
keyIndexint0主键
startSheetIndexint0开始读取的sheet位置
sheetNumint1上传表格需要读取的sheet数量
needSavebooleanfalse是否需要保存上传的Excel
needVerfiybooleanfalse是否需要校验上传的Excel
verifyHanlderIExcelVerifyHandlernull校验处理接口自定义校验
importFieldsString[]null导入时校验数据模板，是不是正确的Excel
saveUrlString“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<CourseEntity> 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);配置下需要校验就可以了

/** * 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。

@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);

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/weixin_43296313/article/details/120547303

内容来源于网络，如有侵权，请联系作者删除！

Javaspring bootpoi

相关文章

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com