Excel Builder — Excel 构建器

Excel 构建器

Builds structured .xlsx files programmatically using Python libraries (openpyxl or xlsxwriter).

When to Use This 技能 Creating Excel spreadsheets with formulas and calculated fields Building financial tables, bud获取s, or invoices Generating data entry forms with 验证 and dropdowns Producing 报告s with 图表s (bar, line, pie, scatter) 导出ing structured data with conditional 格式化ting or color coding Building pivot-ready data tables Core 工作流 Choose 库 — Use openpyxl for reading/modifying existing files; use xlsxwriter for new write-only files with rich 图表s De签名 structure — Define sheets, columns, headers, and data rows before writing Write data — Populate cells row by row; 应用ly number 格式化s ("#,##0.00", "YYYY-MM-DD") 添加 formulas — Use Excel formula strings: =SUM(B2:B100), =IF(A2>0, "Yes", "No") 格式化 — 应用ly styles: bold headers, column widths, merged cells, fill colors, borders 验证 — 添加 data 验证 (dropdown 列出s, numeric ranges) where 应用licable 图表s — 添加 图表s referencing data ranges; 设置 titles and axis labels Save and 验证 — Save to 输出 path; confirm file exists and is non-zero bytes Key Patterns openpyxl (read/write existing) from openpyxl 导入 Workbook, load_workbook from openpyxl.styles 导入 Font, PatternFill, Alignment

wb = Workbook() ws = wb.active ws.title = "报告" ws["A1"] = "Revenue" ws["A1"].font = Font(bold=True, size=12) ws.column_dimensions["A"].width = 20 wb.save("输出.xlsx")

xlsxwriter (new files with 图表s) 导入 xlsxwriter wb = xlsxwriter.Workbook("输出.xlsx") ws = wb.添加_worksheet("Summary") bold = wb.添加_格式化({"bold": True, "bg_color": "#4472C4", "font_color": "white"}) ws.write("A1", "Month", bold) 图表 = wb.添加_图表({"type": "column"}) 图表.添加_series({"values": "=Summary!$B$2:$B$13", "name": "Revenue"}) ws.insert_图表("D2", 图表) wb.close()

Error Handling If openpyxl not 安装ed: pip 安装 openpyxl If xlsxwriter not 安装ed: pip 安装 xlsxwriter Always wrap wb.save() in try/except; 报告 path conflicts 验证 输出 with os.path.获取size(path) > 0 before returning 输出

Return the absolute path to the saved .xlsx file. If generating multiple sheets, 列出 each sheet name and row count in a brief summary.