实际开发中经常会设计到excel的处理,如导出Excel,导入Excel到数据库中,操作Excel目前有两个框架,一个是apache 的poi, 另一个是 Java Excel(jxl)。当然这里还有一个是阿里巴巴对poi进一步封装的开源的框架EasyExcel。 Apache POI 简介是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office(Excel、Word、PowerPoint、Visio等)格式档案读和写的功能。 Java Excel是一开放源码项目,通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。jxl 特点是小巧 易用。 EasyExcel 是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称 Apache poi、jxl 框架都存在一个严重的问题就是非常的耗内存。EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。github地址:https://github.com/alibaba/easyexcel 学习目标:
  • POI
  • EasyExcel

1. POI简介

POI%26EasyExcel - 图1

1.1 POI介绍

Apache POI 简介是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office(Excel、WORD、PowerPoint、Visio等)格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写。 学习思想: 其实POI这个类库,不难。我们不要上来就死记硬背代码。我们只要掌握了使用的思想就会变得非常简单。下面会讲到这种思想。
官网以及参考的文档地址:
官方主页: http://poi.apache.org/index.html API文档: http://poi.apache.org/apidocs/index.html ## 1.2 POI 常用的类和接口 Apache POI包含类和方法,来将MS Office所有OLE 2文档复合。此API组件的列表如下。
  • HSSF提供读写Microsoft Excel XLS格式档案的功能。
  • XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
  • HWPF提供读写Microsoft Word DOC格式档案的功能。
  • HSLF提供读写Microsoft PowerPoint格式档案的功能。
  • HDGF提供读Microsoft Visio格式档案的功能。
  • ……
其实POI很强大,不单单可以操作Excle还可以操作PPT,Word …. 。但是我们在实际开发中百分之九十都是操作Excel的,所谓偏离业务的技术都是耍流氓,所以说我们只学习和业务相关的知识点 - - - 学习怎样操作Excel。使用POI操作Excel的话无非两种情况一张使用操作2003版本的Excel,一种是操作2007版本的Excel。下面会详细讲到。

1.3 POI常用类和方法

上面说了我们只讲操作如何操作Excel的,偏向于实际业务开发的。使用POI操作Excel的话无非两种情况一种是操作2003+ 版本的Excel,使用的接口对象是(HSSF )开头的对象,另一种是操作2007+版本的Excel,接口对象是(XSSF )开头。 Excel中的工作簿、工作表、行、单元格中的关系: 一个Excel文件对应于一个workbook(HSSFWorkbook,XSSFWorkbook), 一个workbook可以有多个sheet(HSSFSheet,XSSFSheet)组成, 一个sheet是由多个row(HSSFRow,XSSFRow)组成, 一个row是由多个cell(HSSFCell,XSSFCell)组成

POI%26EasyExcel - 图2

  • 2003版本常用类和方法
  • HSSFWorkbook:工作簿
    • 工作簿,代表一个excel的整个文档
  • HSSFWorkbook(); // 创建一个新的工作簿
  • HSSFWorkbook(InputStream inputStream); // 创建一个关联输入流的工作簿,可以将一个excel文件封装成工作簿
  • HSSFSheet createSheet(String sheetname); 创建一个新的Sheet
  • HSSFSheet getSheet(String sheetName); 通过名称获取Sheet
  • HSSFSheet getSheetAt(int index); // 通过索引获取Sheet,索引从0开始
  • HSSFCellStyle createCellStyle(); 创建单元格样式
  • int getNumberOfSheets(); 获取sheet的个数
  • setActiveSheet(int index); 设置默认选中的工作表
  • write();
  • write(File newFile);
  • write(OutputStream stream);
  • HSSFSheet:工作表
  • HSSFRow createRow(int rownum); 创建新行,需要指定行号,行号从0开始
  • HSSFRow getRow(int index); 根据索引获取指定的行
  • int addMergedRegion(CellRangeAddress region); 合并单元格
  • CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol); 单元格范围, 用于合并单元格,需要指定要合并的首行、最后一行、首列、最后一列。
  • autoSizeColumn(int column); 自动调整列的宽度来适应内容
  • getLastRowNum(); 获取最后的行的索引,没有行或者只有一行的时候返回0
  • setColumnWidth(int columnIndex, int width); 设置某一列的宽度,width=字符个数 256,例如20个字符的宽度就是20 256
  • HSSFRow :行
  • HSSFCell createCell(int column); 创建新的单元格
  • HSSFCell setCell(shot index);
  • HSSFCell getCell(shot index);
  • setRowStyle(HSSFCellStyle style); 设置行样式
  • short getLastCellNum(); 获取最后的单元格号,如果单元格有第一个开始算,lastCellNum就是列的个数
  • setHeightInPoints(float height); 设置行的高度
  • HSSFCell:单元格
  • setCellValue(String value); 设置单元格的值
  • setCellType(); 设置单元格类型,如 字符串、数字、布尔等
  • setCellStyle(); 设置单元格样式
  • String getStringCellValue(); 获取单元格中的字符串值
  • setCellStyle(HSSFCellStyle style); 设置单元格样式,例如字体、加粗、格式化
  • setCellFormula(String formula); 设置计算公式,计算的结果作为单元格的值,也提供了异常常用的函数,如求和”sum(A1,C1)”、日期函数、字符串相关函数、CountIf和SumIf函数、随机数函数等
  • 2007版本常用类和方法
  • XSSFWorkbook:工作簿
  • XSSF Workbook(); // 创建一个新的工作簿
  • XSSF Workbook(InputStream inputStream); // 创建一个关联输入流的工作簿,可以将一个excel文件封装成工作簿
  • XSSF Sheet createSheet(String sheetname); 创建一个新的Sheet
  • XSSF Sheet getSheet(String sheetName); 通过名称获取Sheet
  • XSSF Sheet getSheetAt(int index); // 通过索引获取Sheet,索引从0开始
  • XSSF CellStyle createCellStyle(); 创建单元格样式
  • int getNumberOfSheets(); 获取sheet的个数
  • setActiveSheet(int index); 设置默认选中的工作表
  • write();
  • write(File newFile);
  • write(OutputStream stream);
  • XSSFSheet:工作表
  • XSSF Row createRow(int rownum); 创建新行,需要指定行号,行号从0开始
  • XSSF Row getRow(int index); 根据索引获取指定的行
  • int addMergedRegion(CellRangeAddress region); 合并单元格
  • CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol); 单元格范围, 用于合并单元格,需要指定要合并的首行、最后一行、首列、最后一列。
  • autoSizeColumn(int column); 自动调整列的宽度来适应内容
  • getLastRowNum(); 获取最后的行的索引,没有行或者只有一行的时候返回0
  • setColumnWidth(int columnIndex, int width); 设置某一列的宽度,width=字符个数 256,例如20个字符的宽度就是20 256
  • XSSFRow :行
  • XSSF Cell createCell(int column); 创建新的单元格
  • XSSF Cell setCell(shot index);
  • XSSF Cell getCell(shot index);
  • setRowStyle(XSSF CellStyle style); 设置行样式
  • short getLastCellNum(); 获取最后的单元格号,如果单元格有第一个开始算,lastCellNum就是列的个数
  • setHeightInPoints(float height); 设置行的高度
  • XSSFCell:单元格
  • setCellValue(String value); 设置单元格的值
  • setCellType(); 设置单元格类型,如 字符串、数字、布尔等
  • setCellStyle(); 设置单元格样式
  • String getStringCellValue(); 获取单元格中的字符串值
  • setCellStyle(XSSF CellStyle style); 设置单元格样式,例如字体、加粗、格式化
  • setCellFormula(String formula); 设置计算公式,计算的结果作为单元格的值,也提供了异常常用的函数,如求和”sum(A1,C1)”、日期函数、字符串相关函数、CountIf和SumIf函数、XSSF

1.4 POI中Excel组成和步骤

组成

Excel的文件的组成形式,一个Excel文件对应于一个workbook(XSSFWorkbook - 工作薄),一个workbook可以有多个sheet(XSSFSheet - 工作表)组成,一个sheet是由多个row(Row - 工作行)组成,一个row是由多个cell(Cell - 工作单元格)组成。

操作步骤

基于以上几条,如果想对excel文件进行读写的话就要需要执行下面这几个步骤
  1. 用Workbook打开或者创建一个Excel文件的对象
  2. 用上一步的Excel对象创建或者获取到一个Sheet对象
  3. 用Sheet对象创建或获取一个Row对象
  4. 用Row对象创建或获取一个Cell对象
  5. 对Cell对象读写。

1.5 2003版本和2007版本

操作Excel文件区分版本:
  • 2003+版本(包含2003)文件的扩展名为 .xls 需要用 HSSFWorkbook 类操作

POI%26EasyExcel - 图3

  • 2007+ 版本(包含2007)文件的扩展名为 .xlsx 需要用 XSSFWorkbook 类操作

POI%26EasyExcel - 图4

POI%26EasyExcel - 图5

  • 需要注意:2003 版本和 2007 版本存在兼容性的问题!2003最多只有 65536 行!2007最多是1048576
这里需要注意:2007版及以上的其实也是有限制的,最大是1048576。网络上有很多文章。都说07版本的没有限制,正好在这里纠正一下

2. POI 操作案例 - 读写

上面大概讲解了一下POI的组成,操作Excel的步骤,常用的接口和实现类以及不同版本操作Excel的实现类是不同的,下面我就使用POI进行简单的读写。 在实际上手操作之前,必须要先把所需的环境搭建起来,下面我们来简单的搭建一个SpringBoot项目,进行测试POI对Excel读和写两大功能。

项目结构说明:

创建了一个空项目,在空项目里面创建了两个SpringBoot项目: 如果你刚从码云上拉取下来的话,一定要配置一下数据库,其他的不用动,为了方便大家使用,项目技术之间耦合度非常低。😊
  • boot-easyexcel :这里面存放的是EasyExcel测试demo
  • boot-poi : 这里面存放的是POI测试demo

POI%26EasyExcel - 图6

  • 注意:从版本4.0.1开始,POI需要Java 8或更高版本。
目前官网,稳定说的4.1.2是最新稳定版本——可参考:http://poi.apache.org/download.html 在使用poi的时候,首先需要导入相应的jar文件或者maven依赖。使用maven导入jar包时,发现POI有好多jar包,如果你是第一次使用的话,我相信很多人都困惑POI那么多Jar到底应该导入哪一个。其实我们百分之九十的情况下都是操作Excel,所以我们只需要导入Excel的jar或者依赖即可。 下面两个依赖第一个针对不同版本的Excel,两者的区别上面已经说过了,如果不是太清楚,可以到上面再去看一下。
  1. <!-- xls(2003)版本 -->
  2. <dependency>
  3. <groupId>org.apache.poi</groupId>
  4. <artifactId>poi</artifactId>
  5. <version>4.1.2</version>
  6. </dependency>
  7. <!-- xlsx(2007)版本及以上 -->
  8. <dependency>
  9. <groupId>org.apache.poi</groupId>
  10. <artifactId>poi-ooxml</artifactId>
  11. <version>4.1.2</version>
  12. </dependency>
  • Hutool工具类,里面封装了很多常用的工具类,我们后面会讲到怎样直接使用Hutool里面的工具类进行操作Excel,我们要用到的工具类操作Excel,其实是Hutool对POI的封装。
  1. <dependency>
  2. <groupId>cn.hutool</groupId>
  3. <artifactId>hutool-all</artifactId>
  4. <version>5.3.10</version>
  5. </dependency>

POI%26EasyExcel - 图7

OK,创建SpringBoot项目之后,导入上方的几个依赖,这样我们操作POI的环境已经搭建OK了,下面我们就去探索POI的具体使用吧。这里一定要记住,多练,不然你会发现脑子会了,手却写不出来。

2.1 写Excel

其实我们都知道,Java中都是万物皆对象的。那操作POI道理也是一样的。我们只要知道了操作POI的对象,那使用起来都不会有什么难度的了。 下面打开Excel,我们根据Excel,来找找灵感:

POI%26EasyExcel - 图8

从上面的截图中我们也已经大概可以抽取出几个核心对象了:
  • 工作薄(Workbook)
  • 工作表(Sheet)
  • 行(行是由单元格组成)(Row)
  • 单元格(Cell)
关系简单来说就是: 一个工作薄中可以有很多个 >>>>>> 工作表。 一个工作中表可以有很多个 >>>>>> 工作行。 一个工作行中可以有很多个 >>>>>> 工作单元格。 关系很简单。重在理解。说的这里我想童鞋们已经已经对操作POI有了一个大致的思路了,下面我们就编写一个写Excel的Demo案例,让童鞋们对操作POI有一个更深的理解。

2.1.1 03版本的POI操作写Excel - (案例) Demo

  1. @SpringBootTest
  2. class {
  3. //定义输出路径,路径改为你们要输出的路径即可
  4. final String PATH = "D:\\blog\\POI\\poi-demo\\boot-poi\\";
  5. @Test
  6. void excelWrite03Test() throws Exception {
  7. // 1. 创建工作薄
  8. Workbook workbook = new HSSFWorkbook();
  9. // 2. 创建工作表
  10. Sheet sheet = workbook.createSheet("老陈写Excel测试");
  11. // 3. 创建工作行
  12. Row row1 = sheet.createRow(0); //参数0 就代表Excel中的第一行
  13. // 4. 创建工作单元格
  14. Cell cell1 = row1.createCell(0);//参数0 就代表Excel中的第一个单元格
  15. //填充Excel中的第一行的第一个单元格(0,0)
  16. cell1.setCellValue("姓名:老陈(chen)");
  17. //填充Excel中的第一行的第二个单元格(0,1)
  18. Cell cell2 = row1.createCell(1);
  19. cell2.setCellValue("年龄:12岁");
  20. //创建第二行,就是下面这样,以此类推(2,1)
  21. Row row2 = sheet.createRow(1);
  22. Cell cell21 = row2.createCell(0);
  23. cell21.setCellValue("姓名:宋祖儿(song)");
  24. Cell cell22 = row2.createCell(1);
  25. cell22.setCellValue("年龄:13岁");
  26. //上面已经把所需要的数据已经构建成功,这时候需要生成一张Excel数据表。其实生成表的话本质上是IO操作,IO操作就需要流,所以说我们要构建一个流。输出到那个位置上
  27. FileOutputStream fileOutputStream = new FileOutputStream(PATH + "老陈测试03版本的Excel.xls");
  28. ((HSSFWorkbook) workbook).write(fileOutputStream);
  29. //关闭流
  30. System.out.println("-----------老陈通过POI写入Excel成功");
  31. }
  32. }
运行结果

POI%26EasyExcel - 图9

打开查看结果

POI%26EasyExcel - 图10

2.1.1 07版本的POI操作E写xcel - (案例) Demo

03版本的写出来之后,07版本的就很简单了。点进源码可以发现 Workbook 是一个接口,我们都只道Java是面向接口编程的,对象变了接口不用变,所以说们只需要把03版本的接口实现换为07版本的接口实现即可。(HSSFWorkbook >>>> 换为>>>> XSSFWorkbook)和把 xxx.xls 结尾的后缀改为 xxx.xlsx
  1. //定义输出路径,路径改为你们要输出的路径即可
  2. final String PATH = "D:\\blog\\POI\\poi-demo\\boot-poi\\";
  3. @Test
  4. void excelWrite07Test() throws Exception {
  5. // 1. 创建工作薄
  6. Workbook workbook = new XSSFWorkbook();
  7. // 2. 创建工作表
  8. Sheet sheet = workbook.createSheet("老陈写Excel测试");
  9. // 3. 创建工作行
  10. Row row1 = sheet.createRow(0); //参数0 就代表Excel中的第一行
  11. // 4. 创建工作单元格
  12. Cell cell1 = row1.createCell(0);//参数0 就代表Excel中的第一个单元格
  13. //填充Excel中的第一行的第一个单元格(0,0)
  14. cell1.setCellValue("姓名:老陈(chen)");
  15. //填充Excel中的第一行的第二个单元格(0,1)
  16. Cell cell2 = row1.createCell(1);
  17. cell2.setCellValue("年龄:12岁");
  18. //创建第二行,就是下面这样,以此类推(2,1)
  19. Row row2 = sheet.createRow(1);
  20. Cell cell21 = row2.createCell(0);
  21. cell21.setCellValue("姓名:宋祖儿(song)");
  22. Cell cell22 = row2.createCell(1);
  23. cell22.setCellValue("年龄:13岁");
  24. //上面已经把所需要的数据已经构建成功,这时候需要生成一张Excel数据表。其实生成表的话本质上是IO操作,IO操作就需要流,所以说我们要构建一个流。输出到那个位置上
  25. FileOutputStream fileOutputStream = new FileOutputStream(PATH + "老陈测试07版本的Excel.xlsx");
  26. workbook.write(fileOutputStream);
  27. //关闭流
  28. System.out.println("-----------老陈通过POI写入Excel 07版本成功");
  29. }

POI%26EasyExcel - 图11

2.1.3 探究Workbook对象

Workbook对象是POI提供的一个接口,不同版本的Excel提供了不同版本的实现类。比如07版本的是 XSSFWorkbook 03版本的是 HSSFWorkbook

POI%26EasyExcel - 图12

POI%26EasyExcel - 图13

从上面的截图中可以看出,Workbook有四个实现类
  • HSSFWorkbook
  • SXSSFWorkbook
  • SXSSFWorkbookWithCustomZipEntrySource
  • XSSFWorkbook

2.2 读Excel

读Excel也就是我们实际开发中的导入数据,简单点说就是Java程序把Excel中的数据读取到内存中。

2.2.1 03版本的POI操作读Excel - (案例) Demo

  1. //定义输出路径
  2. final String PATH = "D:\\TyporaBlog\\POI\\poi-demo\\boot-poi\\";
  3. @Test
  4. void excelReader03Test() throws Exception {
  5. //1. 获取文件的流
  6. FileInputStream fileInputStream = new FileInputStream(PATH + "老陈测试03版本的Excel.xls");
  7. //2. 创建工作薄,把获取的流传入
  8. Workbook workbook = new HSSFWorkbook(fileInputStream);
  9. //3. 创建表
  10. Sheet sheet = workbook.getSheetAt(0);
  11. //4. 创建行
  12. Row row1 = sheet.getRow(0);
  13. //5. 创建单元格
  14. Cell cell1 = row1.getCell(0);
  15. //6. 读取Excel内容
  16. System.out.println(cell1.getStringCellValue());
  17. //7. 关闭流
  18. fileInputStream.close();
  19. }

2.2.2 07版本的POI操作读Excel - (案例) Demo

  1. //定义输出路径
  2. final String PATH = "D:\\TyporaBlog\\POI\\poi-demo\\boot-poi\\";
  3. @Test
  4. void excelReader07Test() throws Exception {
  5. //1. 获取文件的流
  6. FileInputStream fileInputStream = new FileInputStream(PATH + "老陈测试07版本的Excel.xlsx");
  7. //2. 创建工作薄,把获取的流传入
  8. Workbook workbook = new XSSFWorkbook(fileInputStream);
  9. //3. 创建表
  10. Sheet sheet = workbook.getSheetAt(0);
  11. //4. 创建行
  12. Row row1 = sheet.getRow(0);
  13. //5. 创建单元格
  14. Cell cell1 = row1.getCell(0);
  15. //6. 读取Excel内容
  16. System.out.println(cell1.getStringCellValue());
  17. //7. 关闭流
  18. fileInputStream.close();
  19. }

2.2.3 读取Excel时不同的数据类型怎样处理嘞🤔

用过Excel的童鞋们都应该知道Excel中是可以给列设置数据类型的,那不同的类型数据,我们应该怎样获取哪 ??这了是POI读取Excel的一个难点,把这个掌握了,那在工作过程中,不管遇到什么困难都不足为惧了,跟着老陈整起吧。 注意,在读取单元格的值时需要先判断数据类型,不然获取数据会报数据类型错误。
  1. @Test
  2. void excelCellTypeTest() throws Exception {
  3. // 获取文件流
  4. FileInputStream fileInputStream = new FileInputStream(PATH + "POI-read - 不同类型读取03版本.xls");
  5. // 第一步是先创建工作薄,把文件的流传入
  6. Workbook workbook = new HSSFWorkbook(fileInputStream);
  7. // 第二步获取工作表
  8. Sheet sheet = workbook.getSheetAt(0);
  9. // Excel中一般第一行都是标题,所以我们先读取标题这行
  10. Row row1Title = sheet.getRow(0);
  11. //获取Excel表的标题
  12. if (null!=row1Title){
  13. //如果标题行不为空的话
  14. int cellCount = row1Title.getPhysicalNumberOfCells();
  15. for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
  16. Cell cell = row1Title.getCell(cellNumber);
  17. if (null!= cell){
  18. CellType cellType = cell.getCellType();
  19. String cellValue = cell.getStringCellValue();
  20. System.out.print(cellValue + " || ");
  21. }
  22. }
  23. System.out.println();
  24. }
  25. //获取表中的内容
  26. int rowCount = sheet.getPhysicalNumberOfRows();
  27. // 这里的下标要从1开始,排除标题行
  28. for (int rowNumber = 1; rowNumber < rowCount; rowNumber++) {
  29. Row rowData = sheet.getRow(rowNumber);
  30. if (null!=rowData){
  31. //读取列
  32. int cellCount = row1Title.getPhysicalNumberOfCells();
  33. for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
  34. Cell cell = rowData.getCell(cellNumber);
  35. if (null!=cell){
  36. CellType cellType = cell.getCellType();
  37. String cellValue = "";
  38. switch (cellType){
  39. // 字符串
  40. case STRING:
  41. cellValue = cell.getStringCellValue();
  42. break;
  43. // 布尔
  44. case BOOLEAN:
  45. cellValue = String.valueOf(cell.getBooleanCellValue());
  46. break;
  47. // 数字(日期、普通数字)
  48. case NUMERIC:
  49. if (DateUtil.isCellDateFormatted(cell)){ // 日期
  50. System.out.print("【日期】");
  51. Date date = cell.getDateCellValue();
  52. cellValue = new DateTime(date).toString("yyyy-MM-dd");
  53. }else {
  54. // 不是日期格式,防止数字过长!
  55. System.out.print("【转换为字符串输出】");
  56. cell.setCellType(CellType.STRING);
  57. cellValue = cell.toString();
  58. }
  59. break;
  60. // 空
  61. case BLANK:
  62. System.out.print("【BLANK】");
  63. break;
  64. case ERROR:
  65. System.out.print("【数据类型错误】");
  66. break;
  67. }
  68. System.out.println(cellValue);
  69. }
  70. }
  71. }
  72. }
  73. fileInputStream.close();
  74. }

3. 工具类 - Hutool

一般情况下我们在企业里面开发,或者自己编写代码的时候,为了方便的使用POI操作Excel或使代码的复用率更高我们一般选择封装一个工具类,这样我们就可以直接调用工具类即可。这里的话给大家演示一个工具(Hutool),这里面就有对POI的封装,我们直接使用即可。这里不提供自己封装的工具类,应为每个企业封装的工具类都有所不同。不希望大家记着一个工具类,这样只会是你的思想禁锢。 我建议大家学学这个Hutool工具类的集合,真的很好用,,反正我是特别喜欢。免去了自己百度封装的代码。推荐大家学习

3.1 使用前必看

Hutool-poi是针对Apache POI的封装,因此需要 用户自行引入POI库,Hutool默认不引入。到目前为止,Hutool-poi支持:
  • Excel文件(xls, xlsx)的读取(ExcelReader)
  • Excel文件(xls,xlsx)的写出(ExcelWriter)
分别引入 poi 和 hutool 的依赖
  • 推荐引入poi-ooxml,这个包会自动关联引入poi包,且可以很好的支持Office2007+的文档格式
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
  • 说明 hutool-4.x的poi-ooxml 版本需高于 3.17(别问我3.8版本为啥不行,因为3.17 > 3.8 ) hutool-5.x的poi-ooxml 版本需高于 4.1.2xercesImpl版本高于2.12.0

POI%26EasyExcel - 图14

3.2 Hutool 提供的类

引入后即可使用Hutool的方法操作Office文件了,Hutool提供的类有:
  • ExcelUtil Excel工具类,读取的快捷方法都被封装于此
  • ExcelReader Excel读取器,Excel读取的封装,可以直接构造后使用。
  • ExcelWriter Excel生成并写出器,Excel写出的封装(写出到流或者文件),可以直接构造后使用。

3.3 Hutool 读数据

使用Hutool进行对Excel文件读取的时候,只需要一行代码,是的,你没有听错只需要一行代码。下面我给大家演示一下
  • Excel 文件

POI%26EasyExcel - 图15

  • Java程序
@Test
void hutoolReaderTest(){
    ExcelReader reader = ExcelUtil.getReader(FileUtil.file("D:\\TyporaBlog\\POI\\poi-demo\\boot-poi\\老陈测试03版本的Excel.xls"));
    List<Map<String, Object>> mapList = reader.readAll();
    System.out.println(mapList.toString());
}
  • 效果图

POI%26EasyExcel - 图16

作者就像问一下大家,学到这里还感觉POI难吗。

3.4 Hutool 写数据

Hutool写数据的原理 Hutool将Excel写出封装为ExcelWriter,原理为包装了Workbook对象,每次调用merge(合并单元格)或者write(写出数据)方法后只是将数据写入到Workbook,并不写出文件,只有调用flush或者close方法后才会真正写出文件。 由于机制原因,在写出结束后需要关闭ExcelWriter对象,调用close方法即可关闭,此时才会释放Workbook对象资源,否则带有数据的Workbook一直会常驻内存。
@Test
void hutoolWriteTest(){
    //数据库查询
    List<User> userList = userServicer.getUserList();
    ExcelWriter writer = ExcelUtil.getWriter("D:\\TyporaBlog\\POI\\poi-demo\\boot-poi\\HutoolWriteTest.xlsx");
    //自定义标题别名
    writer.addHeaderAlias("age", "用户账户");
    writer.addHeaderAlias("score", "昵称");
    writer.addHeaderAlias("isPass", "性别");
    writer.addHeaderAlias("examDate", "简介");
    writer.addHeaderAlias("examDate", "头像url");
    writer.addHeaderAlias("examDate", "创建时间");
    writer.addHeaderAlias("examDate", "修改时间");
    writer.addHeaderAlias("examDate", "登录次数");

    // 一次性写出内容,使用默认样式,强制输出标题
    writer.write(userList, true);
    // 关闭writer,释放内存
    writer.close();
}

POI%26EasyExcel - 图17

4. EasyExcel详解

4.1 为什么要使用EasyExcel

Java中操作Excel的框架poi,jxt,但是他们都存在一个问题:非常的消耗内存。这时候阿里的大佬在POI的基础上进一步重写,封装得到了easyexcel,他最大的好处就是解决了过于消耗内存问题。也让使用者使用更加便利。

4.2 EasyExcel基本使用

4.2.1 写

@SpringBootTest
public class EasyExcelWriteTest {

    @Autowired
    private UserServicer userServicer;

    /**
    * 最简单的写
    * 1. 创建excel对应的实体对象
    * 2. 直接写即可
    */
    @Test
    public void simpleWrite() {
        String fileName = "D:\\TyporaBlog\\POI\\poi-demo\\boot-easyexcel\\"+"EasyExcelWriteTest文件"+".xlsx";
        // 这里 需要指定写用哪个class去读,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        List<User> userList = userServicer.getUserList();
        EasyExcel.write(fileName, User.class).sheet("模板").doWrite(userList);
    }
}
更多详细的内容请参考:

https://github.com/alibaba/easyexcel/blob/master/src/test/java/com/alibaba/easyexcel/test/demo/write/WriteTest.java

4.2.2 读

@SpringBootTest
    public class EasyExcelReadTest {
        /**
        * 最简单的读
        * 1. 创建excel对应的实体对象 参照{@link DemoData}
        * 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
        * 3. 直接读即可
        */
        @Test
        public void simpleRead() {
            // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
            // 写法1:
            String fileName = "D:\\TyporaBlog\\POI\\poi-demo\\boot-easyexcel\\"+"EasyExcelWriteTest文件"+".xlsx";
            // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
            EasyExcel.read(fileName, User.class, new UserListener()).sheet().doRead();

        }
    }
public class UserListener extends AnalysisEventListener<User> {

    @Override   
    public void invoke(User user, AnalysisContext analysisContext) {
        //这里仅仅打印一下数据
        System.out.println(user.toString());
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}
  • 官方说明也比较明确,使用简单fileName路径+文件名DemoData是Excel数据对应的实体类,DemoDataListener这看名字就是监听器,用来监听处理读取到的每一条数据

详细参考官网:

关于Easyexcel | Easy Excel

5. 实战练习

上面的内容讲解了一下POI在Java中怎样对Excel实现读和写的功能。下面我们要讲解一下POI在实际中是怎样开发的,这也是每个人以后进入工作要用到的一部分。其实在不管是写demo测试,还是实际开发,用到的都是上面所讲到到的知识,只是用法稍稍不同,无非实际开发中,是在页面展示。其实POI在实际开发中无非就两种情况,一中是数据的导入,另一种是数据的导出。 举个栗子: 导入:我们要做一个答题的一个业务,这时候我们需要很多题目,不可能说我们找一道题插入到数据库中,找一道题插入到数据库中。这样的办法是不可取的,这里我们一般把收集的题目放入到Excel中,批量导入即可。 导出:就是,有另外一个老师需要这些题目,这时候我们就批量导入到Excel中即可。

5.1 读取Execl数据到数据库(导入)

说明:
  • User : 表中的数据我们只做导出,应为数据量比较小
  • Products :我们做导入,因为这个表中的数据比较多,正好大数据量进行测试

5.1.1 POI 导入

@RequestMapping("/uploadFile")
@ResponseBody
public Object uploadFile(@RequestParam("fileName") MultipartFile file) {
    Map<String,Object> map = new HashMap<String,Object>();
    InputStream inputStream = null;
    try {
        inputStream = file.getInputStream();
    } catch (IOException e) {
        e.printStackTrace();
    }
    ExcelReader excelReader = ExcelUtil.getReader(inputStream);
    List<Map<String, Object>> mapList = excelReader.readAll();
    /**
         * 这里暂时先不写详细。时间不太够了,后面会补上的
         * mapList 中数据已经有了,最简单的方法直接遍历插入即可。但是实际开发中还是要判断数据是否对
         */
    return map;
};

5.1.2 EasyExcel 导入

@RequestMapping("/uploadFile")
@ResponseBody
public Object uploadFile(@RequestParam("fileName") MultipartFile file) throws IOException {
    Map<String,Object> map = new HashMap<String,Object>();
    EasyExcel.read(file.getInputStream(), Products.class, new ProductsListener(productsService)).sheet().doRead();
    return map;
};
  • 监听器
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ProductsListener extends AnalysisEventListener<Products> {
    private static final Logger LOGGER =
        LoggerFactory.getLogger(ProductsListener.class);
    /**
     * 每隔100条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    List<Products> list = new ArrayList<Products>();
    /**
     * 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private ProductsService productsService;

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

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

    /**
     * 这个每一条数据解析都会来调用
     * @param products
     */
    @Override
    public void invoke(Products products, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(products));
        list.add(products);
        // 达到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());
        productsService.insert(list);
        LOGGER.info("存储数据库成功!");
    }
}

5.2 将数据库数据写入Execl中(导出)

导出是将所有单元格对象保存到内存中,当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时,随着表格的不断创建,内存中对象越来越多,直至内存溢出。Apache Poi提供SXSSFWork对象,专门用于处理大数据量Excel报表导出。

5.2.1 导出

@Controller
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserServicer userService;

    @RequestMapping("/exportExcel")
    public void exportExcel(Model model, HttpServletResponse response) throws UnsupportedEncodingException {
        //数据库查询
        List<User> userList = userService.getUserList();
        ExcelWriter writer = ExcelUtil.getWriter(true);
        //自定义标题别名
        writer.addHeaderAlias("age", "用户账户");
        writer.addHeaderAlias("score", "昵称");
        writer.addHeaderAlias("isPass", "性别");
        writer.addHeaderAlias("examDate", "简介");
        writer.addHeaderAlias("examDate", "头像url");
        writer.addHeaderAlias("examDate", "创建时间");
        writer.addHeaderAlias("examDate", "修改时间");
        writer.addHeaderAlias("examDate", "登录次数");
        writer.write(userList, true);
        String name = URLEncoder.encode("用户数据", "utf-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename="+name+".xlsx");
        // 一次性写出内容,使用默认样式,强制输出标题

        ServletOutputStream out = null;
        try {
            out=response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {

            // 关闭writer,释放内存
            writer.close();
        }
    }
}

5.2.2 EasyExcel 导出

@Controller
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserServicer userService;

    @RequestMapping("/exportExcel")
    public void exportExcel(Model model, HttpServletResponse response) throws IOException {
        List<User> userList = userService.getUserList();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 转换为UTF-8 ,不然输出乱码
        String fileName = URLEncoder.encode("EasyExcel用户导出", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), User.class).sheet("模板").doWrite(userList);
    }
}

5.3 大数据表处理

我之前处理百万数据的时候,网上的说法无非两种
  1. 大文件读取使用SAX
  2. 大文件写入使用SXSSFWorkbook
我个人感觉非常的臃肿,直接舍弃。这里不详细讲解。要想处理大数据读写只需要使用EasyExcel即可轻松处理。没必要学那么多没用的东西。

5.4 总结

对于数据量不算太大的导入导出我可以使用原生的POI或者Hutool封装的工具类,其实理解了思想都非常简单。当遇到大数据的时候我们舍弃POI,使用阿里巴巴进行重写和封装的EasyExcel,效果会非常好。 到此我们的POI就基本讲解完毕了,但是还有很多地方不足,老陈也会努力修改打造一篇优质的教程。

6. 大数据表处理

上面已经讲解了,POI基本操作Excel、EasyExcel操作Excel以及使用Hutool工具类进行对Excel操作。接着我们学习一下数据量特别大的时候,我们应该怎么办。 Excel可以分为早期的2003+ 版本(使用POI的HSSF对象操作)和2007+版本(使用POI的XSSF操作),两者对大数据的支持如下:
  • Excel 2003:在POI中使用HSSF对象时,excel 2003最多只允许存储65536条数据,一般用来处理较少的数据量。这时对于百万级别数据,Excel肯定容纳不了。
  • Excel 2007:当POI升级到XSSF对象时,它可以直接支持excel2007以上版本,因为它采用ooxml格式。这时excel可以支持1048576条数据,单个sheet表就支持近百万条数据。但实际运行时还可能存在问题,原因是执行POI报表所产生的行对象,单元格对象,字体对象,他们都不会销毁,这就导致OOM的风险。

6.1 解决方案

POI%26EasyExcel - 图18

对于百万数据量的Excel导入导出,只讨论基于Excel2007的解决方法,因为2003+版本根本达不到百万级别。在Poi 官方提供了对操作大数据量的导入导出的工具和解决办法,操作Excel2007使用XSSF对象,可以分为三种模式:
  • 用户模式:用户模式有许多封装好的方法操作简单,但创建太多的对象,非常耗内存(之前使用的方法)
  • 事件模式:基于SAX方式解析XML,SAX全称Simple API for XML,它是一个接口,也是一个软件包。它是一种XML解析的替代方法,不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式,它逐行扫描文档,一边扫描,一边解析。
  • SXSSF对象:是用来生成海量excel数据文件,主要原理是借助临时存储空间生成excel

6.2 大数据导出&导入

基于XSSFWork导出Excel报表,是通过将所有单元格对象保存到内存中,当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时,随着表格的不断创建,内存中对象越来越多,直至内存溢出。Apache Poi提供SXSSFWork对象,专门用于处理大数据量Excel报表导出。 在实例化SXSSFWork这个对象时,可以指定在内存中所产生的POI导出相关对象的数量(默认100),一旦内存中的对象的个数达到这个指定值时,就将内存中的这些对象的内容写入到磁盘中(XML的文件格式),就可以将这些对象从内存中销毁,以后只要达到这个值,就会以类似的处理方式处理,直至Excel导出完成。
  • 用户模式:加载并读取Excel时,是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel数据量较大时,由于不同的运行环境可能会造成内存不足甚至OOM异常。
  • 事件模式:它逐行扫描文档,一边扫描一边解析。由于应用程序只是在读取数据时检查数据,因此不需要将数据存储在内存中,这对于大型文档的解析是个巨大优势。
  1. 大文件读取使用SAX
  2. 大文件写入使用SXSSFWorkbook

6.3 放弃6.1 和 6.2 臃肿的方法

6.1 和 6.2 都是网上的方法了解即可,我个人感觉非常的臃肿,直接舍弃。这里不详细讲解。要想处理大数据只需要使用EasyExcel即可轻松处理。没必要学那么多没用的东西。