python读取Excel表格内公式的值
- 背景：在做业务周报的时候，有一个Excel模板，表里面包含了一些公式，dataframe写入到Excel的时候，有公式的部分通过python读出来的结果是None，需要进行优化
- 参考链接：
-
- 如何使用openpyxl读取Excel单元格的值而不是计算它的公式？ python - Dev59
- Openpyxl 1.8.5：使用openpyxl读取单元格中输入的公式的结果 python - Dev59
- stackoverflow：calculating-excel-sheets-without-opening-them-openpyxl-or-xlwt
- pycel/src/pycel/excelcompiler.py at master · dgorissen/pycel
- Calculation (evaluating Excel formulas in Python) — Python tools for Excel 0.0.2b0 documentation
- 实现方案：最终通过openpyxl+pycel的组合，实现了Excel公式内容的计算，并获取公式的计算结果
from datetime import datetime
# import xlwt
import os
import pandas as pd
import xlrd
from openpyxl import load_workbook
import numpy as np
from collections import defaultdict
from collections import Counter
import xlwings as xw
from pycel import ExcelCompiler
from pycel.excelformula import ExcelFormulafrom openpyxl.utils import get_column_letterfile_name = r'周报_20250206.xlsx'
folder_path = os.getcwd()
file_path = os.path.join(folder_path,file_name)
os.path.exists(file_path)# Open Excel workbook and worksheet in openpyxl, data-only.
wb = load_workbook(filename = file_name,data_only=False)
ws = wb.active
sheet_name = ws.title# 获取工作表的行数和列数
max_row = sheet.max_row
max_column = sheet.max_column# 使用 pycel 编译和计算公式
compiler = ExcelCompiler(filename=file_name)# 逐行逐列读取数据
data = []
for row in range(1, max_row + 1):row_data = []for col in range(1, max_column + 1): cell_value =sheet.cell(row=row, column=col) col_letter = get_column_letter(col) # get_column_letter把1列变成A列result = compiler.evaluate(f'{sheet_name}!{col_letter}{row}') # 计算 Sheet1!C1或者Sheet1!C1:D10 单元格的公式row_data.append(result)data.append(row_data)
df = pd.DataFrame(data)
# df.loc[:369]
# df- 难点：xlrd 不支持xlsx文件
-
- 在解决整个问题的过程中，用了kimi和deepseek
- 解决公式未计算的问题的方案：
-
- ① 手动打开并保存文件：在 Excel 中打开文件并保存，这样公式会被计算并存储在文件中
- ② 自动化保存文件：使用
win32com自动打开 Excel 文件并保存，win32com仅适用于 Windows 系统。如果你在 macOS 上工作，建议使用xlwings，因为它支持 macOS 和 Windows。 - ③ 使用
xlwings实时计算公式，允许与 Excel 进行交互，包括打开文件、激活窗口等操作，可以直接调用 Excel 来处理公式计算。这里面会有一个问题，在MacOS环境下使用xlwings会出现权限的问题，要修改MacOS的系统权限才能进行操作，比较麻烦。 - ④ 使用subprocess.Popen激活Excel窗口，打开新的电子表格（相当于手动刷新），并让 Excel 评估电子表格公式，pynput.keyboard 保存更新的电子表格并退出 Excel，使用 data_only=True 的 openpyxl 打开更新的电子表格并获取公式的值。
- ⑤
openpyxl读取Excel的公式内容，注意openpyxl不会自动计算公式的结果。如果需要实时计算公式，可以使用xlwings等库，它能够启动 Excel 并实时计算公式。如果需要读取公式的结果，确保在保存文件时公式已经被计算过，否则data_only=True无法生效。
import openpyxl# 创建新工作簿
workbook = openpyxl.Workbook()# 获取默认工作表
sheet = workbook.active# 写入数据到单元格
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'# 保存工作簿
workbook.save('new_example.xlsx')cell_value = sheet['A1'].value
print(cell_value)for row in sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):for cell in row:print(cell.value)sheet['A1'] = 'Hello World'data = [[1, 2, 3],[4, 5, 6],[7, 8, 9],
]
for row in data:sheet.append(row)from openpyxl.styles import Fontfont = Font(name='Arial', size=14, bold=True, italic=True, color='FF0000')
sheet['A1'].font = font
sheet['A1'].value = 'Hello, World!'from openpyxl.styles import Border, Sidethin = Side(border_style="thin", color="000000")
thick = Side(border_style="thick", color="FF0000")
border = Border(left=thin, right=thick, top=thin, bottom=thick)
sheet['A1'].border = borderfrom openpyxl.styles import PatternFillfill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
sheet['A1'].fill = fillfrom openpyxl import Workbook# 创建一个新的工作簿
wb = Workbook()
ws = wb.active# 在单元格中写入公式
ws['A1'] = 10
ws['B1'] = 20
ws['C1'] = '=SUM(A1:B1)' # 写入公式# 保存工作簿
wb.save('example.xlsx')from openpyxl import load_workbook# 加载工作簿并设置 data_only=True
wb = load_workbook('example.xlsx', data_only=True)
ws = wb.active# 读取公式的结果
result = ws['C1'].value
print(result) # 输出公式计算后的结果在 openpyxl 中，cell.data_type 属性会返回单元格的数据类型，具体类型如下：
'n'：数值
's'：字符串
'b'：布尔值
'd'：日期
'e'：错误import openpyxl# 加载 Excel 文件
file_path = 'example.xlsx'
workbook = openpyxl.load_workbook(file_path)
sheet = workbook.active# 遍历单元格并查看类型
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):for cell in row:print(f"Cell {cell.coordinate} is of type {cell.data_type}")