POI和EasyExcel

今天小编接到一个需求编写一个模块的导入和导出（导出Excel 和 导入Excel到数据库中）。想到做到就是学到，在实现需求的同时又学到相关的技术，岂不美哉～

操作 Excel 目前比较流行的就是 Apache POI 和 阿里巴巴的 easyExcel！

POI 和 EasyExcel 概述

POI（Apache POI）

​ Apache POI是 Apache软件基金会的开放源码函数式子，POI提供API给Java程序对Microsofy Office格式档案读和写功能。

Apache POI 官网：https://poi.apache.org/

基本功能：

• HSSF － 提供读写[Microsoft Excel](https://baike.baidu.com/item/Microsoft Excel?fromModule=lemma_inlink) XLS格式档案的功能。
• XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
• HWPF － 提供读写[Microsoft Word](https://baike.baidu.com/item/Microsoft Word?fromModule=lemma_inlink) DOC格式档案的功能。
• HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
• HDGF － 提供读[Microsoft Visio](https://baike.baidu.com/item/Microsoft Visio?fromModule=lemma_inlink)格式档案的功能。
• HPBF － 提供读[Microsoft Publisher](https://baike.baidu.com/item/Microsoft Publisher?fromModule=lemma_inlink)格式档案的功能。
• HSMF － 提供读[Microsoft Outlook](https://baike.baidu.com/item/Microsoft Outlook?fromModule=lemma_inlink)格式档案的功能。

​ 虽然POI是目前使用最多的用来做excel解析的框架，但这个框架并不那么完美。大部分使用POI都是使用他的userModel模式。userModel的好处是上手容易使用简单，随便拷贝个代码跑一下，剩下就是写业务转换了，虽然转换也要写上百行代码，相对比较好理解。然而userModel模式最大的问题是在于非常大的内存消耗，一个几兆的文件解析要用掉上百兆的内存。现在很多应用采用这种模式，之所以还正常在跑一定是并发不大，并发上来后一定会OOM或者频繁的full gc。

EasyExcel

​ EasyExcel 是阿里巴巴开源的一个Excel处理框架，以使用简单、节省内存著称。

​ 由于阿里的系统大多数都是大并发的情况下运行的，在大并发情况下，会发现poi存在一些bug,如果让POI团在大并发情况下会对其poi抛出一个异常。

​ EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载的内存中，而是从磁盘上一行行读取数据，逐个解析。

​ EasyExcel 官网：https://easyexcel.opensource.alibaba.com/

1、文件解压文件读取通过文件形式

2、避免将全部全部数据一次加载到内存

3、抛弃不重要的数据

Excel解析时候会包含样式，字体，宽度等数据，但这些数据是我们不关心的，如果将这部分数据抛弃可以大大降低内存使用。Excel中数据如下Style占了相当大的空间。

1. POI-Excel 写操作

首先我们来分析一下一个 Excel 文件包括哪几个部分～

相关依赖：

<dependency>
    <groupId>org.apache.poigroupId>
    <artifactId>poiartifactId>
    <version>4.1.2version>
dependency>


<dependency>
    <groupId>org.apache.poigroupId>
    <artifactId>poi-ooxmlartifactId>
    <version>4.1.2version>
dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13

1.1. POI-Excel 简单写操作

POI-Excel 简单写操作

首先我们来生成一个 03 版本的Excel~

private final String  PATH = "/Users/hgw/Documents/Data/java/easyExcelDemo/poi/src/main/resources/warehouse";
1

@Test
public void testWrite03() {
    // 1、创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    // 2、创建一个工作表
    Sheet sheet = workbook.createSheet("人员导入模版03");
    // 3、创建一个行
    Row row = sheet.createRow(0);
    // 4、创建多个单元格
    Cell cell1 = row.createCell(0); // 第一行第一个
    cell1.setCellValue("学号");
    Cell cell2 = row.createCell(1); // 第一行第二个
    cell2.setCellValue("姓名");

    // 5、创建多行多个单元格
    Row row2 = sheet.createRow(1);
    Cell row2Cell1 = row2.createCell(0); // 第二行第一个
    row2Cell1.setCellValue("2019110124");
    Cell row2Cell2 = row2.createCell(1); // 第二行第二个
    row2Cell2.setCellValue("言安");

    FileOutputStream fileOutputStream = null;
    // 6、生成一张表
    try {
        fileOutputStream = new FileOutputStream(PATH + "人员导入模版03.xls");
        workbook.write(fileOutputStream);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    System.out.println("人员导入模版03.xls生成完毕");
}
1
2
3
4
5
6
7
8
9
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

07的同03大致相同，使用 XSSFWorkbook 创建即可，并需要注意03的扩展名是.xls ，而07是.xlsx。

@Test
public void testWrite07() {
    // 1、创建一个工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    // 2、创建一个工作表
    Sheet sheet = workbook.createSheet("人员导入模版07");
    // 3、创建一个行
    Row row = sheet.createRow(0);
    // 4、创建多个单元格
    Cell cell1 = row.createCell(0); // 第一行第一个
    cell1.setCellValue("学号");
    Cell cell2 = row.createCell(1); // 第一行第二个
    cell2.setCellValue("姓名");

    // 5、创建多行多个单元格
    Row row2 = sheet.createRow(1);
    Cell row2Cell1 = row2.createCell(0); // 第二行第一个
    row2Cell1.setCellValue("2019110124");
    Cell row2Cell2 = row2.createCell(1); // 第二行第二个
    row2Cell2.setCellValue("言安");

    FileOutputStream fileOutputStream = null;
    // 6、生成一张表
    try {
        fileOutputStream = new FileOutputStream(PATH + "人员导入模版07.xlsx");
        workbook.write(fileOutputStream);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    System.out.println("人员导入模版07.xls生成完毕");
}
1
2
3
4
5
6
7
8
9
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

此时查看一下目录～

1.2. POI-Excel 数据批量导入

大文件写 HSSF（03）

缺点：最多只能处理 65536行，否则会抛出异常

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
1

优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快。

@Test
public void testWrite03BigData() {
    // 开始时间
    long begin = System.currentTimeMillis();

    // 创建表，批量数据
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("批量写03");
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(rowNum + cellNum);
        }
    }

    FileOutputStream outputStream = null;
    try {
        outputStream = new FileOutputStream(PATH + "批量写03.xls");
        workbook.write(outputStream);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    // 结束时间
    long end = System.currentTimeMillis();

    System.out.println("批量写03.xls 创建完毕" + (double)(end-begin)/1000);
}
1
2
3
4
5
6
7
8
9
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

运行时间：批量写03.xls 创建完毕1.477

大文件写 XSSF（07）

缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如 100 万条

优点：可以写较大的数据量，如20万条。

@Test
public void testWrite07BigData() {
    // 开始时间
    long begin = System.currentTimeMillis();

    // 创建表，批量数据
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("批量写07");
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(rowNum + cellNum);
        }
    }

    FileOutputStream outputStream = null;
    try {
        outputStream = new FileOutputStream(PATH + "批量写07.xlsx");
        workbook.write(outputStream);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    // 结束时间
    long end = System.currentTimeMillis();

    System.out.println("批量写07.xlsx 创建完毕" + (double)(end-begin)/1000);
}
1
2
3
4
5
6
7
8
9
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

运行时间：批量写07.xlsx 创建完毕5.154

如何写入更快呢？使用缓存～

大文件写 SXSSF

优点：可以写非常大的数据量，如100w条甚至更多条，写数据速度快，占用更少的内存。

注意：

• 过程中会产生临时文件，需要清理临时文件 ((SXSSFWorkbook)workbook).dispose();
• 默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件
• 如果想自定义内存中数据的数量，可以使用 new SXSSFWorkbook(int rowAccessWindowSize)

@Test
public void testWrite07BigDataS() {
    // 开始时间
    long begin = System.currentTimeMillis();

    // 创建表，批量数据
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet("批量写07");
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(rowNum + cellNum);
        }
    }

    FileOutputStream outputStream = null;
    try {
        outputStream = new FileOutputStream(PATH + "批量写07Super.xlsx");
        workbook.write(outputStream);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            outputStream.close();
            // 清除临时文件！
            ((SXSSFWorkbook)workbook).dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    // 结束时间
    long end = System.currentTimeMillis();

    System.out.println("批量写07Super.xlsx 创建完毕" + (double)(end-begin)/1000);
}
1
2
3
4
5
6
7
8
9
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

运行时间：批量写07Super.xlsx 创建完毕3.143

2. POI-Excel 读操作

2.1. POI-Excel 简单读操作

@Test
public void testRead03() {
    FileInputStream inputStream = null;
    try {
        // 1、获取文件输入流
        inputStream = new FileInputStream(PATH + "人员导入模版03.xls");
        // 2、得到工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        // 3、得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 4、得到行
        Row row1 = sheet.getRow(0);
        // 5、得到单元格
        Cell cell1 = row1.getCell(0);
        
        System.out.println(cell1.getStringCellValue());
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

注意获取值的类型即可！读取值得时候注意数据类型，数据类型不匹配会报错。批量的话我们只需要创建个for循环接收即可！

@Test
public void testRead07() {
    FileInputStream inputStream = null;
    try {
        // 1、获取文件输入流
        inputStream = new FileInputStream(PATH + "人员导入模版07.xlsx");
        // 2、得到工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 3、得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 4、得到行
        Row row2 = sheet.getRow(1);
        // 5、得到单元格
        Cell cell2 = row2.getCell(0);

        System.out.println(cell2.getStringCellValue());
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.2. POI-Excel 读取不同类型的数据

在使用场景中Excel表格中会存在多种不同类型的数据，我们可以先通过 getCellType 获取当前的单元格的数据类型，然后通过 switch 判断当前的数据类型去做不同的数据处理～

如下订单表中包括了字符串、日期、数字、公式等不同的数据类型：

private void readExcel(FileInputStream inputStream) {
    try {
        // 1、获取文件输入流
        inputStream = new FileInputStream(PATH + "订单表.xlsx");
        // 2、得到工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 3、得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 4、获取标题内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    CellType cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
            System.out.println();
        }
        // 5、获取表中内容（除第一行之外的记录）
        int numMergedRegions = sheet.getPhysicalNumberOfRows();  // 获取有多少行
        for (int rowNum = 1; rowNum < numMergedRegions; rowNum++) {         // 因为第一行是标题，所以从第二行开始 即下标为1
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                // 读取列数
                int cellCount = rowData.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = rowData.getCell(cellNum);
                    // 匹配列的数据类型
                    if (cell != null) {
                        CellType cellType = cell.getCellType();
                        Object cellValue = null;
                        switch (cellType) {
                            case _NONE: // 默认类型
                                cellValue = cell.getStringCellValue();
                                break;
                            case NUMERIC:   // 数字（日期、普通数字）
                                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
                                    cellValue = cell.getDateCellValue();
                                } else {    // 非日期
                                    cellValue = cell.getNumericCellValue();
                                }
                                break;
                            case STRING:   // 字符串
                                cellValue = cell.getStringCellValue();
                                break;
                            case BLANK:     // 空
                                break;
                            case BOOLEAN:   // 布尔
                                cellValue = cell.getBooleanCellValue();
                                break;
                            case ERROR:     // 错误
                                break;
                            case FORMULA:   // 公式
                                // 拿到计算公式
                                FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                                // 计算公式
                                cellValue = formulaEvaluator.evaluate(cell);
                                break;
                        }
                        System.out.print(cellValue + " | ");
                    }
                }
                System.out.println();
            }
        }

    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
1
2
3
4
5
6
7
8
9
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
72
73
74
75
76
77
78
79
80

3. EasyExcel 写操作

在工作中POI用的很少了，接下来进入正题：EasyExcel～

首选导入依赖～

<dependency>
    <groupId>com.alibabagroupId>
    <artifactId>easyexcelartifactId>
    <version>3.0.5version>
dependency>
1
2
3
4
5
6

表格存放路径：

private final String PATH = "/Users/hgw/Documents/Data/java/easyExcelDemo/easyexceldemo/src/main/resources/warehouse/";
1

通用数据生成 后面不会重复写

private List<DemoData> data() {
    List<DemoData> list = ListUtils.newArrayList();
    for (int i = 0; i < 10; i++) {
        DemoData data = new DemoData();
        data.setString("字符串" + i);
        data.setDate(new Date());
        data.setDoubleData(0.56);
        list.add(data);
    }
    return list;
}
1
2
3
4
5
6
7
8
9
10
11

3.1.简单的写

Excel示例：

编写一个实体类：

/**
 * Description:
 * Data time:2022/8/30 14:01
 * Author:TQ-3038-HuangGongWei
 */
@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

最简单的写：

@Test
public void simpleWrite() {
    // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入

    String fileName = PATH + "EasyTest.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    // write(fileName, 格式类)
    // sheet(表名)
    // doWrite(数据)
    EasyExcel.write(fileName, DemoData.class)
            .sheet("模板")
            .doWrite(() -> {
                // 分页查询数据
                return data();
            });
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

其他两种写法：

// 写法二
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
// 写法三
try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
    WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
    excelWriter.write(data(), writeSheet);
}
1
2
3
4
5
6
7

3.2.根据参数只导出指定列

如：忽略 date

@Test
public void excludeOrIncludeWrite() {
    // 根据用户传入字段 假设我们要忽略 date
    String fileName = PATH + "根据参数忽略指定列模板.xlsx";
    Set<String> excludeColumnFiledNames = new HashSet<String>();
    excludeColumnFiledNames.add("date");
    EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
            .doWrite(data());
}
1
2
3
4
5
6
7
8
9

如：只导出 date

@Test
public void excludeOrIncludeWrite() {
    // 根据用户传入字段 假设我们只要导出 date
    fileName = PATH + "根据参数只导出指定列模板.xlsx";
    Set<String> includeColumnFiledNames = new HashSet<String>();
    includeColumnFiledNames.add("date");
    EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
            .doWrite(data());
}
1
2
3
4
5
6
7
8
9

3.3.指定写入的列

指定写入列，如下 第三列 为空～

@Data
public class IndexData {
    @ExcelProperty(value = "字符串标题", index = 0)
    private String string;
    @ExcelProperty(value = "日期标题", index = 1)
    private Date date;
    /**
     * 这里设置3 会导致第二列空的
     */
    @ExcelProperty(value = "数字标题", index = 3)
    private Double doubleData;
}
1
2
3
4
5
6
7
8
9
10
11
12

@Test
public void indexWrite() {
    String fileName = PATH +  "指定写入的列模板.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, IndexData.class).sheet("模板").doWrite(data());
}
1
2
3
4
5
6

3.4.指定标题_日期、数字或者自定义格式转换、自定义行高列宽、表格样式

Excel如下：

@Data
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(15)
// 头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17)
// 内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 12)
public class User {
    /**
     * 我想所有的 字符串起前面加上"自定义："三个字
     */
    @ExcelProperty({"用户表","姓名"})
    @Order(0)
    private String string;
    /**
     * 我想写到excel 用年月日的格式
     */
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty({"用户表","出生日期"})
    @Order(1)
    @ColumnWidth(25)
    private Date date;
    /**
     * 我想写到excel 用百分比表示
     */
    @NumberFormat("#.##%")
    @ExcelProperty({"用户表","成绩比"})
    @Order(2)
    private Double doubleData;
}
1
2
3
4
5
6
7
8
9
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

3.5.web中的写

首先引入所需要的依赖：

<dependency>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-webartifactId>
dependency>

<dependency>
    <groupId>com.alibabagroupId>
    <artifactId>fastjsonartifactId>
    <version>1.2.78version>
dependency>
1
2
3
4
5
6
7
8
9
10

文件下载（失败了会返回一个有部分数据的Excel）

/**
 * 文件下载（失败了会返回一个有部分数据的Excel）
 * 1. 创建excel对应的实体对象
 * 2. 设置返回的 参数
 * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
 */
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("用户表模板", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), User.class).sheet("模板").doWrite(data());
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）

/**
 * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
 *
 * @since 2.1.1
 */
@GetMapping("downloadFailedUsingJson")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    try {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("用户表模板", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        // 这里需要设置不关闭流
        EasyExcel.write(response.getOutputStream(), User.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                .doWrite(data());
    } catch (Exception e) {
        // 重置response
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        Map<String, String> map = MapUtils.newHashMap();
        map.put("status", "failure");
        map.put("message", "下载文件失败" + e.getMessage());
        response.getWriter().println(JSON.toJSONString(map));
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

4. EasyExcel 读操作

4.1. EasyExcel 简单读操作

首先编写一个Domin：

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}
1
2
3
4
5
6
7
8

假设这个是你的DAO存储，这里不去具体写了～

/**
 * Description: 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 */
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}
1
2
3
4
5
6
7
8

具体的逻辑都是在监听器中编写的，这个每一条数据解析都会来调用 invoke

@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;

    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    要读取的类型
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}
1
2
3
4
5
6
7
8
9
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

编写简单的读：

@Test
public void simpleRead() {
    // 写法1：JDK8+ ,不用额外写一个DemoDataListener
    String fileName = PATH + "简单写模板.xlsx";
    // 这里每次会读取100条数据 然后返回过来 直接调用使用数据就行
    EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList 		 -> {
      for (DemoData demoData : dataList) {
        log.info("读取到一条数据{}", JSON.toJSONString(demoData));
      }
    })).sheet().doRead();
}
1
2
3
4
5
6
7
8
9
10
11

4.2. EasyExcel Web读操作

编写实体类：

@Data
public class UploadData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

编写Dao：

@Repository
public class UploadDataDAO {
    public void save(List<UploadData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}
1
2
3
4
5
6

编写监听器，最简单的读的监听器 只是泛型变了

@Slf4j
public class UploadDataListener implements ReadListener<UploadData> {

    private static final int BATCH_COUNT = 100;
    private List<UploadData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private UploadDataDAO uploadDataDAO;

    public UploadDataListener() {
        uploadDataDAO = new UploadDataDAO();
    }

    public UploadDataListener(UploadDataDAO uploadDataDAO) {
        this.uploadDataDAO = uploadDataDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    要读取的类型
     * @param context
     */
    @Override
    public void invoke(UploadData data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        uploadDataDAO.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}
1
2
3
4
5
6
7
8
9
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

编写Controller~

@RestController
public class DataImport {

    @Autowired
    private UploadDataDAO uploadDAO;

    /**
     * 文件上传
     * 1. 创建excel对应的实体对象
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器
     * 3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
        return "success";
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19