Databricks向ADLS写入多Sheet Excel失败，求解决方案

问题背景 #

尝试直接写入Azure Blob存储失败，转而先写入本地临时目录再移动文件到Blob目录，依然报错，具体代码和错误信息如下：

原代码 #

filename = os.path.join(arg_dict['out_dir'], old_attribute_file_path.replace(old_attribute_file_path.split('/')[-1].split('-')[-1].split('.')[0], attribute_files[0].split('-')[1]))

temp_file_name = os.path.join(TMP_PATH, old_attribute_file_path.replace(old_attribute_file_path.split('/')[-1].split('-')[-1].split('.')[0], attribute_files[0].split('-')[1]))

fill_color = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# Write DataFrames to Excel
with pd.ExcelWriter(temp_file_name, engine='openpyxl') as writer:
    df1.to_excel(writer, index=False, sheet_name='Sheet1')
    df2.to_excel(writer, index=False, sheet_name='Sheet2')
    
    # Load the workbook
    workbook = writer.book

    # Save the workbook
    workbook.save(temp_file_name)

shutil.move(temp_file_name, filename)

报错信息 #

File /local_disk0/.ephemeral_nfs/envs/pythonEnv-6a81eac8-0226-477b-9715-070566214b43/lib/python3.10/site-packages/openpyxl/writer/excel.py:294, in save_workbook(workbook, filename)
    292 workbook.properties.modified = datetime.datetime.utcnow()
    293 writer = ExcelWriter(workbook, archive)
--> 294 writer.save()
    295 return True

File /local_disk0/.ephemeral_nfs/envs/pythonEnv-6a81eac8-0226-477b-9715-070566214b43/lib/python3.10/site-packages/openpyxl/writer/excel.py:275, in ExcelWriter.save(self)
    273 def save(self):
    274     """Write data into the archive."""
--> 275 self.write_data()
    276 self._archive.close()

File /local_disk0/.ephemeral_nfs/envs/pythonEnv-6a81eac8-0226-477b-9715-070566214b43/lib/python3.10/site-packages/openpyxl/writer/excel.py:60, in ExcelWriter.write_data(self)
     57 archive = self._archive
     59 props = ExtendedProperties()
--> 60 archive.writestr(ARC_APP, tostring(props.to_tree()))
     62 archive.writestr(ARC_CORE, tostring(self.workbook.properties.to_tree()))
     63 if self.workbook.loaded_theme:

File /usr/lib/python3.10/zipfile.py:1816, in ZipFile.writestr(self, zinfo_or_arcname, data, compress_type, compresslevel)
   1814 zinfo.file_size = len(data)            # Uncompressed size
   1815 with self._lock:
--> 1816     with self.open(zinfo, mode='w') as dest:
   1817 dest.write(data)

File /usr/lib/python3.10/zipfile.py:1182, in _ZipWriteFile.close(self)
   1180     self._fileobj.seek(self._zinfo.header_offset)
   1181     self._fileobj.write(self._zinfo.FileHeader(self._zip64))
--> 1182     self._fileobj.seek(self._zipfile.start_dir)
   1184 # Successfully written: Add file to our caches
   1185 self._zipfile.filelist.append(self._zinfo)

OSError: [Errno 95] Operation not supported

报错原因 #

OSError: [Errno 95] Operation not supported本质是Azure Blob存储的文件系统不支持随机读写（seek操作）：

• openpyxl生成Excel时，需要对文件进行seek操作来修改归档文件的头部信息
• 直接写入Blob或用shutil.move跨文件系统移动文件，都会触发该不支持的操作

解决方案 #

方法一：本地临时文件生成+Azure SDK上传 #

先在本地生成完整的Excel文件，再通过Azure Storage SDK上传到Blob，避免直接操作Blob文件系统：

1. 安装依赖

pip install azure-storage-blob pandas openpyxl

2. 修改后的代码

import os
import pandas as pd
from openpyxl.styles import PatternFill
from azure.storage.blob import BlobServiceClient

# 生成临时文件路径
temp_file_name = os.path.join(TMP_PATH, "temp_multi_sheet.xlsx")

fill_color = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# 写入本地临时文件（with上下文会自动处理保存，无需手动调用workbook.save）
with pd.ExcelWriter(temp_file_name, engine='openpyxl') as writer:
    df1.to_excel(writer, index=False, sheet_name='Sheet1')
    df2.to_excel(writer, index=False, sheet_name='Sheet2')

# 初始化Azure Blob客户端
connect_str = "你的Azure存储账户连接字符串"
blob_service_client = BlobServiceClient.from_connection_string(connect_str)

# 配置目标Blob信息
container_name = "你的容器名称"
blob_path = "目标存储路径/最终文件名.xlsx"  # 对应原代码中filename的Blob路径

# 上传文件到Blob
blob_client = blob_service_client.get_blob_client(container=container_name, blob=blob_path)
with open(temp_file_name, "rb") as data:
    blob_client.upload_blob(data, overwrite=True)

# 清理本地临时文件
os.remove(temp_file_name)

方法二：内存中生成Excel直接上传 #

无需本地磁盘，直接在内存中生成Excel文件流，适合无本地存储的Serverless环境：

import pandas as pd
from openpyxl.styles import PatternFill
from azure.storage.blob import BlobServiceClient
from io import BytesIO

fill_color = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# 在内存中生成Excel文件
output = BytesIO()
with pd.ExcelWriter(output, engine='openpyxl') as writer:
    df1.to_excel(writer, index=False, sheet_name='Sheet1')
    df2.to_excel(writer, index=False, sheet_name='Sheet2')
# 重置文件指针到开头，确保上传完整内容
output.seek(0)

# 上传到Azure Blob
connect_str = "你的Azure存储账户连接字符串"
blob_service_client = BlobServiceClient.from_connection_string(connect_str)
container_name = "你的容器名称"
blob_path = "目标存储路径/最终文件名.xlsx"

blob_client = blob_service_client.get_blob_client(container=container_name, blob=blob_path)
blob_client.upload_blob(output, overwrite=True)

注意事项 #

• 确保Azure存储账户的连接字符串拥有Blob容器的写入权限
• 若使用Serverless环境（如Azure Functions），临时目录需使用平台提供的合法路径（如/tmp）
• 内存生成方式需注意数据量大小，超大DataFrame建议使用本地临时文件方案，避免内存溢出

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