当前位置：主页 > 查看内容

POI和 EasyExcel对Excel的简单操作

发布时间：2021-07-15 00:00| 有位朋友查看

简介：POI Apache POI是Apache软件基金会的开放源码函式库POI提供API给Java程序对Microsoft Office格式档案读和写的功能。结构 HSSF 提供读写Microsoft Excel格式档案的功能。 XSSF 提供读写Microsoft Excel OOXML格式档案的功能。 HWPF 提供读写Microsoft Word格……

POI

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

结构

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

应用

1.将用户信息等文件导出为excel表格（导出数据）
2.将用户信息等文件从excel中导入到数据库等（导入数据）

Excel

在这里插入图片描述
Excel中的对象
（1）工作簿：WorkBook
（2）工作表：Sheet
（3）行：Row
（4）列：Cell

excel2003版和excel2007版的区别

1、保存文档的格式不同

（1）Excel2003的保存格式为xxx.xls，其后缀名名为.xls。
（2）Excel2007的保存格式为xxx.xlsx，其后缀名名为.xlsx。
2、打开的文件类型不同

（1）Excel2003只能够打开后缀名为.xls的Excel文档，打开后缀名为.xlsx的Excel文档时，出现的是乱码。
（2）Excel2007不仅能够打开后缀名为.xls的Excel文档，也能打开后缀名为.xlsx的Excel文档。
3、可用的行和列不同

（1）Excel2003表格共有65536行，256列。
（2）Excel2007表格共有1048576行，16384列。

关于WorkBook

POI中的WorkBook接口下的三个实现类
在这里插入图片描述
HSSFWorkbook 是对03版本的excel文件操作的类
XSSFWorkbook 是对07版本的excel文件操作的类
SXSSFWorkbook Super XSSFWorkbook，是升级版的XSSFWorkbook，更快速的去对07版本的excel文件进行处理
下面我们将会对XSSFworkbook和SXSSFworkbook类对excel文件的操作进行时间上的对比。

POI的写入操作

1.HSSFWorkbook的写入操作

在maven中导入依赖

<!--  excel.xls(03版本的excel依赖)  -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
<!--  excel.xlsx(07版本的excel依赖)  -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
<!--  日期格式化工具  -->
        <dependency>
            <groupId>org.wso2.orbit.joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.9.4.wso2v1</version>
        </dependency>
<!--  测试工具  -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

设置一个全局变量PATH

String PATH = "C:\\Users\\26090\\IdeaProjects\\cn.pdsu.wbb\\src" ;

运行下面代码，生成一个名为 03版excel.xls 的excel文件

@Test
public void testWrite03() throws Exception {
    // 1.创建一个工作簿
    Workbook workbook = new HSSFWorkbook() ;
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("POI初学者") ;
    // 3.创建一个行
    Row row1 = sheet.createRow(0) ;
    // 4.创建一个单元格（这里以行坐标和列坐标创建了一个单元格（0,0））
    Cell cell1 = row1.createCell(0) ;
    // 向单元格内写入数据
    cell1.setCellValue("姓名");
    // 创建单元格（0,1）
    Cell cell2 = row1.createCell(1) ;
    cell2.setCellValue("张三");

    // 创建行
    Row row2 = sheet.createRow(1) ;
    Cell cell3 = row2.createCell(0);
    cell3.setCellValue("时间");
    Cell cell4 = row2.createCell(1);
    // 创建日期并格式化
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell4.setCellValue(time);

    // 将其生成一张表(使用IO流)    03版的excel文件的扩展名是xls
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03版excel.xls") ;
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    System.out.println("03版excel.xls文件生成完毕.");
}

代码执行后在src目录下生成的excel文件
在这里插入图片描述
打开文件可以看到其中的数据

2.XSSFWorkbook的写入操作

 @Test
public void testWrite07() throws Exception {
    // 1.创建一个工作簿
    Workbook workbook = new XSSFWorkbook() ;
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("POI初学者") ;
    // 3.创建一个行
    Row row1 = sheet.createRow(0) ;
    // 4.创建一个单元格（这里以行坐标和列坐标创建了一个单元格（0,0））
    Cell cell1 = row1.createCell(0) ;
    // 向单元格内写入数据
    cell1.setCellValue("姓名");
    // 创建单元格（0,1）
    Cell cell2 = row1.createCell(1) ;
    cell2.setCellValue("张三");

    // 创建行
    Row row2 = sheet.createRow(1) ;
    Cell cell3 = row2.createCell(0);
    cell3.setCellValue("时间");
    Cell cell4 = row2.createCell(1);
    // 创建日期并格式化
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell4.setCellValue(time);

    // 将其生成一张表(使用IO流)
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07版excel.xlsx") ;
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();

    System.out.println("07版excel.xlsx文件生成完毕.");
}

代码执行后在src目录下生成的.xlsx文件

在这里插入图片描述
打开文件

操作较大数据量

在我们的日常应用中一般都是将数据批量的导入或导出的批量，下面我们就来看一下如何进行大数据量的导入

1.HSSF

缺点：关于03版的excel，因为其最多只有65536行，当我们导入的数据超过65536行时，它就会抛出异常

优点：再数据操作过程中，其将数据写入缓存，不操作磁盘，最后再一次性写入磁盘，操作速度快

数据批量导入，导入65536行数据到03版的excel文件中

@Test
public void testWriteBigData03() throws Exception {
    // 开始时间
    long begin = System.currentTimeMillis() ;
    // 1.创建一个工作簿
    Workbook workbook = new HSSFWorkbook() ;
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("POI初学者") ;

    // 写入数据
    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 + "03版bigData_excel.xls") ;
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();
    long end = System.currentTimeMillis() ;
    System.out.println((double) (end-begin)/1000);
    System.out.println("03版excel.xls文件生成完毕.");
}

可以看到耗时5.147s
在这里插入图片描述
而从生成的文件可以看到数据只到65536行，其下方没有空行

将生成的行数改为65537行时再次运行程序

此时的程序抛出了异常，我们在上面说过，03版的excel最多只能存储65536行数据，超过65536行就会异常。
在这里插入图片描述

2.XSSF

缺点：写入数据时数度较慢，非常的消耗内存，也有可能回发生内存溢出的情况

优点：可以写入较多的数据量

数据批量导入，导入65536行数据到07版的excel文件中

@Test
public void testWriteBigData07() throws Exception {
    // 开始时间
    long begin = System.currentTimeMillis() ;
    // 1.创建一个工作簿
    Workbook workbook = new XSSFWorkbook() ;
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("POI初学者") ;

    // 写入数据
    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 + "07版bigData_excel.xlsx") ;
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();
    long end = System.currentTimeMillis() ;
    System.out.println((double) (end-begin)/1000);
    System.out.println("07版excel.xlsx文件生成完毕.");
}

耗时10.972s，比起03版的耗时更长
在这里插入图片描述

可以看到在07版的excel文件中数据生成到6557行后其下方还是有空行
在这里插入图片描述

3.SXSSF

优点：可以写很大的数据量，切写数据的速度快，占用内存更少

在使用SXSSFWorkbook操作文件的过程中回生成临时文件，需要对临时文件进行清理

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

数据批量导入，使用SXSSFWorkbook类对象导入65536行数据到07版的excel文件中

@Test
public void testWriteBigDataS07() throws Exception {
    // 开始时间
    long begin = System.currentTimeMillis() ;
    // 1.创建一个工作簿
    Workbook workbook = new SXSSFWorkbook() ;
    // 2.创建一个工作表
    Sheet sheet = workbook.createSheet("POI初学者") ;

    // 写入数据
    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 + "07版bigDataS_excel.xlsx") ;
    // 输出
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();
    ((SXSSFWorkbook)workbook).dispose() ;   // 删除临时文件
    long end = System.currentTimeMillis() ;
    System.out.println((double) (end-begin)/1000);
    System.out.println("SXSSF_07版excel.xlsx文件生成完毕.");
}

处理相同的数据量耗时比之XSSFWorkbook要少
在这里插入图片描述
生成的excel文件

POI的读取操作

因为excel版本的不同所以对它的操作也分为两种

1.对03版本excel的操作

对于一个单元格数据的读取

@Test
public void testRead01() throws Exception {
    // 获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "src03版excel.xls") ;
    // 创建一个工作簿，用于操作excel的各种功能
    Workbook workbook = new HSSFWorkbook(inputStream) ;
    // 得到表
    Sheet sheet = workbook.getSheetAt(0) ;
    // 得到行
    Row row = sheet.getRow(0) ;
    // 得到列
    Cell cell = row.getCell(0) ;

    System.out.println(cell.getStringCellValue());
    inputStream.close();

}

在这里插入图片描述
在对excel文件中数据的获取时，我们需要针对不同的数据类型做出不同的获取方法

2.对07版本excel的操作

@Test
public void testRead02() throws Exception {
    // 获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "src07版excel.xlsx") ;
    // 创建一个工作簿，用于操作excel的各种功能
    Workbook workbook = new XSSFWorkbook(inputStream) ;
    // 得到表
    Sheet sheet = workbook.getSheetAt(0) ;
    // 得到行
    Row row = sheet.getRow(0) ;
    // 得到列
    Cell cell = row.getCell(0) ;

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

3.读取excel中不同的数据类型

先将表头数据读取出来，再将表中的数据进行类型的判断并输出

@Test
    public void testRead03() throws Exception {
        // 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "src\\excel读操作.xlsx") ;
        // 创建一个工作簿，用于操作excel的各种功能
        Workbook workbook = new XSSFWorkbook(inputStream) ;
        Sheet sheet = workbook.getSheetAt(0) ;
        // 获取标题内容
        Row rowTitle = sheet.getRow(0) ;
        if(rowTitle!=null) {
            // 获取列数
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for(int cellNum = 0 ; cellNum < cellCount ; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum) ;
                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 = rowTitle.getPhysicalNumberOfCells() ;
                for(int cellNum = 0 ;cellNum < cellCount ; cellNum++) {
                    Cell cell = rowData.getCell(cellNum) ;
                    // 匹配列的数据类型
                    if(cell!=null) {
                        CellType cellType = cell.getCellTypeEnum() ; // 获取数据的类型
                        String cellValue = "";

                        switch (cellType) {
                            case STRING :    //  字符串
                                System.out.print("【String】");
                                cellValue = cell.getStringCellValue() ;
                                break;

                            case BOOLEAN :    //  布尔类型
                                System.out.print("【Boolean】");
                                cellValue = String.valueOf(cell.getBooleanCellValue()) ;
                                break;

                            case BLANK :    //  空
                                System.out.print("【Blank】");
                                break;

                            case NUMERIC :    //  数字(数字中分为日期和普通数字)
                                System.out.print("【Number】");
                                if(HSSFDateUtil.isCellDateFormatted(cell)) {    // 判断其是否为日期
                                    System.out.print("【日期】");
                                    Date date = cell.getDateCellValue() ;
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    System.out.print("【转换为字符串输出】");
                                    cell.setCellType(CellType.STRING);
                                    cellValue = cell.toString() ;
                                }
                                break;

                            case ERROR :    //  错误
                                System.out.print("【Error】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        inputStream.close();
    }

在上面的操作中可以看到POI对03版和07版excel的操作的代码基本一致，只需修改文件的后缀和类名即可。

EasyExcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

easyExcel官网：https://www.yuque.com/easyexcel

导入依赖

<!-- 导入easyExcel的pom依赖 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.7</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.76</version>
</dependency>

创建对象

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

    public void setString(String string) {
        this.string = string;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public void setDoubleData(Double doubleData) {
        this.doubleData = doubleData;
    }
}

自动生成数据

private List<DemoData> data() {
    List<DemoData> list = new ArrayList<DemoData>();
    for (int i = 0; i < 10; i++) {
        DemoData data = new DemoData();
        data.setString("字符串" + i);
        data.setDate(new Date());
        data.setDoubleData(0.56);
        list.add(data);
    }

写入数据

调用EasyExcel中的write()方法
其中write()中有两个参数，第一个参数是文件名，第二个参数是格式类
要想生成表要用到sheet()方法，sheet()方法中写入一个字符串表示表的名字
要想将数据写入表中还要调用doWrite()方法

格式如下：
EasyExcel.write(文件名, 类名.class).sheet(表明).doWrite(数据);

@Test
public void simpleWrite() {
    // 写法1
    String fileName = PATH + "easyTest.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // write的两个参数：文件名，格式类
    // sheet生成表明
    // doWrite写入数据
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}

执行后的结果
在这里插入图片描述

读取数据

假设这个是你的DAO存储

public class DemoDAO {
    public void save(List<DemoData> list) {
}

监听器

public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假设这个是一个DAO
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     * DemoData 类型
     * AnalysisContext 分析上下文
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

测试

@Test
public void simpleRead() {
    String fileName = PATH + "easyTest.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

在这里插入图片描述
想要了解更多关于EasyExcel的相关信息就到其官网 https://www.yuque.com/easyexcel

；原文链接：https://blog.csdn.net/weixin_45890113/article/details/115742939
本站部分内容转载于网络，版权归原作者所有，转载之目的在于传播更多优秀技术内容，如有侵权请联系QQ/微信：153890879删除，谢谢！

上一篇：JDBC 下一篇：Java中Class对象详述

随机推荐

MVC、MVP和MVVM分别是什么_动力节点Java

一、MVC MVC模式的意思是，软件可以分成三个部分。视图（View）：用户界面。控...
获近亿元战略融资，觉非科技领跑车路协同

今日国内领先的智能数据服务运营商觉非科技完成近亿元A轮融资。本轮融资由和高资...
微软泄露Windows10新UI！Windows10任务栏

我们知道微软将会在今年给Windows10更换全新设计的UI，让Windows10的界面更加整...
【算法】扑克发牌算法实现

首先给扑克牌中每张牌设定一个编号，下面算法实现的编号规则如下： u 红桃按照从...
jsp中page指令用法详解

本文实例讲述了jsp中page指令用法。分享给大家供大家参考。具体如下：一、JSP ...
水杯的测试用例

从功能测试、性能测试、界面测试、安全性测试、易用性、兼容性测试、震动测试七...
Window十二问（快扶我起来，我还能问）

前言关于Window，你了解多少呢?看看下面这些问题你都能答上来吗。如果你遇到这...
程序员误区，缓存一定能提升程序效率么？

大家好，今天我们来简单的聊一聊缓存问题。什么是缓存呢?它在系统设计中是在一个...
Git 常用命令清单(整理且详细)

git工作区，暂存区，版本库之间的关系：我们建立的项目文件夹就是工作区，在初...
【图像计数】基于matlab硬币计数【含Matl

一、简介本设计为硬币图像识别统计装置通过数码相机获取平铺无重叠堆积的硬币的...