Apache POI & EasyExcel

常用信息

  • 将用户信息导出为excel表格（导出数据）
  • 将Excel表中的信息录入到网站数据库（习题上传）

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

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

Apache POI

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

1594905499738.png

1594905584312.png

easyExcel

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

1594905822667.png

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

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

内存问题：

  • POI ：100w 先加载到内存OOM，在写入文件
  • EasyExcel：小部分消化，一行一行的返回

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

1594906026677.png

官方网址： https://www.yuque.com/easyexcel/doc/easyexcel

POI 操作

POI-Excel写

创建项目

  • 建立一个空项目，创建普通的Maven的 Model

  • 引入pom依赖 

      1. <!-- xls (03) -->
      2. <dependency>
      3. <groupId>org.apache.poi</groupId>
      4. <artifactId>poi</artifactId>
      5. <version>4.1.2</version>
      6. </dependency>
      7. <!-- xlsx (07) -->
      8. <dependency>
      9. <groupId>org.apache.poi</groupId>
      10. <artifactId>poi-ooxml</artifactId>
      11. <version>4.1.2</version>
      12. </dependency>
      13. <!-- 日期格式化工具 -->
      14. <dependency>
      15. <groupId>joda-time</groupId>
      16. <artifactId>joda-time</artifactId>
      17. <version>2.9.9</version>
      18. </dependency>
      19. <!-- test -->
      20. <dependency>
      21. <groupId>junit</groupId>
      22. <artifactId>junit</artifactId>
      23. <version>4.12</version>
      24. <scope>test</scope>
      25. </dependency>

  • 工作簿

  • 1594906885087.png
  • 表格中的对象
    • 工作簿
    • 工作表
    • 行
    • 列
  • 03版本写文件 
    1. public static void testWrite03() {
    2. // 1.创建一个工作簿
    3. Workbook workbook = new HSSFWorkbook();
    4. // 2.创建一个工作表
    5. Sheet sheet = workbook.createSheet("ic 测试表 03");
    6. // 3.创建一个行
    7. Row row0 = sheet.createRow(0);
    8. // 4.创建一个列
    9. Cell cell11 = row0.createCell(0);
    10. // 向格子里面写入数据
    11. cell11.setCellValue("第一行第一列");
    12. Cell cell12 = row0.createCell(1);
    13. cell12.setCellValue("第一行第二列");
    14. Row row1 = sheet.createRow(1);
    15. Cell cell21 = row1.createCell(0);
    16. cell21.setCellValue("统计时间");
    17. Cell cell22 = row1.createCell(1);
    18. String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    19. cell22.setCellValue(time);
    20. // 生成一张表 （IO流）
    21. try {
    22.     FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\ic-test03.xls");
    23.     workbook.write(fileOutputStream);
    24. } catch (Exception e) {
    25.     e.printStackTrace();
    26. } finally {
    27.     System.out.println("success03");
    28. }
    29. }

  • 07版本写文件 

    1. public static void testWrite07() {
    2. // 1.创建一个工作簿
    3. Workbook workbook = new XSSFWorkbook();
    4. // 2.创建一个工作表
    5. Sheet sheet = workbook.createSheet("ic 测试表 07");
    6. // 3.创建一个行
    7. Row row0 = sheet.createRow(0);
    8. // 4.创建一个列
    9. Cell cell11 = row0.createCell(0);
    10. // 向格子里面写入数据
    11. cell11.setCellValue("第一行第一列");
    12. Cell cell12 = row0.createCell(1);
    13. cell12.setCellValue("第一行第二列");
    14. Row row1 = sheet.createRow(1);
    15. Cell cell21 = row1.createCell(0);
    16. cell21.setCellValue("统计时间");
    17. Cell cell22 = row1.createCell(1);
    18. String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    19. cell22.setCellValue(time);
    20. // 生成一张表 （IO流）
    21. try {
    22.     FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\ic-test07.xls");
    23.     workbook.write(fileOutputStream);
    24. } catch (Exception e) {
    25.     e.printStackTrace();
    26. } finally {
    27.     System.out.println("success07");
    28. }
    29. }

  • 测试类

    • ```java package cn.icanci;

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.apache.poi.xssf.usermodel.XSSFWorkbook; import org.joda.time.DateTime;

import java.io.FileOutputStream;

public class ExcelWriteTest { private static String PATH = “E:\IdeaHome\maven\apache-poi”;

  1. public static void main(String[] args) {
  2.     testWrite03();
  3.     testWrite07();
  4. }

}

  2. <a name="45f1d14b"></a>
  3. ##### 数据批量导入
  5. > 大文件写HSSF
  8. 缺点：最多只能处理65536行，否则会抛出异常
  10. ```java
  11. Exception in thread "main" java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

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

  1. public static void testWrite03BigData() throws Exception {
  2.         // 时间
  3.         long begin = System.currentTimeMillis();
  4.         // 创建一个工作簿
  5.         Workbook workbook = new HSSFWorkbook();
  6.         // 创建一张表
  7.         Sheet sheet = workbook.createSheet("03 版本大数据写");
  8.         // 写入数据
  9.         for (int rowNum = 0; rowNum < 65536; rowNum++) {
  10.             Row row = sheet.createRow(rowNum);
  11.             for (int cellNum = 0; cellNum < 10; cellNum++) {
  12.                 Cell cell = row.createCell(cellNum);
  13.                 cell.setCellValue(cellNum);
  14.             }
  15.         }
  16.         System.out.println("over");
  17.         FileOutputStream outputStream = new FileOutputStream(PATH + "\\testWrite03BigData.xls");
  18.         workbook.write(outputStream);
  19.         outputStream.close();
  20.         long end = System.currentTimeMillis();
  21.         System.out.println(end - begin + ":ms");
  22.     }

大文件写XSSF

缺点：写数据时候特别慢，非常消耗内存，也会发生内存溢出，如100w条数据

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

  1. public static void testWrite07BigData() throws Exception {
  2.     // 时间
  3.     long begin = System.currentTimeMillis();
  4.     // 创建一个工作簿
  5.     Workbook workbook = new XSSFWorkbook();
  6.     // 创建一张表
  7.     Sheet sheet = workbook.createSheet("07 版本大数据写");
  8.     // 写入数据
  9.     for (int rowNum = 0; rowNum < 100000; rowNum++) {
  10.         Row row = sheet.createRow(rowNum);
  11.         for (int cellNum = 0; cellNum < 10; cellNum++) {
  12.             Cell cell = row.createCell(cellNum);
  13.             cell.setCellValue(cellNum);
  14.         }
  15.     }
  16.     System.out.println("over");
  17.     FileOutputStream outputStream = new FileOutputStream(PATH + "\\testWrite07BigData.xlsx");
  18.     workbook.write(outputStream);
  19.     outputStream.close();
  20.     long end = System.currentTimeMillis();
  21.     System.out.println(end - begin + ":ms");
  22. }

大文件写SXSSF

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

注意：

写的过程中会出现临时文件，所以需要清理临时文件

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

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

  1. public static void testWrite07BigDataSXSSF() throws Exception {
  2.     // 时间
  3.     long begin = System.currentTimeMillis();
  4.     // 创建一个工作簿
  5.     Workbook workbook = new SXSSFWorkbook();
  6.     // 创建一张表
  7.     Sheet sheet = workbook.createSheet("07 版本大数据写");
  8.     // 写入数据
  9.     for (int rowNum = 0; rowNum < 100000; rowNum++) {
  10.         Row row = sheet.createRow(rowNum);
  11.         for (int cellNum = 0; cellNum < 10; cellNum++) {
  12.             Cell cell = row.createCell(cellNum);
  13.             cell.setCellValue(cellNum);
  14.         }
  15.     }
  16.     System.out.println("over");
  17.     FileOutputStream outputStream = new FileOutputStream(PATH + "\\testWrite07BigDataS.xlsx");
  18.     workbook.write(outputStream);
  19.     outputStream.close();
  20.     // 清除临时文件
  21.     ((SXSSFWorkbook) workbook).dispose();
  22.     long end = System.currentTimeMillis();
  23.     System.out.println(end - begin + ":ms");
  24. }

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

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

POI-Excel 读

03|07

03版本

  1. public static void testRead03()throws Exception {
  2.     // 获取流
  3.     FileInputStream inputStream = new FileInputStream(PATH + "ic-test03.xls");
  4.     // 1.创建一个工作簿 使用Excel可以操作的，都可以操作
  5.     Workbook workbook = new HSSFWorkbook(inputStream);
  6.     Sheet sheet = workbook.getSheetAt(0);
  7.     Row row = sheet.getRow(0);
  8.     Cell cell = row.getCell(0);
  9.     // 读取值的时候需要注意类型，否则会报错
  10.     // 目前获取的是字符串类型 cell.getStringCellValue()
  11.     System.out.println(cell.getStringCellValue());
  12.     inputStream.close();
  13. }

07版本

  1. public static void testRead07()throws Exception {
  2.     // 获取流
  3.     FileInputStream inputStream = new FileInputStream(PATH + "ic-test07.xlsx");
  4.     // 1.创建一个工作簿 使用Excel可以操作的，都可以操作
  5.     Workbook workbook = new XSSFWorkbook(inputStream);
  6.     Sheet sheet = workbook.getSheetAt(0);
  7.     Row row = sheet.getRow(0);
  8.     Cell cell = row.getCell(0);
  9.     // 读取值的时候需要注意类型，否则会报错
  10.     // 目前获取的是字符串类型 cell.getStringCellValue()
  11.     System.out.println(cell.getStringCellValue());
  12.     inputStream.close();
  13. }

读取不同的数据类型（最麻烦的地方）

  1. public static void testCellType() throws Exception {
  2.     // 获取文件流
  3.     FileInputStream in = new FileInputStream(PATH + "明细表.xlsx");
  4.     // 创建一个工作簿
  5.     Workbook workbook = new XSSFWorkbook(in);
  6.     Sheet sheet = workbook.getSheetAt(0);
  7.     // 获取标题内容
  8.     Row rowTitle = sheet.getRow(0);
  9.     if (rowTitle != null) {
  10.         // 一定要掌握
  11.         int cellCount = rowTitle.getPhysicalNumberOfCells();
  12.         for (int cellNum = 0; cellNum < cellCount; cellNum++) {
  13.             Cell cell = rowTitle.getCell(cellNum);
  14.             if (cell != null) {
  15.                 int cellType = cell.getCellType();
  16.                 System.out.print(cell.getStringCellValue() + " ");
  17.             }
  18.         }
  19.     }
  20.     // 获取表中的内容
  21.     int rowCount = sheet.getPhysicalNumberOfRows();
  22.     for (int rowNum = 0; rowNum < rowCount; rowNum++) {
  23.         Row rowData = sheet.getRow(rowNum);
  24.         if (rowData != null) {
  25.             // 读取列
  26.             int cellCount = rowTitle.getPhysicalNumberOfCells();
  27.             for (int cellNum = 0; cellNum < cellCount; cellNum++) {
  28.                 System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
  29.                 // 获得列
  30.                 Cell cell = rowData.getCell(cellNum);
  31.                 // 因为不知道列的类型 所以需要匹配列的类型
  32.                 if (cell != null) {
  33.                     int cellType = cell.getCellType();
  34.                     String cellValue = "";
  35.                     switch (cellType) {
  36.                             //为空
  37.                         case -1:
  38.                             System.out.print("[ _NONE ]");
  39.                             cellValue = "null";
  40.                             break;
  41.                             // 为 空格
  42.                         case 3:
  43.                             System.out.print("[ BLANK ]");
  44.                             break;
  45.                             // 为String类型
  46.                         case 1:
  47.                             System.out.println("[ STRING ]");
  48.                             cellValue = cell.getStringCellValue();
  49.                             break;
  50.                             // 为 number 类型
  51.                         case 0:
  52.                             // 是一个日期
  53.                             if (HSSFDateUtil.isCellDateFormatted(cell)) {
  54.                                 System.out.println("[ Date ]");
  55.                                 Date dateCellValue = cell.getDateCellValue();
  56.                                 cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd HH:mm:ss");
  57.                             } else {
  58.                                 // 是一个数字
  59.                                 System.out.println("[ 转换为字符串显示 ]");
  60.                                 cell.setCellType(CellType.STRING);
  61.                                 cellValue = cell.toString();
  62.                             }
  63.                             break;
  64.                             // 布尔值
  65.                         case 4:
  66.                             System.out.println("[ 布尔值 ]");
  67.                             cellValue = String.valueOf(cell.getBooleanCellValue());
  68.                             break;
  69.                             // 错误
  70.                         case 5:
  71.                             System.out.println("[ 数据类型错误 ]");
  72.                             break;
  73.                     }
  74.                     System.out.println(cellValue);
  75.                 }
  76.             }
  77.         }
  78.     }
  79.     in.close();
  80. }

计算公式（了解即可）

  1. public static void testFormula() throws Exception {
  2.     FileInputStream in = new FileInputStream(PATH + "计算.xlsx");
  4.     Workbook workbook = new XSSFWorkbook(in);
  5.     Sheet sheet = workbook.getSheetAt(0);
  6.     Row row = sheet.getRow(4);
  7.     Cell cell = row.getCell(0);
  8.     // 拿到计算公式
  9.     FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
  10.     // 输出单元格的内容
  11.     int cellType = cell.getCellType();
  12.     switch (cellType) {
  13.             // 是公式
  14.         case 2:
  15.             String cellFormula = cell.getCellFormula();
  16.             System.out.println(cellFormula);
  17.             CellValue evaluate = formulaEvaluator.evaluate(cell);
  18.             String result = evaluate.formatAsString();
  19.             System.out.println(result);
  20.             break;
  21.     }
  22.     in.close();
  23. }

EasyExcel 操作

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

Github案例： https://github.com/alibaba/easyexcel

导入依赖

  1. <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
  2. <dependency>
  3.     <groupId>com.alibaba</groupId>
  4.     <artifactId>easyexcel</artifactId>
  5.     <version>2.2.6</version>
  6. </dependency>
  8. <dependency>
  9.     <groupId>org.projectlombok</groupId>
  10.     <artifactId>lombok</artifactId>
  11.     <version>1.18.12</version>
  12. </dependency>
  13. <dependency>
  14.     <groupId>org.slf4j</groupId>
  15.     <artifactId>slf4j-log4j12</artifactId>
  16.     <version>1.7.25</version>
  17. </dependency>

写入测试

DemoData.java

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

测试写入数据

  1. package cn.icanci;
  3. import com.alibaba.excel.EasyExcel;
  4. import com.alibaba.excel.ExcelWriter;
  5. import com.alibaba.excel.write.metadata.WriteSheet;
  7. import java.util.ArrayList;
  8. import java.util.Date;
  9. import java.util.List;
  11. /**
  12.  * @Author: icanci
  13.  * @ProjectName: apache-poi
  14.  * @PackageName: cn.icanci
  15.  * @Date: Created in 2020/7/17 8:42
  16.  * @ClassAction:
  17.  */
  18. public class TestDemo {
  19.     private static String PATH = "E:\\IdeaHome\\maven\\apache-poi\\";
  21.     public static void main(String[] args) {
  22.         new TestDemo().simpleWrite();
  23.     }
  25.     private List<DemoData> data() {
  26.         List<DemoData> list = new ArrayList<DemoData>();
  27.         for (int i = 0; i < 10; i++) {
  28.             DemoData data = new DemoData();
  29.             data.setString("字符串" + i);
  30.             data.setDate(new Date());
  31.             data.setDoubleData(0.56);
  32.             list.add(data);
  33.         }
  34.         return list;
  35.     }
  37.     /**
  38.      * 根据 list 写入 excel
  39.      */
  40.     public void simpleWrite() {
  41.         // 写法1
  42.         String fileName =PATH+ "simpleWrite1.xlsx";
  43.         // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
  44.         // 如果这里想使用03 则 传入excelType参数即可
  46.         // write (fileName,格式类)
  47.         // sheet (表名)
  48.         // doWrite(数据)  真实情况应该是从数据库读取的数据，或者是用户上传的数据
  49.         EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
  51.         // 写法2
  52.         fileName = PATH + "simpleWrite2.xlsx";
  53.         // 这里 需要指定写用哪个class去写
  54.         ExcelWriter excelWriter = null;
  55.         try {
  56.             excelWriter = EasyExcel.write(fileName, DemoData.class).build();
  57.             WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
  58.             excelWriter.write(data(), writeSheet);
  59.         } finally {
  60.             // 千万别忘记finish 会帮忙关闭流
  61.             if (excelWriter != null) {
  62.                 excelWriter.finish();
  63.             }
  64.         }
  65.     }
  66. }

最终的结果

读取测试

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

Github案例： https://github.com/alibaba/easyexcel

固定套路

  • 写入，固定类格式进行写入
  • 读取，根据监听器设置的规则进行读取

总结

面对对象的方法、思想，学会面向接口编程