Java使用POI导出Excel

灰太狼 2024-04-03 10:50 16阅读 0赞

#### 目录 ####

*   *  一、前景
     *  二、概念
     *   *  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/][https_poi.apache.org]

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

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

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

#### 2.1. 简介 ####

![在这里插入图片描述][a6bad740cabc4af19cb7b80398216fc6.png]

> 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`

![在这里插入图片描述][221989b49cca4d7483ca245f670d8a07.png]

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

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

![在这里插入图片描述][e42d7256332d4942a83e31966759befd.png]

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

![在这里插入图片描述][35cb476119ff4406beb677bc3a86892d.png]

#### 2.3.WorkBook ####

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

![在这里插入图片描述][cd7da251114347cca452074538cac256.png]

**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>

![在这里插入图片描述][17bb820a35254f4e9370e114b7e28385.png]

### 三、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.创建一个工作簿。03
            Workbook 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如下：

![在这里插入图片描述][b5a43e3644d84160b473821e99066725.png]

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

![在这里插入图片描述][01ea3cf395d3416192cbb111a9a15bab.png]

**注意：**

1.  假如路径下已经存在Excel文件，再次生成他会直接覆盖该文件。
2.  使用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.创建一个工作簿。03
            Workbook 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.创建一个工作簿。03
            Workbook 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是个耗时的操作，于是先写到硬盘将内存释放，然后这样就是所谓的硬盘换内存。

![在这里插入图片描述][2aa307f5225c4f1792508f8cb8fdaa1d.png]

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

![在这里插入图片描述][4c1b17d5ead74a7d9941501b52accda1.png]

通过以下配置就可以实现临时文件的自定义配置。再有就是记住临时文件的清理。自带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}
    	 */
    	@PostConstruct
    	public 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.创建一个工作簿。03
            Workbook 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)+"秒");
        }
    }

![在这里插入图片描述][f67036ed1ec346b58ee7cdf161b79b42.png]  
**（2）XSSFWorkbook**

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

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

![在这里插入图片描述][c38042832849417da9faf56c709db826.png]  
**（3）SXSSFWorkbook**

![在这里插入图片描述][55b060f361874f7b8b3d999f1ed1af77.png]

**注意：**

*  过程中产生临时文件，需要清理临时文件。
 *  默认由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个SHEET
            int sheetNum = 3;
    
            for (int i = 0; i < sheetNum; i++) {
        
                Sheet sh = wb.createSheet();
                // 每一个SHEET有 200000 ROW
                for (int rowNum = 0; rowNum < 200000; rowNum++) {
        
                    Row row = sh.createRow(rowNum);
                    //每行有10个CELL
                    for (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样式设置 ####

![在这里插入图片描述][8b996af5fc554767bca87605c8c12bfe.png]

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;
                // Boolean
                case 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;
            }
        }
    }

![在这里插入图片描述][e3127bc35da5447490703c5910bcc88b.png]  
![在这里插入图片描述][b63ecad0024440199a701e0d8abce92d.png]

### 五、POI - 遇到的坑 ###

#### 5.1.为什么模板中的数据获取不到？ ####

根据我对`SXSSFWorkbook`的了解，它只会加载一部分数据到内存，其余的数据全部持久化到本次磁盘。

但是当你噼里啪啦对`SXSSFWorkbook`进行了一顿操作时，你会忽然发现为什么`SXSSFSheet.getRow(0) = null???`

这是因为这些记录存在于硬盘当中！

[https_poi.apache.org]: https://poi.apache.org/
[https_poi.apache.org_components_spreadsheet_index.html]: https://poi.apache.org/components/spreadsheet/index.html
[https_poi.apache.org_apidocs_index.html]: https://poi.apache.org/apidocs/index.html
[https_baike.baidu.com_item_Apache_20POI_4242784_fr_aladdin]: https://baike.baidu.com/item/Apache%20POI/4242784?fr=aladdin
[a6bad740cabc4af19cb7b80398216fc6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/1e975cdeec68425aba59ed264a9d1bd7.png
[221989b49cca4d7483ca245f670d8a07.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/653e4dac36a14517b62c134590a18a39.png
[e42d7256332d4942a83e31966759befd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/e81ba905ba484dd890a0f143706fe944.png
[35cb476119ff4406beb677bc3a86892d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/ae77fc3ef4fc48019442e2cce44f5520.png
[cd7da251114347cca452074538cac256.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/f82704db52d449668d38c6cbe51b0e6b.png
[17bb820a35254f4e9370e114b7e28385.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/ecdbf3e1f2914850a61e28d6ae3cf00d.png
[b5a43e3644d84160b473821e99066725.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/fe35f16faa584ea9871701aac50f5e5c.png
[01ea3cf395d3416192cbb111a9a15bab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/48e1c60b5d944f06bcd257c2ee0e28e4.png
[2aa307f5225c4f1792508f8cb8fdaa1d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/748eb4d3532341eb8eafc2416ecb6a28.png
[4c1b17d5ead74a7d9941501b52accda1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/0f623444326845e0afe6496a222f384e.png
[f67036ed1ec346b58ee7cdf161b79b42.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/0e4cb12dec4e4506a19ee0e10b3a0098.png
[c38042832849417da9faf56c709db826.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/3a5fca2c53b4478290b860ab300cce56.png
[55b060f361874f7b8b3d999f1ed1af77.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/ae6757cb0b80497aa42fa63aa3ad97a4.png
[8b996af5fc554767bca87605c8c12bfe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/0a89dc8e8b2c4619b9559d4e0d03286c.png
[e3127bc35da5447490703c5910bcc88b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/39cade1d90ec40c49d246c0ae5578d63.png
[b63ecad0024440199a701e0d8abce92d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/b6ed0e84046b4243b40701f8008dc13b.png