Spreadsheet Builder

What this skill does

# 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:**
1. Import `exceljs` and create Workbook instance
2. Add worksheet with a name
3. Define columns with headers and widths
4. Add data rows
5. Apply basic formatting (fonts, colors, alignment)
6. Set column widths and row heights
7. Write to .xlsx file

**Implementation:**
```javascript
const ExcelJS = require('exceljs');

async function createBasicWorkbook(data, outputPath) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sales Data');

  // Define columns
  worksheet.columns = [
    { header: 'Date', key: 'date', width: 12 },
    { header: 'Product', key: 'product', width: 25 },
    { header: 'Quantity', key: 'quantity', width: 10 },
    { header: 'Price', key: 'price', width: 12 },
    { header: 'Total', key: 'total', width: 12 }
  ];

  // Style header row
  worksheet.getRow(1).font = { bold: true, size: 12 };
  worksheet.getRow(1).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FF4472C4' }
  };
  worksheet.getRow(1).font = { color: { argb: 'FFFFFFFF' }, bold: true };

  // Add data
  data.forEach(row => {
    worksheet.addRow(row);
  });

  // Format currency columns
  worksheet.getColumn('price').numFmt = '$#,##0.00';
  worksheet.getColumn('total').numFmt = '$#,##0.00';

  await workbook.xlsx.writeFile(outputPath);
}
```

### Workflow 2: Add Formulas and Calculations
**Purpose:** Create spreadsheets with automatic calculations and formulas

**Steps:**
1. Create workbook and worksheet
2. Add data columns
3. Insert formula cells (SUM, AVERAGE, IF, VLOOKUP, etc.)
4. Use cell references for dynamic calculations
5. Add conditional formulas
6. Create calculated columns
7. Add totals and subtotals

**Implementation:**
```javascript
async function createWithFormulas(data, outputPath) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Financial Report');

  worksheet.columns = [
    { header: 'Month', key: 'month', width: 12 },
    { header: 'Revenue', key: 'revenue', width: 15 },
    { header: 'Expenses', key: 'expenses', width: 15 },
    { header: 'Profit', key: 'profit', width: 15 },
    { header: 'Margin %', key: 'margin', width: 12 }
  ];

  // Add data rows
  data.forEach((row, index) => {
    const rowIndex = index + 2; // Account for header row
    worksheet.addRow({
      month: row.month,
      revenue: row.revenue,
      expenses: row.expenses,
      profit: { formula: `B${rowIndex}-C${rowIndex}` }, // Revenue - Expenses
      margin: { formula: `D${rowIndex}/B${rowIndex}` }  // Profit / Revenue
    });
  });

  // Add totals row
  const lastRow = data.length + 2;
  worksheet.addRow({
    month: 'TOTAL',
    revenue: { formula: `SUM(B2:B${lastRow - 1})` },
    expenses: { formula: `SUM(C2:C${lastRow - 1})` },
    profit: { formula: `SUM(D2:D${lastRow - 1})` },
    margin: { formula: `D${lastRow}/B${lastRow}` }
  });

  // Format totals row
  worksheet.getRow(lastRow).font = { bold: true };
  worksheet.getRow(lastRow).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE7E6E6' }
  };

  // Number formatting
  worksheet.getColumn('revenue').numFmt = '$#,##0.00';
  worksheet.getColumn('expenses').numFmt = '$#,##0.00';
  worksheet.getColumn('profit').numFmt = '$#,##0.00';
  worksheet.getColumn('margin').numFmt = '0.00%';

  await workbook.xlsx.writeFile(outputPath);
}
```

### Workflow 3: Apply Conditional Formatting
**Purpose:** Highlight cells based on rules and thresholds

**Steps:**
1. Create workbook with data
2. Define conditional formatting rules
3. Apply color scales for value ranges
4. Use data bars for visual comparison
5. Add icon sets for status indicators
6. Highlight top/bottom values
7. Apply custom formula-based rules

**Implementation:**
```javascript
async function addConditionalFormatting(data, outputPath) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Performance');

  // Add data...
  worksheet.columns = [
    { header: 'Employee', key: 'name', width: 20 },
    { header: 'Sales', key: 'sales', width: 15 },
    { header: 'Target', key: 'target', width: 15 },
    { header: 'Performance', key: 'performance', width: 15 }
  ];

  data.forEach(row => worksheet.addRow(row));

  // Color scale: Green (high) to Red (low)
  worksheet.addConditionalFormatting({
    ref: 'B2:B100',
    rules: [
      {
        type: 'colorScale',
        cfvo: [
          { type: 'min' },
          { type: 'percentile', value: 50 },
          { type: 'max' }
        ],
        color: [
          { argb: 'FFF8696B' }, // Red
          { argb: 'FFFFEB84' }, // Yellow
          { argb: 'FF63BE7B' }  // Green
        ]
      }
    ]
  });

  // Data bars for performance column
  worksheet.addConditionalFormatting({
    ref: 'D2:D100',
    rules: [
      {
        type: 'dataBar',
        minLength: 0,
        maxLength: 100,
        color: { argb: 'FF638EC6' }
      }
    ]
  });

  // Highlight values above target
  worksheet.addConditionalFormatting({
    ref: 'B2:B100',
    rules: [
      {
        type: 'expression',
        formulae: ['B2>C2'], // Sales > Target
        style: {
          fill: {
            type: 'pattern',
            pattern: 'solid',
            bgColor: { argb: 'FFC6EFCE' }
          }
        }
      }
    ]
  });

  await workbook.xlsx.writeFile(outputPath);
}
```

### Workflow 4: Create Charts and Visualizations
**Purpose:** Add charts to visualize data trends and comparisons

**Steps:**
1. Create workbook with data
2. Add data worksheet
3. Create chart worksheet or embed in data sheet
4. Define chart type (bar, line, pie, scatter, etc.)
5. Set data ranges for series
6. Configure chart title, axes, legend
7. Apply styling and colors
8. Position chart on worksheet

**Implementation:**
```javascript
async function createWithChart(data, outputPath) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sales');

  // Add data
  worksheet.columns = [
    { header: 'Month', key: 'month', width: 12 },
    { header: 'Sales', key: 'sales', width: 15 }
  ];

  data.forEach(row => worksheet.addRow(row));

  // Create chart (Note: exceljs has limited chart support, consider using xlsx-chart)
  // For full chart support, you may need to use Excel templates or officegen library

  // Alternative: Add chart using worksheet image
  // Or use a charting library to generate image, then embed

  await workbook.xlsx.writeFile(outputPath);
}

// For advanced charts, consider using officegen or generating chart images
```

### Workflow 5: Multi-Sheet Workbook with Links
**Purpose:** Create complex workbooks with multiple related sheets

**Steps:**
1. Create workbook
2. Add multiple worksheets (Summary, Details, Raw Data, etc.)
3. Create cross-sheet formulas and references
4. Add hyperlinks between sheets
5. Protect sheets with passwords
6. Hide/show sheets as needed
7. Set active sheet and freeze panes

**Implementation:**
```javascript
async function createMultiSheetWorkbook(data, outputPath) {
  const workbook = new ExcelJS.Workbook();

  // Summary sheet
  const summary = workbook.addWorksheet('Summary');
  summary.columns = [
    { header: 'Metric', key: 'metric', width: 25 },
    { header: 'Value', key: 'value', w