working-with-spreadsheets

使用电子表格

快速入门

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Revenue'
sheet['B1'] = 1000
sheet['B2'] = '=B1*1.1'  # Use formulas, not hardcoded values!
wb.save('output.xlsx')

关键规则：使用公式，而非硬编码值

始终使用 Excel 公式，而不是在 Python 中计算。

# WRONG - Hardcoding calculated values
total = df['Sales'].sum()
sheet['B10'] = total  # Hardcodes 5000

# CORRECT - Using Excel formulas
sheet['B10'] = '=SUM(B2:B9)'

财务模型颜色编码标准

颜色	RGB	用途
蓝色文本	0,0,255	硬编码输入、情景值
黑色文本	0,0,0	所有公式和计算
绿色文本	0,128,0	来自其他工作表的链接
红色文本	255,0,0	指向其他文件的外部链接
黄色背景	255,255,0	需要关注的关键假设

from openpyxl.styles import Font

# Input cell (user changeable)
sheet['B5'].font = Font(color='0000FF')  # Blue

# Formula cell
sheet['C5'] = '=B5*1.1'
sheet['C5'].font = Font(color='000000')  # Black

# Cross-sheet link
sheet['D5'] = "=Sheet2!A1"
sheet['D5'].font = Font(color='008000')  # Green

数字格式标准

# Currency with thousands separator
sheet['B5'].number_format = '$#,##0'

# Zeros display as dash
sheet['B5'].number_format = '$#,##0;($#,##0);-'

# Percentages with one decimal
sheet['C5'].number_format = '0.0%'

# Valuation multiples
sheet['D5'].number_format = '0.0x'

# Years as text (not 2,024)
sheet['A1'] = '2024'  # String, not number

库选择

任务	库	示例
数据分析	pandas	df = pd.read_excel('file.xlsx')
公式和格式设置	openpyxl	sheet['A1'] = '=SUM(B:B)'
大型文件（读取）	openpyxl	load_workbook('file.xlsx', read_only=True)
大型文件（写入）	openpyxl	Workbook(write_only=True)

读取 Excel 文件

import pandas as pd
from openpyxl import load_workbook

# pandas - data analysis
df = pd.read_excel('file.xlsx')
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # Dict of DataFrames

# openpyxl - preserve formulas
wb = load_workbook('file.xlsx')
sheet = wb.active
print(sheet['A1'].value)  # Returns formula string

# openpyxl - get calculated values (WARNING: loses formulas on save!)
wb = load_workbook('file.xlsx', data_only=True)

创建 Excel 文件

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
sheet = wb.active
sheet.title = 'Model'

# Headers
sheet['A1'] = 'Metric'
sheet['B1'] = '2024'
sheet['A1'].font = Font(bold=True)

# Data with formulas
sheet['A2'] = 'Revenue'
sheet['B2'] = 1000000
sheet['B2'].font = Font(color='0000FF')  # Blue = input

sheet['A3'] = 'Growth'
sheet['B3'] = '=B2*0.1'
sheet['B3'].font = Font(color='000000')  # Black = formula

# Formatting
sheet['B2'].number_format = '$#,##0'
sheet.column_dimensions['A'].width = 20

wb.save('model.xlsx')

编辑现有文件

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb['Data']  # Or wb.active

# Modify cells
sheet['A1'] = 'Updated Value'
sheet.insert_rows(2)
sheet.delete_cols(3)

# Add new sheet
new_sheet = wb.create_sheet('Analysis')
new_sheet['A1'] = '=Data!B5'  # Cross-sheet reference

wb.save('modified.xlsx')

公式重新计算

openpyxl 会写入公式，但不会计算其值。 使用 LibreOffice 重新计算：

# Recalculate and check for errors
python recalc.py output.xlsx

脚本返回 JSON：

{
  "status": "success",  // or "errors_found"
  "total_errors": 0,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {"count": 2, "locations": ["Sheet1!B5", "Sheet1!C10"]}
  }
}

公式验证清单

构建前

• 先测试 2-3 个示例引用
• 确认列映射（第 64 列是 BL，而不是 BK）
• 记住：DataFrame 第 5 行 = Excel 第 6 行（从 1 开始索引）

常见问题

• 使用值前，通过 pd.notna() 检查是否为 NaN
• FY 数据通常位于第 50 列及之后（靠右侧）
• 搜索所有匹配项，而不只是第一个
• 除法前检查分母（#DIV/0!）
• 确认跨工作表引用使用正确格式（Sheet1!A1）

构建后

• 运行 recalc.py 并修复所有错误
• 确认 #REF!、#DIV/0!、#VALUE!、#NAME? 均为 0

常见错误

错误	原因	修复方法
#REF!	单元格引用无效	检查是否删除了行或列
#DIV/0!	除数为零	添加 IF 检查：=IF(B5=0,0,A5/B5)
#VALUE!	数据类型错误	检查单元格是否包含预期类型
#NAME?	未知函数	检查拼写，以及文本两侧的引号

验证

运行：python scripts/verify.py

相关技能

• building-nextjs-apps - 用于电子表格上传的前端
• scaffolding-fastapi-dapr - 用于电子表格处理的 API