spreadsheet builder skill by jmsktm

Spreadsheet Builder

The Spreadsheet Builder skill enables creation of professional Excel (.xlsx) and CSV files with advanced formatting, formulas, charts, and data analysis features. Using libraries like exceljs and xlsx, this skill handles everything from simple data exports to complex financial models and dashboards.

Generate data reports, financial statements, inventory lists, analysis dashboards, and any tabular data visualization. Support for multiple sheets, cell styling, conditional formatting, formulas, pivot tables, and charts makes this a comprehensive solution for spreadsheet automation.

Core Workflows

Workflow 1: Create Basic Excel Workbook

Purpose: Build a simple Excel file with formatted data

Steps:

Import exceljs and create Workbook instance
Add worksheet with a name
Define columns with headers and widths
Add data rows
Apply basic formatting (fonts, colors, alignment)
Set column widths and row heights
Write to .xlsx file

Implementation:

const ExcelJS = require('exceljs');

Workflow 2: Add Formulas and Calculations

Purpose: Create spreadsheets with automatic calculations and formulas

Steps:

Create workbook and worksheet
Add data columns
Insert formula cells (SUM, AVERAGE, IF, VLOOKUP, etc.)
Use cell references for dynamic calculations
Add conditional formulas
Create calculated columns
Add totals and subtotals

Implementation:

Workflow 3: Apply Conditional Formatting

Purpose: Highlight cells based on rules and thresholds

Steps:

Create workbook with data
Define conditional formatting rules
Apply color scales for value ranges
Use data bars for visual comparison
Add icon sets for status indicators
Highlight top/bottom values
Apply custom formula-based rules

Implementation:

Workflow 4: Create Charts and Visualizations

Purpose: Add charts to visualize data trends and comparisons

Steps:

Create workbook with data
Add data worksheet
Create chart worksheet or embed in data sheet
Define chart type (bar, line, pie, scatter, etc.)
Set data ranges for series
Configure chart title, axes, legend
Apply styling and colors
Position chart on worksheet

Implementation:

Workflow 5: Multi-Sheet Workbook with Links

Purpose: Create complex workbooks with multiple related sheets

Steps:

Create workbook
Add multiple worksheets (Summary, Details, Raw Data, etc.)
Create cross-sheet formulas and references
Add hyperlinks between sheets
Protect sheets with passwords
Hide/show sheets as needed
Set active sheet and freeze panes

Implementation:

Workflow 6: Export to CSV

Purpose: Create simple CSV files for data exchange

Steps:

Format data as array of objects or arrays
Define headers if needed
Convert to CSV format
Handle special characters and quotes
Set delimiter (comma, semicolon, tab)
Write to file with proper encoding

Implementation:

Quick Reference

Action	Command/Trigger
Create Excel workbook	"create excel file with [data]"
Generate CSV	"export [data] to csv"
Add formulas	"add formulas to spreadsheet"
Apply formatting	"format excel cells [style]"
Create chart	"add chart to workbook"
Multi-sheet workbook	"create workbook with [sheets]"
Conditional formatting	"apply conditional formatting"
Freeze panes	"freeze header row"
Protect sheet	"password protect [sheet]"

Best Practices

Data Validation: Validate data types before writing to cells
Number Formatting: Apply appropriate number formats (currency, percentage, date)
Column Widths: Set widths based on content for readability
Freeze Panes: Freeze header rows for scrollable data
Named Ranges: Use named ranges for formulas in complex workbooks
Templates: Create templates for repeated report types
Memory Management: Use streaming for very large datasets (>100k rows)
Error Handling: Wrap formula creation in try-catch for invalid references
CSV Encoding: Use UTF-8 BOM for international characters
Performance: Batch cell operations rather than individual cell writes
Testing: Verify formulas calculate correctly after file creation
Documentation: Comment complex formulas within cells

Common Patterns

Inventory Report:

Financial Dashboard:

Dependencies

Install required packages:

Error Handling

Invalid Formulas: Validate formula syntax before assignment
Cell References: Ensure referenced cells exist
File Permissions: Handle write errors gracefully
Memory Limits: Use streaming mode for files >50MB
Data Types: Coerce data to appropriate types (number, string, date)
Encoding Issues: Ensure UTF-8 encoding for international characters

Performance Tips

Use worksheet.addRows() instead of multiple addRow() calls
Set column properties before adding data
Avoid reading cells unnecessarily
Use streaming write for large datasets
Batch style operations
Pre-calculate values instead of formulas when possible for static data

Advanced Features

Streaming Large Files:

Data Validation:

Protection: