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:

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:

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:

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: 

...