POI Excel处理技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_34224565/article/details/104923670

文章目录

一、须知 + trap
搭建
api
- tip
三、XSSF's example
- example

一、须知 + trap

1.POI支持的类型

https://www.cnblogs.com/gdwkong/p/8669220.html
HSSF － Microsoft Excel97-2003
XSSF － Microsoft Excel03之后，格式是xslx，03之前是xls，长的那个是高的
HWPF － Microsoft Word
HSLF － Microsoft PowerPoint
HDGF － Microsoft Visio

2. POI的版本号一定要注意：`17`是`十七`而不是`零点一七`，我在此处入坑，以为3.8比3.17新。实际上2者差了10年。

3. excel有2个类，03之前用HSSF，后缀是xls，03之后用XSSH，后缀是xlsx，长的那个是高的。

4.row在进行cell的遍历时，一定要注意区分空串和null。空串的CellType是BLANK，而null直接报NPE，所以这里要判断

5. excel的列有很多种类型，Cell对象在get的时候，如果getXxx方法和列的类型不匹配会报错，所以要提前先判断类型。类似于JDBC的操作。

{1} `如果想获取原始输入，可以直接先把所有cell设为STRING，然后再判断是否为STRING`（BLANK不属于STRING）。但excel不能动态的改，也就是说，如果读取一个NUMERIC的单元格，在代码里修改为STRING，只是在内存中修改了，并且读取的时候会有问题（如果这个NUMERIC类型的数字小数点位数超过excel设定的默认值 (默认2位)，比如源是12.122，那么在内存中转成STRING，再以getStringCellValue()读，会输出12.1219999999）。需要先修改类型，然后写入到一个临时的excel，此时这个临时excel的单元格就全是文本类型了，然后再读这个临时excel就可以放心用STRING类型接了。

{2} 纯数字模样的单元格如果设置为文本类型，excel会在单元格左上角提示一个绿色三角号。

{3} `全部改为STRING后，原来BLANK的会全部变为null，所以就不能用iterator了，因为iterator会跳过null的cell`

比如如下青色框起来的单元格，使用iterator遍历时直接越过
在这里插入图片描述

使用代码获取指定位置的cell时，会发现是null

输出：nullTE

6.poi读excel的cell时，是有可能为null的，导致后续npe，用这个方式避免，

row.getCell(12, Row.CREATE_NULL_AS_BLANK).setCellValue(“3级”);
💡row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
当取到null，返回空字符串blank：
row.getCell(cellIndex, Row.MissingCellPolicy.RETURN_NULL_AND_BLANK)
当取到null返回null，取到blank返回blank：
row.getCell(cellIndex, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL)
当取到空字符串blank，返回null：

搭建

1、 pom依赖2个就行，poi 是基础依赖，提供了操作 Excel 文件的核心功能，poi-ooxml 是操作 Office Open XML 格式文件（如 .xlsx、.docx 等）的扩展库
2、 poi版本不要太高，我选的maven仓库里最高的5.2，报log4j的错，换成3.6就行了

<!--poi 是基础依赖，提供了操作 Excel 文件的核心功能-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.6</version>
</dependency>
<!--poi-ooxml 是操作 Office Open XML 格式文件（如 .xlsx、.docx 等）的扩展库。-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.6</version>
</dependency>

api

https://blog.youkuaiyun.com/Java_Fly1/article/details/137716309

**以下代码为将指定excel转成全部为文本的临时excel后再进行读取的操作**

import java.io.{File, FileOutputStream}
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFWorkbook

object POI {
  /*
  * 实验是否可以在POI中把excel中的单元格全设为文本，然后再根据string取
  * */
  def main(args: Array[String]): Unit = {
    val excelPath = "C:\\Users\\HASEE\\Desktop\\清单.xlsx"
    val excelFile = new File(excelPath)
    var excel = new XSSFWorkbook(excelPath)

    //全部单元格修改为STRING并写入到一个临时excel
    val newPath = toSTRING(excel,excelFile)
    //重新给excel赋值，将临时excel赋给excel变量
    excel = new XSSFWorkbook(newPath)

    val iter = excel.sheetIterator()
    while (iter.hasNext) {
      val sheet = iter.next()
      val rowIter = sheet.rowIterator()
      while (rowIter.hasNext) {
        val row = rowIter.next()
        val cellIter = row.cellIterator()
        while (cellIter.hasNext) {
          val cellOp = Option(cellIter.next())
          if (cellOp.isDefined) {
            val cell = cellOp.get
            cell.getCellTypeEnum match {
              case CellType.NUMERIC => {
                println("##" + cell.getNumericCellValue.toString)
                cell.setCellType(CellType.STRING)
                println(cell.getCellTypeEnum + "%%" + cell.getStringCellValue.toString)
              }
              case CellType.STRING => println(cell.getRichStringCellValue)
              case CellType.BLANK => println("BLANK")
            }
          } else {
            println("@@@")
          }
        }
      }
    }
  }
}

package utils

import java.io.{File, FileOutputStream}

import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFWorkbook

object ExcelUtils {
  /*
   * 将指定的excel的单元格全部转为文本并写入到临时文件。临时文件名称为源文件后面加Tmp
   * */
  def toSTRING(excelFile: File) = {
    val excel = new XSSFWorkbook(File)
    val iter = excel.sheetIterator()
    while (iter.hasNext) {
      val sheet = iter.next()
      val rowIter = sheet.rowIterator()
      while (rowIter.hasNext) {
        val row = rowIter.next()
        val cellIter = row.cellIterator()
        while (cellIter.hasNext) {
          val cellOp = Option(cellIter.next())
          if (cellOp.isDefined) {
            val cell = cellOp.get
            cell.setCellType(CellType.STRING)
          } else {
            println("cell为null")
          }
        }
      }
    }
    //临时文件的输出流
    val path = s"${excelFile.getParent}\\${excelFile.getName.substring(excelFile.getName.indexOf(".") + 1)}-Tmp.xlsx"
    val tmpOut = new FileOutputStream(path)
    excel.write(tmpOut)
    path
  }
}

tip

遍历cell时，如果用row.getLastCellNum作为遍历次数，如果当前row只有前面几列有数据，后面都是null，会导致不再遍历后面的，在生成导入数据时可能会有问题。一般会用第一行的列数作为遍历次数，因为第一行一般就是标题行，肯定是最大值。sheet.getRow(0).getPhysicalNumberOfCells
亲测，在使用poi提供的迭代器时，会自动把null的cell给忽略，所以有时必须使用foreach
比如
sheet、row、cell计数都是从0开始。

匹配cell类型用CellType，注意cell为null的情况并不包含在这里面

_NONE(-1),只有内部使用，一般用不到。注意，null和空串都不会匹配这个。  	
NUMERIC(0),数值、日期、分数类型的cell和 
STRING(1),文本  
FORMULA(2),公式 
BLANK(3),空串
BOOLEAN(4),布尔
ERROR(5);？？？

巨坑！！！
sheet、row、cell都是从0开始计，但获取最后一项时，不同的方法有不同的意义。
excel.getNumberOfSheets获取的是总的sheet数，因为第一个sheet的索引是0，所以循环的时候要用until。但Last系列方法，sheet.getLastRowNum和row.getLastCellNum，直接获取最后一项的索引值，所以这里即使是从0开始计，要用to`

for (i <- 0 until excel.getNumberOfSheets)
for(j <- 0 to sheet.getLastRowNum)
for (k <- 0 to row.getLastCellNum)

三、XSSF’s example

example

object Excel2Other {
  def main(args: Array[String]): Unit = {
    val path = "C:\\Users\\Administrator\\Desktop\\123.xlsx"
    val excel = new XSSFWorkbook(new FileInputStream(path))
    val iterExcel = excel.iterator()

    while (iterExcel.hasNext) {

      val sheet = iterExcel.next()
      val rowIter = sheet.iterator()

      while(rowIter.hasNext) {

        val row = rowIter.next()
        /*用迭代器会自动把null的单元格给略过，只能用foreach
        val cellIter = row.cellIterator()*/
        var line = ""
        val sheetColumnNum = sheet.getRow(0).getPhysicalNumberOfCells

        for (i <- 0 until sheetColumnNum){

          val cellOp = Option(row.getCell(i))
          if(cellOp.isDefined){
            val cell = cellOp.get
            val cellType:CellType = cell.getCellTypeEnum
            cellType match {
              case CellType.NUMERIC => {
                /*
                poi会自动默认把数值类型的单元格转成java的double，
                再转成String时，有时会以科学记数法的形式输出，
                所以这里用DecimalFormat把double格式化
                 */
                val doubleValue = cell.getNumericCellValue
                //取整数部分并保留2位小数，且第2位小数由后面的数四舍五入得来。
                line += s"${new DecimalFormat("0.00").format(doubleValue)}|"
              }
              case CellType.STRING => line += s"${cell.getStringCellValue}|"
              case CellType.BLANK => line += s"blank|"  //空串cell
              case CellType._NONE => line += s"none|" // 用于表示在初始化或缺少具体类型之前的状态。仅供内部使用。
            }
          }else{
            line += "|"
          }
        }

        println(line)
       /* while (cellIter.hasNext) {
          val cell = cellIter.next()
          if (cell == None){
            println("###")
          }
          val cellType:CellType = cell.getCellTypeEnum
          cellType match {
            case CellType.NUMERIC => line += s"${cell.getNumericCellValue}|"
            case CellType.STRING => line += s"${cell.getStringCellValue}|"
            case CellType._NONE => line += s"123|"
            case CellType.BLANK => line += s"blank|"
          }
        }*/
        //line = line.substring(0,line.length - 1)
      }
    }
  }
}

从0开始

// 输入流
FileInputStream inputStream = new FileInputStream("C:\\Users\\picc.com.cn\\Desktop\\新建文件夹 (3)\\test.xlsx");
// 创建一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
// 获取工作表getSheetAt,索引从0开始表示第一个工作表
XSSFSheet sheet = workbook.getSheetAt(5);
// 获取工作表中对应的行数
int rows = sheet.getLastRowNum();
    System.out.println("行数："+rows);
    for (int r = 1; r <= rows; r++) {
// 获取指定的行
XSSFRow row = sheet.getRow(r);
// 获取指定的行数所对应的单元格
//根据第四列和第六列进行判断
int cols = sheet.getRow(r).getLastCellNum();
String biaoming = row.getCell(4).getStringCellValue();
String ziduanming = row.getCell(6).getStringCellValue();
Cell biaozwming = row.getCell(3);
Cell ziduanzwming = row.getCell(5);
        if (biaoming.startsWith("tmp_table_claim_sf_kanban_2024_01")){
        biaozwming.setCellValue("理赔看板理赔案件明细表");
            if (ziduanming.equals("claim_element")) ziduanzwming.setCellValue("索赔支付时效分子");
            if (ziduanming.equals("claim_denominator")) ziduanzwming.setCellValue("索赔支付时效分母");
            if (ziduanming.equals("loss_element")) ziduanzwming.setCellValue("出险支付时效分子");
            if (ziduanming.equals("loss_denominator")) ziduanzwming.setCellValue("出险支付时效分母");
            if (ziduanming.equals("get_rate_element")) ziduanzwming.setCellValue("理赔获赔率分子");
            if (ziduanming.equals("get_rate_denominator")) ziduanzwming.setCellValue("理赔获赔率分母");
            if (ziduanming.equals("orgcode")) ziduanzwming.setCellValue("四级机构编号");
            if (ziduanming.equals("orgname")) ziduanzwming.setCellValue("四级机构名称");
            if (ziduanming.equals("report_date")) ziduanzwming.setCellValue("报表日期");
            if (ziduanming.equals("pt")) ziduanzwming.setCellValue("跑批日期");
            row.getCell(12, Row.CREATE_NULL_AS_BLANK).setCellValue("1级");
        }else if (biaoming.startsWith("tmp_table_claim_sf_kanban_2024_02")){
        biaozwming.setCellValue("理赔看板个险结案率明细表");
            if (ziduanming.equals("ind_numerator")) ziduanzwming.setCellValue("理赔个险结案率分子");
            if (ziduanming.equals("ind_denominator")) ziduanzwming.setCellValue("理赔个险结案率分母");
            if (ziduanming.equals("orgcode")) ziduanzwming.setCellValue("机构号");
            if (ziduanming.equals("orgname")) ziduanzwming.setCellValue("机构名称");
            if (ziduanming.equals("report_date")) ziduanzwming.setCellValue("报表日期");
            if (ziduanming.equals("pt")) ziduanzwming.setCellValue("跑批日期");
            row.getCell(12, Row.CREATE_NULL_AS_BLANK).setCellValue("1级");
        } else if (biaoming.startsWith("app_lf_ubi_conpro_transfer_order_source_df_2024_")) {
        biaozwming.setCellValue("转办明细表");
            if (ziduanming.equals("formno")) ziduanzwming.setCellValue("服务单号");
            if (ziduanming.equals("sendtime")) ziduanzwming.setCellValue("发送时间");
            if (ziduanming.equals("dealcomcode")) ziduanzwming.setCellValue("接办分公司（二级机构）");
            if (ziduanming.equals("comcode4l")) ziduanzwming.setCellValue("接办营业区机构号");
            if (ziduanming.equals("comcname4l")) ziduanzwming.setCellValue("接办营业区");
            if (ziduanming.equals("custname")) ziduanzwming.setCellValue("来电人姓名");
            if (ziduanming.equals("appname")) ziduanzwming.setCellValue("投保人姓名");
            if (ziduanming.equals("contno")) ziduanzwming.setCellValue("保单号");
            if (ziduanming.equals("cvalidate")) ziduanzwming.setCellValue("生效日期");
            if (ziduanming.equals("mainriskname")) ziduanzwming.setCellValue("险种名称");
            if (ziduanming.equals("mainmoney")) ziduanzwming.setCellValue("保费");
            if (ziduanming.equals("report_date")) ziduanzwming.setCellValue("报表日期");
            if (ziduanming.equals("pt")) ziduanzwming.setCellValue("跑批日期");
            row.getCell(12, Row.CREATE_NULL_AS_BLANK).setCellValue("3级");
        }

                }
String outfilepath = "C:\\Users\\picc.com.cn\\Desktop\\新建文件夹 (3)\\test2.xlsx";
FileOutputStream fileOutputStream = new FileOutputStream(outfilepath, true);
    workbook.write(fileOutputStream);

// 关闭流
    fileOutputStream.close();