SpringBoot集成EasyPoi

Java, 开发语言, 框架算法 / 作者： / / 发表评论 / EasyPoi, Excel, ExcelUtil, SpringBoot

在开发中导入或者导出excel时，使用jxl或者poi的jar包需要要写一大段代码，而Easypoi对poi进行了封装，在导出的实体类上加入注解即可。

EasyPoi文档：http://easypoi.mydoc.io/

一、依赖引入

1
2
3
4
5
<dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-spring-boot-starter</artifactId>
      <version>3.3.0</version>
</dependency>

在application.properties文件中加入：

1
spring.main.allow-bean-definition-overriding=true

加上上述配置，否则会报错

01
02
03
04
05
06
07
08
09
10
11
12
13
14
***************************
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的实体类

01
02
03
04
05
06
07
08
09
10
11
12
@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、导出工具类

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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、导出示例

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
@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获取错误信息所在的行数。

创一个类去实现这两个接口：

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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进行校验，对它的实体需要加一些注解

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
@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、导入工具类

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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是一个导入返回的类，里面有很多属性，常用的：

01
02
03
04
05
06
07
08
09
10
11
/**
 * 结果集
 */private List<T>  list;
 
/**
 * 失败数据
 */private List<T>  failList;
 
/**
 * 是否存在校验失败
 */private boolean  verfiyFail;

3、导入示例

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
@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));
}
打赏微海报分享

相关文章

发表评论

欢迎阅读『SpringBoot集成EasyPoi|Java、开发语言、框架算法|Nick Tan-梓潼Blog』