working-with-spreadsheets

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Working with Spreadsheets

电子表格处理

Quick Start

快速入门

python

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')

python

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Revenue'
sheet['B1'] = 1000
sheet['B2'] = '=B1*1.1'  # 使用公式，不要硬编码值！
wb.save('output.xlsx')

Critical Rule: Use Formulas, Not Hardcoded Values

重要规则：使用公式，而非硬编码值

Always use Excel formulas instead of calculating in Python.

python

undefined

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

python

undefined

WRONG - Hardcoding calculated values

错误示例 - 硬编码计算值

total = df['Sales'].sum() sheet['B10'] = total # Hardcodes 5000

total = df['Sales'].sum() sheet['B10'] = total # 硬编码为5000

CORRECT - Using Excel formulas

正确示例 - 使用Excel公式

sheet['B10'] = '=SUM(B2:B9)'

undefined

sheet['B10'] = '=SUM(B2:B9)'

undefined

Financial Model Color Coding Standards

财务模型颜色编码标准

Color	RGB	Usage
Blue text	0,0,255	Hardcoded inputs, scenario values
Black text	0,0,0	ALL formulas and calculations
Green text	0,128,0	Links from other worksheets
Red text	255,0,0	External links to other files
Yellow background	255,255,0	Key assumptions needing attention

python

from openpyxl.styles import Font

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

python

from openpyxl.styles import Font

Input cell (user changeable)

输入单元格（用户可修改）

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

sheet['B5'].font = Font(color='0000FF') # 蓝色

Formula cell

公式单元格

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

sheet['C5'] = '=B5*1.1' sheet['C5'].font = Font(color='000000') # 黑色

Cross-sheet link

跨工作表链接

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

undefined

sheet['D5'] = "=Sheet2!A1" sheet['D5'].font = Font(color='008000') # 绿色

undefined

Number Formatting Standards

数字格式规范

python

undefined

python

undefined

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)

年份作为文本（而非数字2024）

sheet['A1'] = '2024' # String, not number

undefined

sheet['A1'] = '2024' # 字符串，不是数字

undefined

Library Selection

库选择

Task	Library	Example
Data analysis	pandas	`df = pd.read_excel('file.xlsx')`
Formulas & formatting	openpyxl	`sheet['A1'] = '=SUM(B:B)'`
Large files (read)	openpyxl	`load_workbook('file.xlsx', read_only=True)`
Large files (write)	openpyxl	`Workbook(write_only=True)`

任务	库	示例
数据分析	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)`

Reading Excel Files

读取Excel文件

python

import pandas as pd
from openpyxl import load_workbook

python

import pandas as pd
from openpyxl import load_workbook

pandas - data analysis

pandas - 数据分析

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

df = pd.read_excel('file.xlsx') all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # 工作表对应的DataFrame字典

openpyxl - preserve formulas

openpyxl - 保留公式

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

wb = load_workbook('file.xlsx') sheet = wb.active print(sheet['A1'].value) # 返回公式字符串

openpyxl - get calculated values (WARNING: loses formulas on save!)

openpyxl - 获取计算后的值（注意：保存后会丢失公式！）

wb = load_workbook('file.xlsx', data_only=True)

undefined

wb = load_workbook('file.xlsx', data_only=True)

undefined

Creating Excel Files

创建Excel文件

python

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

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

python

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

sheet['A2'] = 'Revenue' sheet['B2'] = 1000000 sheet['B2'].font = Font(color='0000FF') # 蓝色 = 输入值

sheet['A3'] = 'Growth' sheet['B3'] = '=B2*0.1' sheet['B3'].font = Font(color='000000') # 黑色 = 公式

Formatting

格式设置

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

wb.save('model.xlsx')

undefined

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

wb.save('model.xlsx')

undefined

Editing Existing Files

编辑现有文件

python

from openpyxl import load_workbook

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

python

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb['Data']  # 或使用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')

undefined

new_sheet = wb.create_sheet('Analysis') new_sheet['A1'] = '=Data!B5' # 跨工作表引用

wb.save('modified.xlsx')

undefined

Formula Recalculation

公式重算

openpyxl writes formulas but doesn't calculate values. Use LibreOffice to recalculate:

bash

undefined

openpyxl仅写入公式，不计算值。使用LibreOffice进行重算：

bash

undefined

Recalculate and check for errors

重算并检查错误

python recalc.py output.xlsx


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

python recalc.py output.xlsx


脚本返回JSON：
```json
{
  "status": "success",  // 或 "errors_found"
  "total_errors": 0,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {"count": 2, "locations": ["Sheet1!B5", "Sheet1!C10"]}
  }
}

Formula Verification Checklist

公式验证检查清单

Before Building

构建前

Test 2-3 sample references first
Confirm column mapping (column 64 = BL, not BK)
Remember: DataFrame row 5 = Excel row 6 (1-indexed)

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

Common Pitfalls

常见陷阱

Check for NaN with
```
pd.notna()
```
before using values
FY data often in columns 50+ (far right)
Search ALL occurrences, not just first match
Check denominators before division (#DIV/0!)
Verify cross-sheet references use correct format (
```
Sheet1!A1
```
)

使用
```
pd.notna()
```
检查是否存在NaN值后再使用数据
财年数据通常在第50列及以后（右侧较远位置）
搜索所有匹配项，而非仅第一个
除法前检查分母是否为零（避免#DIV/0!错误）
验证跨工作表引用格式是否正确（
```
Sheet1!A1
```
）

After Building

构建后

Run
```
recalc.py
```
and fix any errors
Verify #REF!, #DIV/0!, #VALUE!, #NAME? = 0

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

Common Errors

常见错误

Error	Cause	Fix
#REF!	Invalid cell reference	Check deleted rows/columns
#DIV/0!	Division by zero	Add IF check: `=IF(B5=0,0,A5/B5)`
#VALUE!	Wrong data type	Check cell contains expected type
#NAME?	Unknown function	Check spelling, quotes around text

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

Verification

验证

Run:

python scripts/verify.py

运行：

python scripts/verify.py

working-with-spreadsheets

Original

Translation

Working with Spreadsheets

电子表格处理

Quick Start

快速入门

Critical Rule: Use Formulas, Not Hardcoded Values

重要规则：使用公式，而非硬编码值

WRONG - Hardcoding calculated values

错误示例 - 硬编码计算值

CORRECT - Using Excel formulas

正确示例 - 使用Excel公式

Financial Model Color Coding Standards

财务模型颜色编码标准

Input cell (user changeable)

输入单元格（用户可修改）

Formula cell

公式单元格

Cross-sheet link

跨工作表链接

Number Formatting Standards

数字格式规范

Currency with thousands separator

带千位分隔符的货币格式

Zeros display as dash

零值显示为短横线

Percentages with one decimal

保留一位小数的百分比格式

Valuation multiples

估值倍数格式

Years as text (not 2,024)

年份作为文本（而非数字2024）

Library Selection

库选择

Reading Excel Files

读取Excel文件

pandas - data analysis

pandas - 数据分析

openpyxl - preserve formulas

openpyxl - 保留公式

openpyxl - get calculated values (WARNING: loses formulas on save!)

openpyxl - 获取计算后的值（注意：保存后会丢失公式！）

Creating Excel Files

创建Excel文件

Headers

表头

Data with formulas

带公式的数据

Formatting

格式设置

Editing Existing Files

编辑现有文件

Modify cells

修改单元格

Add new sheet

添加新工作表

Formula Recalculation

公式重算

Recalculate and check for errors

重算并检查错误

Formula Verification Checklist

公式验证检查清单

Before Building

构建前

Common Pitfalls

常见陷阱

After Building

构建后

Common Errors

常见错误

Verification

验证

Related Skills

相关技能