Claude Code for ExcelJS: Excel Spreadsheet Generation

ExcelJS creates and reads Excel XLSX files in Node.js — new ExcelJS.Workbook() creates a workbook. workbook.addWorksheet("Sheet1") adds a tab. worksheet.columns = [{ header, key, width }] defines columns. worksheet.addRow(dataObject) inserts a data row. Cell formatting uses cell.font, cell.fill, cell.border, cell.alignment, and cell.numFmt for number format strings like "$#,##0.00" or "MM/DD/YYYY". worksheet.mergeCells("A1:D1") spans cells. worksheet.addImage(imageId, "B2:D5") embeds images. Formula cells use { formula: "SUM(B2:B10)" }. workbook.xlsx.writeBuffer() returns a buffer for streaming. Auto-fit column widths require calculating max content width. worksheet.views controls freeze panes and zoom. Claude Code generates ExcelJS data export functions, styled report templates, formula worksheets, multi-sheet workbooks, and Next.js Route Handler streaming patterns.

CLAUDE.md for ExcelJS

## ExcelJS Stack
- Version: exceljs >= 4.4
- Workbook: const wb = new ExcelJS.Workbook(); const ws = wb.addWorksheet("Sales")
- Columns: ws.columns = [{ header: "Date", key: "date", width: 12 }, ...]
- Add row: ws.addRow({ date: new Date(), amount: 100 }) — or ws.addRow(["A", "B"])
- Style: ws.getCell("A1").font = { bold: true, size: 12 }; cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF3B82F6" } }
- NumFmt: cell.numFmt = "$#,##0.00" — for currency; "MM/DD/YYYY" — for dates
- Buffer: const buf = await wb.xlsx.writeBuffer() — returns Buffer
- Stream: const stream = new ExcelJS.stream.xlsx.WorkbookWriter({ stream: res })

Sales Report Generator

// lib/excel/sales-report.ts — styled Excel report
import ExcelJS from "exceljs"

type SaleRecord = {
  date: Date
  orderNumber: string
  customer: string
  product: string
  quantity: number
  unitPrice: number
  total: number
  status: "completed" | "pending" | "refunded"
}

export async function generateSalesReport(
  sales: SaleRecord[],
  reportTitle = "Sales Report",
): Promise<Buffer> {
  const wb = new ExcelJS.Workbook()
  wb.creator = "Claude Skills 360"
  wb.created = new Date()

  const ws = wb.addWorksheet("Sales Data")

  // ── Title row ──────────────────────────────────────────────
  ws.mergeCells("A1:H1")
  const titleCell = ws.getCell("A1")
  titleCell.value = reportTitle
  titleCell.font = { bold: true, size: 16, color: { argb: "FFFFFFFF" } }
  titleCell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF1E40AF" } }
  titleCell.alignment = { horizontal: "center", vertical: "middle" }
  ws.getRow(1).height = 36

  // Subtitle
  ws.mergeCells("A2:H2")
  const subCell = ws.getCell("A2")
  subCell.value = `Generated: ${new Date().toLocaleDateString("en-US", { dateStyle: "full" })} | ${sales.length} transactions`
  subCell.font = { italic: true, size: 10, color: { argb: "FF6B7280" } }
  subCell.alignment = { horizontal: "center" }
  ws.getRow(2).height = 20

  // ── Column headers ─────────────────────────────────────────
  ws.columns = [
    { key: "date",        width: 14 },
    { key: "orderNumber", width: 14 },
    { key: "customer",    width: 22 },
    { key: "product",     width: 24 },
    { key: "quantity",    width: 10 },
    { key: "unitPrice",   width: 12 },
    { key: "total",       width: 14 },
    { key: "status",      width: 12 },
  ]

  const headers = ["Date", "Order #", "Customer", "Product", "Qty", "Unit Price", "Total", "Status"]
  const headerRow = ws.getRow(3)
  headerRow.values = ["", ...headers]  // Offset by 1 for column key alignment

  headerRow.eachCell((cell, colNumber) => {
    if (colNumber < 2) return
    cell.value = headers[colNumber - 2]
    cell.font = { bold: true, size: 10, color: { argb: "FFFFFFFF" } }
    cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF374151" } }
    cell.alignment = { horizontal: "center", vertical: "middle" }
    cell.border = { bottom: { style: "medium", color: { argb: "FF6B7280" } } }
  })
  headerRow.height = 24

  // ── Data rows ──────────────────────────────────────────────
  const statusColors: Record<string, string> = {
    completed: "FF16A34A",
    pending:   "FFD97706",
    refunded:  "FFDC2626",
  }

  sales.forEach((sale, i) => {
    const dataRow = ws.addRow({
      date:        sale.date,
      orderNumber: sale.orderNumber,
      customer:    sale.customer,
      product:     sale.product,
      quantity:    sale.quantity,
      unitPrice:   sale.unitPrice / 100,
      total:       sale.total / 100,
      status:      sale.status.charAt(0).toUpperCase() + sale.status.slice(1),
    })

    dataRow.height = 20

    // Zebra striping
    const bgColor = i % 2 === 0 ? "FFF9FAFB" : "FFFFFFFF"

    dataRow.eachCell((cell, colNumber) => {
      if (colNumber < 2) return
      cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: bgColor } }
      cell.alignment = { vertical: "middle" }
      cell.border = { bottom: { style: "thin", color: { argb: "FFE5E7EB" } } }
    })

    // Date format
    const dateCell = dataRow.getCell(1)
    dateCell.numFmt = "MM/DD/YYYY"

    // Currency format
    dataRow.getCell(6).numFmt = '$#,##0.00'
    dataRow.getCell(6).alignment = { horizontal: "right" }
    dataRow.getCell(7).numFmt = '$#,##0.00'
    dataRow.getCell(7).alignment = { horizontal: "right" }

    // Quantity center
    dataRow.getCell(5).alignment = { horizontal: "center" }

    // Status color
    const statusCell = dataRow.getCell(8)
    statusCell.font = { bold: true, size: 9, color: { argb: statusColors[sale.status] ?? "FF374151" } }
    statusCell.alignment = { horizontal: "center" }
  })

  // ── Totals row ─────────────────────────────────────────────
  const dataStartRow = 4
  const dataEndRow = 3 + sales.length
  const totalRow = ws.addRow({})
  totalRow.height = 24
  const totalsCell = totalRow.getCell(3)  // Customer column
  totalsCell.value = `${sales.length} transactions`
  totalsCell.font = { bold: true, color: { argb: "FF6B7280" } }

  const totalCell = totalRow.getCell(7)  // Total column
  totalCell.value = { formula: `SUM(G${dataStartRow}:G${dataEndRow})` }
  totalCell.numFmt = '$#,##0.00'
  totalCell.font = { bold: true, size: 11 }
  totalCell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFDBEAFE" } }
  totalCell.border = { top: { style: "medium" } }

  // ── Summary worksheet ──────────────────────────────────────
  const summary = wb.addWorksheet("Summary")
  summary.columns = [
    { key: "metric", width: 24 },
    { key: "value",  width: 16 },
  ]

  const byStatus = Object.groupBy(sales, s => s.status)
  const summaryData = [
    ["Total Revenue", sales.reduce((s, r) => s + r.total, 0) / 100],
    ["Total Orders", sales.length],
    ["Completed", byStatus.completed?.length ?? 0],
    ["Pending", byStatus.pending?.length ?? 0],
    ["Refunded", byStatus.refunded?.length ?? 0],
    ["Avg Order Value", sales.length ? sales.reduce((s, r) => s + r.total, 0) / sales.length / 100 : 0],
  ]

  summaryData.forEach(([label, value]) => {
    const row = summary.addRow({ metric: label, value })
    if (typeof value === "number" && (label as string).includes("Revenue") || (label as string).includes("Value")) {
      row.getCell(2).numFmt = "$#,##0.00"
    }
  })

  // Freeze top rows and enable auto-filter on data sheet
  ws.views = [{ state: "frozen", xSplit: 0, ySplit: 3, topLeftCell: "A4" }]
  ws.autoFilter = `A3:H3`

  const buffer = await wb.xlsx.writeBuffer()
  return Buffer.from(buffer)
}

API Route

// app/api/export/sales/route.ts — stream Excel file
import { NextRequest, NextResponse } from "next/server"
import { generateSalesReport } from "@/lib/excel/sales-report"
import { auth } from "@clerk/nextjs/server"
import { db } from "@/lib/db"

export async function GET(request: NextRequest) {
  const { userId } = await auth()
  if (!userId) return NextResponse.json({ error: "Unauthorized" }, { status: 401 })

  const { searchParams } = new URL(request.url)
  const startDate = new Date(searchParams.get("start") ?? Date.now() - 30 * 24 * 60 * 60 * 1000)
  const endDate = new Date(searchParams.get("end") ?? Date.now())

  const orders = await db.order.findMany({
    where: {
      userId,
      createdAt: { gte: startDate, lte: endDate },
    },
    include: { items: true },
    orderBy: { createdAt: "desc" },
  })

  const sales = orders.flatMap(order =>
    order.items.map(item => ({
      date: order.createdAt,
      orderNumber: order.id.slice(-8).toUpperCase(),
      customer: order.customerName ?? "Unknown",
      product: item.name,
      quantity: item.quantity,
      unitPrice: item.priceCents,
      total: item.priceCents * item.quantity,
      status: order.status as "completed" | "pending" | "refunded",
    })),
  )

  const buffer = await generateSalesReport(sales)

  const filename = `sales-${startDate.toISOString().split("T")[0]}-to-${endDate.toISOString().split("T")[0]}.xlsx`

  return new NextResponse(buffer, {
    headers: {
      "Content-Type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      "Content-Disposition": `attachment; filename="${filename}"`,
      "Content-Length": buffer.byteLength.toString(),
    },
  })
}

For the SheetJS (xlsx) alternative when reading complex existing Excel files with legacy format support (XLS, XLSB, ODS), browser-side parsing, and a larger community ecosystem is preferred — SheetJS excels at parsing but lacks ExcelJS’s rich styling API for generation, see the SheetJS guide. For the Apache POI/OOXML alternative when Java server-side Excel generation with advanced formatting (pivot tables, conditional formatting, sparklines) is needed — while not a JavaScript solution, Apache POI powers many enterprise report generators with feature coverage beyond ExcelJS, see the Java reporting guide. The Claude Skills 360 bundle includes ExcelJS skill sets covering styled reports, formulas, and streaming. Start with the free tier to try Excel generation code.