Apache POI 实现用Java操作Excel完成读写操作

本文涉及的产品
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: Apache POI是一个用于操作Microsoft Office格式文件(包括xls、docx、xlsx、pptx等)的Java API库。POI全称为Poor Obfuscation Implementation,是Apache Software Foundation的一个开源项目。它提供了一组Java API,使得Java程序可以读取、写入和操作Microsoft Office格式文件。

简介

      image.png



Apache POI是一个用于操作Microsoft Office格式文件(包括xls、docx、xlsx、pptx等)的Java API库。POI全称为Poor Obfuscation Implementation,是Apache Software Foundation的一个开源项目。它提供了一组Java API,使得Java程序可以读取、写入和操作Microsoft Office格式文件。

具体来说,POI提供了以下几种主要的组件:


HSSF:用于读写Excel 97-2003格式的xls文件。

XSSF:用于读写Excel 2007格式的xlsx文件。

SXSSF:是Apache POI中用于处理大量数据的API,它基于XSSF,可以处理Excel 2007及以上版本的xlsx文件。SXSSF的特点在于它可以将大量数据分成多个部分进行处理,从而减少内存的占用,提高处理大量数据的效率。

HWPF:用于读写Word 97-2003格式的doc文件。

XWPF:用于读写Word 2007格式的docx文件。

HSLF:用于读写PowerPoint 97-2003格式的ppt文件。

XSLF:用于读写PowerPoint 2007格式的pptx文件。

这里先介绍使用Apache POI 如何操作excel


环境准备

Maven仓库


jdk1.8

poi-3.9

poi-ooxml-3.9

poi-ooxml-schemas-3.9

xmlbeans-2.6.0

junit4.12

joda-time-2.10.1

写入Excel文件

Excel 97-2003 和 2007的区别?

文件格式不同:

Excel 97-2003使用的是 .xls格式,.xls格式采用二进制格式存储数据,

而Excel 2007使用的是 .xlsx格式。而.xlsx格式采用了基于XML的压缩文件格式。

大小限制不同:

Excel 97-2003的工作表大小限制为65536行、256列。

Excel 2007的工作表大小限制为1048576行、16384列。

API介绍

Workbook:表示一个Excel工作簿,包括多个Sheet,提供了创建Sheet、读取Sheet、写入Sheet等方法。

Sheet:表示一个Excel工作表,包括多个Row,提供了读取Row、写入Row、创建Cell等方法。

Row:表示Excel工作表中的一行,包括多个Cell,提供了读取Cell、写入Cell等方法。

Cell:表示Excel工作表中的一个单元格,提供了读取单元格值、写入单元格值、设置单元格样式等方法。

小文件写入

注意:代码中的 PATH 是声明的静态类变量,是基础的文件路径

image.png

static String PATH = "D:\\poi\\";

03版本-小文件写入

//03版本-小数据写入
    @Test
    public void testWrite03() throws IOException {
        /**
         * 1.创建一个03版的工作簿
         *  HSSF-03版本
         *  XSSF=07版本
         *  SXSSF-加速处理07版本
         */
        Workbook workbook = new HSSFWorkbook();
        /**
         * 2.创建一个工作表
         *  通过工作簿来新建工作表,因为工作簿在工作表之上
         */
        Sheet sheet = workbook.createSheet("表1");
        /**
         * 3.创建一个行
         *  通过表创建行,因为表在行之上
         *  0代表第一行
         */
        Row row1 = sheet.createRow(0);
        /**
         * 4.创建单元格
         * 通过行创建单元格
         */
        Cell cell1_1 = row1.createCell(0);//第一行第一列
        cell1_1.setCellValue("id");
        Cell cell1_2 = row1.createCell(1);//第一行第二列
        cell1_2.setCellValue("name");
        Cell cell1_3 = row1.createCell(2);//第一行第三列
        cell1_3.setCellValue("birth");
        //第二行
        Row row2 = sheet.createRow(1);
        Cell cell2_1 = row2.createCell(0);//第二行第一列
        cell2_1.setCellValue(1);
        Cell cell2_2 = row2.createCell(1);//第二行第二列
        cell2_2.setCellValue("嘴哥");
        Cell cell2_3 = row2.createCell(2);//第一行第三列
        //使用 joda包创建时间
        String time = new DateTime().toString("yyyy-MM-dd");
        cell2_3.setCellValue(time);
        //生成一张表 03版本用 xls 结尾
        File file = new File(PATH+"03_1.xls");
        FileOutputStream outputStream = new FileOutputStream(file);
        //输出到本地
        workbook.write(outputStream);
        //关闭流
        outputStream.close();
        System.out.println("文件生成完毕!");
    }

运行结果

image.png



07版本-小文件写入

//07版本-小数据写入
    @Test
    public void testWrite07() throws IOException {
        /**
         * 1.创建一个03版的工作簿
         *  HSSF-03版本
         *  XSSF=07版本
         *  SXSSF-加速处理07版本
         */
        Workbook workbook = new XSSFWorkbook();
        /**
         * 2.创建一个工作表
         *  通过工作簿来新建工作表,因为工作簿在工作表之上
         */
        Sheet sheet = workbook.createSheet("表1");
        /**
         * 3.创建一个行
         *  通过表创建行,因为表在行之上
         *  0代表第一行
         */
        Row row1 = sheet.createRow(0);
        /**
         * 4.创建单元格
         * 通过行创建单元格
         */
        Cell cell1_1 = row1.createCell(0);//第一行第一列
        cell1_1.setCellValue("id");
        Cell cell1_2 = row1.createCell(1);//第一行第二列
        cell1_2.setCellValue("name");
        Cell cell1_3 = row1.createCell(2);//第一行第三列
        cell1_3.setCellValue("birth");
        //第二行
        Row row2 = sheet.createRow(1);
        Cell cell2_1 = row2.createCell(0);//第二行第一列
        cell2_1.setCellValue(1);
        Cell cell2_2 = row2.createCell(1);//第二行第二列
        cell2_2.setCellValue("嘴哥");
        Cell cell2_3 = row2.createCell(2);//第一行第三列
        //使用 joda包创建时间
        String time = new DateTime().toString("yyyy-MM-dd");
        cell2_3.setCellValue(time);
        //生成一张表 03版本用 xls 结尾
        File file = new File(PATH+"03_1.xlsx");
        FileOutputStream outputStream = new FileOutputStream(file);
        //输出到本地
        workbook.write(outputStream);
        //关闭流
        outputStream.close();
        System.out.println("文件生成完毕!");
    }


运行结果


image.png


大文件写入

03版本-大文件写入

//03版本-大数据写入65536行 1400ms
    @Test
    public void testWrite03BigData() throws IOException {
        //开始时间
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNums = 0; rowNums < 65536; rowNums++) {
            Row row = sheet.createRow(rowNums);
            for (int cellNums = 0; cellNums < 10; cellNums++) {
                Cell cell = row.createCell(cellNums);
                cell.setCellValue(cellNums);
            }
        }
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(new File(PATH + "bigData03.xls"));
        workbook.write(outputStream);
        outputStream.close();
        //结束时间
        long end = System.currentTimeMillis();
        System.out.println("总用时 "+(end-begin)+"ms");
    }


07版本-大文件写入

//07版本-大数据写入65537行 6856ms
    @Test
    public void testWrite07BigData() throws IOException {
        //开始时间
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNums = 0; rowNums < 65537; rowNums++) {
            Row row = sheet.createRow(rowNums);
            for (int cellNums = 0; cellNums < 10; cellNums++) {
                Cell cell = row.createCell(cellNums);
                cell.setCellValue(cellNums);
            }
        }
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(new File(PATH + "bigData03.xlsx"));
        workbook.write(outputStream);
        outputStream.close();
        //结束时间
        long end = System.currentTimeMillis();
        System.out.println("总用时 "+(end-begin)+"ms");
    }


运行结果


07升级版-大文件写入

//07升级版-大数据写入10w行 1814ms
    @Test
    public void testWrite07BigDataS() throws IOException {
        //开始时间
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new SXSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNums = 0; rowNums < 100000; rowNums++) {
            Row row = sheet.createRow(rowNums);
            for (int cellNums = 0; cellNums < 10; cellNums++) {
                Cell cell = row.createCell(cellNums);
                cell.setCellValue(cellNums);
            }
        }
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(new File(PATH + "bigData03S.xlsx"));
        workbook.write(outputStream);
        //关闭资源
        outputStream.close();
        //关闭临时文件
        ((SXSSFWorkbook)workbook).dispose();
        //结束时间
        long end = System.currentTimeMillis();
        System.out.println("总用时 "+(end-begin)+"ms");
    }

image.png

总结

HSSF:适用于读写Excel 97-2003格式的xls文件,可以处理一般大小的数据,缺点是处理大量数据时,会占用大量内存,导致程序运行较慢。

XSSF:适用于读写Excel 2007及以上版本的xlsx文件,支持更多的行列数、更好的样式支持等,但处理大量数据时,仍然会占用大量内存,不适合处理大数据量的情况。

SXSSF:基于XSSF,适用于处理大量数据,可以将大量数据分成多个部分进行处理,从而减少内存的占用,提高处理大量数据的效率。但不支持公式计算等高级功能。

综上,选择API应根据具体的需求和数据量来决定。如果处理的数据量较小,可以选择HSSF或XSSF;如果需要处理大量数据,可以选择SXSSF。如果需要同时兼顾处理大量数据和高级功能,可以考虑使用HSSF或XSSF与SXSSF结合的方式来处理数据。


03版-文件读取

@Test
    public void testRead03() throws IOException {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(new File(PATH+"03_1.xls"));
        //1.创建一个工作簿,使用excel能操作的,它都可以操作
        Workbook workbook = new HSSFWorkbook(inputStream);
        //2.获取表
        Sheet sheet0 = workbook.getSheetAt(0);
        //3.得到行
        Row row = sheet0.getRow(1);//获取第1行
        //4.得到列
        Cell cell = row.getCell(0);//获取第一行第一列
        //以Number形式输出-可以选择格式
        //必须对应格式-number类型不可以转为String类型!!
        System.out.println(cell.getNumericCellValue());
        //关闭流资源
        inputStream.close();
    }


07版本-文件读取

@Test
    public void testRead07() throws IOException {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(new File(PATH+"03_1.xlsx"));
        //1.创建一个工作簿,使用excel能操作的,它都可以操作
        Workbook workbook = new XSSFWorkbook(inputStream);
        //2.获取表
        Sheet sheet0 = workbook.getSheetAt(0);
        //3.得到行
        Row row = sheet0.getRow(1);//获取第1行
        //4.得到列
        Cell cell = row.getCell(0);//获取第一行第一列
        //以Number形式输出-可以选择格式
        //必须对应格式-number类型不可以转为String类型!!
        System.out.println(cell.getNumericCellValue());
        //关闭流资源
        inputStream.close();
    }


不同类型数据的读取

针对字符串、数值类型、日期等需要不同的处理方式。

//测试读取不同类型的数据 03版本
    @Test
    public void testCellType() throws IOException {
        FileInputStream inputStream = new FileInputStream(new File(PATH+"test03.xls"));
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //读取标题内容
        Row title = sheet.getRow(0);
        if (title != null){
            int columns = title.getPhysicalNumberOfCells();//列数
            for (int cellNum = 0; cellNum < columns; cellNum++) {
                Cell cell = title.getCell(cellNum);
                if (cell != null){
                    int cellType = cell.getCellType();//我们已知为String
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+" | ");
                }
            }
            System.out.println();
        }
        //读取表中的内容
        int rows = sheet.getPhysicalNumberOfRows();//行数
        for (int rowNum = 1; rowNum < rows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null){
                //读取行中的列
                int columns = title.getPhysicalNumberOfCells();
                for (int col = 0; col < columns; col++) {
                    System.out.print("["+(rowNum+1)+"-"+(col+1)+"]");
                    Cell cell = row.getCell(col);
                    //匹配列的数据类型
                    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_NUMERIC://数字(日期、数字)
                                if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    System.out.print("[DATE]");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else{
                                    System.out.print("[NUMBER]");
                                    //防止数字过长,转为String
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空
                                System.out.print("[NULL]");
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                System.out.print("[BOOLEAN]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                            case HSSFCell.CELL_TYPE_ERROR:
                                System.out.print("[ERROR]");
                                cellValue = String.valueOf(cell.getErrorCellValue());
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        inputStream.close();
    }
相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
相关文章
|
1月前
|
Java API Apache
Java编程如何读取Word文档里的Excel表格,并在保存文本内容时保留表格的样式?
【10月更文挑战第29天】Java编程如何读取Word文档里的Excel表格,并在保存文本内容时保留表格的样式?
142 5
|
1月前
|
Java BI API
Java Excel报表生成:JXLS库的高效应用
在Java应用开发中,经常需要将数据导出到Excel文件中,以便于数据的分析和共享。JXLS库是一个强大的工具,它基于Apache POI,提供了一种简单而高效的方式来生成Excel报表。本文将详细介绍JXLS库的使用方法和技巧,帮助你快速掌握Java中的Excel导出功能。
72 6
|
1月前
|
Java API Apache
|
1月前
|
存储 Java API
Java实现导出多个excel表打包到zip文件中,供客户端另存为窗口下载
Java实现导出多个excel表打包到zip文件中,供客户端另存为窗口下载
76 4
|
2月前
|
XML JavaScript Java
java与XML文件的读写
java与XML文件的读写
31 3
|
2月前
|
前端开发 JavaScript Java
导出excel的两个方式:前端vue+XLSX 导出excel,vue+后端POI 导出excel,并进行分析、比较
这篇文章介绍了使用前端Vue框架结合XLSX库和后端结合Apache POI库导出Excel文件的两种方法,并对比分析了它们的优缺点。
949 0
|
3月前
|
存储 Java
java的Excel导出,数组与业务字典匹配并去掉最后一个逗号
java的Excel导出,数组与业务字典匹配并去掉最后一个逗号
64 2
|
2月前
|
Java Apache
Apache POI java对excel表格进行操作(读、写) 有代码!!!
文章提供了使用Apache POI库在Java中创建和读取Excel文件的详细代码示例,包括写入数据到Excel和从Excel读取数据的方法。
74 0
|
4月前
|
消息中间件 Java Kafka
【Azure 事件中心】在微软云中国区 (Mooncake) 上实验以Apache Kafka协议方式发送/接受Event Hubs消息 (Java版)
【Azure 事件中心】在微软云中国区 (Mooncake) 上实验以Apache Kafka协议方式发送/接受Event Hubs消息 (Java版)
118 1
|
4月前
|
存储 Java Apache

推荐镜像

更多