Back to Details

xlsx-manipulation

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

XLSX Manipulation Skill

XLSX 电子表格操作技能

Overview

概述

This skill enables programmatic creation, editing, and manipulation of Microsoft Excel (.xlsx) spreadsheets using the openpyxl library. Create professional spreadsheets with formulas, formatting, charts, and data validation without manual editing.
本技能支持使用openpyxl库以编程方式创建、编辑和操作Microsoft Excel（.xlsx）电子表格。无需手动编辑，即可创建包含公式、格式设置、图表和数据验证的专业电子表格。

How to Use

使用方法

  1. Describe the spreadsheet you want to create or modify
  2. Provide data, formulas, or formatting requirements
  3. I'll generate openpyxl code and execute it
Example prompts:
  • "Create a budget spreadsheet with monthly tracking"
  • "Add conditional formatting to highlight values above threshold"
  • "Generate a pivot-table-like summary from this data"
  • "Create a dashboard with charts and KPIs"
  1. 描述你想要创建或修改的电子表格
  2. 提供数据、公式或格式设置要求
  3. 我会生成openpyxl代码并执行
示例提示：
  • "创建带月度跟踪的预算电子表格"
  • "添加条件格式以高亮显示超过阈值的值"
  • "从该数据生成类似数据透视表的摘要"
  • "创建包含图表和关键指标（KPI）的仪表盘"

Domain Knowledge

领域知识

openpyxl Fundamentals

openpyxl 基础

python
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Fill, Border, Alignment
from openpyxl.chart import BarChart, Reference
python
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Fill, Border, Alignment
from openpyxl.chart import BarChart, Reference

Create new workbook

创建新工作簿

wb = Workbook() ws = wb.active
wb = Workbook() ws = wb.active

Or open existing

或打开现有工作簿

wb = load_workbook('existing.xlsx') ws = wb.active
undefined
wb = load_workbook('existing.xlsx') ws = wb.active
undefined

Workbook Structure

工作簿结构

Workbook
├── worksheets (sheets/tabs)
│   ├── cells (data storage)
│   ├── rows/columns (formatting)
│   ├── merged_cells
│   └── charts
├── defined_names (named ranges)
└── styles (formatting templates)
Workbook
├── worksheets（工作表/标签页）
│   ├── cells（数据存储单元）
│   ├── rows/columns（行/列，用于格式设置）
│   ├── merged_cells（合并单元格）
│   └── charts（图表）
├── defined_names（命名区域）
└── styles（格式设置模板）

Working with Cells

单元格操作

Basic Cell Operations

基础单元格操作

python
undefined
python
undefined

By cell reference

通过单元格引用赋值

ws['A1'] = 'Header' ws['B1'] = 42
ws['A1'] = '表头' ws['B1'] = 42

By row, column

通过行号和列号赋值

ws.cell(row=1, column=3, value='Data')
ws.cell(row=1, column=3, value='数据')

Multiple cells

批量操作多个单元格

ws['A1:C1'] = [['Col1', 'Col2', 'Col3']]
ws['A1:C1'] = [['列1', '列2', '列3']]

Append rows

追加行

ws.append(['Row', 'Data', 'Here'])
undefined
ws.append(['行', '数据', '示例'])
undefined

Reading Cells

读取单元格数据

python
undefined
python
undefined

Single cell

读取单个单元格

value = ws['A1'].value
value = ws['A1'].value

Cell range

读取单元格区域

for row in ws['A1:C3']: for cell in row: print(cell.value)
for row in ws['A1:C3']: for cell in row: print(cell.value)

Iterate rows

遍历行

for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=3): for cell in row: print(cell.value)
undefined
for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=3): for cell in row: print(cell.value)
undefined

Formulas

公式

python
undefined
python
undefined

Basic formulas

基础公式

ws['D1'] = '=SUM(A1:C1)' ws['D2'] = '=AVERAGE(A2:C2)' ws['E1'] = '=IF(D1>100,"High","Low")'
ws['D1'] = '=SUM(A1:C1)' ws['D2'] = '=AVERAGE(A2:C2)' ws['E1'] = '=IF(D1>100,"高","低")'

Named ranges

命名区域

from openpyxl.workbook.defined_name import DefinedName ref = "Sheet!$A$1:$C$10" defn = DefinedName("SalesData", attr_text=ref) wb.defined_names.add(defn)
from openpyxl.workbook.defined_name import DefinedName ref = "Sheet!$A$1:$C$10" defn = DefinedName("SalesData", attr_text=ref) wb.defined_names.add(defn)

Use named range

使用命名区域

ws['F1'] = '=SUM(SalesData)'
undefined
ws['F1'] = '=SUM(SalesData)'
undefined

Formatting

格式设置

Cell Styles

单元格样式

python
from openpyxl.styles import Font, Fill, PatternFill, Border, Side, Alignment
python
from openpyxl.styles import Font, Fill, PatternFill, Border, Side, Alignment

Font

字体设置

ws['A1'].font = Font( name='Arial', size=14, bold=True, italic=False, color='FF0000' # Red )
ws['A1'].font = Font( name='Arial', size=14, bold=True, italic=False, color='FF0000' # 红色 )

Fill (background)

填充（背景色）

ws['A1'].fill = PatternFill( start_color='FFFF00', # Yellow end_color='FFFF00', fill_type='solid' )
ws['A1'].fill = PatternFill( start_color='FFFF00', # 黄色 end_color='FFFF00', fill_type='solid' )

Border

边框

thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) ws['A1'].border = thin_border
thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) ws['A1'].border = thin_border

Alignment

对齐方式

ws['A1'].alignment = Alignment( horizontal='center', vertical='center', wrap_text=True )
undefined
ws['A1'].alignment = Alignment( horizontal='center', vertical='center', wrap_text=True )
undefined

Number Formats

数字格式

python
undefined
python
undefined

Currency

货币格式

ws['B2'].number_format = '$#,##0.00'
ws['B2'].number_format = '$#,##0.00'

Percentage

百分比格式

ws['C2'].number_format = '0.00%'
ws['C2'].number_format = '0.00%'

Date

日期格式

ws['D2'].number_format = 'YYYY-MM-DD'
ws['D2'].number_format = 'YYYY-MM-DD'

Custom

自定义格式

ws['E2'].number_format = '#,##0.00 "units"'
undefined
ws['E2'].number_format = '#,##0.00 "单位"'
undefined

Conditional Formatting

条件格式

python
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
from openpyxl.styles import PatternFill
python
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
from openpyxl.styles import PatternFill

Color scale (heatmap)

颜色渐变（热力图）

color_scale = ColorScaleRule( start_type='min', start_color='FF0000', end_type='max', end_color='00FF00' ) ws.conditional_formatting.add('A1:A10', color_scale)
color_scale = ColorScaleRule( start_type='min', start_color='FF0000', end_type='max', end_color='00FF00' ) ws.conditional_formatting.add('A1:A10', color_scale)

Cell value rule

单元格值规则

red_fill = PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid') rule = CellIsRule(operator='greaterThan', formula=['100'], fill=red_fill) ws.conditional_formatting.add('B1:B10', rule)
undefined
red_fill = PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid') rule = CellIsRule(operator='greaterThan', formula=['100'], fill=red_fill) ws.conditional_formatting.add('B1:B10', rule)
undefined

Charts

图表

python
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
python
from openpyxl.chart import BarChart, LineChart, PieChart, Reference

Prepare data

准备数据

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5) categories = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5) categories = Reference(ws, min_col=1, min_row=2, max_row=5)

Bar Chart

柱状图

chart = BarChart() chart.type = "col" # or "bar" for horizontal chart.title = "Sales by Region" chart.add_data(data, titles_from_data=True) chart.set_categories(categories) chart.shape = 4 ws.add_chart(chart, "E1")
chart = BarChart() chart.type = "col" # 或"bar"表示横向柱状图 chart.title = "各区域销售额" chart.add_data(data, titles_from_data=True) chart.set_categories(categories) chart.shape = 4 ws.add_chart(chart, "E1")

Line Chart

折线图

line = LineChart() line.title = "Trend Analysis" line.add_data(data, titles_from_data=True) line.set_categories(categories) ws.add_chart(line, "E15")
line = LineChart() line.title = "趋势分析" line.add_data(data, titles_from_data=True) line.set_categories(categories) ws.add_chart(line, "E15")

Pie Chart

饼图

pie = PieChart() pie.add_data(data, titles_from_data=True) pie.set_categories(categories) ws.add_chart(pie, "M1")
undefined
pie = PieChart() pie.add_data(data, titles_from_data=True) pie.set_categories(categories) ws.add_chart(pie, "M1")
undefined

Data Validation

数据验证

python
from openpyxl.worksheet.datavalidation import DataValidation
python
from openpyxl.worksheet.datavalidation import DataValidation

Dropdown list

下拉列表

dv = DataValidation( type="list", formula1='"Option1,Option2,Option3"', allow_blank=True ) dv.error = "Please select from list" dv.errorTitle = "Invalid Input" ws.add_data_validation(dv) dv.add('A1:A100')
dv = DataValidation( type="list", formula1='"选项1,选项2,选项3"', allow_blank=True ) dv.error = "请从列表中选择" dv.errorTitle = "输入无效" ws.add_data_validation(dv) dv.add('A1:A100')

Number range

数字范围验证

dv_num = DataValidation( type="whole", operator="between", formula1="1", formula2="100" ) ws.add_data_validation(dv_num) dv_num.add('B1:B100')
undefined
dv_num = DataValidation( type="whole", operator="between", formula1="1", formula2="100" ) ws.add_data_validation(dv_num) dv_num.add('B1:B100')
undefined

Sheet Operations

工作表操作

python
undefined
python
undefined

Create new sheet

创建新工作表

ws2 = wb.create_sheet("Data") ws3 = wb.create_sheet("Summary", 0) # At position 0
ws2 = wb.create_sheet("数据") ws3 = wb.create_sheet("摘要", 0) # 插入到第0位

Rename

重命名工作表

ws.title = "Main Report"
ws.title = "主报告"

Delete

删除工作表

del wb["Sheet2"]
del wb["Sheet2"]

Copy

复制工作表

source = wb["Template"] target = wb.copy_worksheet(source)
undefined
source = wb["模板"] target = wb.copy_worksheet(source)
undefined

Row/Column Operations

行/列操作

python
undefined
python
undefined

Set column width

设置列宽

ws.column_dimensions['A'].width = 20
ws.column_dimensions['A'].width = 20

Set row height

设置行高

ws.row_dimensions[1].height = 30
ws.row_dimensions[1].height = 30

Hide column

隐藏列

ws.column_dimensions['C'].hidden = True
ws.column_dimensions['C'].hidden = True

Freeze panes

冻结窗格

ws.freeze_panes = 'B2' # Freeze row 1 and column A
ws.freeze_panes = 'B2' # 冻结第1行和A列

Auto-filter

自动筛选

ws.auto_filter.ref = "A1:D100"
undefined
ws.auto_filter.ref = "A1:D100"
undefined

Best Practices

最佳实践

  1. Use Templates: Start with a .xlsx template for complex formatting
  2. Batch Operations: Minimize cell-by-cell operations for speed
  3. Named Ranges: Use defined names for clearer formulas
  4. Data Validation: Add validation to prevent input errors
  5. Save Incrementally: For large files, save periodically
  1. 使用模板：对于复杂格式，从.xlsx模板开始
  2. 批量操作：尽量减少逐个单元格操作以提升速度
  3. 命名区域：使用定义的名称让公式更清晰
  4. 数据验证：添加验证以防止输入错误
  5. 增量保存：处理大文件时，定期保存

Common Patterns

常见模式

Data Import

数据导入

python
def import_csv_to_xlsx(csv_path, xlsx_path):
    import csv
    wb = Workbook()
    ws = wb.active
    
    with open(csv_path) as f:
        reader = csv.reader(f)
        for row in reader:
            ws.append(row)
    
    wb.save(xlsx_path)
python
def import_csv_to_xlsx(csv_path, xlsx_path):
    import csv
    wb = Workbook()
    ws = wb.active
    
    with open(csv_path) as f:
        reader = csv.reader(f)
        for row in reader:
            ws.append(row)
    
    wb.save(xlsx_path)

Report Template

报告模板

python
def create_monthly_report(data, output_path):
    wb = Workbook()
    ws = wb.active
    ws.title = "Monthly Report"
    
    # Headers
    headers = ['Date', 'Revenue', 'Expenses', 'Profit']
    ws.append(headers)
    
    # Style headers
    for col in range(1, 5):
        cell = ws.cell(1, col)
        cell.font = Font(bold=True)
        cell.fill = PatternFill('solid', fgColor='4472C4')
        cell.font = Font(bold=True, color='FFFFFF')
    
    # Data
    for row in data:
        ws.append(row)
    
    # Add totals
    last_row = len(data) + 1
    ws.cell(last_row + 1, 1, 'TOTAL')
    ws.cell(last_row + 1, 2, f'=SUM(B2:B{last_row})')
    ws.cell(last_row + 1, 3, f'=SUM(C2:C{last_row})')
    ws.cell(last_row + 1, 4, f'=SUM(D2:D{last_row})')
    
    wb.save(output_path)
python
def create_monthly_report(data, output_path):
    wb = Workbook()
    ws = wb.active
    ws.title = "月度报告"
    
    # 表头
    headers = ['日期', '收入', '支出', '利润']
    ws.append(headers)
    
    # 表头样式
    for col in range(1, 5):
        cell = ws.cell(1, col)
        cell.font = Font(bold=True)
        cell.fill = PatternFill('solid', fgColor='4472C4')
        cell.font = Font(bold=True, color='FFFFFF')
    
    # 数据行
    for row in data:
        ws.append(row)
    
    # 添加总计
    last_row = len(data) + 1
    ws.cell(last_row + 1, 1, '总计')
    ws.cell(last_row + 1, 2, f'=SUM(B2:B{last_row})')
    ws.cell(last_row + 1, 3, f'=SUM(C2:C{last_row})')
    ws.cell(last_row + 1, 4, f'=SUM(D2:D{last_row})')
    
    wb.save(output_path)

Examples

示例

Example 1: Budget Tracker

示例1：预算跟踪表

python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Budget 2024"
python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "2024年预算"

Headers

表头

months = ['Category', 'Jan', 'Feb', 'Mar', 'Q1 Total'] ws.append(months)
months = ['类别', '1月', '2月', '3月', 'Q1总计'] ws.append(months)

Categories and data

类别和数据

budget_data = [ ['Salary', 5000, 5000, 5000], ['Rent', -1500, -1500, -1500], ['Utilities', -200, -180, -220], ['Food', -400, -450, -380], ['Transport', -150, -160, -140], ['Entertainment', -200, -250, -200], ]
for row in budget_data: ws.append(row + [f'=SUM(B{ws.max_row + 1}:D{ws.max_row + 1})'])
budget_data = [ ['工资', 5000, 5000, 5000], ['房租', -1500, -1500, -1500], ['水电', -200, -180, -220], ['餐饮', -400, -450, -380], ['交通', -150, -160, -140], ['娱乐', -200, -250, -200], ]
for row in budget_data: ws.append(row + [f'=SUM(B{ws.max_row + 1}:D{ws.max_row + 1})'])

Total row

总计行

ws.append(['TOTAL', f'=SUM(B2:B{ws.max_row})', f'=SUM(C2:C{ws.max_row})', f'=SUM(D2:D{ws.max_row})', f'=SUM(E2:E{ws.max_row})' ])
ws.append(['总计', f'=SUM(B2:B{ws.max_row})', f'=SUM(C2:C{ws.max_row})', f'=SUM(D2:D{ws.max_row})', f'=SUM(E2:E{ws.max_row})' ])

Formatting

格式设置

header_fill = PatternFill('solid', fgColor='366092') header_font = Font(bold=True, color='FFFFFF')
for cell in ws[1]: cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal='center')
header_fill = PatternFill('solid', fgColor='366092') header_font = Font(bold=True, color='FFFFFF')
for cell in ws[1]: cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal='center')

Currency format

货币格式

for row in ws.iter_rows(min_row=2, min_col=2, max_col=5): for cell in row: cell.number_format = '$#,##0.00'
for row in ws.iter_rows(min_row=2, min_col=2, max_col=5): for cell in row: cell.number_format = '$#,##0.00'

Column widths

列宽设置

ws.column_dimensions['A'].width = 15 for col in range(2, 6): ws.column_dimensions[get_column_letter(col)].width = 12
wb.save('budget_2024.xlsx')
undefined
ws.column_dimensions['A'].width = 15 for col in range(2, 6): ws.column_dimensions[get_column_letter(col)].width = 12
wb.save('budget_2024.xlsx')
undefined

Example 2: Sales Dashboard

示例2：销售仪表盘

python
from openpyxl import Workbook
from openpyxl.chart import BarChart, PieChart, Reference
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Sales Dashboard"
python
from openpyxl import Workbook
from openpyxl.chart import BarChart, PieChart, Reference
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "销售仪表盘"

Data

数据

ws.append(['Region', 'Q1', 'Q2', 'Q3', 'Q4']) data = [ ['North', 150000, 165000, 180000, 195000], ['South', 120000, 125000, 140000, 155000], ['East', 180000, 190000, 210000, 225000], ['West', 95000, 110000, 125000, 140000], ] for row in data: ws.append(row)
ws.append(['区域', 'Q1', 'Q2', 'Q3', 'Q4']) data = [ ['北部', 150000, 165000, 180000, 195000], ['南部', 120000, 125000, 140000, 155000], ['东部', 180000, 190000, 210000, 225000], ['西部', 95000, 110000, 125000, 140000], ] for row in data: ws.append(row)

Bar Chart

柱状图

data_ref = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=5) cats_ref = Reference(ws, min_col=1, min_row=2, max_row=5)
bar = BarChart() bar.type = "col" bar.title = "Quarterly Sales by Region" bar.add_data(data_ref, titles_from_data=True) bar.set_categories(cats_ref) bar.height = 10 bar.width = 15 ws.add_chart(bar, "A8")
data_ref = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=5) cats_ref = Reference(ws, min_col=1, min_row=2, max_row=5)
bar = BarChart() bar.type = "col" bar.title = "各区域季度销售额" bar.add_data(data_ref, titles_from_data=True) bar.set_categories(cats_ref) bar.height = 10 bar.width = 15 ws.add_chart(bar, "A8")

Pie Chart - Q4 breakdown

饼图 - Q4份额分布

pie_data = Reference(ws, min_col=5, min_row=1, max_row=5) pie = PieChart() pie.title = "Q4 Market Share" pie.add_data(pie_data, titles_from_data=True) pie.set_categories(cats_ref) ws.add_chart(pie, "J8")
wb.save('sales_dashboard.xlsx')
undefined
pie_data = Reference(ws, min_col=5, min_row=1, max_row=5) pie = PieChart() pie.title = "Q4市场份额" pie.add_data(pie_data, titles_from_data=True) pie.set_categories(cats_ref) ws.add_chart(pie, "J8")
wb.save('sales_dashboard.xlsx')
undefined

Limitations

局限性

  • Cannot execute VBA macros
  • Complex pivot tables not fully supported
  • Limited sparkline support
  • External data connections not supported
  • Some advanced chart types unavailable
  • 无法执行VBA宏
  • 复杂数据透视表未完全支持
  • 迷你图支持有限
  • 不支持外部数据连接
  • 部分高级图表类型不可用

Installation

安装

bash
pip install openpyxl
bash
pip install openpyxl

Resources

资源