使用POI操作Excel

使用POI对Excel进行读、写操作

什么是POI？

基本功能？

POI-Excel 写

主要步骤：①创建工作簿; ②创建工作表; ③创建行; ④创建列

(1) 在项目中引入pom依赖

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



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

<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.11</version>
  <scope>test</scope>
</dependency>

03版本：

//构建路径
String PATH="E:\workpaceid\Excel_test\poi_test";

/*测试Excel03版本*/
@Test
public void testWrite03() throws Exception {
    //1.创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    //2.创建一个工作表
    Sheet sheet=workbook.createSheet("信息表");
    //3.创建一个行
    Row row1=sheet.createRow(0);
    //4.创建一个单元格 (构成了 A,1 这个单元格)
    Cell cellA1 = row1.createCell(0);
    //5.向单元格中填写数据
     cellA1.setCellValue("姓名");
        //(构成 A,2 坐标)
    Cell cellB1 = row1.createCell(1);
    cellB1.setCellValue("时间");
        //创建第二行并传入数据
     Row row2 =sheet.createRow(1);
     Cell cellA2 = row2.createCell(0);
     cellA2.setCellValue("张三");
     Cell cellB2 = row2.createCell(1);
     String time= new DateTime().toString("yyyy-MM-DD");
     cellB2.setCellValue(time);

    //6.生成一张表（IO 流） 03版本后缀名使用xls
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "信息表03.xls");
    //通过流输出输出
    workbook.write(fileOutputStream);
    //关闭流
    fileOutputStream.close();
    System.out.println("信息表03生成完毕！");
}

07版本（生成方法和03版本一样）

区别在于对象的不同文件后缀名的不同

/*测试Excel07版本*/
@Test
public void testWrite07() throws Exception {
    //1.创建一个工作簿使用excel操作的 workBook都能操作
    Workbook workbook = new XSSFWorkbook();
    //2.创建一个工作表表中的设置
    Sheet sheet=workbook.createSheet("信息表");
    //3.创建一个行
    Row row1=sheet.createRow(0);
    //4.创建一个单元格 (构成了 A,1 这个单元格)
    Cell cellA1 = row1.createCell(0);
    //5.向单元格中填写数据
    cellA1.setCellValue("姓名");
    //(构成 A,2 坐标)
    Cell cellB1 = row1.createCell(1);
    cellB1.setCellValue("时间");
    //创建第二行并传入数据
    Row row2 =sheet.createRow(1);
    Cell cellA2 = row2.createCell(0);
    cellA2.setCellValue("李四");
    Cell cellB2 = row2.createCell(1);
    String time= new DateTime().toString("yyyy-MM-DD");
    cellB2.setCellValue(time);

    //6.生成一张表（IO 流） 03版本后缀名使用xls
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "信息表07.xlsx");
    //通过流输出输出
    workbook.write(fileOutputStream);
    //关闭流
    fileOutputStream.close();
    System.out.println("信息表07生成完毕！");
}

大文件写HSSF（03版）

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

缺点：最多下入65536行，否则会抛出异常

/**
*测试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++){        //RowNum 行数
        Row row = sheet.createRow(RowNum);          //每一行
        for ( int CellNum=0;CellNum<10;CellNum++){
            Cell cell = row.createCell(CellNum);     //每一个单元格
            cell.setCellValue(CellNum+1);
        }
    }
    System.out.println("over!");
    //生成一张表
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData03.xls");
    //输出
    workbook.write(fileOutputStream);
    //关流
    fileOutputStream.close();
    //结束时间
    long end=System.currentTimeMillis();
    System.out.println((double)(end-begin)/1000);
}

大文件写XSSF（07版）

优点：可以写入大量的数据

缺点：写数据的速度慢，消耗内存，如超过100万条数据，也会发生内存溢出现象

/**
*测试07版本大数据写入
* 优点：可以写入较大的数据量
* 缺点：写数据的速度慢，消耗内存，如超过100万条数据，也会发生内存溢出现象
*/
@Test
public void testWrite07BigData() throws Exception {
    //计算时间开始时间
    long begin=System.currentTimeMillis();
    //创建工作薄
    Workbook workbook=new XSSFWorkbook();
    //创建表
    Sheet sheet = workbook.createSheet();
    //写入数据
    for (int RowNum=0;RowNum<65537;RowNum++){        //RowNum 行数
        Row row = sheet.createRow(RowNum);          //每一行
        for ( int CellNum=0;CellNum<10;CellNum++){
            Cell cell = row.createCell(CellNum);     //每一个单元格
            cell.setCellValue(CellNum+1);
        }
    }
    System.out.println("over!");
    //生成一张表
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData07.xlsx");
    //输出
    workbook.write(fileOutputStream);
    //关流
    fileOutputStream.close();
    //结束时间
    long end=System.currentTimeMillis();
    System.out.println((double)(end-begin)/1000);

}

大文件写SXSSF

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

注意：过程中会产生临时文件，需要清理临时文件；

默认有100条数据会被存入内存中，如果超过这个数量，则最前面的数据会被写入临时文件；

自定义内存中的数量，可以使用new SXSSFWorkBook（数量）。

/**
*测试大文件写SXSSF
*优点：可以写入非常大的数据量，如100万条甚至更多，写数据数据快，占用更少内存
* 注意：过程中会产生临时文件，需要清理临时文件
*       默认有100条数据被存入内存中，如果超过这个数量，则最前面的数据被写入临时文件
*       自定义内存中的数量，可以使用new SXXFWorkBook（数量）
*/
@Test
public void testWrite07BigDatas() throws Exception {
    //计算时间开始时间
    long begin=System.currentTimeMillis();
    //创建工作薄
    Workbook workbook=new SXSSFWorkbook();
    //创建表
    Sheet sheet = workbook.createSheet();
    //写入数据
    for (int RowNum=0;RowNum<65535;RowNum++){        //RowNum 行数
        Row row = sheet.createRow(RowNum);          //每一行
        for ( int CellNum=0;CellNum<10;CellNum++){
            Cell cell = row.createCell(CellNum);     //每一个单元格
            cell.setCellValue(CellNum+1);
        }
    }
    System.out.println("over!");
    //生成一张表
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData07S.xlsx");
    //输出
    workbook.write(fileOutputStream);
    //关流
    fileOutputStream.close();
    //清除临时文件
    ((SXSSFWorkbook)workbook).dispose();
    //结束时间
    long end=System.currentTimeMillis();
    System.out.println((double)(end-begin)/1000);
}

POI-Excel 读

03版本读

在读取Excel中的数据是需要注意获取值的类型

/*测试03版本读取*/
@Test
public void TestRead03() throws Exception {

    //1.获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH+"poi_test信息表03.xls");
    //2.读取工作簿中的内容
    Workbook workbook=new HSSFWorkbook(fileInputStream);
    //3.获取工作中的表
    Sheet sheetAt = workbook.getSheetAt(0); //根据下标获取也可根据表名查找
    //4.获取表中的行
    Row row1 = sheetAt.getRow(0);
    //5.获取单元格
    Cell cellA1 = row1.getCell(0);
    //6.取出单元格中的值
    // 读取值的时候需要注意表中数据的类型
    System.out.println(cellA1.getStringCellValue());   //getStringCellValue() 获取的是制字符串类型
    //关闭流
    fileInputStream.close();
}

07版本读

/*测试07版本读取*/
@Test
public void TestRead07() throws Exception {

    //1.获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH+"poi_test信息表07.xlsx");

    //2.读取工作簿中的内容
    Workbook workbook=new XSSFWorkbook(fileInputStream);

    //3.获取工作簿中的表
    Sheet sheetAt = workbook.getSheetAt(0);

    //4.获取表中的行
    Row row2 = sheetAt.getRow(1);

    //5.获取单元格
    Cell cellB2 = row2.getCell(1);

    //6.取出单元格中的值
    System.out.println(cellB2.getStringCellValue());   //getStringCellValue() 获取的是制字符串类型

    //关闭流
    fileInputStream.close();
}

获取Excel中不同类型的值

/*读取不同类型的数据*/
@Test
public void testCellType() throws Exception {
    //1.获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH+"明细表.xls");
    //2.读取工作簿中的内容
    Workbook workbook=new HSSFWorkbook(fileInputStream);
    //3.获取工作簿中的表
    Sheet sheetAt = workbook.getSheetAt(0);
    //4.获取标题内容
    Row rowTitle = sheetAt.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.print(cellValue+" | ");
            }
        }
        System.out.println();
    }
    //5.获取表中的内容
        //获取所有行数
    int rowCount= sheetAt.getPhysicalNumberOfRows();
    for(int rowNum=1;rowNum<rowCount;rowNum++){
        //获取行
        Row rowData = sheetAt.getRow(rowNum);
        //判断获取到每一行是否为空
        if(rowData!=null){
            //读取列
            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 cellVaule="";
                     switch (cellType){
                         case HSSFCell.CELL_TYPE_STRING:   //为字符串类型
                             System.out.print("[String]");
                             //获取值
                              cellVaule = cell.getStringCellValue();
                              break;
                         case HSSFCell.CELL_TYPE_BOOLEAN:   //为布尔类型
                             System.out.print("[Boolean]");
                             //获取值
                             cellVaule = 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();
                                 cellVaule = new DateTime(date).toString("yyyy-MM-dd");
                             }else{                                               //为普通数字
                                 //如果不是日期，防止数字过长(科学计数法)，转换成字符串输出
                                 System.out.print("[数字]");
                                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                 cellVaule=cell.toString();
                             }
                             break;
                         case HSSFCell.CELL_TYPE_ERROR:   //数据类型错误
                             System.out.print("[数据类型错误]");
                             break;
                     }
                     System.out.println(cellVaule);
                 }
            }
        }
    }
    //关流
    fileInputStream.close();
}

POI-Excel 计算公式

public class TestFormula {
   //路径变量
    String PATH="E:\workpaceid\Excel_test\poi_test\";
    /*测试计算公式*/
    @Test
    public void testFormula() throws Exception {

        //1.创建流
        FileInputStream fileInputStream = new FileInputStream(PATH+"计算公式.xls");
        //2.获取工作簿
        Workbook Workbook = new HSSFWorkbook(fileInputStream);
        //3.获取表
        Sheet sheetAt = Workbook.getSheetAt(0);
        //4.获取计算公式的单元格
        Row row = sheetAt.getRow(5);
        Cell cell = row.getCell(0);
        //5.拿到计算公式eval
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)Workbook);
        //6.输出单元格的公式
            //获取类型
        int cellType = cell.getCellType();
            //判断
        switch (cellType){
            case Cell.CELL_TYPE_FORMULA:   //公式
                String cellFormula = cell.getCellFormula();
                //进行计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                //得到计算结果的值
                String value = evaluate.formatAsString();
                System.out.println(value);
                break;
        }
    }
}

相关阅读:
「AtCoder AGC023F」01 on Tree
「Wallace 笔记」平面最近点对解法汇总
「Codeforces 1181E」A Story of One Country (Easy & Hard)
「NOI2018」「LOJ #2720」「Luogu P4770」你的名字
IdentityServer4设置RefreshTokenExpiration=Sliding不生效的原因
【知识点】IQueryable.SumAsync方法的NULL异常
Beyond Compare 4 密钥被吊销
【知识点】Uri对象的完整地址
git文件夹大小写问题
.Net Core学习资料

原文地址：https://www.cnblogs.com/gnos/p/13489446.html