POI 操作 Excel

为什么会需要用到 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/

基本功能

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

POI 写

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

引入依赖

<dependencies>
    <!-- xls（03）-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <!-- xlsx（07） -->
    <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 说明

代码示例

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 路径下生成一个文件

大文件写 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();
    } 
}

输出：

生成的文件内容如下：

当我们将行改为 65537 时就会抛出异常了

大文件写 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();
    } 
}

输出：

可以发现，使用 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();
    } 
}

输出：

可以发现，用时是比较少的

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

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

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

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

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

POI 读

HSSF

Excel 内容如下：

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();
    }
}

输出：

XSSF

Excel 文件内容如下：

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();
    }
}

输出：

读取不同类型的数据

Excel 内容如下：

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();
    }
}

输出：