name: xlsx
description: Create and manipulate Excel spreadsheets with formulas, formatting, charts, and data analysis. Use when working with Excel files, CSV data, or spreadsheet operations.
license: MIT
metadata:
author: thesimpleapp
version: "1.0"

Excel Spreadsheet Processing

Work with Excel files: read, write, formulas, formatting, and analysis.

Python Libraries

openpyxl - Full Excel Support

from openpyxl import Workbook, load_workbook

# Create new workbook
wb = Workbook()
ws = wb.active
ws.title = "Data"

# Write data
ws['A1'] = "Name"
ws['B1'] = "Value"
ws.append(["Item 1", 100])
ws.append(["Item 2", 200])

# Add formula
ws['B4'] = "=SUM(B2:B3)"

# Save
wb.save("output.xlsx")

pandas - Data Analysis

import pandas as pd

# Read Excel
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")

# Process data
df['Total'] = df['Price'] * df['Quantity']
summary = df.groupby('Category').sum()

# Write to Excel with multiple sheets
with pd.ExcelWriter("output.xlsx") as writer:
    df.to_excel(writer, sheet_name="Data")
    summary.to_excel(writer, sheet_name="Summary")

JavaScript Libraries

xlsx (SheetJS)

import XLSX from 'xlsx';

// Read file
const workbook = XLSX.readFile('data.xlsx');
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(sheet);

// Create workbook
const newWb = XLSX.utils.book_new();
const newWs = XLSX.utils.json_to_sheet([
  { Name: "Item 1", Value: 100 },
  { Name: "Item 2", Value: 200 }
]);
XLSX.utils.book_append_sheet(newWb, newWs, "Data");
XLSX.writeFile(newWb, "output.xlsx");

Common Formulas

Formatting with openpyxl

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

# Header styling
header_font = Font(bold=True, size=12)
header_fill = PatternFill("solid", fgColor="4472C4")

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# Number formatting
ws['B2'].number_format = '$#,##0.00'
ws['C2'].number_format = '0.00%'

# Column width
ws.column_dimensions['A'].width = 20

Charts

from openpyxl.chart import BarChart, Reference

# Create chart
chart = BarChart()
chart.title = "Sales by Category"

# Data reference
data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)

chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# Add to sheet
ws.add_chart(chart, "D2")

Best Practices

1. Use named ranges for clarity in formulas
2. Freeze panes for large datasets (ws.freeze_panes = 'A2')
3. Validate data before writing to avoid corruption
4. Handle dates carefully (Excel date serial numbers)
5. Close files properly to avoid file locks