XLSX - Excel Spreadsheets
Spreadsheet manipulation: formulas, charts, data transformations, and comprehensive Excel automation.
npx degit LangbaseInc/agent-skills/document-skills/xlsx my-xlsx-skill
Work with Excel spreadsheets (.xlsx) programmatically for data processing, analysis, and reporting.
Data Management
- Read/write cells
- Bulk data operations
- Multiple worksheets
- Named ranges
- Data validation
Formulas & Functions
- Insert formulas
- Calculate values
- Array formulas
- Named formulas
- Cross-sheet references
Formatting
- Cell formatting
- Number formats
- Fonts and colors
- Borders and fills
- Conditional formatting
- Column/row sizing
Visualizations
- Charts (bar, line, pie, etc.)
- Sparklines
- Data tables
- Pivot tables
Advanced Features
- Freeze panes
- Filtering
- Sorting
- Grouping
- Protection
- Macros (VBA)
Python - openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Fill
from openpyxl.chart import BarChart, Reference
# Create workbook
wb = Workbook()
ws = wb.active
# Write data
ws['A1'] = 'Name'
ws['B1'] = 'Score'
ws.append(['Alice', 95])
ws.append(['Bob', 87])
# Save
wb.save('data.xlsx')
Python - pandas
import pandas as pd
# Read Excel
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# Write Excel
df.to_excel('output.xlsx', sheet_name='Data', index=False)
# Multiple sheets
with pd.ExcelWriter('output.xlsx') as writer:
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
Node.js - ExcelJS
const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('My Sheet');
worksheet.columns = [
{ header: 'Name', key: 'name', width: 15 },
{ header: 'Score', key: 'score', width: 10 }
];
worksheet.addRow({ name: 'Alice', score: 95 });
worksheet.addRow({ name: 'Bob', score: 87 });
await workbook.xlsx.writeFile('data.xlsx');
from openpyxl import load_workbook
# Load workbook
wb = load_workbook('data.xlsx')
ws = wb.active
# Read cell
value = ws['A1'].value
# Read range
for row in ws['A1:B10']:
for cell in row:
print(cell.value)
# Iterate all rows
for row in ws.iter_rows(min_row=2, values_only=True):
name, score = row
print(f"{name}: {score}")
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# Write single cell
ws['A1'] = 'Header'
# Write row
ws.append([1, 2, 3, 4])
# Write bulk data
data = [
['Name', 'Age', 'City'],
['Alice', 25, 'NYC'],
['Bob', 30, 'LA'],
]
for row in data:
ws.append(row)
wb.save('output.xlsx')
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# Simple formula
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = '=SUM(A1:A2)'
# More complex
ws['B1'] = '=IF(A1>15, "High", "Low")'
ws['C1'] = '=VLOOKUP(A1, $E$1:$F$10, 2, FALSE)'
# Named range formula
ws['D1'] = '=SUM(MyRange)'
wb.save('formulas.xlsx')
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
wb = Workbook()
ws = wb.active
# Font
ws['A1'].font = Font(name='Arial', size=14, bold=True, color='FF0000')
# Fill
ws['A1'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# Alignment
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
# Border
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
ws['A1'].border = thin_border
# Number format
ws['B1'] = 1234.56
ws['B1'].number_format = '$#,##0.00'
# Date format
from datetime import datetime
ws['C1'] = datetime.now()
ws['C1'].number_format = 'YYYY-MM-DD'
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
# Add data
data = [
['Month', 'Sales'],
['Jan', 100],
['Feb', 150],
['Mar', 120],
]
for row in data:
ws.append(row)
# Create chart
chart = BarChart()
chart.title = "Monthly Sales"
chart.x_axis.title = "Month"
chart.y_axis.title = "Sales"
# Add data to chart
data = Reference(ws, min_col=2, min_row=1, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
# Add chart to sheet
ws.add_chart(chart, "D1")
wb.save('chart.xlsx')
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule
wb = Workbook()
ws = wb.active
# Add data
for i in range(1, 11):
ws[f'A{i}'] = i * 10
# Add conditional formatting
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
ws.conditional_formatting.add(
'A1:A10',
CellIsRule(operator='greaterThan', formula=['50'], fill=red_fill)
)
wb.save('conditional.xlsx')
from openpyxl import Workbook
wb = Workbook()
# Create sheets
ws1 = wb.active
ws1.title = "Sales"
ws2 = wb.create_sheet("Expenses")
ws3 = wb.create_sheet("Summary", 0) # Insert at beginning
# Write to different sheets
ws1['A1'] = 'Sales Data'
ws2['A1'] = 'Expense Data'
ws3['A1'] = 'Summary'
# Access by name
summary = wb['Summary']
summary['B1'] = '=Sales!A1'
wb.save('multi_sheet.xlsx')
import pandas as pd
# Read Excel
df = pd.read_excel('data.xlsx')
# Data analysis
summary = df.describe()
grouped = df.groupby('Category').sum()
# Data transformation
df['Total'] = df['Price'] * df['Quantity']
df_filtered = df[df['Score'] > 80]
# Write results
with pd.ExcelWriter('analysis.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Raw Data')
summary.to_excel(writer, sheet_name='Summary')
grouped.to_excel(writer, sheet_name='Grouped')
import pandas as pd
# Read data
df = pd.read_excel('sales.xlsx')
# Create pivot table
pivot = pd.pivot_table(
df,
values='Sales',
index=['Region', 'Product'],
columns='Month',
aggfunc='sum',
fill_value=0
)
# Save
pivot.to_excel('pivot.xlsx')
- Data analysis and reporting
- Financial modeling
- Inventory management
- Grade books
- Budget tracking
- Data transformation
- ETL processes
- Automated reporting
- Use pandas for large datasets
- Close workbooks after operations
- Validate data before writing
- Use appropriate data types
- Optimize for performance
- Handle exceptions
- Document formulas
- Version control templates
- Test with large files