Excel Builder
Builds structured .xlsx files programmatically using Python libraries (openpyxl or xlsxwriter).
When to Use This Skill
- - Creating Excel spreadsheets with formulas and calculated fields
- Building financial tables, budgets, or invoices
- Generating data entry forms with validation and dropdowns
- Producing reports with charts (bar, line, pie, scatter)
- Exporting structured data with conditional formatting or color coding
- Building pivot-ready data tables
Core Workflow
- 1. Choose library — Use
openpyxl for reading/modifying existing files; use xlsxwriter for new write-only files with rich charts - Design structure — Define sheets, columns, headers, and data rows before writing
- Write data — Populate cells row by row; apply number formats (
"#,##0.00", "YYYY-MM-DD") - Add formulas — Use Excel formula strings:
=SUM(B2:B100), INLINECODE6 - Format — Apply styles: bold headers, column widths, merged cells, fill colors, borders
- Validate — Add data validation (dropdown lists, numeric ranges) where applicable
- Charts — Add charts referencing data ranges; set titles and axis labels
- Save and verify — Save to output path; confirm file exists and is non-zero bytes
Key Patterns
openpyxl (read/write existing)
CODEBLOCK0
xlsxwriter (new files with charts)
CODEBLOCK1
Error Handling
- - If
openpyxl not installed: INLINECODE8 - If
xlsxwriter not installed: INLINECODE10 - Always wrap
wb.save() in try/except; report path conflicts - Verify output with
os.path.getsize(path) > 0 before returning
Output
Return the absolute path to the saved .xlsx file. If generating multiple sheets, list each sheet name and row count in a brief summary.
Excel Builder
使用Python库（openpyxl或xlsxwriter）以编程方式构建结构化的.xlsx文件。
何时使用此技能
- - 创建包含公式和计算字段的Excel电子表格
- 构建财务表格、预算或发票
- 生成带有验证和下拉菜单的数据录入表单
- 制作包含图表（柱状图、折线图、饼图、散点图）的报告
- 导出带有条件格式或颜色编码的结构化数据
- 构建可用于数据透视表的数据表格
核心工作流程
- 1. 选择库 — 使用openpyxl读取/修改现有文件；使用xlsxwriter创建仅写入的新文件并支持丰富图表
- 设计结构 — 在写入前定义工作表、列、表头和数据行
- 写入数据 — 逐行填充单元格；应用数字格式（#,##0.00、YYYY-MM-DD）
- 添加公式 — 使用Excel公式字符串：=SUM(B2:B100)、=IF(A2>0, Yes, No)
- 格式化 — 应用样式：加粗表头、列宽、合并单元格、填充颜色、边框
- 验证 — 在适用处添加数据验证（下拉列表、数值范围）
- 图表 — 添加引用数据范围的图表；设置标题和坐标轴标签
- 保存并验证 — 保存到输出路径；确认文件存在且非零字节
关键模式
openpyxl（读取/写入现有文件）
python
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
wb = Workbook()
ws = wb.active
ws.title = 报告
ws[A1] = 收入
ws[A1].font = Font(bold=True, size=12)
ws.column_dimensions[A].width = 20
wb.save(output.xlsx)
xlsxwriter（带图表的新文件）
python
import xlsxwriter
wb = xlsxwriter.Workbook(output.xlsx)
ws = wb.add_worksheet(摘要)
bold = wb.add
format({bold: True, bgcolor: #4472C4, font_color: white})
ws.write(A1, 月份, bold)
chart = wb.add_chart({type: column})
chart.add_series({values: =摘要!$B$2:$B$13, name: 收入})
ws.insert_chart(D2, chart)
wb.close()
错误处理
- - 如果未安装openpyxl：pip install openpyxl
- 如果未安装xlsxwriter：pip install xlsxwriter
- 始终将wb.save()包裹在try/except中；报告路径冲突
- 在返回前使用os.path.getsize(path) > 0验证输出
输出
返回保存的.xlsx文件的绝对路径。如果生成多个工作表，在简要摘要中列出每个工作表的名称和行数。
