You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Sheet.getRow返回Null触发空指针异常，求Scala ETL代码修复方案

avatar
AI 小北
2026-6-25
Scala ETL操作Excel时NullPointerException问题解决

错误日志 

Iteration : 10
Parent row--------------------:154
Parent Row Creation:null
Current row:----------10
error processing the load. Error: null
error processing data load and performing rollback
Closing JDBC connection...
error processing the load. Error: null
error processing data load and performing rollback
Closing JDBC connection...
Error occured during extract process.1 Error: 
java.lang.NullPointerException: null
    at com.bcbsm.ie.etl.services.COMMRBCEExtDynamic.$anonfun$doETL$3(COMMRBCEExtDynamic.scala:182) ~[addedFile1823515003766928211FAM_Report_1_0_0-2844a.jar:?]
    at scala.collection.immutable.Range.foreach$mVc$sp(Range.scala:158) ~[----workspace_spark_3_1--maven-trees--hive-2.3__hadoop-2.7--org.scala-lang--scala-library_2.12--org.scala-lang__scala-library__2.12.10.jar:?]
    at com.bcbsm.ie.etl.services.COMMRBCEExtDynamic.$anonfun$doETL$2(COMMRBCEExtDynamic.scala:179) ~[addedFile1823515003766928211FAM_Report_1_0_0-2844a.jar:?]
    at scala.collection.immutable.Range.foreach$mVc$sp(Range.scala:158) ~[----workspace_spark_3_1--maven-trees--hive-2.3__hadoop-2.7--org.scala-lang--scala-library_2.12--org.scala-lang__scala-library__2.12.10.jar:?]
    at com.bcbsm.ie.etl.services.COMMRBCEExtDynamic.$anonfun$doETL$1(COMMRBCEExtDynamic.scala:177) ~[addedFile1823515003766928211FAM_Report_1_0_0-2844a.jar:?]
    at scala.collection.immutable.Range.foreach$mVc$sp(Range.scala:158) ~[----workspace_spark_3_1--maven-trees--hive-2.3__hadoop-2.7--org.scala-lang--scala-library_2.12--org.scala-lang__scala-library__2.12.10.jar:?]
    at com.bcbsm.ie.etl.services.COMMRBCEExtDynamic.doETL(COMMRBCEExtDynamic.scala:172) ~[addedFile1823515003766928211FAM_Report_1_0_0-2844a.jar:?]
    at com.bcbsm.ie.etl.services.ETLService.doETL(ETLService.scala:191) ~[addedFile1823515003766928211FAM_Report_1_0_0-2844a.jar:?]
    at com.bcbsm.ie.etl.main.RedshiftToExcelExt$.main(RedshiftToExcelExt.scala:69) ~[addedFile1823515003766928211FAM_Report_1_0_0-2844a.jar:?]

预期输出 

sheetName: Withhold Report by RBCE - Comm
rowNumber: 10
rowCount: 192
lastRow: 24
Add Rows Completed
Iteration : 10
Parent row--------------------:202
Current row:----------10
Current CELL:----0
ParCELL Type3
Current row:----------26
Current CELL:----0
ParCELL Type3
Current row:----------42
Current CELL:----0
ParCELL Type3
Current row:----------58
Current CELL:----0
ParCELL Type3
Current row:----------74
Current CELL:----0
ParCELL Type3
Current row:----------90
Current CELL:----0
ParCELL Type3
Current row:----------106
Current CELL:----0
ParCELL Type3
Current row:----------122
Current CELL:----0
ParCELL Type3
Current row:----------138
Current CELL:----0
ParCELL Type3
Current row:----------154
Current CELL:----0
ParCELL Type3
Current row:----------170
Current CELL:----0
ParCELL Type3
Current row:----------186
Current CELL:----0
ParCELL Type3
Iteration : 11

问题代码 

//val inputStream = new FileInputStream(new File(destFile));
val fs = FileSystem.get(new URI("s3a://"+IEUtil.getProperty("awsS3BucketName")), new Configuration)
val inputStream = fs.open(new Path(destFile));
val workbook = WorkbookFactory.create(inputStream);
val sheet = workbook.getSheet(sheetName)
logger.info("Sheet Name For Comm:" + sheet)
var lastStoredRowNum = sheet.getLastRowNum().toInt;
logger.info("--------last row:" + lastStoredRowNum)
var getTheCell = ((dosRowNumber.toInt) * (no_of_Rbce - 1) + 10)
logger.info("Get the Last Cell:" + getTheCell)

var noforbce = no_of_Rbce - 1.toInt
logger.info("No of RBCE:" + noforbce)
for (i <- 10 to 23 by 1) {
  logger.info("Iteration : " + i)
  logger.info("Parent row--------------------:" + ((16 * (no_of_Rbce - 1)) + i).toString())
  val parentRow = sheet.getRow((16.toInt * (no_of_Rbce - 1)).toInt + i.toInt)           
  logger.info("Parent Row Creation:" + parentRow)
  for (j <- 10 to 23 by 1) {
    if (i == j) {
      for (k <- 0 to no_of_Rbce - 2 by 1) {
        var currRow = sheet.createRow(j + (16 * k))
        logger.info("Current row:----------" + (j + (16 * k)))
        val col_count = parentRow.getLastCellNum()
        logger.info("Column Count Cell:" + col_count)

        for (l <- 0 to (col_count - 1) by 1) {
          val parent_cell = parentRow.getCell(l)
          currRow.createCell(l)
          val curr_cell = currRow.getCell(l)
          logger.info("Current CELL:----" + l)

          breakable {
            if (parent_cell == null) {
              break
            } else {
              val parent_style = parent_cell.getCellStyle()
              curr_cell.setCellStyle(parent_style)
              logger.info("ParCELL Type" + parent_cell.getCellType())

              if (parent_cell.getCellType() == 0.toInt) {
                curr_cell.setCellValue(parent_cell.getNumericCellValue())
                logger.info("Numeric  value:" + parent_cell.getNumericCellValue())

              } else if (parent_cell.getCellType() == 1.toInt) {

                curr_cell.setCellValue(parent_cell.getStringCellValue())
                logger.info("string value:" + parent_cell.getStringCellValue())

              } else if (parent_cell.getCellType() == 2.toInt) {
                var src_formula = parent_cell.getCellFormula()
                logger.info("Source formula" + src_formula)
                var number_list = ("""\d+""".r findAllIn src_formula).toList
                for (m <- 0 to (number_list.length) - 1 by 1) {
                  src_formula = src_formula.replace(number_list(m).toString(), ((number_list(m).toInt) - 16 * ((no_of_Rbce - 1) - k)).toString())
                }

                curr_cell.setCellFormula(src_formula)
                //logger.info("Formula value:"+ parent_cell.getCellFormula())
              } else if (parent_cell.getCellType() == 3.toInt) {
                curr_cell.setCellType(CellType.BLANK)
                //logger.info("Formula value:"+ parent_cell.getCellFormula())
              } else {
                logger.info(".")
              }
            }
          }

        }
      }
    }
  }
}

for (n <- 0 to (no_of_Rbce - 1) by 1) {
  var headerRow = sheet.getRow(10 + (16 * n));
  var headerCell = headerRow.getCell(0)
  headerCell.setCellValue(rbce_idName(n))
}

inputStream.close();

//val outputStream = new FileOutputStream(new File(destFile));
val outputStream = new FileOutputStream(fs.create(new Path(destFile)).toString());
workbook.write(outputStream);
workbook.close();
outputStream.close();

} catch {
  case e: Exception =>
    logger.info("error processing the load. Error: " + e.getMessage);
    logger.info("error processing data load and performing rollback")
    e.printStackTrace()
    //connection.rollback()
    throw e;
} finally {

  cleanup();
  stmt.close();
  rs.shutdown();
  //(s"hadoop fs -rm -R  $s3Path_write").!
}

}

def cleanup() {

  /*if (hdfs != null) {
    hdfs.shutdown();
  }*/
}
}

解决方案

问题根源

  1. 行不存在导致getRow返回null：POI库中sheet.getRow(rowNum)如果目标行从未被创建（无任何单元格数据），会直接返回null而非空行。日志中父行号154远大于sheet.getLastRowNum()的24，调用parentRow.getLastCellNum()时触发NPE。
  2. 运算符优先级错误：no_of_Rbce - 1.toInt中，1.toInt优先级高于减法，若no_of_Rbce为字符串类型会引发隐式转换问题，需明确优先级。
  3. 后续行/单元格未做null校验：遍历header行时，sheet.getRow和headerRow.getCell都可能返回null，未处理会触发NPE。
  4. 输出流创建错误：原代码将S3路径转成字符串创建本地文件输出流，无法正确写入S3。

修改后的代码 

//val inputStream = new FileInputStream(new File(destFile));
val fs = FileSystem.get(new URI("s3a://"+IEUtil.getProperty("awsS3BucketName")), new Configuration)
val inputStream = fs.open(new Path(destFile));
val workbook = WorkbookFactory.create(inputStream);
val sheet = workbook.getSheet(sheetName)
logger.info("Sheet Name For Comm:" + sheet)
var lastStoredRowNum = sheet.getLastRowNum().toInt;
logger.info("--------last row:" + lastStoredRowNum)
var getTheCell = ((dosRowNumber.toInt) * (no_of_Rbce - 1) + 10)
logger.info("Get the Last Cell:" + getTheCell)

// 修正运算符优先级
var noforbce = (no_of_Rbce - 1).toInt
logger.info("No of RBCE:" + noforbce)
for (i <- 10 to 23 by 1) {
  logger.info("Iteration : " + i)
  val parentRowNum = (16.toInt * (no_of_Rbce - 1)).toInt + i.toInt
  logger.info("Parent row--------------------:" + parentRowNum.toString())
  // 行null安全处理：不存在则创建
  val parentRow = Option(sheet.getRow(parentRowNum)).getOrElse(sheet.createRow(parentRowNum))           
  logger.info("Parent Row Creation:" + parentRow)
  for (j <- 10 to 23 by 1) {
    if (i == j) {
      for (k <- 0 to no_of_Rbce - 2 by 1) {
        var currRow = sheet.createRow(j + (16 * k))
        logger.info("Current row:----------" + (j + (16 * k)))
        val col_count = parentRow.getLastCellNum()
        logger.info("Column Count Cell:" + col_count)

        // 仅当列数有效时执行遍历
        if (col_count > 0) {
          for (l <- 0 to (col_count - 1) by 1) {
            val parent_cell = parentRow.getCell(l)
            currRow.createCell(l)
            val curr_cell = currRow.getCell(l)
            logger.info("Current CELL:----" + l)

            breakable {
              if (parent_cell == null) {
                break
              } else {
                val parent_style = parent_cell.getCellStyle()
                curr_cell.setCellStyle(parent_style)
                logger.info("ParCELL Type" + parent_cell.getCellType())

                // 用枚举替代硬编码，提升可读性
                if (parent_cell.getCellType() == CellType.NUMERIC.getCode) {
                  curr_cell.setCellValue(parent_cell.getNumericCellValue())
                  logger.info("Numeric  value:" + parent_cell.getNumericCellValue())

                } else if (parent_cell.getCellType() == CellType.STRING.getCode) {
                  curr_cell.setCellValue(parent_cell.getStringCellValue())
                  logger.info("string value:" + parent_cell.getStringCellValue())

                } else if (parent_cell.getCellType() == CellType.FORMULA.getCode) {
                  var src_formula = parent_cell.getCellFormula()
                  logger.info("Source formula" + src_formula)
                  var number_list = ("""\d+""".r findAllIn src_formula).toList
                  for (m <- 0 to (number_list.length) - 1 by 1) {
                    src_formula = src_formula.replace(number_list(m).toString(), ((number_list(m).toInt) - 16 * ((no_of_Rbce - 1) - k)).toString())
                  }
                  curr_cell.setCellFormula(src_formula)
                } else if (parent_cell.getCellType() == CellType.BLANK.getCode) {
                  curr_cell.setCellType(CellType.BLANK)
                } else {
                  logger.info(".")
                }
              }
            }
          }
        }
      }
    }
  }
}

for (n <- 0 to (no_of_Rbce - 1) by 1) {
  val headerRowNum = 10 + (16 * n)
  // header行null安全处理
  var headerRow = Option(sheet.getRow(headerRowNum)).getOrElse(sheet.createRow(headerRowNum));
  // header单元格null安全处理：不存在则创建
  var headerCell = Option(headerRow.getCell(0)).getOrElse(headerRow.createCell(0))
  headerCell.setCellValue(rbce_idName(n))
}

inputStream.close();

// 修正输出流：直接使用S3文件系统的输出流
val outputStream = fs.create(new Path(destFile))
workbook.write(outputStream);
workbook.close();
outputStream.close();

} catch {
  case e: Exception =>
    logger.info("error processing the load. Error: " + e.getMessage);
    logger.info("error processing data load and performing rollback")
    e.printStackTrace()
    //connection.rollback()
    throw e;
} finally {
  cleanup();
  stmt.close();
  rs.shutdown();
  //(s"hadoop fs -rm -R  $s3Path_write").!
}

}

def cleanup() {
  /*if (hdfs != null) {
    hdfs.shutdown();
  }*/
}
}

关键修改点

  1. 行/单元格null安全处理：用Option(xxx).getOrElse(xxx)确保获取的行、单元格非null，不存在则创建。
  2. 列数有效性校验：新增if (col_count > 0)判断，避免新创建行（列数为-1）触发循环错误。
  3. 运算符优先级修正：将no_of_Rbce -1.toInt改为(no_of_Rbce -1).toInt。
  4. 输出流修正：直接使用S3文件系统的输出流，避免创建本地文件。
  5. CellType枚举替代硬编码：用CellType枚举的getCode替代数字常量，提升代码可读性和维护性。

内容的提问来源于stack exchange，提问作者Vicky

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.25 17:55:55