菜单
Create and manipulate Microsoft Excel workbooks programmatically. Build spreadsheets with formulas, charts, conditional formatting, and pivot tables. Handle large datasets efficiently with streaming mode.
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/vamseeachanta/workspace-hub --skill openpyxl命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
基于 SOC 职业分类
正在显示 SKILL.md
| name | openpyxl |
| description | Create and manipulate Microsoft Excel workbooks programmatically. Build spreadsheets with formulas, charts, conditional formatting, and pivot tables. Handle large datasets efficiently with streaming mode. |
| version | 1.0.0 |
| category | office-docs |
| type | skill |
| capabilities | ["workbook_creation","cell_formatting","formula_support","chart_generation","conditional_formatting","pivot_tables","large_dataset_handling","streaming_mode"] |
| tools | ["python","openpyxl","pandas"] |
| tags | ["excel","xlsx","spreadsheet","formulas","charts","data-analysis","office-automation"] |
| platforms | ["windows","macos","linux"] |
| related_skills | ["pandas-data-processing","python-docx","plotly"] |
Openpyxl is a Python library for reading and writing Excel 2010+ xlsx/xlsm files. This skill covers comprehensive patterns for spreadsheet automation including:
# 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
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border
from openpyxl.chart import BarChart, LineChart, PieChart
from openpyxl.utils.dataframe import dataframe_to_rows
print("openpyxl installed successfully!")
"""
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
def create_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 in enumerate(headers, start=):
cell = ws.cell(row=, column=col, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_alignment
cell.border = thin_border
data = [
[, , , , ],
[, , , , ],
[, , , , ],
[, , , , ],
]
row_idx, row_data (data, start=):
ws.cell(row=row_idx, column=, value=row_data[]).border = thin_border
col_idx, value (row_data[:], start=):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
cell.border = thin_border
cell.number_format =
total_cell = ws.cell(
row=row_idx,
column=,
value=
)
total_cell.border = thin_border
total_cell.font = Font(bold=)
total_cell.number_format =
total_row = (data) +
ws.cell(row=total_row, column=, value=).font = Font(bold=)
col (, ):
col_letter = get_column_letter(col)
cell = ws.cell(
row=total_row,
column=col,
value=
)
cell.font = Font(bold=)
cell.number_format =
cell.border = thin_border
column_widths = [, , , , , ]
i, width (column_widths, start=):
ws.column_dimensions[get_column_letter(i)].width = width
ws.freeze_panes =
wb.save(output_path)
()
create_basic_workbook()
"""
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
def create_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=)
wb.add_named_style(percentage_style)
ws.merge_cells()
title_cell = ws[]
title_cell.value =
title_cell.font = Font(bold=, size=, color=)
title_cell.alignment = Alignment(horizontal=, vertical=)
ws.row_dimensions[].height =
ws.merge_cells()
subtitle_cell = ws[]
subtitle_cell.value =
subtitle_cell.font = Font(italic=, size=, color=)
subtitle_cell.alignment = Alignment(horizontal=)
ws.row_dimensions[].height =
headers = [, , , , , ]
col, header (headers, start=):
cell = ws.cell(row=, column=col, value=header)
cell.style =
data = [
[, , ],
[, , ],
[, , ],
[, , ],
[, , ],
]
row_idx, (category, budget, actual) (data, start=):
ws.cell(row=row_idx, column=, value=category)
ws.cell(row=row_idx, column=, value=budget).style =
ws.cell(row=row_idx, column=, value=actual).style =
variance_cell = ws.cell(row=row_idx, column=)
variance_cell.value =
variance_cell.style =
pct_cell = ws.cell(row=row_idx, column=)
pct_cell.value =
pct_cell.style =
ws.cell(row=row_idx, column=, value=)
green_fill = PatternFill(start_color=, end_color=, fill_type=)
red_fill = PatternFill(start_color=, end_color=, fill_type=)
ws.conditional_formatting.add(
,
CellIsRule(
operator=,
formula=[],
fill=green_fill,
font=Font(color=)
)
)
ws.conditional_formatting.add(
,
CellIsRule(
operator=,
formula=[],
fill=red_fill,
font=Font(color=)
)
)
status_validation = DataValidation(
=,
formula1=,
allow_blank=
)
status_validation.error =
status_validation.errorTitle =
ws.add_data_validation(status_validation)
status_validation.add()
ws[] =
ws[].fill = GradientFill(
stop=[, ],
degree=
)
ws[].font = Font(color=, bold=)
ws.merge_cells()
widths = {: , : , : , : , : , : }
col, width widths.items():
ws.column_dimensions[col].width = width
wb.save(output_path)
()
create_formatted_workbook()
"""
Create various chart types in Excel.
"""
from openpyxl import Workbook
from openpyxl.chart import (
BarChart, LineChart, PieChart, AreaChart, ScatterChart,
Reference, Series
)
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.layout import Layout, ManualLayout
def create_charts_workbook(output_path: str) -> None:
"""Create workbook with various chart examples."""
wb = Workbook()
ws = wb.active
ws.title = "Chart Data"
# Sample data for charts
data = [
["Month", "Sales", "Expenses", "Profit"],
["Jan", 15000, 12000, 3000],
["Feb", 18000, 13000, 5000],
["Mar", 22000, 14500, 7500],
["Apr", 20000, 14000, 6000],
["May", 25000, 15000, 10000],
["Jun", 28000, 16000, 12000],
]
for row in data:
ws.append(row)
# Bar Chart
bar_chart = BarChart()
bar_chart. =
bar_chart.grouping =
bar_chart.title =
bar_chart.y_axis.title =
bar_chart.x_axis.title =
data_ref = Reference(ws, min_col=, max_col=, min_row=, max_row=)
cats_ref = Reference(ws, min_col=, min_row=, max_row=)
bar_chart.add_data(data_ref, titles_from_data=)
bar_chart.set_categories(cats_ref)
bar_chart.shape =
bar_chart.style =
bar_chart.width =
bar_chart.height =
ws.add_chart(bar_chart, )
line_chart = LineChart()
line_chart.title =
line_chart.y_axis.title =
line_chart.x_axis.title =
line_chart.style =
profit_data = Reference(ws, min_col=, min_row=, max_row=)
line_chart.add_data(profit_data, titles_from_data=)
line_chart.set_categories(cats_ref)
line_chart.series[].marker.symbol =
line_chart.series[].marker.size =
line_chart.series[].graphicalProperties.line.width =
ws.add_chart(line_chart, )
pie_ws = wb.create_sheet()
pie_data = [
[, ],
[, ],
[, ],
[, ],
[, ],
[, ],
]
row pie_data:
pie_ws.append(row)
pie_chart = PieChart()
pie_chart.title =
pie_data_ref = Reference(pie_ws, min_col=, min_row=, max_row=)
pie_labels_ref = Reference(pie_ws, min_col=, min_row=, max_row=)
pie_chart.add_data(pie_data_ref)
pie_chart.set_categories(pie_labels_ref)
pie_chart.dataLabels = DataLabelList()
pie_chart.dataLabels.showPercent =
pie_chart.dataLabels.showVal =
pie_chart.dataLabels.showCatName =
pie_ws.add_chart(pie_chart, )
area_ws = wb.create_sheet()
area_data = [
[, , , ],
[, , , ],
[, , , ],
[, , , ],
[, , , ],
]
row area_data:
area_ws.append(row)
area_chart = AreaChart()
area_chart.title =
area_chart.style =
area_chart.grouping =
area_data_ref = Reference(area_ws, min_col=, max_col=, min_row=, max_row=)
area_cats_ref = Reference(area_ws, min_col=, min_row=, max_row=)
area_chart.add_data(area_data_ref, titles_from_data=)
area_chart.set_categories(area_cats_ref)
area_ws.add_chart(area_chart, )
scatter_ws = wb.create_sheet()
scatter_data = [
[, ],
[, ],
[, ],
[, ],
[, ],
[, ],
[, ],
[, ],
]
row scatter_data:
scatter_ws.append(row)
scatter_chart = ScatterChart()
scatter_chart.title =
scatter_chart.x_axis.title =
scatter_chart.y_axis.title =
scatter_chart.style =
x_values = Reference(scatter_ws, min_col=, min_row=, max_row=)
y_values = Reference(scatter_ws, min_col=, min_row=, max_row=)
series = Series(y_values, x_values, title=)
scatter_chart.series.append(series)
openpyxl.chart.trendline Trendline
series.trendline = Trendline(trendlineType=)
scatter_ws.add_chart(scatter_chart, )
wb.save(output_path)
()
create_charts_workbook()
"""
Apply conditional formatting rules for visual data analysis.
"""
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Border, Side
from openpyxl.formatting.rule import (
ColorScaleRule, DataBarRule, IconSetRule,
CellIsRule, FormulaRule, Rule
)
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.utils import get_column_letter
def create_conditional_formatting_workbook(output_path: str) -> None:
"""Create workbook demonstrating conditional formatting."""
wb = Workbook()
# Sheet 1: Color Scales
ws1 = wb.active
ws1.title = "Color Scales"
# Header
ws1['A1'] = "Performance Scores"
ws1['A1'].font = Font(bold=True, size=14)
# Data
scores = [85, 72, 91, 68, 95, 78, 82, 60, 88, 75, 93, 71, 86, 79, 64]
for i, score in enumerate(scores, start=3):
ws1.cell(row=i, column=1, value=f"Employee {i-}")
ws1.cell(row=i, column=, value=score)
color_scale_rule = ColorScaleRule(
start_type=,
start_color=,
mid_type=,
mid_value=,
mid_color=,
end_type=,
end_color=
)
ws1.conditional_formatting.add(, color_scale_rule)
ws2 = wb.create_sheet()
ws2[] =
ws2[].font = Font(bold=, size=)
regions = [
(, ),
(, ),
(, ),
(, ),
(, ),
]
i, (region, sales) (regions, start=):
ws2.cell(row=i, column=, value=region)
ws2.cell(row=i, column=, value=sales)
data_bar_rule = DataBarRule(
start_type=,
start_value=,
end_type=,
color=,
showValue=,
minLength=,
maxLength=
)
ws2.conditional_formatting.add(, data_bar_rule)
ws2.column_dimensions[].width =
ws3 = wb.create_sheet()
ws3[] =
ws3[].font = Font(bold=, size=)
ws3[] =
ws3[] =
ws3[] =
projects = [
(, ),
(, ),
(, ),
(, ),
(, ),
]
i, (project, completion) (projects, start=):
ws3.cell(row=i, column=, value=project)
ws3.cell(row=i, column=, value=completion / )
ws3.cell(row=i, column=).number_format =
icon_set_rule = IconSetRule(
,
,
[, , ],
showValue=,
reverse=
)
ws3.conditional_formatting.add(, icon_set_rule)
ws4 = wb.create_sheet()
ws4[] =
ws4[].font = Font(bold=, size=)
col, header ([, , , ], start=):
ws4.cell(row=, column=col, value=header).font = Font(bold=)
inventory = [
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
]
i, (product, stock, reorder) (inventory, start=):
ws4.cell(row=i, column=, value=product)
ws4.cell(row=i, column=, value=stock)
ws4.cell(row=i, column=, value=reorder)
red_fill = PatternFill(start_color=, fill_type=)
red_font = Font(color=)
ws4.conditional_formatting.add(
,
CellIsRule(
operator=,
formula=[],
fill=red_fill,
font=red_font
)
)
green_fill = PatternFill(start_color=, fill_type=)
green_font = Font(color=)
ws4.conditional_formatting.add(
,
CellIsRule(
operator=,
formula=[],
fill=green_fill,
font=green_font
)
)
ws5 = wb.create_sheet()
ws5[] =
ws5[].font = Font(bold=, size=)
col, header ([, , , ], start=):
ws5.cell(row=, column=col, value=header).font = Font(bold=)
employees = [
(, , , ),
(, , , ),
(, , , ),
(, , , ),
(, , , ),
]
i, (name, dept, salary, status) (employees, start=):
ws5.cell(row=i, column=, value=name)
ws5.cell(row=i, column=, value=dept)
ws5.cell(row=i, column=, value=salary)
ws5.cell(row=i, column=, value=status)
gray_fill = PatternFill(start_color=, fill_type=)
ws5.conditional_formatting.add(
,
FormulaRule(
formula=[],
fill=gray_fill
)
)
blue_fill = PatternFill(start_color=, fill_type=)
ws5.conditional_formatting.add(
,
FormulaRule(
formula=[],
fill=blue_fill
)
)
wb.save(output_path)
()
create_conditional_formatting_workbook()
"""
Handle large datasets efficiently with read-only and write-only modes.
"""
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from typing import Generator, List, Dict, Any, Iterator
import time
def write_large_dataset_streaming(
output_path: str,
data_generator: Generator,
headers: List[str],
chunk_size: int = 10000
) -> int:
"""Write large dataset using write-only mode for memory efficiency."""
# Use write_only mode for streaming
wb = Workbook(write_only=True)
ws = wb.create_sheet("Large Data")
# Write headers
ws.append(headers)
rows_written = 0
start_time = time.time()
for row in data_generator:
ws.append(row)
rows_written += 1
if rows_written % chunk_size == 0:
elapsed = time.time() - start_time
print(f"Written {rows_written:,} rows ({elapsed:.1f}s)")
wb.save(output_path)
total_time = time.time() - start_time
print(f"Total: {rows_written:,} rows written in {total_time:.1f}s")
return rows_written
def read_large_dataset_streaming() -> Generator:
wb = load_workbook(file_path, read_only=)
ws = wb.active
chunk = []
headers =
row_idx, row (ws.iter_rows(values_only=)):
row_idx == :
headers = row
row_dict = ((headers, row))
chunk.append(row_dict)
(chunk) >= chunk_size:
chunk
chunk = []
chunk:
chunk
wb.close()
() -> Generator:
random
datetime datetime, timedelta
base_date = datetime(, , )
categories = [, , , , ]
regions = [, , , ]
i (num_rows):
[
i + ,
,
random.choice(categories),
random.choice(regions),
(random.uniform(, ), ),
random.randint(, ),
(base_date + timedelta(days=random.randint(, ))).strftime(),
]
() -> :
headers = [, , , , , , ]
num_rows =
()
output_path =
rows_written = write_large_dataset_streaming(
output_path,
generate_sample_data(num_rows),
headers
)
()
total_revenue =
category_totals = {}
chunk read_large_dataset_streaming(output_path, chunk_size=):
row chunk:
revenue = row[] * row[]
total_revenue += revenue
category = row[]
category_totals[category] = category_totals.get(category, ) + revenue
()
()
category, total (category_totals.items()):
()
"""
Create pivot table structures in Excel (note: full pivot table functionality
requires Excel to be installed and opened).
"""
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from typing import List, Dict, Any
from collections import defaultdict
def create_pivot_like_table(
data: List[Dict[str, Any]],
row_field: str,
col_field: str,
value_field: str,
aggregation: str = 'sum'
) -> Dict[str, Dict[str, float]]:
"""Create pivot table structure from data."""
pivot_data = defaultdict(lambda: defaultdict(float))
row_totals = defaultdict(float)
col_totals = defaultdict(float)
grand_total = 0
for record in data:
row_val = record[row_field]
col_val = record[col_field]
value = record[value_field]
if aggregation == 'sum':
pivot_data[row_val][col_val] += value
row_totals[row_val] += value
col_totals[col_val] += value
grand_total += value
elif aggregation == 'count':
pivot_data[row_val][col_val] +=
row_totals[row_val] +=
col_totals[col_val] +=
grand_total +=
{
: (pivot_data),
: (row_totals),
: (col_totals),
: grand_total
}
() -> :
ws = wb.create_sheet(sheet_name)
header_fill = PatternFill(start_color=, fill_type=)
header_font = Font(bold=, color=)
total_fill = PatternFill(start_color=, fill_type=)
total_font = Font(bold=)
border = Border(
left=Side(style=),
right=Side(style=),
top=Side(style=),
bottom=Side(style=)
)
ws[] = title
ws[].font = Font(bold=, size=)
ws.merge_cells()
pivot_data = pivot_result[]
row_totals = pivot_result[]
col_totals = pivot_result[]
grand_total = pivot_result[]
all_cols = ((col row_data pivot_data.values() col row_data.keys()))
all_rows = (pivot_data.keys())
start_row =
ws.cell(row=start_row, column=, value=).border = border
col_idx, col_name (all_cols, start=):
cell = ws.cell(row=start_row, column=col_idx, value=col_name)
cell.fill = header_fill
cell.font = header_font
cell.alignment = Alignment(horizontal=)
cell.border = border
total_col = (all_cols) +
cell = ws.cell(row=start_row, column=total_col, value=)
cell.fill = header_fill
cell.font = header_font
cell.border = border
row_idx, row_name (all_rows, start=start_row + ):
cell = ws.cell(row=row_idx, column=, value=row_name)
cell.fill = header_fill
cell.font = header_font
cell.border = border
col_idx, col_name (all_cols, start=):
value = pivot_data[row_name].get(col_name, )
cell = ws.cell(row=row_idx, column=col_idx, value=value)
cell.number_format =
cell.border = border
cell = ws.cell(row=row_idx, column=total_col, value=row_totals[row_name])
cell.fill = total_fill
cell.font = total_font
cell.number_format =
cell.border = border
totals_row = start_row + (all_rows) +
cell = ws.cell(row=totals_row, column=, value=)
cell.fill = header_fill
cell.font = header_font
cell.border = border
col_idx, col_name (all_cols, start=):
cell = ws.cell(row=totals_row, column=col_idx, value=col_totals[col_name])
cell.fill = total_fill
cell.font = total_font
cell.number_format =
cell.border = border
cell = ws.cell(row=totals_row, column=total_col, value=grand_total)
cell.fill = total_fill
cell.font = total_font
cell.number_format =
cell.border = border
col_idx (, total_col + ):
ws.column_dimensions[get_column_letter(col_idx)].width =
() -> :
sales_data = [
{: , : , : },
{: , : , : },
{: , : , : },
{: , : , : },
{: , : , : },
{: , : , : },
{: , : , : },
{: , : , : },
{: , : , : },
]
wb = Workbook()
ws_data = wb.active
ws_data.title =
headers = [, , ]
ws_data.append(headers)
row sales_data:
ws_data.append([row[], row[], row[]])
pivot_result = create_pivot_like_table(
sales_data,
row_field=,
col_field=,
value_field=,
aggregation=
)
write_pivot_table_to_excel(
wb,
pivot_result,
,
)
wb.save(output_path)
()
create_pivot_table_example()
"""
Integration with pandas for data analysis workflows.
"""
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font, PatternFill, Alignment
def dataframe_to_styled_excel(
df: pd.DataFrame,
output_path: str,
sheet_name: str = "Data",
header_color: str = "4472C4"
) -> None:
"""Export pandas DataFrame to styled Excel file."""
wb = Workbook()
ws = wb.active
ws.title = sheet_name
# Write DataFrame to worksheet
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True)):
for c_idx, value in enumerate(row, start=1):
cell = ws.cell(row=r_idx + 1, column=c_idx, value=value)
# Style header row
if r_idx == 0:
cell.fill = PatternFill(start_color=header_color, fill_type="solid")
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = Alignment(horizontal="center")
# Auto-adjust column widths
for column in ws.columns:
max_length = 0
column_letter = column[0].column_letter
cell column:
:
((cell.value)) > max_length:
max_length = ((cell.value))
:
ws.column_dimensions[column_letter].width = (max_length + , )
wb.save(output_path)
()
() -> pd.DataFrame:
df = pd.read_excel(
file_path,
sheet_name=sheet_name,
engine=
)
dtype_mapping:
col, dtype dtype_mapping.items():
col df.columns:
df[col] = df[col].astype(dtype)
df
() -> :
pd.ExcelWriter(output_path, engine=) writer:
sheet_name, df dataframes.items():
df.to_excel(writer, sheet_name=sheet_name, index=)
ws = writer.sheets[sheet_name]
cell ws[]:
cell.fill = PatternFill(start_color=, fill_type=)
cell.font = Font(bold=, color=)
()
"""
Generate Excel reports from database queries.
"""
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from datetime import datetime
import sqlite3
from typing import List, Tuple, Any
def generate_database_report(
db_path: str,
queries: dict,
output_path: str
) -> None:
"""Generate Excel report from multiple database queries."""
conn = sqlite3.connect(db_path)
wb = Workbook()
# Remove default sheet
wb.remove(wb.active)
# Styles
header_fill = PatternFill(start_color="2F5496", fill_type="solid")
header_font = Font(bold=True, color="FFFFFF")
border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
for sheet_name, query in queries.items():
# Execute query
cursor = conn.execute(query)
columns = [description[0] for description in cursor.description]
rows = cursor.fetchall()
# Create sheet
ws = wb.create_sheet(sheet_name)
# Add metadata
ws['A1'] =
ws[].font = Font(bold=, size=)
ws[] =
ws[].font = Font(italic=, size=)
col_idx, header (columns, start=):
cell = ws.cell(row=, column=col_idx, value=header)
cell.fill = header_fill
cell.font = header_font
cell.alignment = Alignment(horizontal=)
cell.border = border
row_idx, row (rows, start=):
col_idx, value (row, start=):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
cell.border = border
(value, (, )):
cell.number_format =
col_idx (, (columns) + ):
ws.column_dimensions[get_column_letter(col_idx)].width =
ws.cell(row=(rows) + , column=, value=)
conn.close()
wb.save(output_path)
()
"""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
"""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 efficiently
for 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
"""Robust error handling for Excel operations."""
from pathlib import Path
import logging
logger = logging.getLogger(__name__)
def safe_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 locked
if Path(output_path).exists():
try:
Path(output_path).rename(output_path)
except PermissionError:
logger.error(f"File is locked: {output_path}")
return False
wb.save(output_path)
logger.info(f"Workbook saved: {output_path}")
return True
except Exception as e:
logger.exception(f"Failed to save workbook: {e}")
return False
# 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
# 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)
# 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
This skill provides comprehensive patterns for Excel automation refined from production data processing systems.