Excel操作-Apache-POI

一、场景需求

  1. 将一些数据库信息导出为Excel表格

  1. 将Excel表格数据导入数据库

  1. 大量的数据的导入导出操作

解决方案

常用的解决方案：Apache POI与阿里巴巴easyExcel

Apache-POI介绍

Apache-POI是基于office open XML标准（OOXML）和Microsoft和OLE2复合文档格式（OLE2）处理各种格式的开源项目，简而言之，您可以使用java读写MS excel文件，可以使用java读写MS word 和MS powerPoint文件

模块

  • HSSF: 提供读写Microsoft Excel XLS格式(2003)档案的功能

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

  • SXSSF： 提供低内存占用量读写Microsoft Excel OOXML XLSX格式档案的功能

  • HWPF: 提供读写Microsoft Word DOC97（2003）格式档案的功能

  • XWPF： 提供读写Microsoft Word Doc2003格式（2007+）档案的功能

  • HSLF/XSLF: 提供读写Misrosoft powerPoint格式档案的功能

  • HDGF/XDGF: 提供读Miscrosoft Visio格式档案的功能

  • HSMF: 提供Misrosoft outlook格式档案的功能

POI存在的问题

java解析、生成EExcel比较有名的框架有POI、JXL。但是他们都存在一个严重的问题，就是非常耗内存，也就是数量量比较大的时候可能会出现OOM（内存溢出）的问题，但是POI有一套SAX模式可以一定程度上解决一些内存溢出的问题，但是依旧没有完全解决内存消耗过大的问题

Excel表格03和07版本区别

POI可以支持不同版本的Excel表格的操作，那么现在大提升的就是03版本和07+版本，那么这两个表格有什么区别如下：

  • 03版本：最多行数支持65536，并且后缀为.xls

  • 07+版本：理论上是没有限制的，但是实际支持的行数为：1048576，并且后缀名为xlsx

POI操作Excel

首先第一件事情，我们需要创建项目导入对应的maven，这里我们需要演示03版本和07+版本，所以两个依赖都导入

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

接下来我们完成通过POI写入Excel表格数据的操作，那么我们首先要知道写一个表格需要的步骤

  1. 创建工作簿：Workbook

  1. 创建工作表：sheet

  1. 创建行：Row

  1. 创建列（单元格）：Cell

  1. 具体数据写入

03版本测试

package ExcelTest;

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.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @ClaseName: ExcelWrite03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 13:49$
 * @param: $
 * @return: $
 */
public class ExcelWrite03 {
    public static void main(String[] args) {
//        03版本的写入
//        创建工作簿
        Workbook workbook = new HSSFWorkbook();
//        创建工作表
        Sheet sheet = workbook.createSheet("03版本写入测试");
//        创建行（第一行）
        Row row = sheet.createRow(0);
//        创建单元格
        Cell cell11 = row.createCell(0);
//        写入数据
        cell11.setCellValue("商品ID");

//        写入数据（1,2）
        Cell cell = row.createCell(1);
        cell.setCellValue("商品名称");

//        第二行
        Row row1 = sheet.createRow(1);//创建行
        Cell cell21 = row1.createCell(0);//创建单元格
        cell21.setCellValue(1);
        Cell cell22 = row1.createCell(1);
        cell22.setCellValue("电脑");

//        生成一张表-IO流
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream("./03版本写入测试.xls");
            workbook.write(fileOutputStream);
            try {
//                关闭流
                fileOutputStream.close();
                System.out.println("表格生成完毕");
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

运行结果

07版本测试

package ExcelTest;

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.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @ClaseName: ExcelWrite03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 13:49$
 * @param: $
 * @return: $
 */
public class ExcelWrite07 {
    public static void main(String[] args) {
//        03版本的写入
//        创建工作簿
        Workbook workbook = new XSSFWorkbook();
//        创建工作表
        Sheet sheet = workbook.createSheet("07版本写入测试");
//        创建行（第一行）
        Row row = sheet.createRow(0);
//        创建单元格
        Cell cell11 = row.createCell(0);
//        写入数据
        cell11.setCellValue("商品ID");

//        写入数据（1,2）
        Cell cell = row.createCell(1);
        cell.setCellValue("商品名称");

//        第二行
        Row row1 = sheet.createRow(1);//创建行
        Cell cell21 = row1.createCell(0);//创建单元格
        cell21.setCellValue(1);
        Cell cell22 = row1.createCell(1);
        cell22.setCellValue("电脑");

//        生成一张表-IO流
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream("./07版本写入测试.xlsx");
            workbook.write(fileOutputStream);
            try {
//                关闭流
                fileOutputStream.close();
                System.out.println("表格生成完毕");
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

运行结果

批量数据写入

那么不管使用03还是07+版本，那么在实际开发中都会碰到批量写入数据的操作，尤其是数据量比较多的时候，那么03版本和07+版本是处理的方式是不同的：

03版本HSSF:

  • 最多支持65536行数据的写入，超出会抛异常

  • 操作方式为，先将所有的数据放入到缓存中，最后一次性写入磁盘，写入速度快

07版本XSSF:

  • 数据写入速度比较慢，因为XSSF是获取全部行的数据，因此会消耗大量的内存，数据量庞大的时候有可能会发现内存溢出

  • 可以写入较大的数据，比如10W+数据

具体测试

03版本HSSF

package ExcelTest;

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.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @ClaseName: WriteBatchDate03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 14:48$
 * @param: $
 * @return: $
 */
public class WriteBatchDate03 {//03版本批量写入

    public static void main(String[] args) {
        //    开始时间
        long start = System.currentTimeMillis();
//    创建工作簿
        Workbook workbook = new HSSFWorkbook();
//    创建表
        Sheet sheet = workbook.createSheet("03版本批量写入");
//    创建数据
        for (int rowNum = 0; rowNum < 65536; rowNum++) {//行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 20; cellNum++) {//列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum + 1);
            }
        }
        try {
            FileOutputStream fileOutputStream = new FileOutputStream("./WriteBatchDate03.xls");
            try {
                workbook.write(fileOutputStream);
                fileOutputStream.close();
                System.out.println("表格生成完毕！！！！");
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
//    结束时间
        long end = System.currentTimeMillis();
        System.out.println("执行时间："+(end-start)/1000+"s");
    }

}

07版本HSSF

package ExcelTest;

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.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @ClaseName: WriteBatchDate03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 14:48$
 * @param: $
 * @return: $
 */
public class WriteBatchDate07 {//07版本批量写入

    public static void main(String[] args) {
        //    开始时间
        long start = System.currentTimeMillis();
//    创建工作簿
        Workbook workbook = new XSSFWorkbook();
//    创建表
        Sheet sheet = workbook.createSheet("07版本批量写入");
//    创建数据
        for (int rowNum = 0; rowNum < 65537; rowNum++) {//行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 20; cellNum++) {//列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum + 1);
            }
        }
        try {
            FileOutputStream fileOutputStream = new FileOutputStream("./WriteBatchDate07.xlsx");
            try {
                workbook.write(fileOutputStream);
                fileOutputStream.close();
                System.out.println("表格生成完毕！！！！");
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
//    结束时间
        long end = System.currentTimeMillis();
        System.out.println("执行时间："+(end-start)/1000+"s");
    }

}

大数据量写入

大数据写SXSSF

  • 为了解决大数据量写入的问题，所以官方提供了SXSSF来解决大文件写入问题，所以它可以写入非常大量的数据，比如上百万条的数据，并且写入速度更快，占用内存更少

  • 官方文档翻译：SXSSF在兼容XSSF的同时，能够应对大数据量和内存空间有限的情况，SXSSF每次获取的行数是在一个数值范围内，这个范围称为“滑动窗口”，在这个窗口内的数据均存在于内存中，超过这个窗口大小时，数据会被写入磁盘，由此控制内存使用，相比较而言，XSSF则每次获取全部行

  • 窗口默认大小为100（可以自定义），如果行数为101行，那么此时超过了窗口限制，索引值最小的行会被“刷入”磁盘，需要注意，SXSSF会自动分配临时文件，这些临时文件需要我们手动清除，清除的方式是使用dispose（）方法；

  • 自定义的方式：SXSSFWorkBook wb = new SXSSFWorkbook(自定义窗口大小)

package ExcelTest;

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 org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @ClaseName: WriteBigDate$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 15:15$
 * @param: $
 * @return: $
 */
public class WriteBigDate {
    public static void main(String[] args) {
        //    开始时间
        long start = System.currentTimeMillis();
//    创建工作簿
        Workbook workbook = new SXSSFWorkbook();
//    创建表
        Sheet sheet = workbook.createSheet("07版本大数量写入");
//    创建数据
        for (int rowNum = 0; rowNum < 100000; rowNum++) {//行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 20; cellNum++) {//列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum + 1);
            }
        }
        try {
            FileOutputStream fileOutputStream = new FileOutputStream("./WriteBigDate07.xlsx");
            try {
                workbook.write(fileOutputStream);
                fileOutputStream.close();
                System.out.println("表格生成完毕！！！！");
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
//    结束时间
        long end = System.currentTimeMillis();
        System.out.println("执行时间："+(end-start)/1000+"s");
    }
}

POI操作--读取Excel

其实读取用到的对象和写入的对象是一样的：

  • 工作簿：WorkBoot（所有的Excel工作簿的操作都是通过此类型进行设置）

03 HSSF

07 XSSF

  • 数据行：Sheet（所有关于数据表的操作都是通过sheet都可以进行设置）

  • 行：ROW

  • 单元格：Cell

具体读取步骤

利用文件流来读取

  1. 获取工作簿

  1. 获取表

  1. 获取行

  1. 获取单元格

  1. 读取数据

那么这里我们还是针对03和07版本的读取进行测试

03版本读取

package ExcelTest;

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.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

/**
 * @ClaseName: ReadExcel03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 15:25$
 * @param: $
 * @return: $
 */
public class ReadExcel03 {
    public static void main(String[] args) {
        try {
//            通过文件流读取
            FileInputStream inputStream = new FileInputStream("./03版本写入测试.xls");
//            读取工作簿
            Workbook workbook = new HSSFWorkbook(inputStream);
//            获取表(通过下标方式来进行读取或者可以采用表名进行读取)
            Sheet sheet = workbook.getSheetAt(0);
//            获取行（采用下标方式获取）
            Row row = sheet.getRow(0);
//            获取单元格（采用下标方式获取）
            Cell cell = row.getCell(0);
//            读取数据
            String stringCellValue = cell.getStringCellValue();
            System.out.println(stringCellValue);
            inputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

07版本读取

package ExcelTest;

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.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

/**
 * @ClaseName: ReadExcel03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 15:25$
 * @param: $
 * @return: $
 */
public class ReadExcel07 {
    public static void main(String[] args) {
        try {
//            通过文件流读取
            FileInputStream inputStream = new FileInputStream("./07版本写入测试.xlsx");
//            读取工作簿
            Workbook workbook = new XSSFWorkbook(inputStream);
//            获取表(通过下标方式来进行读取或者可以采用表名进行读取)
            Sheet sheet = workbook.getSheetAt(0);
//            获取行（采用下标方式获取）
            Row row = sheet.getRow(0);
//            获取单元格（采用下标方式获取）
            Cell cell = row.getCell(0);
//            读取数据
            String stringCellValue = cell.getStringCellValue();
            System.out.println(stringCellValue);
            inputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

批量读取

在我们实际的开发过程中，经常会遇到不同类型的数据，这也是我们工作中最麻烦的操作，首先我们假设有一张表：商品表.xls,具体数据如下：

那么我们想要读取这张表，需要首先读取标题部分，然后在读取具体的数据部分，这两不一定要分开的

读取标题部分

package ExcelTest;

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.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

/**
 * @ClaseName: ReadExcel03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 15:25$
 * @param: $
 * @return: $
 */
public class ReadExcelCellType {
    public static void main(String[] args) {
        try {
//            通过文件流读取
            FileInputStream inputStream = new FileInputStream("./商品表.xls");
//            读取工作簿
            Workbook workbook = new HSSFWorkbook(inputStream);
//            获取表(通过下标方式来进行读取或者可以采用表名进行读取)
            Sheet sheet = workbook.getSheetAt(0);
//            获取表中的第一行数据
            Row titleRow = sheet.getRow(0);
            if (titleRow!=null){
//                获取标题单元格的数量，用于表里获取所有单元格
                int cellNum = titleRow.getPhysicalNumberOfCells();
                System.out.println(cellNum);
                for (int i = 0; i <cellNum ; i++) {
//                    获取所有单元格
                    Cell cell = titleRow.getCell(i);
                    if (cell!=null){
//                        获取单元格的数据
                        String stringCellValue = cell.getStringCellValue();
                        System.out.print(stringCellValue+"\t");
                    }
                    
                }
            }


            inputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

.getCellType()的几种类型值

CELL_TYPE_NUMERIC

数值型

CELL_TYPE_STRING

字符串型

CELL_TYPE_FORMULA

公式型

CELL_TYPE_BLANK

空值

CELL_TYPE_BOOLEAN

布尔型

CELL_TYPE_ERROR

错误

读取数据部分

package ExcelTest;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @ClaseName: ReadExcel03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 15:25$
 * @param: $
 * @return: $
 */
public class ReadExcelCellType {
    public static void main(String[] args) throws IOException {

//            通过文件流读取
        FileInputStream inputStream = new FileInputStream("./商品表.xls");
//            读取工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
//            获取表(通过下标方式来进行读取或者可以采用表名进行读取)
        Sheet sheet = workbook.getSheetAt(0);
        //获取标题以下的具体内容
        //获取一共有多少行数据
        int rowNum = sheet.getPhysicalNumberOfRows();
//            跳过第一行标题数据获取以下具体内容
        for (int i = 1; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
//                    获取每一行里面有多少单元格
                int NumberOfCells = row.getPhysicalNumberOfCells();
                for (int j = 0; j < NumberOfCells; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
//                    获取所有读取数据的类型
                        CellType cellType = cell.getCellType();
//                            根据不同的数据类型来读取数据
                        String cellVal = "";
                        switch (cellType) {
                            case STRING://字符串
                                cellVal = cell.getStringCellValue();
                                break;
                            case NUMERIC://数值类型--日期也会当做数值类型
//                                    判断是否为日期
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    cellVal = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                } else {
                                    cellVal = cell.toString();
                                }
                                break;
                        }
                        System.out.print(cellVal + "\t");
                    }

                }
                System.out.println();
            }
        }
        inputStream.close();

    }


}

完整代码

package ExcelTest;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @ClaseName: ReadExcel03$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 15:25$
 * @param: $
 * @return: $
 */
public class ReadExcelCellType {
    public static void main(String[] args) throws IOException {

//            通过文件流读取
        FileInputStream inputStream = new FileInputStream("./商品表.xls");
//            读取工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
//            获取表(通过下标方式来进行读取或者可以采用表名进行读取)
        Sheet sheet = workbook.getSheetAt(0);
//            获取表中的第一行数据
        Row titleRow = sheet.getRow(0);
        if (titleRow != null) {
//                获取标题单元格的数量，用于表里获取所有单元格
            int cellNum = titleRow.getPhysicalNumberOfCells();
            for (int i = 0; i < cellNum; i++) {
//                    获取所有单元格
                Cell cell = titleRow.getCell(i);
                if (cell != null) {
//                        获取单元格的数据
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue + "\t");
                }
            }
        }
        System.out.println();
        //获取标题以下的具体内容
        //获取一共有多少行数据
        int rowNum = sheet.getPhysicalNumberOfRows();
//            跳过第一行标题数据获取以下具体内容
        for (int i = 1; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
//                    获取每一行里面有多少单元格
                int NumberOfCells = row.getPhysicalNumberOfCells();
                for (int j = 0; j < NumberOfCells; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
//                    获取所有读取数据的类型
                        CellType cellType = cell.getCellType();
//                            根据不同的数据类型来读取数据
                        String cellVal = "";
                        switch (cellType) {
                            case STRING://字符串
                                cellVal = cell.getStringCellValue();
                                break;
                            case NUMERIC://数值类型--日期也会当做数值类型
//                                    判断是否为日期
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    cellVal = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                } else {
                                    cellVal = cell.toString();
                                }
                                break;
                        }
                        System.out.print(cellVal + "\t");
                    }

                }
                System.out.println();
            }
        }
        inputStream.close();

    }


}

测试结果

ExcelUtil封装

在我们给用户的excel文件的模板中隐藏一行“实体类名”，以便我们在读取文件的时候获取，并作为key值存储

封装的工具类需求：用户只需要传入Workbook对象（匹配版本），文件输入流，对应实体的class。就可以得到接卸表哥以后的结果 ，同时通过传入的实体类类型集合的方式返回

package Util;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ClaseName: ExcelUtil$
 * @Description:用户只需要传入Workbook对象（匹配版本），文件输入流，对应实体的class。就可以得到接卸表哥以后的结果 ，同时通过传入的实体类类型集合的方式返回* *
 * @Author: wuhs
 * @Date: 2022/12/12$ 16:34$
 * @param: $
 * @return: $
 */
public class ExcelUtil {
    public static <T> List<T> readExcel(Workbook workbook, FileInputStream inputStream, Class<T> T) throws Exception {
//        给用户返回的实体类集合
        List<T> result = new ArrayList<>();
//        在工作簿中获取工作表
        Sheet sheet = workbook.getSheetAt(0);
//        获取工作表中的行数（第一行是标题、第二行是对应的实体）
        int NumberOfRows = sheet.getPhysicalNumberOfRows();
//        获取第一行数据（隐藏行）
        Row row = sheet.getRow(1);
//        遍历第一行数据就是当前实体类对应的所有属性，同时要把这些数据放入到Map中的key
        List<String> key = new ArrayList<>();
        for (Cell cell : row
        ) {
            if (cell != null) {
//                获取单元格中的数据
                String value = cell.getStringCellValue();
                key.add(value);
            }
        }
        for (int i = 2; i < NumberOfRows; i++) {
//            获取属性名以下的数据
            row = sheet.getRow(i);
            if (row != null) {
//                计数器j用于映射数据使用
                int j = 0;
//                用于保存每条数据的Map，并且在Map中建立对应的数据的映射关系
                Map<String, String> excelMap = new HashMap<>();
                for (Cell cell : row) {
                    if (cell != null) {
//                       把所有的单元格格式设置为String
                        String celVale = getCelVale(cell);
                        if (celVale != null && !celVale.equals("")) {
                            excelMap.put(key.get(j), celVale);
                            j++;
                        }
                    }
                }
                System.out.println();
//                创建对应实体类类型，并且把读取到的数据转换成实体类对象
                T t = mapToEntity(excelMap,T);
                result.add(t);
            }
        }
        inputStream.close();
        return result;
    }

    public static NumberFormat nf = NumberFormat.getInstance();

    static {
        nf.setGroupingUsed(false);

    }

    public static String getCelVale(Cell cell) {
        CellType cellType = cell.getCellType();
        String valueCel = "";
        switch (cellType) {
            case STRING://字符串
                valueCel = cell.getStringCellValue();
                break;
            case NUMERIC://数值类型--日期也会当做数值类型
//                                    判断是否为日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    valueCel = new SimpleDateFormat("yyyy-MM-dd").format(date);
                } else {
                    valueCel = nf.format(cell.getNumericCellValue());
                }
                break;
            case BLANK://空字符串
                break;
            case BOOLEAN://布尔类型
                valueCel = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                break;
        }
        System.out.print(valueCel + "\t");
        return valueCel;
    }


    private static <T> T mapToEntity(Map<String, String> map, Class<T> entity) throws Exception, IllegalAccessException {
        T t = null;
        t = entity.newInstance();//基于反射拿到实体类属性
        for (Field field : entity.getDeclaredFields()) {
            if (map.containsKey(field.getName())) {//属性的名称map中是否包含
                boolean flag = field.isAccessible();
//                值为 true 则指示反射的对象在使用时应该取消 Java 语言访问检查。
//                值为 false 则指示反射的对象应该实施 Java 语言访问检查。
                field.setAccessible(true);
//                根据key拿到value值
                String mapValue = map.get(field.getName());
//                获取实体类属性的类型
                String type = field.getGenericType().toString();
                System.out.println("type------------"+type);
                //重新制定对应的属性的值
                if (mapValue != null) {
                    if (type.equals("class java.lang.String")) {
//                        Field.set()向对象的这个Field属性设置新值value
                        field.set(t, mapValue);
                    } else if (type.equals("double")) {
                        field.set(t, Double.parseDouble(String.valueOf(mapValue)));
                    } else if (type.equals("int")) {
                        field.set(t, Integer.parseInt(String.valueOf(mapValue)));
                    } else if (type.equals("class java.util.Date")) {
                        Date date = new SimpleDateFormat("yyyy-MM-dd").parse(mapValue);
                        field.set(t, date);
                    }

                }
                field.setAccessible(flag);
            }

        }
        return t;
    }

}

测试：

package ExcelTest;

import Util.ExcelUtil;
import entity.Product;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;
import java.util.List;

/**
 * @ClaseName: UtilTest$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 16:50$
 * @param: $
 * @return: $
 */
public class UtilTest {
    public static void main(String[] args)throws Exception {
        FileInputStream fileInputStream = new FileInputStream("./商品表.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        List<Product> products = ExcelUtil.readExcel(workbook, fileInputStream, Product.class);
        System.out.println(products);

    }
}

结果：

Excel中的公式---POI读取公式

excel表中会有一些公式（求和、求差等），所以如果我们想要获取公式的内容，那么我们需要在读取的时候用到POI提供的一些公式对象，当然如果是想直接读取数值的话，可以直接通过数据类型来读取

package ExcelTest;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;

/**
 * @ClaseName: GetFormula$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 18:40$
 * @param: $
 * @return: $
 */
public class GetFormula {
    public static void main(String[] args) throws Exception {
        //    通过文件流读取excel工作簿
        FileInputStream fileInputStream = new FileInputStream("./读取公式.xlsx");
//        获取工作簿
        Workbook workbook = new XSSFWorkbook(fileInputStream);
//        获取表（通过下标的方式来获取）
        Sheet sheet = workbook.getSheetAt(0);
//        获取行和单元格（带公式）
        Row row = sheet.getRow(2);
        Cell cell = row.getCell(0);
        也可以直接读取数值
//        System.out.println(cell.getNumericCellValue());
        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//        获取单元格的内容
        CellType cellType = cell.getCellType();
        switch (cellType){
            case FORMULA://公式
//                获取公式
                String formula = cell.getCellFormula();
                System.out.println(formula);
                CellValue value = formulaEvaluator.evaluate(cell);
                String val = value.formatAsString();
                System.out.println(val);
                break;

        }
        fileInputStream.close();
    }

}

执行结果

Excel操作-EasyExcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目，在尽可能节约内存的情况下支持读写百M的Excel。具有快速、简单避免OOM的java处理的Excel工具

具体操作：

注意：这个工具的操作都是依赖于实体类，所以我们需要建立一个实体类

package entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.Date;

/**
 * @ClaseName: Product$
 * @Description:
 * @Author: wuhs
 * @Date: 2022/12/12$ 16:57$
 * @param: $
 * @return: $
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Product {
    private int id;
    private String name;
    private double price;
    private int count;
    private Date createTime;
}

写入操作：

直接参照官方文档即可，里面写的很详细；https://easyexcel.opensource.alibaba.com/

# java
Logo
GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

cover

GitTalk | 使用面向业务的狮偶编程语言提升开发效率

avatar GitCode 开源社区
cover

GitTalk | DevUI Suits 场景解决方案

avatar GitCode 开源社区
cover

GitTalk | DevUI Admin 前端项目构建

avatar GitCode 开源社区