【POI和EasyExcel】Java操作Excel

一、介绍

1、POI

Apache POI是基于Office Open XML标准（OOXML）和Microsoft的OLE 2复合文档格式（OLE2）处理各种文件格式的开源项目。 简而言之，您可以使用Java读写MS Excel文件，可以使用Java读写MS Word和MS PowerPoint文件。
POI需要占用较大内存的主要原因是在解析Excel时将文件数据一次性全部加载到内存中。

本文章只是介绍Java操作Excel，我们知道2003版的Excel和2007+版的Excel是有比较大的差异的，所以操作 2003版的Excel 和 2007+版的Excel 的对象是不同的。

• HSSF － 提供读写Microsoft Excel XLS格式(Microsoft Excel 97 (-2003))档案的功能。

• XSSF － 提供读写Microsoft Excel OOXML XLSX格式(Microsoft Excel XML (2007+))档案的功能。

• SXSSF － 提供读写Microsoft Excel OOXML XLSX格式(Microsoft Excel XML (2007+))档案的功能,速度更快，使用缓存，具有临时文件。

2、EasyExcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从 磁盘上一行行读取数据，逐个解析。
EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）。

64M内存1分钟内读取75M(46W行25列)的Excel

当然还有急速模式能更快，但是内存占用会在100M多一点

Excel导入导出的应用场景

1. 数据导入：减轻录入工作量
2. 数据导出：统计信息归档
3. 数据传输：异构系统之间数据传输

二、POI 实现 Excel 的基本写入

注意是否为空判断（我没有写）

1、相关依赖

<!-- office 2003 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.0.0</version>
</dependency>

<!-- office 2007 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>

<!-- 日期格式化工具 -->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.10</version>
</dependency>

2、将数据写入xls中

@Test
void contextLoads01() throws Exception {
    String path = "E:\\ideaProject\\ExcelOpt\\excel_opt-poi-01\\";
    // 1 创建一个xls工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 2 创建一个sheet（设置名字为demo）
    HSSFSheet sheet = workbook.createSheet("demo");

    // 3 在sheet中创建第一行（是从0开始的）
    HSSFRow row1 = sheet.createRow(0);
    // 4 在第一行中创建第一个单元格（0,0）
    HSSFCell cell11 = row1.createCell(0);
    // 5 设置单元格的值
    cell11.setCellValue("名称");
    // 6 在第一行中创建第二个单元格（0,1）
    HSSFCell cell12 = row1.createCell(1);
    // 7 设置单元格的值
    cell12.setCellValue("studioustiger");

    // 在sheet中创建第二行
    HSSFRow row2 = sheet.createRow(1);
    // 在第二行中创建第一个单元格（1,0）
    HSSFCell cell21 = row2.createCell(0);
    // 设置单元格的值
    cell21.setCellValue("创建时间");
    // 在第二行中创建第二个单元格（1,1）
    HSSFCell cell22 = row2.createCell(1);
    // 设置单元格的值
    cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

    // 8 创建一个文件输出流（指定Excel的位置和名称及其后缀）
    FileOutputStream fileOutputStream = new FileOutputStream(path + "demo01.xls");
    // 9 写入工作目标文件
    workbook.write(fileOutputStream);
    // 10 关闭文件流
    fileOutputStream.close();

    System.out.println("ok");
}

3、将数据写入xlsx中

xlsx和xls的唯一一点不同就是使用的是XSSF，其他方面没有区别

@Test
void contextLoads02() throws Exception {
    String path = "E:\\ideaProject\\ExcelOpt\\excel_opt-poi-01\\";
    // 1 创建一个xlsx工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    // 2 创建一个sheet
    XSSFSheet sheet = workbook.createSheet("demo");

    // 3 在sheet中创建第一行
    XSSFRow row1 = sheet.createRow(0);
    // 4 在第一行中创建第一个单元格（0,0）
    XSSFCell cell11 = row1.createCell(0);
    // 5 设置单元格的值
    cell11.setCellValue("名称");
    // 6 在第一行中创建第二个单元格（0,1）
    XSSFCell cell12 = row1.createCell(1);
    // 7 设置单元格的值
    cell12.setCellValue("studioustiger");

    // 在sheet中创建第二行
    XSSFRow row2 = sheet.createRow(1);
    // 在第二行中创建第一个单元格（1,0）
    XSSFCell cell21 = row2.createCell(0);
    // 设置单元格的值
    cell21.setCellValue("创建时间");
    // 在第二行中创建第二个单元格（1,1）
    XSSFCell cell22 = row2.createCell(1);
    // 设置单元格的值
    cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

    // 8 创建一个文件输出流
    FileOutputStream fileOutputStream = new FileOutputStream(path + "demo02.xlsx");
    // 9 写入工作目标文件
    workbook.write(fileOutputStream);
    // 10 关闭文件流
    fileOutputStream.close();

    System.out.println("ok");
}

三、POI 实现大量数据的 Excel 写入

1、使用 HSSF 写大量数据

使用HSSF的方式写入Excel，有一个局限性是写入的数据最大不能超过65536条。

当我们的数据条数超过65536条数据时，会出现如下的错误：

注意是否为空判断（我没有写）

写入65536条数据时

// 使用 HSSF 写大量数据（写入数据不可以超过65536）
@Test
void contextLoads03() throws Exception {
    long begin = System.currentTimeMillis();
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("sheet01");

    for (int i = 0; i < 65536; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < 10; j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(j+1);
        }
    }

    FileOutputStream fileOutputStream = new FileOutputStream(path + "bigDataInsert.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();

    System.out.println((double)(end-begin)/1000 +" s");
}

2、使用 XSSF 写大量数据

使用XSSF的方式写入Excel，对于写入的数据的条数没有要求，但是不足的是耗时比较长，而且当我们写入的数据过大时，会出现内存移除现象（例如：一次性写入10000000条数据）

写入100000条数据时

// 使用 XSSF 写大量数据（写入数据可以超过65536，速度比较慢）
@Test
void contextLoads04() throws Exception {
    long begin = System.currentTimeMillis();
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("sheet01");

    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+1);
        }
    }

    FileOutputStream fileOutputStream = new FileOutputStream(path + "bigDataInsert.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();

    System.out.println((double)(end-begin)/1000 +" s");
}

3、使用 SXSSF 写大量数据

SXSSF是在XSSF的基础上进行改进的，其优点就是耗时更小了，这是因为SXSSF使用了缓存技术提高了效率。默认是一次将100条数据写入缓存区。因为使用到了缓冲区，所以存在临时文件

写入100000条数据时

// 使用 SXSSF 写大量数据（写入数据可以超过65536，速度更快）
@Test
void contextLoads05() throws Exception {
    long begin = System.currentTimeMillis();
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet("sheet01");

    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+1);
        }
    }

    FileOutputStream fileOutputStream = new FileOutputStream(path + "bigDataInsertSuper.xlsx");
    workbook.write(fileOutputStream);
    ((SXSSFWorkbook)workbook).dispose();   // 清除临时文件
    fileOutputStream.close();
    long end = System.currentTimeMillis();

    System.out.println((double)(end-begin)/1000 +" s");
}

四、POI 实现 Excel 的基本读取

注意是否为空判断（我没有写）

1、简单获取2003版本中的数据

workbook.getSheetAt(0)：通过索引获取sheet表

workbook.getSheet(“demo”)：通过名称获取sheet表

cell.getStringCellValue()：这个和原生的jdbc很类似，因为获取的内容，要是用与其类型相符的方法去获取

// 简单获取2003版本中的数据
@Test
void contextLoads06() throws Exception {
	// 1 创建一个文件输入流
    FileInputStream fileInputStream = new FileInputStream(path + "demo01.xls");

	// 2 获取workbook，将创建一个文件输入流作为workbook的参数
    Workbook workbook = new HSSFWorkbook(fileInputStream);

//        通过sheet的名称获取指定的表
//        Sheet demo = workbook.getSheet("demo");
    // 3 通过索引获取指定的表（0 表示第一个sheet表）
    Sheet sheet = workbook.getSheetAt(0);
    // 4 获取指定的行
    Row row = sheet.getRow(0);
    // 5 获取行中的指定的额单元格
    Cell cell = row.getCell(0);

	// 6 因为（0,0）的数据时String类型，所以这里使用getStringCellValue
    String value = cell.getStringCellValue();
    // 7 关闭流
    fileInputStream.close();
    System.out.println(value);

//        可以获取不同类型的数据
//        double value = cell.getNumericCellValue();
//        boolean value = cell.getBooleanCellValue();
//        Date value = cell.getDateCellValue();
}

2、简单获取2007+版本中的数据

// 简单获取2007+版本中的数据
@Test
void contextLoads07() throws Exception {
    FileInputStream fileInputStream = new FileInputStream(path + "demo02.xlsx");

    Workbook workbook = new XSSFWorkbook(fileInputStream);

    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);

    String value = cell.getStringCellValue();
    fileInputStream.close();
    System.out.println(value);
}

五、POI实现读取不同数据类型的数据（☆）

通常情况下，一个Excel表中的数据会有很多中，如下：

对于这种情况，我们需要使用不同的方法来获取其中的数据，但是我们通常又不知道Excel表中的数据类型，所以我们又必须进行判断，然后进行获取。

下面我们以xls的Excel为例，进行读取不同的数据类型的数据（如果你就得比较麻烦，那么你可以将其封装成一个工具类）

// 读取不同的数据类型的数据（2003版）
@Test
void contextLoads08() throws Exception{
    FileInputStream fileInputStream = new FileInputStream(path + "demo01.xls");
    Workbook workbook = new HSSFWorkbook(fileInputStream);

    // 获取名称为demo的sheet表
    Sheet sheet = workbook.getSheet("demo");
    // 获取sheet表中行数
    int rowNum = sheet.getPhysicalNumberOfRows();
    // 遍历sheet表中的行
    for (int rowIndex = 0; rowIndex < rowNum; rowIndex++) {
        // 获取一行
        Row row = sheet.getRow(rowIndex);
        // 判断是否为空
        if(row!=null){
            // 获取该行中的单元格的个数
            int cellNum = row.getPhysicalNumberOfCells();
            // 遍历该行中的单元格
            for (int cellIndex = 0; cellIndex < cellNum; cellIndex++) {
                // 获取单元格
                Cell cell = row.getCell(cellIndex);
                // 判断是否为空
                if (cell!=null){
                    // 获取当前单元格数据的类型
                    CellType cellType = cell.getCellType();
                    switch (cellType){
                        case _NONE:   // none
                            System.out.print(" \t");
                            break;
                        case BLANK:   // 空
                            System.out.print(" \t");
                            break;
                        case ERROR:   // 错误
                            System.out.println("error"+"\t");
                            break;
                        case STRING:   // 字符串
                            System.out.print(String.valueOf(cell.getStringCellValue()+"\t"));
                            break;
                        case BOOLEAN:  // 布尔值
                            System.out.print(String.valueOf(cell.getBooleanCellValue()+"\t"));
                            break;
                        case FORMULA:  // 公式
                            FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);
                            CellValue evaluate = formulaEvaluator.evaluate(cell);
                            System.out.println(evaluate.formatAsString()+"\t");
                            break;
                        case NUMERIC:  // 数字（数字和日期）
                            System.out.print(String.valueOf(cell.getNumericCellValue()+"\t"));
                            break;
                    }
                }
            }
            System.out.println();
        }
    }
    
    // 关闭流
    fileInputStream.close();
}

六、计算公式（拓展）

在Excel中会使用大量的公式，对于使用公式的单元格，我们并不能使用上述的方法直接将其读取出来。对于使用公式的单元格POI给我们提供的专门的操作方法。

如下图所示的单元格就是一个求和公式，下面我们就会使用

HSSFFormulaEvaluator((HSSFWorkbook)workbook)：公式直译器，就是通过这个对象可以将Excel中的公式进行翻译计算

cell.getCellFormula()：获取公式

formulaEvaluator.evaluate(cell).formatAsString()：计算公式结果并格式化成字符串

// 翻译计算公式
@Test
void contextLoads09() throws Exception {
    FileInputStream fileInputStream = new FileInputStream(path + "demo01.xls");
    Workbook workbook = new HSSFWorkbook(fileInputStream);

    // 获取名称为demo的sheet表
    Sheet sheet = workbook.getSheet("demo");
    Row row = sheet.getRow(3);
    Cell cell = row.getCell(1);

    // 创建一个公式直译器（将workbook作为参数传递进去）
    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 formulaRes = evaluate.formatAsString();
            System.out.println(formulaRes);
    }
}

七、EasyExcel 操作 Excel（☆）

easyexcel文档：https://www.yuque.com/easyexcel/doc/read

1、依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.10</version>
</dependency>

2、写Excel

在easyExcel中实现Excel的写是通过对象进行写入的，easyExcel会自动解析对象中的属性，写入Excel

案例一

效果图

我们可以定义一个实体类，类中包含三个字段，即上图对应的三列

@Data
public class DemoData {
    @ExcelProperty(value = "字符串标题")
    private String string;
    @ExcelProperty(value = "日期标题")
    private Date date;
    @ExcelProperty(value = "数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

向DemoData中填入数据，将一个一个的实体类封装早list集合中

private List<DemoData> data01(){
    ArrayList<DemoData> lists = new ArrayList<>();

    for (int i = 0; i < 10; i++) {
        DemoData demoData = new DemoData();
        demoData.setString("tiger"+(i+1));
        // 比较有意思的其会自动格式化成yyyy-MM-dd HH:mm:ss
        demoData.setDate(new Date());
        demoData.setDoubleData(1.8);
        lists.add(demoData);
    }
    return lists;
}

写入Excel

fileName：Excel的路径

DemoData.class：Excel对应的实体类

sheet(“模板”)：sheet表的名称

doWrite(data01())：写入的数据，参数是List类型

private String path = "E:\\ideaProject\\ExcelOpt\\easy_excel-01\\";

@Test
void contextLoads01() {
    String fileName = path + "simpleWrite.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data01());

}

案例二

效果图

实体类

@Data
public class IndexData {
    @ExcelProperty(value = "字符串标题", index = 0)
    private String string;
    @ExcelProperty(value = "日期标题", index = 1)
    private Date date;
    /**
     * 这里设置3 会导致第二列空的
     */
    @ExcelProperty(value = "数字标题", index = 3)
    private Double doubleData;
}

填充list

private List<IndexData> data02(){
    ArrayList<IndexData> lists = new ArrayList<>();

    for (int i = 0; i < 10; i++) {
        IndexData data = new IndexData();
        data.setString("tiger"+(i+1));
        // 比较有意思的其会自动格式化成yyyy-MM-dd HH:mm:ss
        data.setDate(new Date());
        data.setDoubleData(1.8);
        lists.add(data);
    }
    return lists;
}

写入Excel

private String path = "E:\\ideaProject\\ExcelOpt\\easy_excel-01\\";

void contextLoads02(){
    String fileName = path + "indexWrite.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, IndexData.class).sheet("模板").doWrite(data02());
}

案例三

效果图

实体类

@Data
public class ComplexHeadData {
    @ExcelProperty({"主标题", "字符串标题"})
    private String string;
    @ExcelProperty({"主标题", "日期标题"})
    private Date date;
    @ExcelProperty({"主标题", "数字标题"})
    private Double doubleData;
}

填充list

private List<ComplexHeadData> data04(){
    ArrayList<ComplexHeadData> lists = new ArrayList<>();

    for (int i = 0; i < 10; i++) {
        ComplexHeadData data = new ComplexHeadData();
        data.setString("tiger"+(i+1));
        // 比较有意思的其会自动格式化成yyyy-MM-dd HH:mm:ss
        data.setDate(new Date());
        data.setDoubleData(1.8);
        lists.add(data);
    }
    return lists;
}

写入Excel

@Test
void contextLoads04(){
    String fileName = path + "complexHeadWrite.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, ComplexHeadData.class).sheet("模板").doWrite(data04());

}

案例四

效果图

实体类

@Data
public class ConverterData {

    @ExcelProperty("字符串标题")
    private String string;
    /**
     * 我想写到excel 用年月日的格式
     */
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("日期标题")
    private Date date;
    /**
     * 我想写到excel 用百分比表示
     */
    @NumberFormat("#.##%")
    @ExcelProperty(value = "数字标题")
    private Double doubleData;
}

填充list

private List<ConverterData> data03(){
    ArrayList<ConverterData > lists = new ArrayList<>();

    for (int i = 0; i < 10; i++) {
        ConverterData  data = new ConverterData ();
        data.setString("tiger"+(i+1));
        // 比较有意思的其会自动格式化成yyyy-MM-dd HH:mm:ss
        data.setDate(new Date());
        data.setDoubleData(0.8);
        lists.add(data);
    }
    return lists;
}

写入Excel

    @Test
    void contextLoads03(){
        String fileName = path + "converterWrite.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, ConverterData.class).sheet("模板").doWrite(data03());
    }

3、读Excel

在easyExcel中,读取数据的步骤有些复杂，但是很高效，它是将数据的读取与持久化写在了一起，接下来我们就以读取如下的Excel的数据为例

实体类

对于读取的每一都是一个对象，索引我们需要一个与Excel中的行相对应的对象

@Data
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

监听器
easyExcel是通过监控 EasyExcel.read(…) 实现读读操作和持久化的

public class DemoDataListener extends AnalysisEventListener<DemoData> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;
    /**
     * 缓存的数据（使用List做缓存）
     */
    private List<DemoData> list = new ArrayList<>(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用持久化这个对象没用。
     */
    private DemoDAO demoDAO;

    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来（☆☆☆）
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        // 打印读取的数据
        System.out.println(JSON.toJSONString(data));
        // 将读取的数据添加到list（缓存）中
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库（持久化），防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            // 对应的是DemoDAO中的save()
            saveData();
            // 存储完成清理 list（所谓的清理list就是分配一个空的list将原来的list进行覆盖）
            list = new ArrayList<>(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        System.out.println("所有数据解析完成！");
    }

    /**
     * 用于持久化数据
     */
    private void saveData() {
        System.out.println("{"+list.size()+"}条数据，开始存储数据库！");
        // 将缓存中的数据持久化到数据库中
        demoDAO.save(list);
        System.out.println("存储数据库成功！");
    }
}

持久化
如果你需要将读取的数据进行持久化，那么你需要在下面的类中调用自己的持久化业务

/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

读Excel

// 最简单的读
@Test
public void simpleRead() {
    String fileName = path+"simpleWrite.xlsx";
    // 这里需要指定用哪一个class去读，然后默认读第一个sheet（你也可以设置参数，指定读取哪一个sheet），文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

提示：其他的一些高阶的Excel操作你可以查看 easyexcel文档：https://www.yuque.com/easyexcel/doc/read

---

✈ ❀ 希望平凡の我，可以给你不凡の体验 ☂ ✿ ，白嫖有罪 ☠ ，记得关注哦 ❥(^_-)