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 天前更新