XLSX Toolkit Skill

Overview

This skill provides comprehensive spreadsheet automation capabilities for working with Excel files (.xlsx, .xlsm), CSV/TSV files, and Google Sheets. It handles creation, editing, analysis, visualization, formulas, formatting, and data insights.

Trigger Conditions

ALWAYS USE THIS SKILL when user mentions:

• Any spreadsheet file operation: "read Excel", "parse CSV", "edit spreadsheet"
• Formula work: "add formula", "create SUM", "Excel functions"
• Formatting: "style cells", "bold headers", "freeze panes"
• Analysis: "analyze data", "pivot table", "filter data", "sort"
• Visualization: "add chart", "create graph", "pivot chart"
• Data operations: "merge files", "split data", "clean data"
• Google Sheets: "Google Sheets", "Sheets export", "import to Sheets"
• Any of: xlsx, xlsm, csv, tsv, spreadsheet, workbook, worksheet, cell, range, column, row

Core Capabilities

1. File Operations

• Read: Open and parse .xlsx, .xlsm, .csv, .tsv files
• Create: Generate new workbooks from scratch
• Edit: Modify existing cells, sheets, formatting
• Save: Export to any supported format
• Convert: Transform between Excel, CSV, JSON, HTML

2. Data Operations

• Read/write cell values and ranges
• Insert/delete rows and columns
• Merge/split cells
• Find and replace
• Data validation (dropdowns, rules)
• Auto-filter and advanced filtering
• Sort by single or multiple columns
• Remove duplicates
• Fill down/series

3. Formula Engine

• All Excel functions: SUM, AVERAGE, COUNT, VLOOKUP, HLOOKUP, INDEX, MATCH, IF, IFS, SUMIF, COUNTIF, etc.
• Array formulas and dynamic arrays
• Formula auditing (trace precedents/dependents)
• Circular reference detection
• Custom named ranges

4. Formatting

• Cell styles: font, size, color, bold, italic, underline
• Fill colors and gradients
• Borders (all styles)
• Number formats (currency, date, percentage, custom)
• Alignment (horizontal, vertical, wrap text, merge)
• Conditional formatting rules
• Cell protection/lock

5. Visualization

• Charts: Column, Bar, Line, Pie, Area, Scatter, Stock, Radar
• Sparklines
• Pivot tables
• Pivot charts
• Chart formatting and styling

6. Multi-Sheet Operations

• Create/rename/delete sheets
• Copy/move sheets
• Sheet protection
• Cross-sheet references
• Consolidate data from multiple sheets

7. Data Analysis

• Statistical analysis (sum, average, median, std dev, etc.)
• Data profiling (nulls, duplicates, outliers)
• Correlation and basic analysis
• Grouping and subtotals
• What-if analysis support

8. Google Sheets Integration

• Export to Google Sheets format
• Handle Google Sheets-specific functions
• Preserve sharing permissions note

Implementation Patterns

Reading a File

from openpyxl import load_workbook
import pandas as pd

# Method 1: Using openpyxl for exact cell control
wb = load_workbook('file.xlsx')
ws = wb.active
value = ws['A1'].value

# Method 2: Using pandas for data analysis
df = pd.read_excel('file.xlsx', sheet_name='Sheet1')
df = pd.read_csv('file.csv')

Writing/Editing

from openpyxl import Workbook, load_workbook

# Create new file
wb = Workbook()
ws = wb.active
ws['A1'] = 'Hello'
wb.save('new.xlsx')

# Edit existing
wb = load_workbook('file.xlsx')
ws = wb.active
ws['A1'] = 'Updated'
wb.save('file.xlsx')

Formulas

ws['A3'] = '=SUM(A1:A2)'
ws['B1'] = '=VLOOKUP(D1, Sheet2!A:B, 2, FALSE)'
# Force recalculation on open
wb.calculation.calcMode = 'auto'

Formatting

from openpyxl.styles import Font, PatternFill, Alignment, Border
from openpyxl.styles.numbers import FORMAT_NUMBER_COMMA_SEPARATED1

# Style a header row
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center')

# Number format
ws['A1'].number_format = FORMAT_NUMBER_COMMA_SEPARATED1
ws['B1'].number_format = 'YYYY-MM-DD'

Charts

from openpyxl.chart import BarChart, Reference

chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=10, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=10)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, 'E1')

Conditional Formatting

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule

# Color scale
ws.conditional_formatting.add('A1:A10',
    ColorScaleRule(start_type='min', start_color='F8696B',
                   mid_type='percentile', mid_value=50, mid_color='FFEB84',
                   end_type='max', end_color='63BE7B'))

# Formula-based
ws.conditional_formatting.add('B2:B100',
    FormulaRule(formula=['$B2>1000'], fill=PatternFill(start_color='FFC7CE', fill_type='solid')))

Pivot Tables

from openpyxl.pivot import PivotTable, PivotField

# Note: openpyxl has limited pivot support
# For complex pivots, consider using xlsxwriter or pandas

Libraries to Use

Task	Library	Install
Excel read/write	openpyxl	pip install openpyxl
Advanced Excel	xlsxwriter	pip install xlsxwriter
Data analysis	pandas	pip install pandas
CSV/TSV	csv (stdlib)	-
Google Sheets	gspread, pygsheets	pip install gspread
PDF exports	reportlab, fpdf	pip install reportlab

Quality Standards

• Always verify: After any write operation, re-open and confirm data
• Preserve formatting: Never lose existing styling when editing
• Handle errors gracefully: Missing sheets, invalid formulas, encoding issues
• Large files: Use iter_chunks() for files >100MB
• Backup: Always create backup before major modifications

Common Patterns

Error Handling

try:
    wb = load_workbook('file.xlsx')
except FileNotFoundError:
    # Handle missing file
    pass
except Exception as e:
    # Log error, provide fallback
    pass

Safe Cell Access

value = ws.cell(row=1, column=1).value  # Safer than ws['A1']
if value is None:
    value = ''  # or default

Performance

# Disable calculation temporarily
wb.calculation.calcMode = 'manual'
# ... make changes ...
wb.calculation.calcMode = 'auto'

User Communication

When working with spreadsheets:

1. Always confirm the file path and format
2. Report what was changed (cells, sheets, formulas)
3. Show preview of data when relevant
4. Warn about any data loss or formatting changes
5. Offer to save in multiple formats if useful

Example Commands to Handle

• "Create an Excel file with sales data"
• "Add a SUM formula to column E"
• "Make row 1 bold with blue background"
• "Create a bar chart from this data"
• "Read the CSV and show me the columns"
• "Add conditional formatting to highlight duplicates"
• "Create a pivot table from this sheet"
• "Freeze the first row"
• "Export this to CSV"
• "Add data validation dropdown to cell B2"