XLSX Skill

Description

This skill creates and manipulates .xlsx Excel files using the exceljs library. Supports formulas, conditional formatting, data validation, multiple sheets, styled headers, auto-filters, and chart generation.

Instructions

When the user asks you to work with Excel files, use these patterns:

Creating a Workbook

Copy
const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
workbook.creator = 'Claude';
workbook.created = new Date();

const sheet = workbook.addWorksheet('Sales Data', {
  views: [{ state: 'frozen', ySplit: 1 }],  // Freeze header row
});

// Define columns with headers
sheet.columns = [
  { header: 'Date', key: 'date', width: 15 },
  { header: 'Product', key: 'product', width: 25 },
  { header: 'Quantity', key: 'qty', width: 12 },
  { header: 'Unit Price', key: 'price', width: 14, style: { numFmt: '$#,##0.00' } },
  { header: 'Total', key: 'total', width: 14, style: { numFmt: '$#,##0.00' } },
];

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

// Add data rows
const data = [
  { date: '2025-01-15', product: 'Widget A', qty: 100, price: 29.99 },
  { date: '2025-01-16', product: 'Widget B', qty: 50, price: 49.99 },
];

data.forEach((row, i) => {
  const r = sheet.addRow(row);
  // Formula for total column
  r.getCell('total').value = { formula: `C${i + 2}*D${i + 2}` };
});

// Add auto-filter
sheet.autoFilter = 'A1:E1';

await workbook.xlsx.writeFile('sales-report.xlsx');

Conditional Formatting

Copy
sheet.addConditionalFormatting({
  ref: 'E2:E100',
  rules: [
    {
      type: 'cellIs', operator: 'greaterThan',
      formulae: [1000],
      style: { fill: { type: 'pattern', pattern: 'solid', bgColor: { argb: 'FF92D050' } } },
    },
    {
      type: 'cellIs', operator: 'lessThan',
      formulae: [100],
      style: { font: { color: { argb: 'FFFF0000' } } },
    },
  ],
});

Reading Existing Files

Copy
const workbook = new ExcelJS.Workbook();
await workbook.xlsx.readFile('input.xlsx');

const sheet = workbook.getWorksheet('Sheet1');
sheet.eachRow((row, rowNumber) => {
  console.log(`Row ${rowNumber}:`, row.values);
});

Data Validation (Dropdowns)

Copy
sheet.getCell('B2').dataValidation = {
  type: 'list',
  allowBlank: true,
  formulae: ['"Option A,Option B,Option C"'],
  showErrorMessage: true,
  errorTitle: 'Invalid',
  error: 'Please select from the list',
};

Rules

• Install dependency: npm install exceljs
• Always freeze the header row for data tables
• Use numFmt for currency, percentage, and date formatting
• Apply auto-filter to header rows for large datasets
• Set column widths based on content length
• For large datasets (>10K rows), use streaming: workbook.xlsx.createInputStream()
• Formula references use Excel-style notation (A1, B2:C10, etc.)
• When reading files, check worksheet.rowCount before iterating

Examples

User: Export this JSON data to Excel Action: Create styled workbook with headers, data rows, and auto-filters

User: Create a budget spreadsheet template Action: Multi-sheet workbook with categories, formulas for totals, and conditional formatting

User: Parse this Excel file and summarize the data Action: Read the file, aggregate values, and output summary statistics