Java使用POI操作Excel

极客李华

已于 2024-02-01 00:59:46 修改

阅读量2.9k

点赞数 1

分类专栏： Excel使用技巧文章标签： java excel microsoft

于 2023-02-19 11:56:48 首次发布

本文链接：https://blog.csdn.net/qq_51447496/article/details/129085675

版权

Excel使用技巧专栏收录该内容

7 篇文章 0 订阅

订阅专栏

作者简介：一名后端开发人员，每天分享后端开发以及人工智能相关技术，行业前沿信息，面试宝典。
座右铭：未来是不可确定的，慢慢来是最快的。
个人主页：极客李华-CSDN博客
合作方式：私聊+
这个专栏内容：BAT等大厂常见后端java开发面试题详细讲解，更新数目100道常见大厂java后端开发面试题。
我的CSDN社区：https://bbs.csdn.net/forums/99eb3042821a4432868bb5bfc4d513a8
微信公众号，抖音，b站等平台统一叫做：极客李华，加入微信公众号领取各种编程资料，加入抖音，b站学习面试技巧，职业规划

Java使用POI操作Excel（把mysql数据库转换成Excel）

POI简介

简介
Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java对Microsoft Office格式档案读和写的功能。
常用的包

HSSF －提供读写Microsoft Excel XLS格式档案的功能。

XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。

HWPF －提供读写Microsoft Word DOC格式档案的功能。

HSLF －提供读写Microsoft PowerPoint格式档案的功能。

HDGF －提供读Microsoft Visio格式档案的功能。

HPBF －提供读Microsoft Publisher格式档案的功能。

HSMF －提供读Microsoft Outlook格式档案的功能。

引入的依赖

<!--xls-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <!--xlsx-->
        <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.10</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

代码编写

创建项目

在这里插入图片描述

删掉最开始的那个模块，父模块，然后就是这个样子了，

引入依赖

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>POIStudy</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <!--xlsx-->
        <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.10</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.8.1</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

</project>

创建工作蒲（写数据）

对于java操作Excel主要是操作这几个地方
工作薄
工作表
行
列
需要注意的是：2003 版本和 2007 版本存在兼容性的问题！03最多只有 65535 行！
在这里插入图片描述

03版本

文件结构
在这里插入图片描述
演示代码

public class Main {
    String path = "D:\\系统默认\\桌面\\博客素材\\POIDemo\\";
    @Test
    public void testExcel03() throws IOException{
        // 1.创建一个工作薄
        Workbook workbook = new HSSFWorkbook();

        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("极客李华的粉丝表");

        // 3.创建一个行（1,1）
        Row row1 = sheet.createRow(0);

        // 4.创建一个单元格
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("今日新增粉丝");

        // (1, 2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(233);

        // 创建第二行
        Row row2 = sheet.createRow(1);

        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        // 创建第三行单元格
        Cell cell22 = row2.createCell(1);
        // 格式化创建时间
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        // 新建一输出文件流
        FileOutputStream out = new FileOutputStream(path + "极客李华的观众统计表03.xls");

        // 把相应的Excel工作蒲存盘
        workbook.write(out);

        // 操作结束 关闭文件
        out.close();
        System.out.println("文件生成成功");
    }

}

这里就是生成的Excel文件
在这里插入图片描述

07版本

07版本相比于03版本它的不同之处在于，这个创建工作薄的时候使用的方法不一样，然后07版与03班的Excel文件的结尾也是不一样的，07是.xlsx。
项目结构
在这里插入图片描述

项目代码

@Test
    public void testExcel07() throws IOException{
        // 1.创建一个工作薄
        Workbook workbook = new XSSFWorkbook();

        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("极客李华的粉丝表");

        // 3.创建一个行（1,1）
        Row row1 = sheet.createRow(0);

        // 4.创建一个单元格
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("今日新增粉丝");

        // (1, 2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(233);

        // 创建第二行
        Row row2 = sheet.createRow(1);

        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        // 创建第三行单元格
        Cell cell22 = row2.createCell(1);
        // 格式化创建时间
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        // 新建一输出文件流
        FileOutputStream out = new FileOutputStream(path + "极客李华的观众统计表03.xls");

        // 把相应的Excel工作蒲存盘
        workbook.write(out);

        // 操作结束 关闭文件
        out.close();
        System.out.println("文件生成成功");
    }

运行结果
在这里插入图片描述

对比

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

演示代码
这里用的是HSSFWorkbook

@Test
    public void testWrite03BigData() throws IOException {
        //记录开始时间
        long begin = System.currentTimeMillis();
        //创建一个SXSSFWorkbook
        Workbook workbook = new HSSFWorkbook();
        //创建一个sheet
        Sheet sheet = workbook.createSheet();
        //xls文件最大支持65536行
        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);
            }
        }
        System.out.println("操作结束");
        FileOutputStream out = new FileOutputStream(path+"bigdata03.xls");
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }

运行结果
写满数据这个1s不到就写完了，还是很快的。
在这里插入图片描述

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

演示代码
这里用的是XSSFWorkbook

@Test
    public void testWrite07BigData() throws IOException {
        //记录开始时间
        long begin = System.currentTimeMillis();
        //创建一个XSSFWorkbook
        Workbook workbook = new XSSFWorkbook();
        //创建一个sheet
        Sheet sheet = workbook.createSheet();
        //xls文件最大支持65536行
        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);
            }
        }
        System.out.println("done");
        FileOutputStream out = new FileOutputStream(path+"bigdata07.xlsx");
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }

运行结果
这个结果花了近10s还是很慢的，虽然可以操作的数据变多了，但是时间消耗更多了
在这里插入图片描述

演示代码
这里用的是SXSSFWorkbook
项目结构
在这里插入图片描述

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

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

@Test
    public void testWrite07BigDataFast() throws IOException {
        //记录开始时间
        long begin = System.currentTimeMillis();
        //创建一个SXSSFWorkbook
        Workbook workbook = new SXSSFWorkbook();
        //创建一个sheet
        Sheet sheet = workbook.createSheet();
        //xls文件最大支持65536行
        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);
            }
        }
        System.out.println("done");
        FileOutputStream out = new FileOutputStream(path+"bigdata07-fast.xlsx");
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();
        //清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }

运行结果：
从结果看这个运行结果还是蛮快的
在这里插入图片描述

读工作蒲（读数据）

03版本

这里演示的是如何读取03版本的Excel

@Test
    public void testRead03() throws Exception{
        InputStream is = new FileInputStream(path+"极客李华的观众统计表03.xls");
        Workbook workbook = new HSSFWorkbook(is);
        // 这里是读取第一个工作薄
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        // 输出单元内容
        System.out.println(cell.getStringCellValue());
        // 操作结束，关闭文件
        is.close();
    }

运行结果
在这里插入图片描述

07版本

这里演示的是如何读取07版本的Excel
演示代码

@Test
    public void testRead07() throws Exception{
        InputStream is = new FileInputStream(path+"极客李华的观众统计表07.xlsx");
        Workbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        // 输出单元内容
        System.out.println(cell.getStringCellValue());
        // 操作结束，关闭文件
        is.close();
    }

运行结果
在这里插入图片描述

读这个Excel

在这里插入图片描述

@Test
    public void WriteExcel03() throws Exception {
        InputStream is = new FileInputStream(path + "会员消费商品明细表.xls");
        
        // 创建工作薄
        Workbook workbook = new HSSFWorkbook(is);
        
        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 读取标题所有内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {// 行不为空
            // 读取cell
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    int cellType = cell.getCellType();
                    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) {// 行不为空
// 读取cell
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    System.out.print("【" + (rowNum + 1) + "-" + (cellNum + 1) +
                            "】");
                    Cell cell = rowData.getCell(cellNum);
                    if (cell != null) {
                        int cellType = cell.getCellType();
//判断单元格数据类型
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                System.out.print("【STRING】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                System.out.print("【BOOLEAN】");
                                cellValue =
                                        String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空
                                System.out.print("【BLANK】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                System.out.print("【NUMERIC】");

                                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(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                System.out.print("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        is.close();
    }

运行结果
在这里插入图片描述

公式计算（这个东西了解即可没有很多应用）

现在要对这个Excel表格进行求和处理。
在这里插入图片描述
获取A5位置的公式

如果获取的单元格没有公式的话，那么就是打印的结果是没有结果的。

运行结果

@Test
    public void testFormula() throws Exception{
        InputStream is = new FileInputStream(path + "计算公式.xls");
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第五行第一列
        // 这个row获取的是
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        //公式计算器
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        // 输出单元内容
        int cellType = cell.getCellType();
        System.out.println(cellType);
        switch (cellType) {
            case Cell.CELL_TYPE_FORMULA://2
        //得到公式
                String formula = cell.getCellFormula();
                System.out.println(formula);
                CellValue evaluate = formulaEvaluator.evaluate(cell);

                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }

如果大家觉得有用的话，可以关注我下面的微信公众号，极客李华，我会在里面更新更多行业资讯，企业面试内容，编程资源，如何写出可以让大厂面试官眼前一亮的简历等内容，让大家更好学习编程，我的抖音，B站也叫极客李华。

极客李华

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
Java使用POI操作Excel

简介Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java对Microsoft Office格式档案读和写的功能。常用的包HSSF －提供读写Microsoft Excel XLS格式档案的功能。XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。HWPF －提供读写Microsoft Word DOC格式档案的功能。
复制链接

扫一扫