通过

读取 Excel 文件

重要

此功能以 Beta 版本提供。 工作区管理员可以从 预览 页控制对此功能的访问。 请参阅 管理 Azure Databricks 预览版。

可以使用内置的 Excel 文件格式支持在批量和流式工作负载中引入、解析和查询 Excel 文件。 它会自动推断架构和数据类型，无需外部库或手动文件转换。 此功能提供对本地上传和云存储的无缝数据接入。

主要功能

  • 使用 Databricks SQL 和 Spark API 直接读取 .xls 和 .xlsx 文件。
  • 使用“添加数据”UI 直接上传 .xls 和 .xlsx 文件。 请参阅将文件上传到 Azure Databricks。
  • 从多工作表文件读取任何工作表。
  • 指定确切的单元格边界或区域。
  • 自动推断架构、标头和数据类型。
  • 引入已评估的公式。
  • 使用自动加载程序对 Excel 文件进行结构化流式处理。

先决条件

Databricks Runtime 17.1 或更高版本。

在 UI 中创建或修改表

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

查询 Excel 文件

可以使用 Spark 批处理（spark.read）和流式处理 APIspark.readstream 查询 Excel 文件。 可以选择自动推断架构或指定自己的架构来分析 Excel 文件。 默认情况下，分析器读取从左上单元格开始到第一个工作表中右下角非空单元格的所有单元格。 若要读取其他工作表或单元格区域，请使用该选项dataAddress。

可以通过将 选项设置为 来查询 Excel 文件中的工作表列表。

Excel 分析选项

以下选项可用于分析 Excel 文件：

数据源选项 Description
dataAddress 用于读取的单元格区域的地址（Excel 语法）。 如果未指定，分析器将从第一个工作表读取所有有效的单元格。
  • "" 或省略：读取第一个工作表中的所有数据。
  • "MySheet!C5:H10"：从名为MySheet的工作表中读取C5到H10的范围。
  • "C5:H10"：读取第一个工作表中范围从C5到H10。
  • "Sheet1!A1:A1"：仅读取Sheet1中的单元格A1。
  • "Sheet1"：从 Sheet1中读取所有数据。
  • "'My Sheet!?>'!D5:G10"：从 My Sheet!?>中读取 D5 到 G10。 将工作表名称用'括起来。
headerRows 要视为标题行并读取为列名的 Excel 文件中的初始行数。 当指定 dataAddress 时，headerRows 应用于该单元格区域中的表头行。 支持的值是 0 和 1。 默认值为0，在这种情况下，通过将列号追加到_c（例如：_c1、、_c2_c3...）来自动生成列名。
示例：
  • dataAddress: "A2:D5"， headerRows: "0"：将列名称推断为 _c1..._c4。 从第 2 行读取第一行数据，介于A2到D2之间。
  • dataAddress: "A2:D5"， headerRows: "1"：将列名设置为第 2 A2D2行中的单元格值。 从第 3 行读取第一行数据：A3 到 D3。
operation 指示对 Excel 工作簿执行的操作。 默认值为 readSheet从工作表读取数据。 另一个受支持的操作是listSheets，它返回工作簿中的工作表列表。 对于listSheets操作，返回的架构是struct，具有以下字段：
  • sheetIndex：长
  • sheetName：字符串
timestampNTZFormat 用于时间戳值的自定义格式字符串（该时间戳存储为 Excel 中的字符串），不含时区，遵循日期时间格式模式。 这适用于读取为 . 的 TimestampNTZType字符串值。 默认值：yyyy-MM-dd'T'HH:mm:ss[.SSS]。
dateFormat 遵循日期时间模式格式的自定义日期格式字符串。 这适用于读取为Date的字符串值。 默认值：yyyy-MM-dd。

例子

查找使用内置连接器 Lakeflow Connect 读取 Excel 文件的代码示例。

使用 Spark 批处理读取 Excel 文件

可以使用 spark.read.excel 从云存储（例如 S3、ADLS）读取 Excel 文件。 例如：

# 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 读取 Excel 文件

可以使用 read_files 表值函数直接使用 SQL 引入 Excel 文件。 例如：

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

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');

分析复杂的非结构化 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>))

列出工作表

可以使用 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"
)

局限性

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

FAQ

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

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

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

我能够导入包含复杂布局或单个工作表含有多个表格的 Excel 文件吗？

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

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

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

我能否在 Auto Loader 和流式处理任务中使用 Excel 数据导入功能？

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

是否支持受密码保护的 Excel？

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

  • Last updated on