读取Excel文件

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

主要功能

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

先决条件

Databricks Runtime 17.1 或更高版本。

在 UI 中创建或修改表

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

查询Excel文件

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

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

Excel分析选项

以下选项可用于分析Excel文件。 有关 DataFrameReader 选项的完整列表，请参阅 DataFrameReader 选项。

例子

查找使用内置连接器 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"
)

局限性

• 不支持将数据帧写入Excel格式。 可以将数据导出到 CSV 或 Parquet 等其他格式。
• 不支持受密码保护的文件。
• 仅支持一个标题行。
• 合并的单元格值仅填充左上角的单元格。 剩余的子单元格设置为 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格式？

否。 内置Excel连接器仅支持读取。 若要导出数据，请使用支持的写入格式，如 CSV 或 Parquet。

支持受密码保护的Excel？

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