Spark读取含#REF!的Excel时记录被截断，求解决方法

问题根源在于：当直接指定DateType这类强类型Schema时，即使开启PERMISSIVE模式，com.crealytics.spark.excel解析器仍会因为无法将#REF!错误值转换为日期类型，导致对应行被截断或标记为损坏。以下是可靠的解决步骤：

步骤1：以全字符串Schema读取所有数据 #

先将所有列定义为StringType，绕过类型校验，确保所有行（包括含#REF!的记录）都被完整加载：

from pyspark.sql.types import StructType, StructField, StringType, DateType
from pyspark.sql.functions import to_date, when

# 临时全字符串Schema，确保无记录丢失
temp_schema = StructType([
        StructField('Col1', StringType(), True),
        StructField('Col2', StringType(), True),
        StructField('Col3', StringType(), True)
])

temp_df = (
    spark.read.format("com.crealytics.spark.excel")
    .option("header", header_option)
    .option("parseMode", "PERMISSIVE")
    .option("keepUndefinedRows", True)
    .option("useNullForErrorCells", True)
    .option("treatEmptyValuesAsNulls", True)
    .option("maxRowsInMemory", 1000)
    .option("dataAddress", "Test!A1:C200")
    .schema(temp_schema)
    .load("File.xlsx")
)

步骤2：手动转换为目标类型并处理错误值 #

在全量读取后，再将指定列转换为DateType，同时把#REF!映射为Null（或你需要的默认值）：

# 转换列类型并处理#REF!错误值
final_df = temp_df \
    .withColumn("Col1", when(temp_df["Col1"] == "#REF!", None).otherwise(to_date(temp_df["Col1"]))) \
    .withColumn("Col2", when(temp_df["Col2"] == "#REF!", None).otherwise(to_date(temp_df["Col2"])))

额外注意事项 #

• 移除重复设置的useNullForErrorCells参数，避免参数冲突；
• setErrorCellsToFallbackValues参数对日期类型的错误值处理效果有限（会设为默认日期值而非Null），因此建议通过后续的when逻辑手动处理更可控。

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