easyExcel

编程工具

easyExcel

谈谈POI 和easyExcel

常用信息

1、将用户信息导出为excel表格（导出数据….），一般导出为xls

2、将Excel表中的信息录入到网站数据库（习题上传….），可以减轻网站的录入量

开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中！

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

Apache POI

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

image-20200429081226241

image-20200429081433889

Excel分为03版本和07版本

03版本里面的行数最多是65536行，而07版本里的行数是没有限制

POI是原生的，所以可能会有点麻烦

当量比较大的时候，会出现OOM异常

easyExcel

easyExcel 官网地址：https://github.com/alibaba/easyexcel

image-20200429081452886

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

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

在读或写Excel的时候，这个时候只需要添加一行代码，但EasyExcel中也存在一些坑。

下图是EasyExcel 和POI 在解析Excel时的对比图

image-20200429081523271

官方文档：https://www.yuque.com/easyexcel/doc/easyexcel

Excel中03版本和07版本的区别

03版本里面的行数最多是65536行，而07版本里的行数是没有限制

03版本的后缀是xls，07版本的后缀是xlsx，所以它们对应的工具类也不同

image-20200429101706550

1.工作簿 2.工作表 3.行 4.列

POI-Excel写

创建项目

1、建立一个空项目Bilibili-狂神说java，创建普通Maven的Moudle kuang-poi

2、引入pom依赖

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
<dependencies>
    <!--xls(03)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <!--xlsx(07)-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>

    <!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.1</version>
    </dependency>
    <!--test-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

03 | 07 版本的写，就是对象不同，方法一样的！

需要注意：2003 版本和2007 版本存在兼容性的问题！03最多只有65535 行！

03版本：使用XSSFWorkbook

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
package com.kuang;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest {
    String path = 
        "D:\\狂神说Java\\【狂神】小专题\\POI-EasyExcel\\Bilibili-狂神说java\\kuang-poi\\";
        @Test
        public void testWrite03() throws IOException {
        // 创建新的Excel 工作簿
        Workbook workbook = new HSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值Sheet0
        //Sheet sheet = workbook.createSheet();
        // 如要新建一名为"会员登录统计"的工作表，其语句为：
        Sheet sheet = workbook.createSheet("狂神观众统计表");
        // 创建行（row 1），0代表创建第一行
        Row row1 = sheet.createRow(0);
        // 创建单元格（col 1-1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日新增关注");

        // 创建单元格（col 1-2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(999);
        // 创建行（row 2）
        Row row2 = sheet.createRow(1);
        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        //创建单元格（col 2-2）
        Cell cell22 = row2.createCell(1);
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);
        // 新建一输出文件流（注意：要先创建文件夹）03版本就是使用xls结尾
        FileOutputStream out = new FileOutputStream(path+"狂神观众统计表03.xls");
        // 把相应的Excel 工作簿存盘
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();

        System.out.println("文件生成成功");
        }
}

07版本：使用XSSFWorkbook

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
@Test
public void testWrite07() throws IOException {
   // 创建新的Excel 工作簿, 只有对象变了
   Workbook workbook = new XSSFWorkbook();
   // 如要新建一名为"会员登录统计"的工作表，其语句为：
   Sheet sheet = workbook.createSheet("狂神观众统计表");
   // 创建行（row 1）
   Row row1 = sheet.createRow(0);
   // 创建单元格（col 1-1）
   Cell cell11 = row1.createCell(0);
   cell11.setCellValue("今日新增关注");
	//创建单元格（col 1-2）
   Cell cell12 = row1.createCell(1);
   cell12.setCellValue(666);
   // 创建行（row 2）
   Row row2 = sheet.createRow(1);
   // 创建单元格（col 2-1）
   Cell cell21 = row2.createCell(0);
   cell21.setCellValue("统计时间");
   //创建单元格（第三列）
   Cell cell22 = row2.createCell(1);
   String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
   cell22.setCellValue(dateTime);
   // 新建一输出文件流（注意：要先创建文件夹）07版本就是使用xlsx结尾
   FileOutputStream out = new FileOutputStream(path+"狂神观众统计表07.xlsx");
   // 把相应的Excel 工作簿存盘
   workbook.write(out);
   // 操作结束，关闭文件
   out.close();
   System.out.println("文件生成成功");
}

注意：注意对象的一个区别，文件后缀

数据批量导入

03版大文件写HSSF

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

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

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

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
@Test
public void testWrite03BigData() throws IOException {
    //记录开始时间
    long begin = System.currentTimeMillis();
    //创建一个SXSSFWorkbook
    Workbook workbook = new HSSFWorkbook();
    //创建一个sheet

    Sheet sheet = workbook.createSheet(); 
    //xls文件最大支持65536行
    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(cellNum);
        }
    }

    System.out.println("done");
    FileOutputStream out = new FileOutputStream(path+"bigdata03.xls");
    workbook.write(out);
    // 操作结束，关闭文件
    out.close();
    //记录结束时间
    long end = System.currentTimeMillis();
    System.out.println((double)(end - begin)/1000);
}

07版大文件写XSSF

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

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

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
@Test
public void testWrite07BigData() throws IOException {
   //记录开始时间
   long begin = System.currentTimeMillis();
   //创建一个XSSFWorkbook
   Workbook workbook = new XSSFWorkbook();
   //创建一个sheet
   Sheet sheet = workbook.createSheet();
 
   for (int rowNum = 0; rowNum < 100000; rowNum++) {
       //创建一个行
       Row row = sheet.createRow(rowNum);
       for (int cellNum = 0; cellNum < 10; cellNum++) {//创建单元格
           Cell cell = row.createCell(cellNum);
           cell.setCellValue(cellNum);
      }
  }
   System.out.println("done");
   FileOutputStream out = new FileOutputStream(path+"bigdata07.xlsx");
   workbook.write(out);
   // 操作结束，关闭文件
   out.close();
   //记录结束时间
   long end = System.currentTimeMillis();
   System.out.println((double)(end - begin)/1000);
}

大文件写SXSSF

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

注意：

过程中会产生临时文件，需要清理临时文件

默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件

如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook ( 数量)

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
@Test
public void testWrite07BigDataFast() throws IOException {
  //记录开始时间
  long begin = System.currentTimeMillis();
  //创建一个SXSSFWorkbook
  Workbook workbook = new SXSSFWorkbook();
  //创建一个sheet
  Sheet sheet = workbook.createSheet();
  //xls文件最大支持65536行
  for (int rowNum = 0; rowNum < 100000; rowNum++) {
     //创建一个行
     Row row = sheet.createRow(rowNum);
     for (int cellNum = 0; cellNum < 10; cellNum++) {//创建单元格
        Cell cell = row.createCell(cellNum);
        cell.setCellValue(cellNum);
    }
  }
  System.out.println("done");
  FileOutputStream out = new FileOutputStream(path+"bigdata07-fast.xlsx");
  workbook.write(out);
  // 操作结束，关闭文件
  out.close();
  //清除临时文件
  ((SXSSFWorkbook)workbook).dispose();
  //记录结束时间
  long end = System.currentTimeMillis();
  System.out.println((double)(end - begin)/1000);
}

SXSSFWorkbook-来至官方的解释：实现“BigGridDemo”策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。

请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注释……仍然只存储在内存中，因此如果广泛使用，可能需要大量内存。

POI-Excel读

03|07

03版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Test
public void testRead03() throws Exception{
   //获取文件流
   InputStream is = new FileInputStream(path+"狂神观众统计表03.xls");
   // 创建一个工作簿，使用excel能操作的内容，使用这个对象都可以设置
   Workbook workbook = new HSSFWorkbook(is);
   // 得到表
   Sheet sheet = workbook.getSheetAt(0);
   // 读取第一行第一列
   Row row = sheet.getRow(0);
   Cell cell = row.getCell(0);
   // 输出单元内容，这里是获取字符串类型的值
   // 读取值的时候，一定要注意类型
   System.out.println(cell.getStringCellValue());
   // 操作结束，关闭文件
   is.close();
}

07版本

1
2
3
4
5
6
7
8
9
10
11
12
13
@Test
public void testRead07() throws Exception{
   InputStream is = new FileInputStream(path+"/狂神观众统计表07.xlsx");
   Workbook workbook = new XSSFWorkbook(is);
   Sheet sheet = workbook.getSheetAt(0);
   // 读取第一行第一列
   Row row = sheet.getRow(0);
   Cell cell = row.getCell(0);
   // 输出单元内容
   System.out.println(cell.getStringCellValue());
   // 操作结束，关闭文件
   is.close();
}

==注意获取值的类型即可==

读取不同的数据类型

image-20200429131535228

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
@Test
public void testCellType() throws Exception {
    InputStream is = new FileInputStream(path+"/会员消费商品明细表.xls");
    Workbook workbook = new HSSFWorkbook(is);
    Sheet sheet = workbook.getSheetAt(0);
    // 读取标题所有内容
    Row rowTitle = sheet.getRow(0);
    if (rowTitle != null) {// 行不为空
        // 读取cell
        int cellCount = rowTitle.getPhysicalNumberOfCells();
        for (int cellNum = 0; cellNum < cellCount; cellNum++) {
            Cell cell = rowTitle.getCell(cellNum);
            if (cell != null) {
                int cellType = cell.getCellType();
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + "|");
            }
        }
        System.out.println();
    }
    
    // 获取表中的内容
    // 获取所有行的统计
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1; rowNum < rowCount; rowNum++) {
        Row rowData = sheet.getRow(rowNum);
        if (rowData != null) {// 行不为空
            // 读取cell列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                System.out.print("【" + (rowNum + 1) + "-" + (cellNum + 1) +"】");
                
                Cell cell = rowData.getCell(cellNum);
                
                //匹配列的数据类型
                if (cell != null) {
                    int cellType = cell.getCellType();
                    
                    //判断单元格数据类型
                    String cellValue = "";
                    switch (cellType) {
                        case HSSFCell.CELL_TYPE_STRING://字符串
                            System.out.print("【STRING】");
                            cellValue = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                            System.out.print("【BOOLEAN】");
                            cellValue =
                                String.valueOf(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BLANK://空
                            System.out.print("【BLANK】");
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC://数字（日期、普通数字）
                            System.out.print("【NUMERIC】");
                            //cellValue =
                            String.valueOf(cell.getNumericCellValue());
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                                    System.out.print("【日期】");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            } else {
                                // 不是日期格式，则防止当数字过长时以科学计数法显示
                                System.out.print("【转换成字符串】");
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            System.out.print("【数据类型错误】");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }
    is.close();
}

输出结果

image-20200429143538605

注意类型转换问题

计算公式

image-20200429135349842

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
@Test
public void testFormula() throws Exception{
   InputStream is = new FileInputStream(path + "计算公式.xls");
   Workbook workbook = new HSSFWorkbook(is);
   Sheet sheet = workbook.getSheetAt(0);
   // 读取第五行第一列
   Row row = sheet.getRow(4);
   Cell cell = row.getCell(0);
   
   //公式计算器
   FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    
   // 输出单元内容
   int cellType = cell.getCellType();
   switch (cellType) {
       case Cell.CELL_TYPE_FORMULA://公式
           //得到公式
           String formula = cell.getCellFormula();
           //输出公式
           System.out.println(formula);
           
           //计算
           CellValue evaluate = formulaEvaluator.evaluate(cell);
           //String cellValue = String.valueOf(evaluate.getNumberValue());
           //将输出结果变成字符串
           String cellValue = evaluate.formatAsString();
           System.out.println(cellValue);
           break;
  }
}

输出结果

image-20200429143338978

EasyExcel操作

在github上查看pom文档将标签引入

导入依赖

1
2
3
4
5
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.1.7</version>
</dependency>

导入这个类的时候要将xls03和xlsx07这两个依赖删除

写出测试

1、DemoData.java

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

2、测试写入数据

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
package com.kuang;
import com.alibaba.excel.EasyExcel;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class EasyExcelTest {
   String path = 
       "D:\\狂神说Java\\【狂神】小专题\\POI-EasyExcel\\Bilibili-狂神说java\\kuang-poi\\";
    
   private List<DemoData> data() {
       List<DemoData> list = new ArrayList<DemoData>();
       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;
  }
    
   // 最简单的写
   // 根据list，写入excel
   @Test
   public void simpleWrite() {
       // 写法1
       String fileName = path+"EasyExcel.xlsx";
       // 这里需要指定写用哪个class去写，然后写到第一个sheet，名字为模板然后文件流会自动关闭
       // 如果这里想使用03 则传入excelType参数即可
       // wirte(fileName,格式类)
       // sheet(表名)
       // doWrite(数据)
       EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
  }
}

最终的结果：

image-20200429152643867

读取测试

1、DemoData.java

1
2
3
4
5
6
@Data
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

2、Listener监听器

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
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假设这个是一个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
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    // 读取数据会执行invoke方法
    // DemoData类型
    // AnalysisContext 分析上下文
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();//持久化逻辑
            // 存储完成清理 list
            list.clear();
        }
    }

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

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

3、DemoDAO.java持久层

1
2
3
4
5
6
7
8
9
10
/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 **/
public class DemoDAO {

    public void save(List<DemoData> list) {
        //持久化操作
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

测试方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/**
 * 最简单的读
 * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
 * <p>3. 直接读即可
 */
@Test
public void simpleRead() {
    // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    // 写法1：
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    
    // 重点注意读取的逻辑DemoDataListener
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

    // 写法2：
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();
}

固定套路：

1、写入，固定类格式进行写入

2、读取，根据监听器设置的规则进行

本文标题:easyExcel

文章作者:爱吃鱼的呆先生

发布时间:2020-04-29, 08:14:27

最后更新:2020-04-29, 16:00:23

原始链接:http://yoursite.com/2020/04/29/0编程工具/easyExcel/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. easyExcel
    1. 1.1. 谈谈POI 和easyExcel
    2. 1.2. POI-Excel写
    3. 1.3. 数据批量导入
    4. 1.4. POI-Excel读
    5. 1.5. EasyExcel操作
|