读取和流式传输Excel文件

Azure Databricks包括对读取.xls和.xlsx文件的内置支持，无需外部库或手动文件转换。 您可以从多工作表工作簿中读取任何工作表，指定特定的单元格区域，自动推断架构和数据类型，并将公式值作为其计算结果处理。 Excel文件可以从云存储中读取或直接上传到“添加数据”UI 中，并使用自动加载程序支持批处理和流式处理工作负荷。

先决条件

读取和流式处理Excel文件需要 Databricks Runtime 17.1 或更高版本，以及流式处理工作负荷的自动加载程序。

选项

使用 .option() 的 .options() 和 DataFrameReader 方法来配置 Excel 数据源。 有关支持选项的完整列表，请参阅DataFrameReaderExcel选项和DataFrameWriterExcel选项。

Usage

以下示例演示如何使用 Spark 批处理（spark.read）和流式处理 API 读取Excel文件。 默认情况下，分析程序从左上角到右下角非空单元格读取第一个工作表中的所有单元格;使用此选项 dataAddress 以特定工作表或单元格区域为目标。 架构会自动推断，也可以指定自己的架构。

在 UI 中创建或修改表

可以使用 创建或修改表 UI 从Excel文件创建表。 首先加载Excel文件或从卷或外部位置选择Excel文件。 选择工作表，调整标题行数，并且可以选择指定单元格区域。 UI 支持从所选文件和工作表创建单个表。

读取Excel文件

您可以使用 spark.read.excel 或 SQL 的 read_files 函数从云存储（例如 S3、ADLS）读取 Excel 文件。

Python

# Read the first sheet from a single Excel file or from multiple Excel files in a directory
df = (spark.read.excel(<path to excel directory or file>))

# Infer schema field name from the header row
df = (spark.read
       .option("headerRows", 1)
       .excel(<path to excel directory or file>))

# Read a specific sheet and range
df = (spark.read
       .option("headerRows", 1)
       .option("dataAddress", "Sheet1!A1:E10")
       .excel(<path to excel directory or file>))

SQL

-- Read an entire Excel file
CREATE TABLE my_table AS
SELECT * FROM read_files(
  "<path to excel directory or file>",
  schemaEvolutionMode => "none"
);

-- Read a specific sheet and range
CREATE TABLE my_sheet_table AS
SELECT * FROM read_files(
  "<path to excel directory or file>",
  format => "excel",
  headerRows => 1,
  dataAddress => "Sheet1!A2:D10",
  schemaEvolutionMode => "none"
);

使用自动加载程序流式传输Excel文件

可以通过将 cloudFiles.format 设置为 excel，使用自动加载程序流式传输Excel文件。 例如：

df = (
  spark
    .readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "excel")
    .option("cloudFiles.inferColumnTypes", True)
    .option("headerRows", 1)
    .option("cloudFiles.schemaLocation", "<path to schema location dir>")
    .option("cloudFiles.schemaEvolutionMode", "none")
    .load(<path to excel directory or file>)
)
df.writeStream
  .format("delta")
  .option("mergeSchema", "true")
  .option("checkpointLocation", "<path to checkpoint location dir>")
  .table(<table name>)

使用 COPY INTO 引入Excel文件

使用 COPY INTO 以幂等方式将云存储中的 Excel 文件加载到 Delta 表中。

CREATE TABLE IF NOT EXISTS excel_demo_table;

COPY INTO excel_demo_table
FROM "<path to excel directory or file>"
FILEFORMAT = EXCEL
FORMAT_OPTIONS ('mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

列出工作表

可以使用 listSheets 操作列出Excel文件中的工作表。 返回的架构具有以下 struct 字段：

• sheetIndex：长
• sheetName：字符串

例如：

Python

# List the name of the Sheets in an Excel file
df = (spark.read.format("excel")
       .option("operation", "listSheets")
       .load(<path to excel directory or file>))

SQL

SELECT * FROM read_files("<path to excel directory or file>",
  schemaEvolutionMode => "none",
  operation => "listSheets"
)

分析复杂非结构化Excel工作表

对于复杂的非结构性的 Excel 工作表（例如，每个工作表有多个表格、数据孤岛），Databricks 建议提取以使用 dataAddress 选项创建 Spark 数据帧所需的单元格区域。

df = (spark.read.format("excel")
       .option("headerRows", 1)
       .option("dataAddress", "Sheet1!A1:E10")
       .load(<path to excel directory or file>))

局限性

• 不支持受密码保护的文件。
• 仅支持一个标题行。
• 合并的单元格值仅填充左上角的单元格。 剩余的子单元格设置为 NULL。
• 自动加载程序支持流式处理Excel文件，但不支持架构演变。 必须显式设置 schemaEvolutionMode="None"。
• 不支持“严格的开放 XML 电子表格（Strict OOXML）”。
• 在 .xlsm 文件中不支持宏执行。
• ignoreCorruptFiles 选项不受支持。

FAQ

在 Lakeflow Connect 中查找有关 Excel 连接器的常见问题的解答。

是否可以同时读取所有工作表？

分析程序每次只从Excel文件中读取一个工作表。 默认情况下，它会读取第一个表。 可以使用 dataAddress 选项指定不同的工作表。 若要处理多个工作表，请先将 operation 选项设置为 listSheets 来检索工作表列表，然后遍历工作表名称，并在 dataAddress 选项中提供名称以读取每个工作表。

是否可以导入包含复杂布局或每个工作表有多个表格的Excel文件？

默认情况下，分析程序将读取从左上角单元格到右下角非空单元格的所有Excel单元格。 可以使用dataAddress选项指定不同的单元格区域。

如何处理公式和合并单元格？

公式作为其计算值引入。 对于合并的单元格，只保留左上角的值（子单元格为 NULL）。

我可以在自动加载器和流式处理作业中使用Excel导入吗？

可以，可以使用 cloudFiles.format = "excel" 流式传输Excel文件。 但是，不支持架构演变，因此必须将"schemaEvolutionMode"设置为"None"。

支持受密码保护的Excel？

否。 如果此功能对工作流至关重要，请联系 Databricks 帐户代表。

其他资源

• 读取和写入 CSV 文件：如果数据源可以导出到 CSV，则 CSV 是一种更简单的格式，具有更广泛的工具支持，并且不依赖于专用分析程序。