POI操作excel表格

最新推荐文章于 2024-02-28 18:56:45 发布

我哪会这个啊

最新推荐文章于 2024-02-28 18:56:45 发布

阅读量844

点赞数

分类专栏： easyexcel 和poi 文章标签： excel表格操作

本文链接：https://blog.csdn.net/qq_45243783/article/details/121776718

版权

easyexcel 和poi 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

在日常的公司业务需求中，经常需要接触到数据的导入导出，那摩就会借助很多第三方工具包，这里记录一下阿帕奇出品的poi,其使用场景有如下：

将用户信息导出为excel表格(导出表格…)
将Excel表中的信息录入到网站数据库（习题上传）大大减轻网站录入量

操作Excel目前比较流行的就是ApachePOI和阿里巴巴的easyExcel

POI提供API给java程序对Microsoft Office格式档案读写功能

基本功能

HSSF – 提供读写MicroSoftExcel格式档案的功能
XSSF – 提供读写MicrosoftExcel OOXML格式档案的功能
HWPF – 提供读写MicrosoftWord格式档案的功能
HSLF – 提供读写Microsoft PowerPoint 格式档案的功能
HDGF – 提供读写MicrosoftVisio格式档案的功能

内存问题：加入我们的表格有100M大小，POI会先将我们的表格加载到内存中，这个时候就有可能会导致内存溢出

下面演示操作POI

1.先建一个Maven项目，导入如下依赖

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

                <!--日期格式化工具-->
             <dependency>
                 <groupId>joda-time</groupId>
                 <artifactId>joda-time</artifactId>
                 <version>2.10.1</version>
             </dependency>

                <!--test-->
             <dependency>
                 <groupId>junit</groupId>
                 <artifactId>junit</artifactId>
                 <version>4.12</version>
             </dependency>

假设你对excel中的工作簿、工作表，单元格已经很熟悉了


public class ExcelWriteTest {

    String PATH = "E:\\poi\\";

    @Test   // 创建03版本的xls
    public void testWrite03() throws Exception {
        //1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        // 2 创建一个工作表
        Sheet sheet = workbook.createSheet("观众统计表03.xls");
        //3. 创建一个行
        Row row1 = sheet.createRow(0);
        // 4.创建一个单元格  (第一行第一列)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日新增观众");
        //第一行第二列（1,2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        //第二行 (2,1)
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        //(2,2)
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);


        //生成一张表(IO流)  03版本就是使用xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "观众统计表03.xls");

        //  工作簿写出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("观众表生成完毕");
    }


//  07版本
    @Test
    public void testWrite07() throws Exception {
        //1.创建一个工作簿  07版本 XSSF
        Workbook workbook = new XSSFWorkbook();
        // 2 创建一个工作表
        Sheet sheet = workbook.createSheet("观众统计表03.xls");
        //3. 创建一个行
        Row row1 = sheet.createRow(0);
        // 4.创建一个单元格  (第一行第一列)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日新增观众");
        //第一行第二列（1,2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        //第二行 (2,1)
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        //(2,2)
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);


        //生成一张表(IO流)  03版本就是使用xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "观众统计表07.xlsx");

        //  工作簿写出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();

        System.out.println("观众表生成完毕");
    }

这里需要注意一下对象的区别和文件后缀

当我们的数据量特别大的时候，比如有几百万条数据的时候，我们不可能一条条的写入，这个时候就需要做数据批量导入

大文件写HSSF(03版本)
缺点：最多只能处理65536行，否则会报错
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

      @Test    //   大量数据
    public void testWrite03BigData() throws Exception {
        //  当前 时间
        long begin = System.currentTimeMillis();
        // 创建一个簿
        Workbook workbook = new HSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        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("over");
        FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite03BigData03.xls");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);
    }

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

       @Test    //   大量数据  ,耗时较长
    public void testWrite07BigData() throws Exception {
        //  当前 时间
        long begin = System.currentTimeMillis();

        // 创建一个簿
        Workbook workbook = new XSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();

        //写入数据
        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("over");
        FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite07BigData07.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);
    }

大文件写SXSSF(也是针对07版本的优化)
优点：可以写非常大的数据量，如100万条甚至更多，写数据速度快，占用更少的内存

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

@Test    //   大量数据    SXSSF
    public void testWrite07BigDataS() throws Exception {
        //  当前 时间
        long begin = System.currentTimeMillis();
        // 创建一个簿
        Workbook workbook = new SXSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        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("over");
        FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        //清除零时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);
    }

SXFFWorkbook来自官方的解释：实现“BigGridDemo”策略的流式XSSFWorkbook版本，这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保留在内存中。

注意: 仍然可能会消耗掉大量内存，这些内存基于您正在使用的功能，例如合并区域，注释…任然只存储在内存中，因此如果广泛使用，可能需要大量内存。

POI-Excel读
同上一样对应着03版本和07版本

03版本

public class ExcelReadTest {

    String PATH= "E:\\poi\\";

        @Test
     public void  testRead03() throws Exception {

               //  获得文件流
            FileInputStream inputStream = new FileInputStream(PATH + "观众统计表03.xls");
                 // 1 创建一个工作簿  使用excel能操作的，这边都可以操作
            Workbook workbook = new HSSFWorkbook(inputStream);
                 //  2.得到表
            Sheet sheet = workbook.getSheetAt(0);
            // 得到行
            Row row = sheet.getRow(0);
                 //  得到列
            Cell cell = row.getCell(1);

                    // 读取值的时候，一定要注意类型
               // getStringCellValue()  ,字符串类型
           // System.out.println(cell.getStringCellValue());

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

07版本

@Test
    public void  testRead07() throws Exception {

        //  获得文件流
        FileInputStream inputStream = new FileInputStream(PATH + "观众统计表07.xlsX");
        // 1 创建一个工作簿  使用excel能操作的，这边都可以操作
        Workbook workbook = new XSSFWorkbook(inputStream);
        //  2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 得到行
        Row row = sheet.getRow(0);
        //  得到列
        Cell cell = row.getCell(1);

        // 读取值的时候，一定要注意类型
        // getStringCellValue()  ,字符串类型
        // System.out.println(cell.getStringCellValue());

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

注意获取值的类型即可

读取不同的类型，大概这里也是最麻烦的地方了，下面以一个表格为例，内容如下图所示

在这里插入图片描述

@Test
    public void testCellType() throws Exception {
        // 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "明细表.xls");
        //创建一个工作簿，使用excel能操作的这边他都可以操作
        Workbook workbook = new HSSFWorkbook(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);
                if (cell != null) {
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.println(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++) {
                    System.out.println("[" + (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.println("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:  // 布尔
                                System.out.println("[BOOLEAN]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:  // 空
                                System.out.println("[BLANK]");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:  // 数字 （日期，普通数字）
                                System.out.println("[NUMERIC]");
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {  //日期
                                    System.out.println("[日期]");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    //  不是日期格式，防止数字过长
                                    System.out.println("[转换为字符串输出]");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                System.out.println("[数据类型错误]");
                                break;
                        }

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

计算公式:有时候表格的某些列需要我们计算求值，这时候就需要用到计算公式

在这里插入图片描述

代码如下：

 @Test
    public void testFormula() throws Exception {
        FileInputStream inputStream = new FileInputStream(PATH + "公式.xls");
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        // 拿到计算书公式 eval
        FormulaEvaluator fFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

        //输出单元格的内容
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_FORMULA:  //公式
                String formula = cell.getCellFormula();
                System.out.println(formula);

                // 计算
                CellValue evaluate = fFormulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString(); // 将值转换为String格式
                System.out.println(cellValue);
                break;
        }

    }

我哪会这个啊

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
POI操作excel表格

在日常的公司业务需求中，经常需要接触到数据的导入导出，那摩就会借助很多第三方工具包，这里记录一下阿帕奇出品的poi,其使用场景有如下：将用户信息导出为excel表格(导出表格…)将Excel表中的信息录入到网站数据库（习题上传）大大减轻网站录入量操作Excel目前比较流行的就是ApachePOI和阿里巴巴的easyExcelPOI提供API给java程序对Microsoft Office格式档案读写功能基本功能HSSF – 提供读写MicroSoftExcel格式档案的功能XSSF –
复制链接

扫一扫