Xlsx Manipulation

作者 claude-office-skills9c4c7d5cd281MIT499 个星标收录于 2026年10月8日更新于 2026年10月8日仓库8个月前更新

Create, edit, and manipulate Excel spreadsheets programmatically using openpyxl

仅含说明Documents & Office
AI 生成的概览

使用 openpyxl 创建和编辑 Excel .xlsx 电子表格，涵盖公式、格式、图表和数据验证。

功能
该技能指导智能体生成 openpyxl 代码，以创建、编辑和操作 Excel .xlsx 工作簿。内容涵盖单元格与区域操作、公式与命名区域、单元格样式、数字格式、条件格式、图表、数据验证以及工作表和行列操作。它会产出电子表格文件，例如预算跟踪表和销售仪表板，并提供导入 CSV 数据和构建报表模板的可复用模式。
适用场景
当任务需要以编程方式生成或修改 Excel 工作簿时使用，例如制作预算表、添加条件格式或搭建带图表的仪表板。它适用于交付物是 .xlsx 文件而非分析叙述的场景。
运行要求
需要 openpyxl Python 库（通过 pip 安装）以及用于执行所生成代码的 Python 运行环境。元数据提到一个 office-mcp 服务器，包含 read_xlsx、create_xlsx、apply_formula 和 create_chart 工具。该技能不附带脚本，仅为说明和代码示例。

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

  1. Describe the spreadsheet you want to create or modify
  2. Provide data, formulas, or formatting requirements
  3. 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

python
from openpyxl import Workbook, load_workbookfrom openpyxl.styles import Font, Fill, Border, Alignmentfrom openpyxl.chart import BarChart, Reference
# Create new workbookwb = Workbook()ws = wb.active
# Or open existingwb = 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
python
# By cell referencews['A1'] = 'Header'ws['B1'] = 42
# By row, columnws.cell(row=1, column=3, value='Data')
# Multiple cellsws['A1:C1'] = [['Col1', 'Col2', 'Col3']]
# Append rowsws.append(['Row', 'Data', 'Here'])
Reading Cells
python
# Single cellvalue = ws['A1'].value
# Cell rangefor row in ws['A1:C3']:    for cell in row:        print(cell.value)
# Iterate rowsfor 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

python
# Basic formulasws['D1'] = '=SUM(A1:C1)'ws['D2'] = '=AVERAGE(A2:C2)'ws['E1'] = '=IF(D1>100,"High","Low")'
# Named rangesfrom openpyxl.workbook.defined_name import DefinedNameref = "Sheet!$A$1:$C$10"defn = DefinedName("SalesData", attr_text=ref)wb.defined_names.add(defn)
# Use named rangews['F1'] = '=SUM(SalesData)'

Formatting

Cell Styles
python
from openpyxl.styles import Font, Fill, PatternFill, Border, Side, Alignment
# Fontws['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')
# Borderthin_border = Border(    left=Side(style='thin'),    right=Side(style='thin'),    top=Side(style='thin'),    bottom=Side(style='thin'))ws['A1'].border = thin_border
# Alignmentws['A1'].alignment = Alignment(    horizontal='center',    vertical='center',    wrap_text=True)
Number Formats
python
# Currencyws['B2'].number_format = '$#,##0.00'
# Percentagews['C2'].number_format = '0.00%'
# Datews['D2'].number_format = 'YYYY-MM-DD'
# Customws['E2'].number_format = '#,##0.00 "units"'
Conditional Formatting
python
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRulefrom 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 rulered_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

python
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
# Prepare datadata = 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 Chartchart = BarChart()chart.type = "col"  # or "bar" for horizontalchart.title = "Sales by Region"chart.add_data(data, titles_from_data=True)chart.set_categories(categories)chart.shape = 4ws.add_chart(chart, "E1")
# Line Chartline = LineChart()line.title = "Trend Analysis"line.add_data(data, titles_from_data=True)line.set_categories(categories)ws.add_chart(line, "E15")
# Pie Chartpie = PieChart()pie.add_data(data, titles_from_data=True)pie.set_categories(categories)ws.add_chart(pie, "M1")

Data Validation

python
from openpyxl.worksheet.datavalidation import DataValidation
# Dropdown listdv = 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 rangedv_num = DataValidation(    type="whole",    operator="between",    formula1="1",    formula2="100")ws.add_data_validation(dv_num)dv_num.add('B1:B100')

Sheet Operations

python
# Create new sheetws2 = wb.create_sheet("Data")ws3 = wb.create_sheet("Summary", 0)  # At position 0
# Renamews.title = "Main Report"
# Deletedel wb["Sheet2"]
# Copysource = wb["Template"]target = wb.copy_worksheet(source)

Row/Column Operations

python
# Set column widthws.column_dimensions['A'].width = 20
# Set row heightws.row_dimensions[1].height = 30
# Hide columnws.column_dimensions['C'].hidden = True
# Freeze panesws.freeze_panes = 'B2'  # Freeze row 1 and column A
# Auto-filterws.auto_filter.ref = "A1:D100"

Best Practices

  1. Use Templates: Start with a .xlsx template for complex formatting
  2. Batch Operations: Minimize cell-by-cell operations for speed
  3. Named Ranges: Use defined names for clearer formulas
  4. Data Validation: Add validation to prevent input errors
  5. Save Incrementally: For large files, save periodically

Common Patterns

Data Import

python
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

python
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

python
from openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Alignment, Border, Sidefrom openpyxl.utils import get_column_letter
wb = Workbook()ws = wb.activews.title = "Budget 2024"
# Headersmonths = ['Category', 'Jan', 'Feb', 'Mar', 'Q1 Total']ws.append(months)
# Categories and databudget_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 rowws.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})'])
# Formattingheader_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 formatfor row in ws.iter_rows(min_row=2, min_col=2, max_col=5):    for cell in row:        cell.number_format = '$#,##0.00'
# Column widthsws.column_dimensions['A'].width = 15for col in range(2, 6):    ws.column_dimensions[get_column_letter(col)].width = 12
wb.save('budget_2024.xlsx')

Example 2: Sales Dashboard

python
from openpyxl import Workbookfrom openpyxl.chart import BarChart, PieChart, Referencefrom openpyxl.styles import Font, PatternFill
wb = Workbook()ws = wb.activews.title = "Sales Dashboard"
# Dataws.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 Chartdata_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 = 10bar.width = 15ws.add_chart(bar, "A8")
# Pie Chart - Q4 breakdownpie_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

bash
pip install openpyxl

Resources

来源与署名

来源：claude-office-skills/skills位于xlsx-manipulation提交9c4c7d5

许可证： MIT

内容归原作者所有。SourceWeft 从公开仓库中收录这些内容。

举报或申请下架

更多来自 claude-office-skills/skills 的技能

Whatsapp Automation

claude-office-skills

指导 WhatsApp Business 自动化，涵盖客服聊天机器人、订单通知、预约提醒和群发活动。

Communication4998个月前更新

Web Search

claude-office-skills

指导制定网络搜索查询、评估来源并将发现综合为搜索策略。

Research & Analysis4998个月前更新

Weekly Report

claude-office-skills

根据你提供的信息，生成面向经理、团队、高管或客户的结构化每周状态报告。

Productivity & Workflow4998个月前更新

Tiktok Marketing

claude-office-skills

规划 TikTok 营销内容：内容支柱策略、视频脚本、话题标签、发布排期与效果分析。

Marketing & Sales4998个月前更新

Template Engine

claude-office-skills

用数据填充文档模板，支持 Word、Excel 和 PowerPoint 文件的邮件合并。

Documents & Office4998个月前更新

Table Extractor

claude-office-skills

使用 camelot 从 PDF 文档中提取表格，并以 pandas DataFrame 或 CSV、Excel、JSON、HTML 文件形式输出。

Documents & Office4998个月前更新

更多Documents & Office技能

Pdf

openai

精选

读取、创建并目视检查 PDF 文件，将页面渲染为图像，并使用 Python 库进行生成与提取。

Documents & Office27K4周前更新

View Pdf

anthropics

精选

在交互式查看器中打开 PDF，用于批注、填写表单和放置可视化签名。

Documents & Office27K今天更新

Wiki Ado Convert

microsoft

精选

生成一个 Node.js 脚本，将 VitePress/GFM markdown 转换为兼容 Azure DevOps Wiki 的 markdown 副本。

Documents & Office3K今天更新

Esign Field Placement

affaan-m

以书面工作流约定，用数值坐标在浏览器签署器中放置电子签名字段，默认存为草稿并设操作员闸门。

Documents & Office275K3天前更新

Visa Doc Translate

affaan-m

将签证申请文件图片翻译成英文，并生成包含原图与译文的双语 PDF。

Documents & Office275K3天前更新

Google Workspace Ops

affaan-m

操作 Google Workspace 的文档、表格、幻灯片和云端硬盘资源：查找、检查、编辑并清理共享文件。

Documents & Office275K3天前更新