Java使用POI导出Excel

- 一、前景
- 二、概念
- - 2.1. 简介
  - 2.2.Excel版本和相关对象
  - 2.3.WorkBook
  - 2.4.POI依赖
- 三、POI - 写
- - 3.1.代码示例
  - 3.2. 性能对比
  - 3.3. 测试rowAccessWindowSize
  - 3.4. 导出Excel样式设置
- 四、POI - 读
- - 4.1.代码示例
  - 4.2.读取不同的数据类型
  - 4.3.读取公式
- 五、POI - 遇到的坑
- - 5.1.为什么模板中的数据获取不到？

一、前景

在项目开发中往往需要使用到Excel的导入和导出，导入就是从Excel中导入到DB中，而导出就是从DB中查询数据然后使用POI写到Excel上。

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

废话不多说，开始撸起来！！！

二、概念

POI官网；https://poi.apache.org/

POI官网API：https://poi.apache.org/components/spreadsheet/index.html

POI的Javadocs文档：https://poi.apache.org/apidocs/index.html

百度百科介绍：https://baike.baidu.com/item/Apache%20POI/4242784?fr=aladdin

2.1. 简介

在这里插入图片描述

POI不仅仅可以操作Excel，他的定位是操作Microsoft Office读和写，Microsoft Office其中包含了很多常用的办公文件，例如：Excel、ppt、word、Visio等等…

结构：

HSSF－提供读写Microsoft Excel XLS格式档案的功能。
XSSF－提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF－提供读写Word(97-2003) 的 Java 组件，XWPF是 POI 支持 Word 2007+ 的 Java组件，提供简单文件的读写功能；
HSLF－提供读写Microsoft PowerPoint格式档案的功能。
HDGF －提供读Microsoft Visio格式档案的功能。
HPBF －提供读Microsoft Publisher格式档案的功能。
HSMF－提供读Microsoft Outlook格式档案的功能。

截止目前最新的版本是5.2.3（Sep 17, 2022），现在还在不断的完善

2.2.Excel版本和相关对象

Excel有两个版本：

2003版本和2007版本存在兼容性的问题！03最多只有65536行！07版本最多有1048576行！
2003版本的文件名后缀是.xls
2007版本的文件后缀名是.xlsx

相关对象：工作簿、工作表、行、列 对应的POI当中的对象是Workbook、Sheet、Row、Cell

在这里插入图片描述

03最多只有65536行，如下所示：

在poi当中往往会说超过65535行会报错，原因是poi当中0代表的是第一行！

在这里插入图片描述

07最多只有1048576行，如下所示：

在这里插入图片描述

2.3.WorkBook

首先我们知道POI中我们最熟悉的莫过于WorkBook这样一个接口，WorkBook代表的就是我们上面所提到的工作簿，WorkBook有如下三个实现类。明确一点，这三个都是WorkBook的实现类，所以用法上基本上是一致的！

在这里插入图片描述

HSSFWorkbook: 这个实现类是我们早期使用最多的对象，它可以操作Excel2003以前（包含2003）的所有Excel版本。在2003以前Excel的版本后缀还是.xls

缺点： 最多只能导出 65535行，也就是导出的数据函数超过这个数据就会报错;
优点： 一般不会报内存溢出。（因为数据量还不到7w所以内存一般都够用，首先你得明确知道这种方式是将数据先读取到内存中，然后再操作）

XSSFWorkbook： 这个实现类现在在很多公司都可以发现还在使用，它是操作的Excel2003–Excel2007之间的版本，Excel的扩展名是.xlsx

优点： 这种形式的出现是为了突破HSSFWorkbook的65535行局限，是为了针对Excel2007版本的1048576行，16384列，最多可以导出104w条数据；
缺点： 伴随的问题来了，虽然导出数据行数增加了好多倍，但是随之而来的内存溢出问题也成了噩梦。因为你所创建的book，Sheet，row，cell等在写入到Excel之前，都是存放在内存中的（这还没有算Excel的一些样式格式等等），可想而知，内存不溢出就有点不科学了！！！

SXSSFWorkbook : 这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx

优点： 这种方式不会一般不会出现内存溢出（它使用了硬盘来换取内存空间，也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来，而内存中存的都是最新的数据），并且支持大型Excel文件的创建（存储百万条数据绰绰有余）。
缺点：
- 既然一部分数据持久化到了硬盘中，且不能被查看和访问那么就会导致，在同一时间点我们只能访问一定数量的数据，也就是内存中存储的数据;
- sheet.clone()方法将不再支持,还是因为持久化的原因;
- 不再支持对公式的求值，还是因为持久化的原因，在硬盘中的数据没法读取到内存中进行计算；
- 在使用模板方式下载数据的时候，不能改动表头，还是因为持久化的问题，写到了硬盘里就不能改变了；

经过了解也知道了这三种Workbook的优点和缺点，那么具体使用哪种方式还是需要看情况的：

当我们经常导入导出的数据不超过7w的情况下，可以使用HSSFWorkbook或者XSSFWorkbook都行；
当数据量超过7w并且导出的Excel中不牵扯对Excel的样式，公式，格式等操作的情况下，推荐使用SXSSFWorkbook;
当数据量超过7w，并且我们需要操做Excel中的表头，样式，公式等，这时候我们可以使用XSSFWorkbook配合进行分批查询，分批写入Excel的方式来做；

2.4.POI依赖

hutool是一个工具合集，使用poi实际上只需要引入poi-ooxml就可以。因为poi-ooxml里面已经引入了poi和poi-ooxml-schemas的依赖。

<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version>
</dependency>
<dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.8</version>
</dependency>

在这里插入图片描述

三、POI - 写

workbook常用API:
createSheet()：创建Excel工作表返回类型为HSSFSheeet

setSheetName()：设置Excel工作表的名称，语法结构如下

public void setSheetName(int sheetIx,String name)

3.1.代码示例

（1）HSSFWorkbook

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;public class ExcelWriterTest03 {public static void main(String[] args) throws IOException {String path = "D:\\poi\\";// 1.创建一个工作簿。03Workbook workbook = new HSSFWorkbook();// 2.创建一个工作表Sheet sheet = workbook.createSheet("统计表");// 3.创建行。第一行Row row = sheet.createRow(0);// 4.创建列。// (1,1) 第一行第一列的单元格Cell cell = row.createCell(0);cell.setCellValue("我们都一样");// (1,2) 第一行第二列的单元格Cell cell2 = row.createCell(1);cell2.setCellValue(666);// 第二行。(1,0)Row row1 = sheet.createRow(1);//（2,1）第二行第一列的单元格Cell cell1 = row1.createCell(0);cell1.setCellValue(DateUtil.now());// 判断文件是否存在，不存在就创建if (FileUtil.isEmpty(new File(path))) {FileUtil.mkdir(path);}// 5.生成一张表。03版本的工作簿是以.xls结尾FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");// 输出workbook.write(fileOutputStream);// 6.关闭流fileOutputStream.close();System.out.println("03表生成成功！");}
}

生成的Excel如下：

在这里插入图片描述

最多65536行，而poi是以0为第一行，所以这里只能写65535，类似于数组以0代表第一个元素一样，一旦超过65535就会报以下异常：

在这里插入图片描述

注意：

假如路径下已经存在Excel文件，再次生成他会直接覆盖该文件。
使用HSSFWorkbook也可以使用xlsx结尾，正常也是可以打开的，但是超过65535同样会报错

（2）XSSFWorkbook

他是可以超过65535行的并且不会报错，并且他兼容.xls、.xlsx两种格式都是可以的。这里需要注意一下，即时是使用的.xls，只要使用的是XSSFWorkbook，超过65535行同样也不会报错！

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;public class ExcelWriterTest03 {public static void main(String[] args) throws IOException {String path = "D:\\poi\\";// 1.创建一个工作簿。03Workbook workbook = new XSSFWorkbook(); // 07和03版本只有对象不同，其他操作一样// 2.创建一个工作表Sheet sheet = workbook.createSheet("统计表");// 3.创建行。第一行Row row = sheet.createRow(0);// 4.创建列。// (1,1) 第一行第一列的单元格Cell cell = row.createCell(0);cell.setCellValue("我们都一样");// (1,2) 第一行第二列的单元格Cell cell2 = row.createCell(1);cell2.setCellValue(666);// 第65537行。(65537,0)Row row1 = sheet.createRow(65536);//（2,1）第二行第一列的单元格Cell cell1 = row1.createCell(0);cell1.setCellValue(DateUtil.now());// 判断文件是否存在，不存在就创建if (FileUtil.isEmpty(new File(path))) {FileUtil.mkdir(path);}// 5.生成一张表。03版本的工作簿是以.xls结尾FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");// 输出workbook.write(fileOutputStream);// 6.关闭流fileOutputStream.close();System.out.println("03表生成成功！");}
}

（3）SXSSFWorkbook

SXSSFWorkbook同XSSFWorkbook使用方法一样！也是不受行数限制！只不过他是硬盘换时间，在大数据量的情况下，会将内存当中的数据写到临时文件当中，这样达到释放内存，因此占用内存较小，然后速度要比XSSFWorkbook快！

使用SXSSFWorkbook wb = new SXSSFWorkbook(100)创建的工作簿在读取数据时，会根据所传入的阈值（此处是100，默认也是100）。当内存中的对象达到这个阈值时，生成一个临时文件，以临时文件进行存储，来实现分段读取与写入。举个例子：假如写入1-10行数据，然后设置的阀值是2，那么会将1-8行的数据写到硬盘，9和10行的写到内存。类似于一个队列先进先出的规则！

//当为-1的时候表示 将会把所有的行刷新到临时文件
Workbook workbook = new SXSSFWorkbook(-1);
//当为100的时候表示 将会把超过100行的数据刷新到临时文件
Workbook workbook = new SXSSFWorkbook(100);
//表示手动刷新所有数据到临时文件的方式 ，可指定参数 行数
((SXSSFSheet) sheet).flushRows()；

这里需要注意的是，当每次刷新到临时文件。内存中的数据就不存在了，因此避免了OOM。有些小伙伴可能会犯还去拿行数，或者操作行数据的问题。这些数据已经被刷新到临时文件，内存中已经不存在了。所以就拿不到了。（抛异常）

SXSSF在把内存数据刷新到硬盘时，是把每个SHEET生成一个临时文件，这个临时文件可能会很大，有可以会达到G级别，如果文件的过大对你来说是一个问题，你可以使用wb.setCompressTempFiles(true);方法让SXSSF来进行压缩，当然性能也会有一定的影响。

默认的临时文件存放目录：

windows下：AppData\Local\Temp\poifiles文件夹下，生成一个叫poi-sxssf-sheet**************的文件
Linux系统下：会在/tmp/poifiles文件下生成该临时文件

代码示例： 这里我故意设置了为5000 Workbook workbook = new SXSSFWorkbook(5000);，然后在workbook.createSheet这个地方打断点，当执行完的时候临时文件已经创建了！当执行完for循环后，临时文件已经存在内容了，执行write之后会将所有内容都写入临时文件，没有执行write之前，会将超过阀值的数据提前写入临时文件当中，关于这一点大家可以自行测试！

public class ExcelWriterTest03BigData {public static void main(String[] args) throws IOException {// 开始时间long start = System.currentTimeMillis();String path = "D:\\poi\\";// 1.创建一个工作簿。03Workbook workbook = new SXSSFWorkbook(5000);// 2.创建一个工作表Sheet sheet = workbook.createSheet("统计表");// 3.创建行。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(rowNum + "," + cellNum);}}// 5.生成一张表。03版本的工作簿是以.xlsx结尾FileOutputStream fileOutputStream = new FileOutputStream(path + "07BigDataUpGrade.xlsx");// 输出workbook.write(fileOutputStream);// 6.关闭流fileOutputStream.close();// 7.清除临时文件((SXSSFWorkbook) workbook).dispose();System.out.println("07大数据量表优化后生成成功！");// 结束时间long end = System.currentTimeMillis();System.out.println("用时：" + ((end - start) / 1000) + "秒");}
}

通过以下会发现，他是写到了xml当中。然后又通过读取xml当中的内容转换到我们设置的Excel文件当中。写到Excel是个耗时的操作，于是先写到硬盘将内存释放，然后这样就是所谓的硬盘换内存。

在这里插入图片描述

这是执行完write方法之后文件的大小：

在这里插入图片描述

通过以下配置就可以实现临时文件的自定义配置。再有就是记住临时文件的清理。自带api就有实现
((SXSSFWorkbook) workbook).dispose(); 清理临时缓存文件。因为我用的是父类所以强转了。

@Component
public class ExcelConfig {private final static Logger logger = LoggerFactory.getLogger(ExcelConfig.class);@Value("${application.tmp.path}")private String applicationTmpPath;/*** 设置使用SXSSFWorkbook对象导出excel报表时，TempFile使用的临时目录，代替{java.io.tmpdir}*/@PostConstructpublic void setExcelSXSSFWorkbookTmpPath() {String excelSXSSFWorkbookTmpPath = applicationTmpPath + "/poifiles";File dir = new File(excelSXSSFWorkbookTmpPath);if (!dir.exists()) {dir.mkdirs();}TempFile.setTempFileCreationStrategy(new TempFile.DefaultTempFileCreationStrategy(dir));logger.info("setExcelSXSSFWorkbookTmpPath={}", excelSXSSFWorkbookTmpPath);}}

3.2. 性能对比

（1）HSSFWorkbook

优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快。
缺点：最多只能处理65536行，否则会抛出异常。

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 java.io.FileOutputStream;
import java.io.IOException;public class ExcelWriterTest03BigData {public static void main(String[] args) throws IOException {// 开始时间long start = System.currentTimeMillis();String path = "D:\\poi\\";// 1.创建一个工作簿。03Workbook workbook = new HSSFWorkbook();// 2.创建一个工作表Sheet sheet = workbook.createSheet("统计表");// 3.创建行。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);}}// 5.生成一张表。03版本的工作簿是以.xls结尾FileOutputStream fileOutputStream = new FileOutputStream(path + "03BigData.xls");// 输出workbook.write(fileOutputStream);// 6.关闭流fileOutputStream.close();System.out.println("03大数据量表生成成功！");// 结束时间long end = System.currentTimeMillis();System.out.println("用时："+((end-start)/1000)+"秒");}
}

在这里插入图片描述
（2）XSSFWorkbook

直接使用以上示例来测试即可，然后将Workbook 换成XSSFWorkbook

缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条。
优点：可以写较大的数据量，如20万条。

在这里插入图片描述
（3）SXSSFWorkbook

在这里插入图片描述

注意：

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

3.3. 测试rowAccessWindowSize

对于不一样的rowAccessWindowSize值，进行耗时测试。

例子：生成三个SHEET，每一个SHEET有 200000 行记录，共60万行记录flex

rowAccessWindowSize：1的时候执行是30s
rowAccessWindowSize：100的时候执行是34s
rowAccessWindowSize：200的时候执行是51s
rowAccessWindowSize：5000的时候执行是326s

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.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;public class ClassSXSSFWorkBookUtil {public static void main(String[] args) throws IOException {long curr_time = System.currentTimeMillis();// 内存中缓存记录行数int rowAccess = 100;SXSSFWorkbook wb = new SXSSFWorkbook(rowAccess);// 生成3个SHEETint sheetNum = 3;for (int i = 0; i < sheetNum; i++) {Sheet sh = wb.createSheet();// 每一个SHEET有 200000 ROWfor (int rowNum = 0; rowNum < 200000; rowNum++) {Row row = sh.createRow(rowNum);//每行有10个CELLfor (int cellnum = 0; cellnum < 10; cellnum++) {Cell cell = row.createCell(cellnum);String address = new CellReference(cell).formatAsString();cell.setCellValue(address);}// 每当行数达到设置的值就刷新数据到硬盘,以清理内存，这块本质上其实不加这个poi在达到阀值也会向临时文件写数据，// 假如导出60w数据3个sheet，加上手动刷新是34s，然后不加是40s，所以在一定程度上来讲手动刷新要快一点if (rowNum % rowAccess == 0) {((SXSSFSheet) sh).flushRows();}}}FileOutputStream os = new FileOutputStream("D:\\poi\\biggrid.xlsx");wb.write(os);os.close();System.out.println("耗时(秒):" + (System.currentTimeMillis() - curr_time) / 1000);}
}

这个测试出来的结果跟电脑配置有很大关系，实际开发当中，可以采取这种方式然后看看设置多少比较快，然后进行优化！

3.4. 导出Excel样式设置

在这里插入图片描述

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;import java.io.FileOutputStream;
import java.io.IOException;public class Test {public static void main(String[] args) throws IOException {//创建HSSFWorkbook对象HSSFWorkbook wb = new HSSFWorkbook();//建立sheet对象HSSFSheet sheet = wb.createSheet("成绩表");// 设置列宽sheet.setColumnWidth(0, 25 * 256);sheet.setColumnWidth(1, 25 * 256);sheet.setColumnWidth(2, 25 * 256);sheet.setColumnWidth(3, 25 * 256);sheet.setColumnWidth(4, 25 * 256);// 记住一点设置单元格样式相关的都是CellStyle来控制的,设置完之后只需set给单元格即可：cell.setCellStyle(cellStyle);// 合并单元格后居中CellStyle cellStyle = wb.createCellStyle();// 垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setAlignment(HorizontalAlignment.CENTER);// 设置字体Font font = wb.createFont();font.setFontName("宋体");font.setFontHeightInPoints((short) 16);font.setItalic(false);font.setStrikeout(false);cellStyle.setFont(font);// 设置背景色cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 设置边框（一般标题不设置边框，是标题下的所有表格设置边框）cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框//在sheet里创建第一行，参数为行索引HSSFRow row1 = sheet.createRow(0);// 合并单元格：参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));// 创建单元格HSSFCell cell = row1.createCell(0);cell.setCellStyle(cellStyle);//设置单元格内容cell.setCellValue("学生成绩表");//在sheet里创建第二行HSSFRow row2 = sheet.createRow(1);//创建单元格并设置单元格内容row2.createCell(0).setCellValue("姓名");row2.createCell(1).setCellValue("班级");row2.createCell(2).setCellValue("语文成绩");row2.createCell(3).setCellValue("数学成绩");row2.createCell(4).setCellValue("英语成绩");//在sheet里创建第三行HSSFRow row3 = sheet.createRow(2);row3.createCell(0).setCellValue("小明");row3.createCell(1).setCellValue("1班");row3.createCell(2).setCellValue(80);row3.createCell(3).setCellValue(75);row3.createCell(4).setCellValue(88);//在sheet里创建第四行HSSFRow row4 = sheet.createRow(3);row4.createCell(0).setCellValue("小红");row4.createCell(1).setCellValue("1班");row4.createCell(2).setCellValue(82);row4.createCell(3).setCellValue(70);row4.createCell(4).setCellValue(90);FileOutputStream fileOutputStream = new FileOutputStream("D:\\poi\\04.xlsx");wb.write(fileOutputStream);fileOutputStream.close();}
}

四、POI - 读

当你企图使用SXSSFWorkbook去加载一个已存在的Excel模板时，首先你应该用XSSFWorkbook去获取它，以下列举了常用的四种获取XSSFWorkbook的方式。

XSSFWorkbook(java.io.File file)
XSSFWorkbook(java.io.InputStream is)
XSSFWorkbook(OPCPackage pkg)
XSSFWorkbook(java.lang.String path)

4.1.代码示例

使用SXSSFWorkbook写的文档，必须使用SXSSFWorkbook来读，否则报错！同样HSSFWorkbook写入也必须用HSSFWorkbook读取！当然SXSSFWorkbook是不能用来读取的！

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 java.io.FileInputStream;
import java.io.IOException;public class ExcelReadTest03 {public static void main(String[] args) throws IOException {String path = "D:\\poi\\";FileInputStream fileInputStream = new FileInputStream(path + "03.xlsx");// 1.创建一个工作簿。使得excel能操作的，这边他也能操作。
//        Workbook workbook = new HSSFWorkbook(fileInputStream);Workbook workbook = new XSSFWorkbook(fileInputStream);// 2.得到表。Sheet sheet = workbook.getSheetAt(0);// 3.得到行。Row row = sheet.getRow(0);// 4.得到列。Cell cell = row.getCell(0);// 读取值。一定要注意类型，否则会读取失败System.out.println(cell.getStringCellValue());// 字符串类型Cell cell1 = row.getCell(1);System.out.println(cell1.getNumericCellValue());// 数字类型// 5.关闭流。fileInputStream.close();}
}

4.2.读取不同的数据类型

这里重点会用到CellType枚举类，就是获取当前单元格的类型，CellType cellType = cell.getCellType();，旧版本poi直接获取的是int值，int cellType = cell.getCellType();，这块还是有一定的区别的，但是枚举都是用的这个类。

_NONE(-1), // none类型
NUMERIC(0), // 数值类型
STRING(1), // 字符串类型
FORMULA(2), // 公式类型
BLANK(3), // 空格类型
BOOLEAN(4), // 布尔类型
ERROR(5); // 错误

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;public class ExcelReadTestType {public static void main(String[] args) throws IOException {String path = "D:\\poi\\";// 1.获取文件流FileInputStream fileInputStream = new FileInputStream(path + "会员消费商品明细表.xls");// 2.创建一个工作簿。使用excel能操作的这边他也可以操作。Workbook workbook = new HSSFWorkbook(fileInputStream);
//        Workbook workbook = new XSSFWorkbook(fileInputStream);// 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) {System.out.print(cell.getStringCellValue() + "|");}}System.out.println();}// 5.获取表中的记录// 获取有多少行记录int rowCount = sheet.getPhysicalNumberOfRows();for (int rowNum = 1; rowNum < rowCount; rowNum++) {// 获取每一行记录Row rowData = sheet.getRow(rowNum);if (rowData != null) {// 读取列int cellCount = rowTitle.getPhysicalNumberOfCells();for (int cellNum = 0; cellNum < cellCount; cellNum++) {// 获得单元格Cell cell = rowData.getCell(cellNum);// 匹配列的数据类型String cellValueByCell = getCellValueByCell(cell);System.out.println(cellValueByCell);}}System.out.println("----");}fileInputStream.close();}//获取单元格各类型值，返回字符串类型public static String getCellValueByCell(Cell cell) {//判断是否为null或空串if (cell == null || cell.toString().trim().equals("")) {return "";}String cellValue = "";CellType cellType = cell.getCellType();switch (cellType) {// 数字case NUMERIC:short format = cell.getCellStyle().getDataFormat();if (DateUtil.isCellDateFormatted(cell)) {SimpleDateFormat sdf = null;//System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());if (format == 20 || format == 32) {sdf = new SimpleDateFormat("HH:mm");} else if (format == 14 || format == 31 || format == 57 || format == 58) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)sdf = new SimpleDateFormat("yyyy-MM-dd");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);cellValue = sdf.format(date);} else {// 日期sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");}try {// 日期cellValue = sdf.format(cell.getDateCellValue());} catch (Exception e) {try {throw new Exception("exception on get date data !".concat(e.toString()));} catch (Exception e1) {e1.printStackTrace();}} finally {sdf = null;}} else {BigDecimal bd = new BigDecimal(cell.getNumericCellValue());// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值cellValue = bd.toPlainString();}break;// 字符串case STRING:cellValue = cell.getStringCellValue();break;// Booleancase BOOLEAN:cellValue = cell.getBooleanCellValue() + "";break;// 公式case FORMULA:cellValue = cell.getCellFormula();break;// 空值case BLANK:cellValue = "";break;// 故障case ERROR:cellValue = "ERROR VALUE";break;default:cellValue = "UNKNOW VALUE";break;}return cellValue;}
}

getPhysicalNumberOfRows()获取的是物理行数，也就是不包括空行（隔行）的情况。
getLastRowNum()获取的是最后一行的编号（编号从0开始）

注意：日常中我们进行POI读取导入EXCEL表格操作时，一定要保证工作薄干净，即有效数据区域外的单元格千万不要动。不然可能会出现，明明Excel有两条数据，但是读出来好多空格内容，往往就是我们不小心动了别的单元格导致，然后肉眼还看不出来，但是getPhysicalNumberOfRows获取行数就会有好几行空格内容！

4.3.读取公式

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;import java.io.FileInputStream;
import java.io.IOException;public class GS {public static void main(String[] args) throws IOException {String path = "D:\\poi\\";FileInputStream fileInputStream = new FileInputStream(path + "计算公式.xls");// 1.创建一个工作簿。使得excel能操作的，这边他也能操作。Workbook workbook = new HSSFWorkbook(fileInputStream);// 2.得到表。Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(4);Cell cell = row.getCell(0);// 拿到计算公式FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);// 输出单元格内容CellType cellType = cell.getCellType();switch (cellType) {case FORMULA:String cellFormula = cell.getCellFormula();System.out.println(cellFormula);// 计算CellValue evaluate = formulaEvaluator.evaluate(cell);String cellValue = evaluate.formatAsString();System.out.println(cellValue);break;}}
}