xlsx-manipulation
Create, edit, and manipulate Excel spreadsheets programmatically using openpyxl
- License 许可
- MIT
- 最近更新
- 2026-07-18
- Stars
- ★ 0
- Forks
- 1
01 前言 Front Matter
- mcp
- { "tools": [ "read_xlsx", "create_xlsx", "apply_formula", "create_chart" ], "server": "office-mcp" }
- name
- xlsx-manipulation
- tags
- [ "xlsx", "excel", "manipulation", "editing" ]
- author
- claude-office-skills
- models
- { "compatible": [ "claude-3-5-sonnet", "gpt-4", "gpt-4o" ], "recommended": [ "claude-sonnet-4", "claude-opus-4" ] }
- license
- MIT
- version
- 1.0
- category
- spreadsheet
- languages
- [ "en", "zh" ]
- department
- All
- description
- Create, edit, and manipulate Excel spreadsheets programmatically using openpyxl
- capabilities
- [ "spreadsheet_creation", "formula_handling", "charting" ]
02 资源文件 1
- 参考文档metadata.json488 B
03 SKILL.md 正文
XLSX Manipulation Skill
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.
How to Use
- Describe the spreadsheet you want to create or modify
- Provide data, formulas, or formatting requirements
- 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"
Domain Knowledge
openpyxl Fundamentals
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
# Or open existing
wb = load_workbook('existing.xlsx')
ws = wb.active
Workbook Structure
Workbook
├── worksheets (sheets/tabs)
│ ├── cells (data storage)
│ ├── rows/columns (formatting)
│ ├── merged_cells
│ └── charts
├── defined_names (named ranges)
└── styles (formatting templates)
Working with Cells
Basic Cell Operations
# By cell reference
ws['A1'] = 'Header'
ws['B1'] = 42
# By row, column
ws.cell(row=1, column=3, value='Data')
# Multiple cells
ws['A1:C1'] = [['Col1', 'Col2', 'Col3']]
# Append rows
ws.append(['Row', 'Data', 'Here'])
Reading Cells
# Single cell
value = ws['A1'].value
# Cell range
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)
Formulas
# Basic formulas
ws['D1'] = '=SUM(A1:C1)'
ws['D2'] = '=AVERAGE(A2:C2)'
ws['E1'] = '=IF(D1>100,"High","Low")'
# 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)
# Use named range
ws['F1'] = '=SUM(SalesData)'
Formatting
Cell Styles
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
)
# Fill (background)
ws['A1'].fill = PatternFill(
start_color='FFFF00', # Yellow
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
# Alignment
ws['A1'].alignment = Alignment(
horizontal='center',
vertical='center',
wrap_text=True
)
Number Formats
# Currency
ws['B2'].number_format = '$#,##0.00'
# Percentage
ws['C2'].number_format = '0.00%'
# Date
ws['D2'].number_format = 'YYYY-MM-DD'
# Custom
ws['E2'].number_format = '#,##0.00 "units"'
Conditional Formatting
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)
# 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)
Charts
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)
# 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")
# 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")
# Pie Chart
pie = PieChart()
pie.add_data(data, titles_from_data=True)
pie.set_categories(categories)
ws.add_chart(pie, "M1")
Data Validation
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')
# Number range
dv_num = DataValidation(
type="whole",
operator="between",
formula1="1",
formula2="100"
)
ws.add_data_validation(dv_num)
dv_num.add('B1:B100')
Sheet Operations
# Create new sheet
ws2 = wb.create_sheet("Data")
ws3 = wb.create_sheet("Summary", 0) # At position 0
# Rename
ws.title = "Main Report"
# Delete
del wb["Sheet2"]
# Copy
source = wb["Template"]
target = wb.copy_worksheet(source)
Row/Column Operations
# Set column width
ws.column_dimensions['A'].width = 20
# Set row height
ws.row_dimensions[1].height = 30
# Hide column
ws.column_dimensions['C'].hidden = True
# Freeze panes
ws.freeze_panes = 'B2' # Freeze row 1 and column A
# Auto-filter
ws.auto_filter.ref = "A1:D100"
Best Practices
- Use Templates: Start with a .xlsx template for complex formatting
- Batch Operations: Minimize cell-by-cell operations for speed
- Named Ranges: Use defined names for clearer formulas
- Data Validation: Add validation to prevent input errors
- Save Incrementally: For large files, save periodically
Common Patterns
Data Import
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
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)
Examples
Example 1: Budget Tracker
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"
# Headers
months = ['Category', 'Jan', 'Feb', 'Mar', 'Q1 Total']
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})'])
# 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})'
])
# 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')
# 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'
# 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')
Example 2: Sales Dashboard
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"
# 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)
# 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")
# Pie Chart - Q4 breakdown
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')
Limitations
- Cannot execute VBA macros
- Complex pivot tables not fully supported
- Limited sparkline support
- External data connections not supported
- Some advanced chart types unavailable
Installation
pip install openpyxl
Resources
04 同仓库更多 Skills
arxiv-search
Search arXiv physics, math, and computer science preprints using natural language queries. Powered by Valyu semantic search.
arxiv-search
Search arXiv physics, math, and computer science preprints using natural language queries. Powered by Valyu semantic search.
working-with-legacy-code
Safely change and test untested codebases using Feathers' "Working Effectively with Legacy Code". Use when the user mentions "legacy code", "no tests", "untested codebase", "how do I test this", "seams", "characterization tests", "golden master", "sprout method", "afraid to change this code", "monster method", "dependency breaking", or "inherited a messy codebase". Also trigger when changing code without tests safely, getting a class under test when constructors, statics, or singletons block it, adding features to tangled modules, or planning incremental test coverage for an old codebase. Covers the legacy-code change algorithm, seams, characterization tests, sprout/wrap, and dependency-breaking techniques. For refactoring code that already has tests, see refactoring-patterns. For day-to-day code quality, see clean-code.
working-with-legacy-code
Safely change and test untested codebases using Feathers' "Working Effectively with Legacy Code". Use when the user mentions "legacy code", "no tests", "untested codebase", "how do I test this", "seams", "characterization tests", "golden master", "sprout method", "afraid to change this code", "monster method", "dependency breaking", or "inherited a messy codebase". Also trigger when changing code without tests safely, getting a class under test when constructors, statics, or singletons block it, adding features to tangled modules, or planning incremental test coverage for an old codebase. Covers the legacy-code change algorithm, seams, characterization tests, sprout/wrap, and dependency-breaking techniques. For refactoring code that already has tests, see refactoring-patterns. For day-to-day code quality, see clean-code.
web-typography
Select, pair, and implement typefaces for web projects. Use when the user mentions "font pairing", "which typeface", "line height", "responsive typography", "web font loading", "type hierarchy", "variable fonts", "FOUT/FOIT", "typographic scale", or "the text is hard to read". Also trigger when choosing between system fonts and web fonts, optimizing font-loading performance, or designing readable long-form content. Covers readability evaluation, CSS implementation, and performance optimization. For overall UI design systems, see refactoring-ui. For dramatic typographic experiences, see top-design.
web-typography
Select, pair, and implement typefaces for web projects. Use when the user mentions "font pairing", "which typeface", "line height", "responsive typography", "web font loading", "type hierarchy", "variable fonts", "FOUT/FOIT", "typographic scale", or "the text is hard to read". Also trigger when choosing between system fonts and web fonts, optimizing font-loading performance, or designing readable long-form content. Covers readability evaluation, CSS implementation, and performance optimization. For overall UI design systems, see refactoring-ui. For dramatic typographic experiences, see top-design.