使用Databricks从Azure Data Lake读取Excel值遇公式单元格返回None问题

问题场景 #

将Excel文件从Sharepoint复制到Azure Data Lake（ADL）后，使用Databricks读取带公式的单元格时返回None，具体操作流程：

1. 从Sharepoint获取Excel文件并通过openpyxl加载（data_only=True），此时能正常读取公式单元格的计算值
2. 将加载后的工作簿保存到本地，再复制到ADL
3. 从ADL读取该Excel文件，同样用openpyxl加载（data_only=True），公式单元格返回None

问题原因 #

openpyxl的data_only=True参数读取的是Excel文件中缓存的公式计算结果，这个值是Excel客户端上次计算后写入文件的。但openpyxl本身不具备公式计算能力，当你用openpyxl保存工作簿时，它不会保留原有的缓存值，也不会重新计算公式，导致保存后的文件中公式单元格的缓存值丢失，再次用data_only=True读取时就会返回None。

解决方案 #

方案1：直接保存Sharepoint获取的原始文件到ADL（推荐） #

跳过openpyxl加载和保存的步骤，直接将从Sharepoint请求到的原始文件内容写入ADL，这样文件会保留Excel原有的缓存计算值，后续读取时就能正常获取公式单元格的值。

修改后的代码：

import os
import requests
import io
from openpyxl import load_workbook

# 从Sharepoint获取文件
get_file_url = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{drive_id}/items/{file_id}/content"
response = requests.get(get_file_url, headers=headers)
file_content = response.content

# 直接将原始内容写入ADL，不经过openpyxl处理
file_name= 'test.xlsx'
datalake_path = f"/dbfs/mnt/forms"

with open(f"{datalake_path}/{file_name}", "wb") as f:
    f.write(file_content)

# 验证读取
with open(f"{datalake_path}/{file_name}", "rb") as file:
    excel_data = io.BytesIO(file.read())
    workbook2 = load_workbook(excel_data, data_only=True)
    sheet2 = workbook2['Form']
    print(sheet2['T1'].value)

方案2：保存前将公式单元格替换为计算值 #

如果必须通过openpyxl处理文件（比如需要修改内容），可以在保存前遍历所有单元格，将公式单元格的内容替换为当前读取到的缓存值，这样保存后的文件就不再包含公式，只有计算结果。

代码示例：

import os
from shutil import copyfile
import requests
import io
from openpyxl import load_workbook

# 从Sharepoint获取文件
get_file_url = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{drive_id}/items/{file_id}/content"
response = requests.get(get_file_url, headers=headers)
file_content = response.content
workbook = load_workbook(filename=io.BytesIO(file_content), data_only=True)

sheet_name = 'Form'
sheet = workbook[sheet_name]

# 遍历所有单元格，替换公式为计算值
for row in sheet.iter_rows():
    for cell in row:
        # 判断单元格是否为公式类型
        if cell.data_type == 'f':
            # 将单元格值设置为已读取的缓存计算值
            cell.value = cell.value
            # 根据值类型设置单元格数据类型（可选，确保格式正确）
            if isinstance(cell.value, (int, float)):
                cell.data_type = 'n'
            elif isinstance(cell.value, str):
                cell.data_type = 's'

# 保存并复制到ADL
file_name= 'test.xlsx'
workbook.save(f"/tmp/{file_name}")
workbook.close()

datalake_path = f"/dbfs/mnt/forms"
copyfile(f"/tmp/{file_name}", f"{datalake_path}/{file_name}")

# 验证读取
with open(f"{datalake_path}/{file_name}", "rb") as file:
    excel_data = io.BytesIO(file.read())
    workbook2 = load_workbook(excel_data, data_only=True)
    sheet2 = workbook2[sheet_name]
    print(sheet2['T1'].value)

方案3：读取ADL文件时计算公式值 #

如果已经保存了带公式的文件到ADL，可以使用支持公式计算的库（如xlcalculator）来读取并计算公式结果，无需依赖文件中的缓存值。

首先安装依赖（Databricks中可以通过%pip安装）：

%pip install xlcalculator

然后使用以下代码读取：

import io
from openpyxl import load_workbook
from xlcalculator import ModelCompiler, Evaluator

datalake_path = f"/dbfs/mnt/forms"
file_name= 'test.xlsx'

with open(f"{datalake_path}/{file_name}", "rb") as file:
    excel_data = io.BytesIO(file.read())
    # 用data_only=False加载，读取公式本身
    workbook2 = load_workbook(excel_data, data_only=False)
    sheet_name = 'Form'

    # 编译Excel模型并计算单元格值
    compiler = ModelCompiler()
    model = compiler.read_workbook(excel_data)
    evaluator = Evaluator(model)

    # 计算指定单元格的值
    cell_address = f"{sheet_name}!T1"
    calculated_value = evaluator.evaluate(cell_address)
    print(calculated_value)

注意：xlcalculator支持大部分Excel内置函数，但少数特殊函数可能无法兼容，需要根据实际情况测试。

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