springboot功能模块之POI操作Excel

一、前言

文件的导入导出:

这个功能主要就是帮助我们的用户能够快速的将数据导入到数据库中,不用在自己手动的一条一条的将数据新增到我们的数据库中.同时又能够方便我们能够将数据导出之后打印出来给领导们查看.不用非得带着电脑这里那里的跑.非常实用的功能.

文件的导入导出功能目前主要是两家独大,一个就是Apache的POI,另一家就是阿里的EasyExcel.这里两种技术我都会在下面的文章里面详细讲解.

二、POI

2.1概述

官网：Apache POI™ - the Java API for Microsoft Documents

Apache POI项目的任务是创建和维护Java API，以基于Office Open
XML标准（OOXML）和Microsoft的OLE 2复合文档格式（OLE2）来处理各种文件格式。简而言之，您可以使用Java读写MS
Excel文件。此外，您可以使用Java读写MS Word和MS PowerPoint文件。Apache POI是您的Java Excel解决方案（适用于Excel 97-2008）。

 基本功能

• HSSF － 提供读写Microsoft Excel格式档案的功能。
• XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
• HWPF － 提供读写Microsoft Word格式档案的功能。
• HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
• HDGF － 提供读写Microsoft Visio格式档案的功能。

2.2 03版Excel与07版Excel区别

在使用POI之前,我们需要先了解一下Excel的版本更替,这样能够方便我们更好的了解POI的使用.

这里面Excel主要就是有两类,分别是Excel03版本和Excel07版本

这两个版本之间主要有以下的差别:

• 两者数据量都是有限制的

  03版本行数最多只能到65536,列数最多只能到256

  03版本行数最多只能到1048576 ,列数最多只能到16384

• 两者的文件名后缀也不一样,03版本的后缀是xls,07版本的后缀是xlsx,既然两者的后缀不一样就说明操作两者的工具类肯定也就是不一样的,这一点我们会在下面的代码中着重体现,其次就是 .xlsx文件比.xls的压缩率高，也就是相同数据量下，.xlsx的文件会小很多。

2.3POI数据写入操作

基础概念

​

几种对象:工作簿,工作表,行,单元格

依赖配置

​
<!--        xls03版本--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency>
<!--        xlsx07版本--><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>​

03版本-HSSFWorkbook

  private static final String PATH = System.getProperty("user.dir") + java.io.File.separator + "files";@Testpublic void  testExcel03() throws Exception{//创建一个工作簿Workbook workbook=new HSSFWorkbook();//创建一张工作表Sheet sheet=workbook.createSheet("我是一个新表格");//创建一行即(1,1)的单元格Row row1=sheet.createRow(0);Cell cell11=row1.createCell(0);//往该单元格中填充数据cell11.setCellValue("姓名");//创建(1,2)单元格Cell cell12=row1.createCell(1);cell12.setCellValue("印某人");Row row2=sheet.createRow(1);Cell cell21=row2.createCell(0);cell21.setCellValue("注册日期");Cell cell22=row2.createCell(1);String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell22.setCellValue(time);FileOutputStream fileOutputStream=new FileOutputStream(PATH+ java.io.File.separator+"登记表03.xls");workbook.write(fileOutputStream);fileOutputStream.close();System.out.println("文件生成成功");}

07版本-XSSFWorkbook

@Testpublic void testExcel07()throws Exception{Workbook workbook=new XSSFWorkbook();Sheet sheet=workbook.createSheet("我是一个新表格");Row row1=sheet.createRow(0);Cell cell11=row1.createCell(0);cell11.setCellValue("姓名");Cell cell12=row1.createCell(1);cell12.setCellValue("印某人");Row row2=sheet.createRow(1);Cell cell21=row2.createCell(0);cell21.setCellValue("注册日期");Cell cell22=row2.createCell(1);String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell22.setCellValue(time);FileOutputStream fileOutputStream=new FileOutputStream(PATH+ java.io.File.separator+"登记表07.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();System.out.println("文件生成成功");}

大数据量下写入速度对比

我们了解了基本的写入数据的流程之后,接下来我们测试一下,在大数据量的情况下,他们生成相应的文件需要多长的时间,看看他们两者的性能又是如何的.顺便我们也了解一下他们写入数据的整个流程.

• 03版本-HSSFWorkbook:

@Testpublic void test03BigData()throws Exception{Long begin=System.currentTimeMillis();Workbook workbook=new HSSFWorkbook();Sheet sheet=workbook.createSheet();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);}}FileOutputStream fileOutputStream=new FileOutputStream(PATH+ java.io.File.separator+"test03BigData.xls");workbook.write(fileOutputStream);System.out.println("文件生成完毕");Long end=System.currentTimeMillis();System.out.println("共用时:"+(double)(end-begin)/1000+"秒");}

这里我们运行完成之后可以看到一共运行了1.537秒,还是很快的

但是就上我们上面所说的一样,03版本的只支持最多65536条数据,如果超过这个数据量的话,是会报这个错的: Invalid row number (65536) outside allowable range (0..65535) ,这里我们运行测试一下看一下报错:

看完他的运行速度之后我们来看看,为什么HSSFWorkbook能够这么快就能将数据写入到文件中呢.

因为HSSFworkbook是直接将整个文件写入到内存中的,文件直接就能从内存中读到,所以使得整个写入的过程十分的快速.既然选择写入内存里面,那么就会出现一个问题那就是内存不够,直接就爆了,严重影响性能,所以可能是出于这个问题的考虑,03版本才会限制数据的条数

• 07版本-XSSFWorkbook:

 @Testpublic void test07BigData()throws Exception{Long begin=System.currentTimeMillis();Workbook workbook=new XSSFWorkbook();Sheet sheet=workbook.createSheet();for(int rownum=0;rownum<65537;rownum++){Row row=sheet.createRow(rownum);for(int cellnum=0;cellnum<10;cellnum++){Cell cell=row.createCell(cellnum);cell.setCellValue(cellnum);}}FileOutputStream fileOutputStream=new FileOutputStream(PATH+ java.io.File.separator+"test07BigData.xlsx");workbook.write(fileOutputStream);System.out.println("文件生成完毕");Long end=System.currentTimeMillis();System.out.println("共用时:"+(double)(end-begin)/1000+"秒");}

这里我们再来看一下XSSFWorkbook写入数据的速度:

同样的数据量,用时5.82秒

我们再来看一下如果是10万条数据的话,看看时间会是多少:

用时10.013秒,时间还能接受,毕竟在10万条数据的情况下

既然这样我们也来分析一下XSSFWorkbook写入数据的流程,这里XSSFWorkbook和HSSFWorkbook一样,也是直接将数据写入内存中的,但是我们要知道因为XSSFWorkbook支持的数据量更多了,所以就必定会出现OOM即内存爆掉的情况,所以怎么办呢,这里我猜想的是,他是按照一定的量来将数据写入内存之中,就好比我是每10000条写入内存一次,那样的话,既能较快的写入数据,同时又能够支持比较大的数据量----这里也是我自己的猜想,感觉应该是这样.

大致可以通过下面的图来模拟:

• 07版本进阶-SXSSFWorkbook

 @Testpublic void test07BigDataS()throws Exception{Long begin=System.currentTimeMillis();Workbook workbook=new SXSSFWorkbook();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);}}FileOutputStream fileOutputStream=new FileOutputStream(PATH+ java.io.File.separator+"test07BigDataS.xlsx");workbook.write(fileOutputStream);System.out.println("文件生成完毕");Long end=System.currentTimeMillis();System.out.println("共用时:"+(double)(end-begin)/1000+"秒");}

大家看名字就知道这个SXSSFWorkbook其实就是XSSFWorkbook的加强版(Super XSSFWorkbook),他的优点比较明显,既能够支持写入大量的数据,同时写入数据的速度也是非常的快.

这里我们上来就直接测试10万条数据玩玩:

这速度跟闹着玩一样,10万条数据只要1.839秒,属实是牛逼

既然这样我们就更加要深挖一下,这玩意儿为啥这么快呢?

按照网上的说法,其实XSSFWorkbook写入数据的思路和XSSFWorkbook写入数据的思路差不多的,上面我们说过了XSSFWorkbook写入数据是每隔一个数据量进行输入,在已经向内存写入10000条数据后,程序就在进行等待,

等待着10000条数据写入文件之后,他才继续向内存里面写入数据.

SXSSFWorkbook的思路是这样,他一开始也是向内存里面写入数据,但是他有一个临界值默认是100.超过这个数据量之后的数据,他会自动在磁盘上创建一个临时文件,将数据写入该文件中,之后当内存中的数据写完之后就直接从临时文件中将数据拷贝过来,这样就大大的节省了时间,可以看到程序执行过程是没有断开的,是一直在执行的,意味着最耗时的部分一直在工作.所以才会使得SXSSFWorkbook既能写入大量的数据,同时又能够在非常快的时间内完成.

大致可以通过下面的图来模拟:

其次就是SXSSFWorkbook与XSSFWorkbook有本质上的区别,这个我们可以通过他们引入的包名看出来:

可以看到SXSSFWorkbook本质上是通过流来实现的,XSSFWorkbook则还是通过usermodel来实现的.显然流肯定是更快一点的.

2.4POI数据读取操作

03版本-HSSFWorkbook:

  @Testpublic void test03Read()throws Exception{//引入输入文件流FileInputStream fileInputStream=new FileInputStream(PATH + java.io.File.separator+"test03BigData.xls");//创建工作簿Workbook workbook=new HSSFWorkbook(fileInputStream);//通过索引创建工作表Sheet sheet=workbook.getSheetAt(0);//通过索引获取行Row row=sheet.getRow(0);//通过索引获取单元格Cell cell=row.getCell(0);//打印单元格内容System.out.println(cell.getNumericCellValue());}

这是最简单的读写操作流程.并且其中的工作表,行,单元格都是通过索引来获取,除了索引,POI还为我们提供了其他的获取方法,下面我们来详细说明一下.

获取工作表:

第一种就是直接通过工作表的表名来进行获取,第二种就是直接通过工作簿内工作表的索引来进行获取.

获取行就是只能通过索引来获取

剩下的就是获取单元格了:

第一种也是直接通过索引来进行获取,第二种只不是多加了一层的判断语句,这个我们可以点进源码里面看一下:

主要有这三个

• RETURN_NULL_AND_BLANK

  英文解释： Missing cells are returned as null, Blank cells are returned as normal

  缺失的单元格会返回为空，空的单元格就正常返回即可。

• RETURN_BLANK_AS_NULL

  英文解释： Missing cells are returned as null, as are blank cells

​ 缺失的单元格返回为空，空的单元格也是如此。

• RETURN_BLANK_AS_NULL

  英文解释： A new, blank cell is created for missing cells. Blank cells are returned as normal

  缺失的单元格不仅返回为空，同时还将为这个缺失的单元格创建一个新的单元格。空的单元格就正常返回即可。

其实这三种概念的理念差不多，基本上主要都是用来处理如果出现缺失的单元格情况时，可能会影响后续数据的读写操作。

07版本-XSSFWorkbook

// 定义一个名为test07Read的方法，用于读取Excel文件中的数据
public void test07Read() throws Exception {// 创建一个FileInputStream对象，用于读取指定路径下的Excel文件FileInputStream fileInputStream = new FileInputStream(PATH + java.io.File.separator + "test07BigData.xlsx");// 使用XSSFWorkbook类创建一个工作簿对象，用于操作Excel文件Workbook workbook = new XSSFWorkbook(fileInputStream);// 获取工作簿中的第一个工作表Sheet sheet = workbook.getSheetAt(0);// 获取工作表中的第一行数据Row row = sheet.getRow(0);// 创建一个Cell数组，用于存储单元格数据Cell cell[] = new Cell[4];// 获取第一行的前四个单元格数据cell[0] = row.getCell(0);cell[1] = row.getCell(1, Row.RETURN_NULL_AND_BLANK);cell[2] = row.getCell(2);cell[3] = row.getCell(3);// 遍历单元格数组，打印每个单元格的内容for (int i = 0; i < 4; i++) {System.out.println(cell[i]);}
}

可以看到我们只是简单的修改了一下对象,其他的操作我们都是没有改的,所以我们在编写的时候,只需要注意我们版本对应的对象就行了.

POI读取不同数据类型的数据

表格数据:

这里我们已经将我们平常能够遇到的数据类型全部都包含到了.

接下来我们通过这段代码进行测试:

  @Testpublic void testMultipleTypeRead() throws Exception {// 创建一个文件输入流，用于读取指定路径的Excel文件FileInputStream fileInputStream = new FileInputStream(PATH + "test.xls");// 使用HSSFWorkbook类创建一个工作簿对象，用于操作Excel文件Workbook workbook = new HSSFWorkbook(fileInputStream);// 获取第一个工作表Sheet sheet = workbook.getSheetAt(0);// 获取表格的标题行Row rowTitle = sheet.getRow(0);// 获取标题行的列数int cellNum = rowTitle.getLastCellNum();// 遍历标题行的所有单元格if (rowTitle != null) {for (int i = 0; i < cellNum; i++) {// 获取当前单元格Cell cell = rowTitle.getCell(i);// 获取单元格的类型int cellType = cell.getCellType();// 如果单元格不为空，则输出单元格的值和类型if (cell != null) {System.out.print(cell + "-" + cellType + " | ");}}}System.out.println();// 获取表格的数据部分的行数int RowNum = sheet.getLastRowNum();// 遍历数据部分的每一行for (int i = 1; i <= RowNum; i++) {// 获取当前行Row rowData = sheet.getRow(i);// 如果当前行不为空，则遍历该行的所有单元格if (rowData != null) {int cellnum = rowData.getLastCellNum();for (int j = 0; j < cellnum; j++) {// 获取当前单元格Cell cell = rowData.getCell(j);// 获取单元格的类型int cellType = cell.getCellType();// 如果单元格不为空，则根据单元格类型进行相应的数据输出if (cell != null) {switch (cellType) {// 数字类型数据case HSSFCell.CELL_TYPE_NUMERIC:System.out.print(cell.getNumericCellValue() + "-" + cellType + " | ");continue;// 字符串类型数据case HSSFCell.CELL_TYPE_STRING:System.out.print(cell.getStringCellValue() + "-" + cellType + " | ");continue;// 公式类型case HSSFCell.CELL_TYPE_FORMULA:System.out.print("null" + "-" + cellType + " | ");continue;// 空单元格case HSSFCell.CELL_TYPE_BLANK:System.out.print(cell.getStringCellValue() + "-" + cellType + " | ");continue;// 布尔值类型case HSSFCell.CELL_TYPE_BOOLEAN:System.out.print(cell.getBooleanCellValue() + "-" + cellType + " | ");continue;// 错误单元格case HSSFCell.CELL_TYPE_ERROR:System.out.print(cell.getErrorCellValue() + "-" + cellType + " | ");continue;}}}System.out.println();}}}

这里我们可以看到能够输出下面的结果:

其中上面的单元格类型变量,我们既可以通过直接的0,1,2....来定义,同时也能够直接通过HSSFCell的变量值来直接定义.

这里为了方便大家更好的理解,我们点进源码查看一下:

2.5计算公式

这里我们在之前的test.xls文件里面为一个单元格增加了一个公式:

接下来我们通过下面的代码将公式以及公式计算的结果读取出来:

 @Testpublic void testFORMULA() throws Exception {// 创建一个文件输入流，用于读取指定路径的Excel文件FileInputStream fileInputStream = new FileInputStream(PATH +java.io.File.separator+ "test.xls");// 使用HSSFWorkbook类创建一个工作簿对象，用于操作Excel文件Workbook workbook = new HSSFWorkbook(fileInputStream);// 获取第一个工作表Sheet sheet = workbook.getSheetAt(0);// 获取第四行（索引为3）Row row = sheet.getRow(3);// 获取第八列（索引为7）的单元格Cell cell = row.getCell(7);// 创建一个公式求值器，用于计算单元格中的公式FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);// 获取单元格的类型int cellType = cell.getCellType();// 根据单元格类型进行判断switch (cellType) {// 如果单元格类型是公式类型case HSSFCell.CELL_TYPE_FORMULA:// 获取单元格中的公式内容String formula = cell.getCellFormula();System.out.println(formula);// 使用公式求值器计算单元格的值CellValue evaluate = formulaEvaluator.evaluate(cell);// 将计算结果转换为字符串并输出String cellValue = evaluate.formatAsString();System.out.println(cellValue);break;}}