Java使用POI操作Excel

GeekLiHua

发布于 2025-01-21 20:03:34

13400

代码可运行

文章被收录于专栏：JavaJava

运行总次数：0

代码可运行

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-02-01，如有侵权请联系 cloudcommunity@tencent.com 删除

java