Excel POI(了解,推荐使用阿里开源 EasyExcel)

最新推荐文章于 2024-09-03 20:52:56 发布

yzhSWJ

最新推荐文章于 2024-09-03 20:52:56 发布

阅读量1.3k

点赞数

分类专栏： java excel

本文链接：https://blog.csdn.net/yuzheh521/article/details/105968783

版权

java 同时被 2 个专栏收录

108 篇文章 3 订阅

订阅专栏

excel

11 篇文章 0 订阅

订阅专栏

一、POI概述

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。(相对比较麻烦,数据量大可能会报OOM(内存用完异常))

结构：
HSSF －提供读写Microsoft Excel格式档案的功能。
XSSF －提供读写Microsoft Excel OOXML格式档案的功能。

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

操作Excel文件区分版本：
2003版本（包含2003）以前的扩展名为.xls需要用HSSFWorkbook类操作
2007版本（包含2007）以后的扩展名为.xlsx需要用XSSFWorkbook类操作

excel 对应的对象:
工作簿
工作表
行
列

依赖

<dependencies>
        <!--导入依赖-->
        <!--旧版本支持,eg .xls Excel(03)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!--新版本支持,eg .xlsx Excel(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>
        <!--text-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>

        </dependency>

    </dependencies>

2003版本（包含2003）以前的扩展名为.xls需要用HSSFWorkbook类操作

public class ExcelWriteTest {
   private static final String PATH = "D:\\IDEA_Project\\Excel_POI_Easy\\excel-poi\\";
    private FileOutputStream fileOutputStream;
    //03版本
    @Test
    public void ExcelVersion03() throws IOException {
        //1.创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建工作表
        Sheet sheet = workbook.createSheet("swj521");
        //3.创建单元格第一行
        Row row1 = sheet.createRow(0);
        //4.单元格第一行第一列
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("swj521521");
        //第一行第二列单元格
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("yzh521swj");
        //创建单元格第二行
        Row row2 = sheet.createRow(1);
        //第二行第一列
        Cell row2Cell = row2.createCell(0);
        row2Cell.setCellValue("swj521yzh");
        //第二行第二列
        Cell row2Cell2 = row2.createCell(1);
        row2Cell2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
        //5.生成一张表(IO流),03版本的用xls结尾
        try {
        fileOutputStream = new FileOutputStream(PATH+"yzh521swj03表.xls");
            workbook.write(fileOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            fileOutputStream.close();
        }
        System.out.println("yzh521swj03表生成成功");

    }

}

2007版本（包含2007）以后的扩展名为.xlsx需要用XSSFWorkbook类操作

public class ExcelWriteTest2 {
    private static final String PATH = "D:\\IDEA_Project\\Excel_POI_Easy\\excel-poi\\";
    private FileOutputStream fileOutputStream;
    //03版本
    @Test
    public void ExcelVersion03() throws IOException {
        //1.创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建工作表
        Sheet sheet = workbook.createSheet("swj521yzh");
        //3.创建单元格第一行
        Row row1 = sheet.createRow(0);
        //4.单元格第一行第一列
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("swj521521");
        //第一行第二列单元格
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("yzh521swj");
        //创建单元格第二行
        Row row2 = sheet.createRow(1);
        //第二行第一列
        Cell row2Cell = row2.createCell(0);
        row2Cell.setCellValue("swj521yzh");
        //第二行第二列
        Cell row2Cell2 = row2.createCell(1);
        row2Cell2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
        //5.生成一张表(IO流),03版本的用xlsx结尾
        try {
            fileOutputStream = new FileOutputStream(PATH+"yzh521swj07表.xlsx");
            workbook.write(fileOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            fileOutputStream.close();
        }
        System.out.println("yzh521swj07表生成成功");

    }
}

1.大文件写HSSF

缺点**😗*最多只能处理65536行，否则会抛出异常

优点**😗*过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

ava.lang.IllegalArgumentException: Invalid row number (65536) outsideallowable range (0..6553

      //03版本大量数据
    @Test
    public void ExcelVersion03BigData() throws IOException {
        FileOutputStream fileOutputStream1 = null;
        //1.创建工作簿
        Workbook hssfWorkbook = new HSSFWorkbook();
        //2.创建工作表
        Sheet sheet = hssfWorkbook.createSheet();
        //3.写入数据
        for (int rows = 0; rows < 65536; rows++) {
            Row row = sheet.createRow(rows);
            for (int cell = 0; cell < 10; cell++) {
                Cell cell1 = row.createCell(cell);
                cell1.setCellValue(cell);
            }
        }
        System.out.println("over");
        try {
            fileOutputStream1 = new FileOutputStream(PATH + "Excel03BigData.xls");
            hssfWorkbook.write(fileOutputStream1);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            fileOutputStream1.close();
        }

    }

2.大文件写XSSF

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

//07版本大量数据
    @Test
    public void ExcelVersion07BigData() throws IOException {
        FileOutputStream fileOutputStream1 = null;
        //1.创建工作簿
        Workbook Workbook = new XSSFWorkbook();
        //2.创建工作表
        Sheet sheet = Workbook.createSheet();
        //3.写入数据
        for (int rows = 0; rows < 100000; rows++) {
            Row row = sheet.createRow(rows);
            for (int cell = 0; cell < 10; cell++) {
                Cell cell1 = row.createCell(cell);
                cell1.setCellValue(cell);
            }
        }
        System.out.println("over");
        try {
            //4.创建表
            fileOutputStream1 = new FileOutputStream(PATH + "Excel07BigData.xlsx");
            //5.导出表
            Workbook.write(fileOutputStream1);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            //6.关闭流
            fileOutputStream1.close();
        }

    }

2.大文件写SXSSF

优点**😗*可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存注意：过程中会产生临时文件，需要清理临时文件默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook ( 数量 )

//07版本大量数据升级版解决速度慢
    @Test
    public void ExcelVersion07BigDataS() throws IOException {
        FileOutputStream fileOutputStream1 = null;
        //1.创建工作簿
        Workbook Workbook = new SXSSFWorkbook();
        //2.创建工作表
        Sheet sheet = Workbook.createSheet();
        //3.写入数据
        for (int rows = 0; rows < 100000; rows++) {
            Row row = sheet.createRow(rows);
            for (int cell = 0; cell < 10; cell++) {
                Cell cell1 = row.createCell(cell);
                cell1.setCellValue(cell);
            }
        }
        System.out.println("over");
        try {
            //4.创建表
            fileOutputStream1 = new FileOutputStream(PATH + "Excel07BigDataS.xlsx");
            //5.导出表
            Workbook.write(fileOutputStream1);
            //6.清除临时文件(多态)
            ((SXSSFWorkbook) Workbook).dispose();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            //6.关闭流
            fileOutputStream1.close();
        }

    }

4、POI-Excel读

03版本

public class ExcelReadTest {
    private static final String PATH = "D:\\IDEA_Project\\Excel_POI_Easy\\excel-poi\\";

    @Test
    public void testRead03() throws IOException {
        FileInputStream inputStream = new FileInputStream(PATH + "yzh521swj03表.xls");
        //1.创建一个工作簿,对excel的所有操作都可以
        Workbook workbook = new HSSFWorkbook(inputStream);
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        // 输出单元内容
        //读取值得时候注意数据类型
        //cell.getStringCellValue()字符串类型
        System.out.println(cell.getStringCellValue());//获取字符串
        // 操作结束，关闭文件
        inputStream.close();
    }
}

07版本

public class ExcelReadTest {
    private static final String PATH = "D:\\IDEA_Project\\Excel_POI_Easy\\excel-poi\\";

    @Test
    public void testRead07() throws IOException {
        FileInputStream inputStream = new FileInputStream(PATH + "yzh521swj07表.xlsx");
        //1.创建一个工作簿,对excel的所有操作都可以
        Workbook workbook = new XSSFWorkbook(inputStream);
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        // 输出单元内容
        //读取值得时候注意数据类型
        //cell.getStringCellValue()字符串类型
        System.out.println(cell.getStringCellValue());//获取字符串
        // 操作结束，关闭文件
        inputStream.close();
    }
}

读取不同的数据类型

public class ExcelReadTest {
    private static final String PATH = "D:\\IDEA_Project\\Excel_POI_Easy\\excel-poi\\";

    @Test
    public void testRead07() throws IOException {
        FileInputStream inputStream = new FileInputStream(PATH + "yzh521swj03表.xls");
        //1.创建一个工作簿,对excel的所有操作都可以
        Workbook workbook = new HSSFWorkbook(inputStream);
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第一行(获取标题内容)
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            //读取这一行的所有内容(包括这一行有多少列有数据)
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            //读取所有的列
            for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
                Cell cell = rowTitle.getCell(cellNumber);
                if (cell != null) {
                    //获取类型
                    int cellType = cell.getCellType();
                    //读取内容(已知是string类型)
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue + "|");
                }
            }
            System.out.println();
        }
        //获取表多少行
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            //读每一行
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                //获取每一行多少列(单元格)
                int cells = rowTitle.getPhysicalNumberOfCells();
                //从第二行开始读
                for (int cellNum = 0; cellNum < cells; 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);
                    }

                }
            }


        }
        inputStream.close();
    }
}

标题计算公式

 @Test
    public void excelTestFormula() throws Exception {
        FileInputStream fileInputStream = new FileInputStream(PATH + "计算公式.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第2行第一列
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(0);
        //公式计算器
        FormulaEvaluator formulaEvaluator = 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 = formulaEvaluator.evaluate(cell);
        
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }