如何在Azure Synapse Notebook中用openpyxl打开ADLS Gen2 Excel文件？

为什么直接用openpyxl会报错？ #

openpyxl是针对本地文件系统的Python库，无法识别ADLS Gen2的abfss://协议，所以直接传入该路径会提示文件不存在，即使路径正确也无法访问。

方法1：临时下载文件到本地后用openpyxl处理 #

利用mssparkutils.fs.cp将ADLS里的Excel文件复制到Synapse Notebook的本地临时目录，再用openpyxl读取：

from openpyxl import load_workbook
import os

# 定义ADLS文件路径和本地临时路径
adls_path = "abfss://container@acct.dfs.core.windows.net/excelfolder/excelfile.xlsx"
local_temp_path = f"/tmp/{os.path.basename(adls_path)}"

# 从ADLS复制文件到本地临时目录
mssparkutils.fs.cp(adls_path, f"file:{local_temp_path}")

# 用openpyxl读取本地文件
workbook = load_workbook(local_temp_path)
sheet = workbook.worksheets[0]

# 处理完成后可删除临时文件（可选）
os.remove(local_temp_path)

方法2：Synapse Spark原生读取（更优方案） #

Synapse Spark支持直接读取ADLS Gen2中的Excel文件，无需下载，还能批量处理并合并工作表，且不需要SAS令牌（只要Notebook所在工作区有ADLS的访问权限）。

单文件读取 #

# 读取单个Excel文件的指定工作表
df = spark.read.format("com.crealytics.spark.excel") \
    .option("header", "true") \
    .option("inferSchema", "true") \
    .option("sheetName", "Sheet1") \
    .load("abfss://container@acct.dfs.core.windows.net/excelfolder/excelfile.xlsx")

# 查看数据
df.show()

批量读取并合并多个Excel文件的所有工作表 #

如果要动态读取目录下所有Excel文件的所有工作表并合并成一个DataFrame：

from pyspark.sql import DataFrame
from pyspark.sql.functions import lit

# 定义ADLS目录路径
adls_dir = "abfss://container@acct.dfs.core.windows.net/excelfolder/"

# 获取目录下所有Excel文件路径
file_paths = [file.path for file in mssparkutils.fs.ls(adls_dir) if file.path.endswith(".xlsx")]

# 初始化空DataFrame
combined_df = None

for file_path in file_paths:
    # 读取当前文件的所有工作表
    df = spark.read.format("com.crealytics.spark.excel") \
        .option("header", "true") \
        .option("inferSchema", "true") \
        .option("dataAddress", "'*'!A1")  # 读取所有工作表
        .load(file_path)
    
    # 添加文件名列，方便溯源（可选）
    df = df.withColumn("source_file", lit(file_path))
    
    # 合并到总DataFrame
    if combined_df is None:
        combined_df = df
    else:
        combined_df = combined_df.unionByName(df, allowMissingColumns=True)

# 查看合并后的数据
combined_df.show()

说明 #

• 确保Synapse工作区已配置ADLS Gen2的访问权限（如通过托管身份、RBAC角色），无需SAS令牌即可访问。
• com.crealytics.spark.excel是Spark的Excel读取插件，Synapse默认已预装，无需额外安装。
• 批量处理时利用Spark的分布式能力，效率远高于单线程的openpyxl方法，适合处理大量Excel文件。

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