springboot+easypoi,快速搞定excel导入导出

发中经常会遇到excel的处理,导入导出解析等等,java中比较流行的用poi,但是每次都要写大段工具类来搞定这事儿,此处推荐一个别人造好的轮子【easypoi】,下面介绍下“轮子”的使用。

1.pom引入

[XML] 纯文本查看 复制代码

?

<dependency>

<groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>3.0.3</version>
    </dependency>

编写实体类

此处注意必须要有空构造函数,否则会报错“对象创建错误”

关于注解@Excel,其他还有@ExcelCollection,@ExcelEntity ,@ExcelIgnore,@ExcelTarget等,此处我们用不到,可以去官方查看更多

[Java] 纯文本查看 复制代码

?

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

import java.util.Date;

public class Person {

@Excel(name = "姓名", orderNum = "0")
private String name;

@Excel(name = "性别", replace = {"男_1", "女_2"}, orderNum = "1")
private String sex;

@Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "2")
private Date birthday;

public Person(String name, String sex, Date birthday) {
    this.name = name;
    this.sex = sex;
    this.birthday = birthday;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getSex() {
    return sex;
}

public void setSex(String sex) {
    this.sex = sex;
}

public Date getBirthday() {
    return birthday;
}

public void setBirthday(Date birthday) {
    this.birthday = birthday;
}

}

[Java] 纯文本查看 复制代码

?

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

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){

ExportParams exportParams = new ExportParams(title, sheetName);
    exportParams.setCreateHeadRows(isCreateHeader);
    defaultExport(list, pojoClass, fileName, response, exportParams);

}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
    defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}

public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){

defaultExport(list, fileName, response);
}

private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
    if (workbook != null);
    downLoadExcel(fileName, response, workbook);
}

private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
    try {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    } catch (IOException e) {
        throw new NormalException(e.getMessage());
    }
}

private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {

Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
    if (workbook != null);
    downLoadExcel(fileName, response, workbook);
}

public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
    if (StringUtils.isBlank(filePath)){
        return null;
    }
    ImportParams params = new ImportParams();
    params.setTitleRows(titleRows);
    params.setHeadRows(headerRows);
    List<T> list = null;
    try {
        list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }catch (NoSuchElementException e){
        throw new NormalException("模板不能为空");
    } catch (Exception e) {
        e.printStackTrace();
        throw new NormalException(e.getMessage());
    }
    return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
    if (file == null){
        return null;
    }
    ImportParams params = new ImportParams();
    params.setTitleRows(titleRows);
    params.setHeadRows(headerRows);
    List<T> list = null;
    try {
        list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }catch (NoSuchElementException e){
        throw new NormalException("excel文件不能为空");
    } catch (Exception e) {
        throw new NormalException(e.getMessage());
    }
    return list;
}

测试

[Java] 纯文本查看 复制代码

?

@RequestMapping("export")

public void export(HttpServletResponse response){

    //模拟从数据库获取需要导出的数据
    List<Person> personList = new ArrayList<>();
    Person person1 = new Person("路飞","1",new Date());
    Person person2 = new Person("娜美","2", DateUtils.addDate(new Date(),3));
    Person person3 = new Person("索隆","1", DateUtils.addDate(new Date(),10));
    Person person4 = new Person("小狸猫","1", DateUtils.addDate(new Date(),-10));
    personList.add(person1);
    personList.add(person2);
    personList.add(person3);
    personList.add(person4);

    //导出操作
    FileUtil.exportExcel(personList,"花名册","草帽一伙",Person.class,"海贼王.xls",response);
}

@RequestMapping("importExcel")
public void importExcel(){
    String filePath = "F://海贼王.xls";
    //解析excel,
    List<Person> personList = FileUtil.importExcel(filePath,1,1,Person.class);
    //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
    System.out.println("导入数据一共【"+personList.size()+"】行");

    //TODO 保存数据库

原文 

https://segmentfault.com/a/1190000021129318

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » springboot+easypoi,快速搞定excel导入导出

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址