Create and manipulate Microsoft Excel workbooks programmatically. Build spreadsheets with formulas, charts, conditional formatting, and pivot tables. Handle large datasets efficiently with streaming mode.
Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.
Quelldateien prüfen
Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.
Create and manipulate Microsoft Excel workbooks programmatically. Build spreadsheets with formulas, charts, conditional formatting, and pivot tables. Handle large datasets efficiently with streaming mode.
Openpyxl is a Python library for reading and writing Excel 2010+ xlsx/xlsm files. This skill covers comprehensive patterns for spreadsheet automation including:
Workbook creation with multiple worksheets
Cell operations including formatting, merging, and data validation
Formula support for calculations and dynamic content
Chart generation for data visualization within Excel
Conditional formatting for visual data analysis
Large dataset handling with optimized read/write modes
Pivot table creation for data summarization
Style management for professional appearances
When to Use This Skill
USE when:
Creating Excel reports with formulas and calculations
Generating spreadsheets from database queries
Automating financial reports and dashboards
Building Excel templates with formatting
Processing and transforming existing Excel files
Creating charts and visualizations in Excel
Applying conditional formatting rules
Building data entry forms with validation
Handling large datasets (100k+ rows)
Creating pivot tables programmatically
DON'T USE when:
Only need to read data into pandas (use pandas.read_excel directly)
Need real-time Excel manipulation (use xlwings on Windows)
Working with .xls format (use xlrd/xlwt)
Creating complex macros (requires VBA)
Need Excel-specific features like Power Query
Prerequisites
Installation
# Basic installation
pip install openpyxl
# Using uv (recommended)
uv pip install openpyxl
# With image support
pip install openpyxl Pillow
# With pandas integration
pip install openpyxl pandas
# Full installation
pip install openpyxl Pillow pandas numpy
Verify Installation
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border
openpyxl.chart BarChart, LineChart, PieChart
openpyxl.utils.dataframe dataframe_to_rows
()
from
import
from
import
print
"openpyxl installed successfully!"
Core Capabilities
1. Basic Workbook Creation
"""
Create a basic Excel workbook with data and formatting.
"""from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from datetime import datetime
defcreate_basic_workbook(output_path: str) -> None:
"""Create a basic workbook with common elements."""# Create workbook and select active sheet
wb = Workbook()
ws = wb.active
ws.title = "Sales Report"# Set document properties
wb.properties.creator = "Excel Generator"
wb.properties.title = "Monthly Sales Report"
wb.properties.created = datetime.now()
# Define styles
header_font = Font(bold=True, color="FFFFFF", size=12)
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_alignment = Alignment(horizontal="center", vertical="center")
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
# Headers
headers = ["Product", "Q1", "Q2", "Q3", "Q4", "Total"]
for col, header inenumerate(headers, start=1):
cell = ws.cell(row=1, column=col, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_alignment
cell.border = thin_border
# Data
data = [
["Widget A", 1500, 1800, 2200, 2500],
["Widget B", 800, 950, 1100, 1300],
["Widget C", 2000, 2300, 2600, 2900],
["Widget D", 500, 600, 750, 900],
]
for row_idx, row_data inenumerate(data, start=2):
# Product name
ws.cell(row=row_idx, column=1, value=row_data[0]).border = thin_border
# Quarterly valuesfor col_idx, value inenumerate(row_data[1:], start=2):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
cell.border = thin_border
cell.number_format = '#,##0'# Total formula
total_cell = ws.cell(
row=row_idx,
column=6,
value=f"=SUM(B{row_idx}:E{row_idx})"
)
total_cell.border = thin_border
total_cell.font = Font(bold=True)
total_cell.number_format = '#,##0'# Add totals row
total_row = len(data) + 2
ws.cell(row=total_row, column=1, value="TOTAL").font = Font(bold=True)
for col inrange(2, 7):
col_letter = get_column_letter(col)
cell = ws.cell(
row=total_row,
column=col,
value=f"=SUM({col_letter}2:{col_letter}{total_row-1})"
)
cell.font = Font(bold=True)
cell.number_format = '#,##0'
cell.border = thin_border
# Adjust column widths
column_widths = [15, 12, 12, 12, 12, 14]
for i, width inenumerate(column_widths, start=1):
ws.column_dimensions[get_column_letter(i)].width = width
# Freeze header row
ws.freeze_panes = "A2"# Save workbook
wb.save(output_path)
print(f"Workbook saved to {output_path}")
create_basic_workbook("sales_report.xlsx")
2. Advanced Cell Formatting
"""
Advanced cell formatting with styles, merging, and data validation.
"""from openpyxl import Workbook
from openpyxl.styles import (
Font, PatternFill, Alignment, Border, Side,
GradientFill, NamedStyle, Color
)
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule, CellIsRule, FormulaRule
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation
defcreate_formatted_workbook(output_path: str) -> None:
"""Create workbook with advanced formatting."""
wb = Workbook()
ws = wb.active
ws.title = "Formatted Data"# Create named styles for reuse
header_style = NamedStyle(name="header_style")
header_style.font = Font(bold=True, color="FFFFFF", size=11)
header_style.fill = PatternFill(start_color="2F5496", fill_type="solid")
header_style.alignment = Alignment(horizontal="center", vertical="center")
header_style.border = Border(
bottom=Side(style='medium', color="1F4E79")
)
wb.add_named_style(header_style)
currency_style = NamedStyle(name="currency_style")
currency_style.number_format = '"$"#,##0.00'
currency_style.alignment = Alignment(horizontal="right")
wb.add_named_style(currency_style)
percentage_style = NamedStyle(name="percentage_style")
percentage_style.number_format = '0.0%'
percentage_style.alignment = Alignment(horizontal="center")
wb.add_named_style(percentage_style)
# Title with merged cells
ws.merge_cells('A1:F1')
title_cell = ws['A1']
title_cell.value = "Financial Summary Report"
title_cell.font = Font(bold=True, size=16, color="1F4E79")
title_cell.alignment = Alignment(horizontal="center", vertical="center")
ws.row_dimensions[1].height = 30# Subtitle
ws.merge_cells('A2:F2')
subtitle_cell = ws['A2']
subtitle_cell.value = "Fiscal Year 2026"
subtitle_cell.font = Font(italic=True, size=12, color="5B9BD5")
subtitle_cell.alignment = Alignment(horizontal="center")
ws.row_dimensions[2].height = 20# Headers row
headers = ["Category", "Budget", "Actual", "Variance", "% of Budget", "Status"]
for col, header inenumerate(headers, start=1):
cell = ws.cell(row=4, column=col, value=header)
cell.style = "header_style"# Data with various formats
data = [
["Revenue", 1000000, 1150000],
["Personnel", 500000, 485000],
["Operations", 200000, 215000],
["Marketing", 150000, 142000],
["Technology", 100000, 108000],
]
for row_idx, (category, budget, actual) inenumerate(data, start=5):
# Category
ws.cell(row=row_idx, column=1, value=category)
# Budget
ws.cell(row=row_idx, column=2, value=budget).style = "currency_style"# Actual
ws.cell(row=row_idx, column=3, value=actual).style = "currency_style"# Variance formula
variance_cell = ws.cell(row=row_idx, column=4)
variance_cell.value = f"=C{row_idx}-B{row_idx}"
variance_cell.style = "currency_style"# Percentage formula
pct_cell = ws.cell(row=row_idx, column=5)
pct_cell.value = f"=C{row_idx}/B{row_idx}"
pct_cell.style = "percentage_style"# Status (will be filled by conditional formatting)
ws.cell(row=row_idx, column=6, value="")
# Add conditional formatting for variance column# Green for positive, red for negative
green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
ws.conditional_formatting.add(
'D5:D9',
CellIsRule(
operator='greaterThan',
formula=['0'],
fill=green_fill,
font=Font(color="006100")
)
)
ws.conditional_formatting.add(
'D5:D9',
CellIsRule(
operator='lessThan',
formula=['0'],
fill=red_fill,
font=Font(color="9C0006")
)
)
# Data validation for status column
status_validation = DataValidation(
type="list",
formula1='"On Track,At Risk,Over Budget,Under Budget"',
allow_blank=True
)
status_validation.error = "Please select from the dropdown"
status_validation.errorTitle = "Invalid Status"
ws.add_data_validation(status_validation)
status_validation.add('F5:F9')
# Gradient fill example
ws['A12'] = "Gradient Fill Example"
ws['A12'].fill = GradientFill(
stop=["4472C4", "70AD47"],
degree=90
)
ws['A12'].font = Font(color="FFFFFF", bold=True)
ws.merge_cells('A12:C12')
# Column widths
widths = {'A': 15, 'B': 15, 'C': 15, 'D': 15, 'E': 15, 'F': 15}
for col, width in widths.items():
ws.column_dimensions[col].width = width
wb.save(output_path)
print(f"Formatted workbook saved to {output_path}")
create_formatted_workbook("formatted_report.xlsx")
"""Best practices for memory-efficient Excel operations."""# DO: Use write_only mode for large writes
wb = Workbook(write_only=True)
ws = wb.create_sheet()
for row in large_data:
ws.append(row) # Streams directly to file
wb.save('output.xlsx')
# DO: Use read_only mode for large reads
wb = load_workbook('large_file.xlsx', read_only=True)
for row in wb.active.iter_rows(values_only=True):
process_row(row)
wb.close() # Important: close when done# DON'T: Load entire file into memory unnecessarily# wb = load_workbook('large_file.xlsx') # Loads all into memory
2. Style Reuse
"""Reuse styles for better performance."""from openpyxl.styles import NamedStyle
# DO: Create named styles once, apply many times
header_style = NamedStyle(name="header")
header_style.font = Font(bold=True, color="FFFFFF")
header_style.fill = PatternFill(start_color="4472C4", fill_type="solid")
wb.add_named_style(header_style)
# Apply to multiple cells efficientlyfor cell in ws[1]:
cell.style = "header"# DON'T: Create style objects for each cell# for cell in ws[1]:# cell.font = Font(bold=True) # Creates new Font each time
3. Error Handling
"""Robust error handling for Excel operations."""from pathlib import Path
import logging
logger = logging.getLogger(__name__)
defsafe_save_workbook(wb: Workbook, output_path: str) -> bool:
"""Safely save workbook with error handling."""try:
# Ensure directory exists
Path(output_path).parent.mkdir(parents=True, exist_ok=True)
# Check if file is lockedif Path(output_path).exists():
try:
Path(output_path).rename(output_path)
except PermissionError:
logger.error(f"File is locked: {output_path}")
returnFalse
wb.save(output_path)
logger.info(f"Workbook saved: {output_path}")
returnTrueexcept Exception as e:
logger.exception(f"Failed to save workbook: {e}")
returnFalse
Troubleshooting
Common Issues
1. Formula Not Calculating
# Problem: Formulas show as text, not calculated# Solution: Ensure proper formula format# DO: Use equals sign and proper cell references
ws['A1'] = '=SUM(B1:B10)'# DON'T: Use string that looks like formula# ws['A1'] = 'SUM(B1:B10)' # Missing =# Note: Formulas are calculated when Excel opens the file
2. Large File Performance
# Problem: Memory error with large files# Solution: Use streaming modes# For writing
wb = Workbook(write_only=True)
# For reading
wb = load_workbook('file.xlsx', read_only=True, data_only=True)
3. Style Not Appearing
# Problem: Styles don't appear in Excel# Solution: Ensure style is properly applied# DO: Apply fill with fill_type
fill = PatternFill(start_color="FF0000", fill_type="solid")
# DON'T: Missing fill_type# fill = PatternFill(start_color="FF0000") # Won't show
