POI 操作 Excel

一时失言乱红尘 2023-03-04 09:26 75阅读 0赞

### 文章目录 ###

*  Apache POI
 *   *  基本功能
     *  POI 写
     *   *  引入依赖
         *  代码示例
     *  大文件写 HSSF
     *   *  代码示例（xls）
     *  大文件写 XSSF
     *   *  代码示例（xlsx）
     *  大文件写 SXSSF
     *  POI 读
     *   *  HSSF
         *  XSSF
         *  读取不同类型的数据

为什么会需要用到 POI 和 EasyExcel 呢？

*  将用户信息导出为 Excel 表格（导出数据）
 *  将 Excel 表中的信息录入到网站数据库（习题上传）大大减少网站录入量

开发中经常会涉及到 Excel 的处理，如导出 Excel，导入 Excel 到数据库中

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

# Apache POI #

> Apache POI 是 Apache 软件基金会的开放源码函式库，POI 提供 API 给 Java 程序对 Microsoft Office 格式档案读和写的功能（来源百度百科）

Apache POI 官网地址 [http://poi.apache.org/][http_poi.apache.org]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70]

## 基本功能 ##

*  HSSF：提供读写 Excel 格式档案的功能
 *  XSSF：提供读写 Excel OOXML 格式档案的功能
 *  HWPF：提供读写 Word 格式档案的功能
 *  HSLF：提供读写 PowerPoint 格式档案的功能
 *  HDGF：提供读写 Visio 格式档案的功能

## POI 写 ##

我们先创建一个 Maven 项目，创建项目的步骤这里就不详细说了

### 引入依赖 ###

<dependencies>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        
        <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.5</version>
        </dependency>
    </dependencies>

Excel 说明  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 1]

### 代码示例 ###

03 是指 Excel 版本是 2003 的（后缀为 .xls），07 是指 Excel 版本是 2007 的（后缀为 .xlsx）。注意对象的一个区别，就是文件后缀名

package com.java.demo.write;
    
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import java.io.FileOutputStream;
    
    /** * @author Woo_home * @create 2020/8/3 11:57 */
    
    public class WriteExcelDemo { 
        
        private static final String PATH = "src\\main\\java\\com\\java\\demo\\excel\\";
    
        public static void testWrite() throws Exception { 
            // 创建一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建一个工作表
            HSSFSheet sheet = workbook.createSheet("统计表");
            // 创建一个行，0 表示第一行
            Row row1 = sheet.createRow(0);
            // 创建一个单元格，0 表示一个格子
            Cell cell = row1.createCell(0);
            cell.setCellValue("id");
    
    		// 第一行第二列
            Cell cell1 = row1.createCell(1);
            cell1.setCellValue("name");
    
            // 创建第二行，1 表示第二行
            Row row2 = sheet.createRow(1);
            Cell cell2 = row2.createCell(0);
            cell2.setCellValue("111");
    		
    		// 第二行第二列
            Cell cell3 = row2.createCell(1);
            cell3.setCellValue("lisa");
    
            // 生成一张表（IO 流）
            // 如果需要生成 xls，直接修改后缀即可
            FileOutputStream fileOutputStream = new FileOutputStream(PATH + "demo.xlsx");
    
            // 写入文件
            workbook.write(fileOutputStream);
            
            // 关闭流
            fileOutputStream.close();
    
            System.out.println("文件生成成功");
        }
        
        public static void main(String[] args) throws Exception { 
            testWrite();
        } 
    }

运行该程序之后就会在定义的 PATH 路径下生成一个文件  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 2]

## 大文件写 HSSF ##

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

### 代码示例（xls） ###

package com.java.demo.write;
    
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    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;
    
    /** * @author Woo_home * @create 2020/8/3 11:57 */
    
    public class WriteExcelDemo { 
        
        private static final String PATH = "src\\main\\java\\com\\java\\demo\\excel\\";
    
        public static void testWrite() throws Exception { 
    
            long startTime = System.currentTimeMillis();
    
            // 使用 HSSFWorkbook 创建一个工作簿
            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);
                }
            }
    
            // 生成一张表（IO 流）
            FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigDemo.xls");
    
            // 写入文件
            workbook.write(fileOutputStream);
            
            // 关闭流
            fileOutputStream.close();
    
            System.out.println("文件生成成功");
    
            long endTime = System.currentTimeMillis();
    
            System.out.println("用时：" + (endTime - startTime) / 1000 + " s");
        }
        
        public static void main(String[] args) throws Exception { 
            testWrite();
        } 
    }

输出：  
![在这里插入图片描述][2020080312580851.png]  
生成的文件内容如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 3]  
当我们将行改为 65537 时就会抛出异常了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 4]

## 大文件写 XSSF ##

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

### 代码示例（xlsx） ###

package com.java.demo.write;
    
    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.FileOutputStream;
    
    /** * @author Woo_home * @create 2020/8/3 11:57 */
    
    public class WriteExcelDemo { 
        
        private static final String PATH = "src\\main\\java\\com\\java\\demo\\excel\\";
    
        public static void testWrite() throws Exception { 
    
            long startTime = System.currentTimeMillis();
    
            // 使用 XSSFWorkbook 创建一个工作簿
            Workbook workbook = new XSSFWorkbook();
            // 创建一个工作表
            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);
                }
            }
    
            // 生成一张表（IO 流）
            FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigDemo.xlsx");
    
            // 写入文件
            workbook.write(fileOutputStream);
            
            // 关闭流
            fileOutputStream.close();
    
            System.out.println("文件生成成功");
    
            long endTime = System.currentTimeMillis();
    
            System.out.println("用时：" + (endTime - startTime) / 1000 + " s");
        }
        
        public static void main(String[] args) throws Exception { 
            testWrite();
        } 
    }

输出：  
![在这里插入图片描述][20200803130333373.png]  
可以发现，使用 XSSF 时，会非常的耗时，但是数据量不会限制于 65536

## 大文件写 SXSSF ##

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

package com.java.demo.write;
    
    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.streaming.SXSSFWorkbook;
    import java.io.FileOutputStream;
    
    /** * @author Woo_home * @create 2020/8/3 11:57 */
    
    public class WriteExcelDemo { 
        
        private static final String PATH = "D:\\IDEA\\ideaworkspace\\demo\\src\\main\\java\\com\\java\\demo\\excel\\";
    
        public static void testWrite() throws Exception { 
    
            long startTime = System.currentTimeMillis();
    
            // 使用 SXSSFWorkbook 创建一个工作簿
            Workbook workbook = new SXSSFWorkbook();
            // 创建一个工作表
            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);
                }
            }
    
            // 生成一张表（IO 流）
            FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigDemoS.xlsx");
    
            // 写入文件
            workbook.write(fileOutputStream);
            
            // 关闭流
            fileOutputStream.close();
    
            System.out.println("文件生成成功");
    
            // 清除临时文件
            ((SXSSFWorkbook) workbook).dispose();
    
            long endTime = System.currentTimeMillis();
    
            System.out.println("用时：" + (endTime - startTime) / 1000 + " s");
        }
        
        public static void main(String[] args) throws Exception { 
            testWrite();
        } 
    }

输出：  
![在这里插入图片描述][20200803131219561.png]  
可以发现，用时是比较少的

> **注意：** 过程中会产生临时文件，需要清理临时文件，默认由 100 条记录被保存在内存中，如果超过这个数量，则最前面的数据被写入临时文件。如果想自定义内存中的数据量，可是使用 new SXSSFWorkbook（数量）

可以使用 dispose() 方法清除临时文件

// 清除临时文件
    ((SXSSFWorkbook) workbook).dispose();

SXSSFWorkbook 来自官方的解释：实现 BigGridDemo 策略的流式 XSSFWorkbook 版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中

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

## POI 读 ##

### HSSF ###

Excel 内容如下：  
![在这里插入图片描述][20200803154900682.png]

package com.java.demo.read;
    
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import java.io.FileInputStream;
    
    /** * @author Woo_home * @create 2020/8/3 13:41 */
    
    public class ReadExcelDemo { 
    
        private static final String PATH = "src\\main\\java\\com\\java\\demo\\excel\\";
    
        public static void testRead() throws Exception { 
            // 获取文件流
            FileInputStream fileInputStream = new FileInputStream(PATH + "demo.xls");
    
            // 使用 HSSFWorkbook 创建一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
    
            // 获取表
            HSSFSheet sheet = workbook.getSheetAt(0);
    
            // 获取行
            Row row = sheet.getRow(0);
    
            // 获取列
            Cell cell = row.getCell(0);
    
    		// getStringCellValue 获取字符串类型
            System.out.println(cell.getStringCellValue());
    
    		// 关闭流
            fileInputStream.close();
        }
    
        public static void main(String[] args) throws Exception { 
            testRead();
        }
    }

输出：  
![\*\*加粗样式\*\*][20200803154925922.png]

### XSSF ###

Excel 文件内容如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 5]

package com.java.demo.read;
    
    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;
    
    /** * @author Woo_home * @create 2020/8/3 13:41 */
    
    public class ReadExcelDemo { 
    
        private static final String PATH = "src\\main\\java\\com\\java\\demo\\excel\\";
    
        public static void testRead() throws Exception { 
            // 获取文件流
            FileInputStream fileInputStream = new FileInputStream(PATH + "BigDemo.xlsx");
    
            // 创建一个工作簿
            Workbook workbook = new XSSFWorkbook(fileInputStream);
    
            // 得到表
            Sheet sheet = workbook.getSheetAt(0);
    
            // 得到行
            Row row = sheet.getRow(0);
    
            // 得到列
            Cell cell = row.getCell(1);
    
            // 获取 double 类型
            System.out.println(cell.getNumericCellValue());
    
            // 关闭流
            fileInputStream.close();
        }
    
        public static void main(String[] args) throws Exception { 
            testRead();
        }
    }

输出：  
![在这里插入图片描述][20200803155521769.png]

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

Excel 内容如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 6]

package com.java.demo.read;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    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.joda.time.DateTime;
    import java.io.FileInputStream;
    import java.util.Date;
    
    /** * @author Woo_home * @create 2020/8/3 13:41 */
    
    public class ReadExcelDemo { 
    
        private static final String PATH = "src\\main\\java\\com\\java\\demo\\excel\\";
    
        public static void testRead() throws Exception { 
            // 获取文件流
            FileInputStream fileInputStream = new FileInputStream(PATH + "demo.xls");
    
            // 创建一个工作簿
            Workbook workbook = new HSSFWorkbook(fileInputStream);
    
            Sheet sheet = workbook.getSheetAt(0);
    
            // 获取标题内容
            // 这里获取第一行标题内容
            Row row = sheet.getRow(0);
            // 如果第一行不为空
            if (row != null) { 
                // 获取总列数
                int cellCounts = row.getPhysicalNumberOfCells();
                // 遍历总列数
                for (int cellNum = 0; cellNum < cellCounts; cellNum++) { 
                    // 获得单列内容
                    Cell cell = row.getCell(cellNum);
                    // 如果列不为空
                    if (cell != null) { 
                        // 获取内容
                        String cellValue = cell.getStringCellValue();
                        System.out.print(cellValue + " | ");
                    }
                }
                System.out.println();
            }
    
            // 获取表中的内容
            int rowCount = sheet.getPhysicalNumberOfRows();
            // 从第二行开始遍历
            for (int rowNum = 1; rowNum < rowCount; rowNum++) { 
                // 获取所有行
                Row rowData = sheet.getRow(rowNum);
                if (rowData != null) { 
                    // 读取所有列总数
                    int cellCount = row.getPhysicalNumberOfCells();
                    for (int cellNum = 0; cellNum < cellCount; cellNum++) { 
                        // 遍历获取所有列的内容
                        Cell cell = rowData.getCell(cellNum);
                        // 匹配列的数据类型
                        if (cell != null) { 
                            int cellType = cell.getCellType();
                            String cellValue = "";
                            switch (cellType) { 
                                case Cell.CELL_TYPE_STRING: // 字符串
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN: // 布尔值
                                    cellValue = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case Cell.CELL_TYPE_BLANK: // 空值
                                    break;
                                case Cell.CELL_TYPE_NUMERIC: // 数字（日期、普通数字）
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {  // 日期
                                        Date date = cell.getDateCellValue();
                                        cellValue = new DateTime(date).toString("yyyy-MM-dd h:mm:ss");
                                    } else { 
                                        // 如果不是日期格式，防止数字过长
                                        cellValue = cell.toString();
                                    }
                                    break;
                                case HSSFCell.CELL_TYPE_ERROR: // 错误
                                    System.out.println("【数据类型错误】");
                                    break;
                            }
                            System.out.print(cellValue + " | ");
                        }
                    }
                }
            }
    
            // 关闭流
            fileInputStream.close();
        }
    
        public static void main(String[] args) throws Exception { 
            testRead();
        }
    }

输出：  
![在这里插入图片描述][20200803164320266.png]

[http_poi.apache.org]: http://poi.apache.org/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70]: /images/20230208/38e280101e4c4116b8ec4828d74d792f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 1]: /images/20230208/b6a222a6bf9d4693a8f7a6824624fa80.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 2]: /images/20230208/f13e41c2f1524d0abcba9c5558284dbb.png
[2020080312580851.png]: /images/20230208/d531181fe0a9406994b246cbe344cb8e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 3]: /images/20230208/d9071193f7544f6dbd3c16c074bd5f4f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 4]: /images/20230208/fe18edc067e6413ea71d4225e445987d.png
[20200803130333373.png]: /images/20230208/09b73a546c5b4a2bb47ad9de182d1160.png
[20200803131219561.png]: /images/20230208/a8dcc9c67ab14cff8cafc8e0de8caa98.png
[20200803154900682.png]: /images/20230208/5f602d26aa2f44248cd1549df3863e9c.png
[20200803154925922.png]: /images/20230208/ec038953b72c4cc5be3d815dba481895.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 5]: /images/20230208/82d4b114a6e54f6e974acda8a83272d2.png
[20200803155521769.png]: /images/20230208/609a091c689a4e8b894836c182d55fe4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dvb19ob21l_size_16_color_FFFFFF_t_70 6]: /images/20230208/ba838f73dde34be2aa6d60f49f476a5d.png
[20200803164320266.png]: /images/20230208/5dfc54abdc54467a9890a0688c82ca34.png