Xlsx Generator

#!/usr/bin/env -S deno run --allow-read

/**
 * analyze-template.ts - Extract structure and content from XLSX files
 *
 * Extracts sheets, cells, formulas, named ranges, and placeholders from Excel
 * spreadsheets for template analysis and content replacement planning.
 *
 * Usage:
 *   deno run --allow-read scripts/analyze-template.ts <input.xlsx> [options]
 *
 * Options:
 *   -h, --help       Show help
 *   -v, --verbose    Enable verbose output
 *   --pretty         Pretty-print JSON output
 *   --sheet <name>   Analyze only specific sheet
 *
 * Permissions:
 *   --allow-read: Read XLSX file
 */

import { parseArgs } from "jsr:@std/cli@1.0.9/parse-args";
import { basename } from "jsr:@std/path@1.0.8";
// deno-lint-ignore no-explicit-any
import * as XLSX from "npm:xlsx@0.18.5" as any;

// === Types ===

export interface CellInfo {
  address: string;
  row: number;
  col: number;
  value: string | number | boolean | null;
  formula?: string;
  type: "string" | "number" | "boolean" | "date" | "error" | "empty";
  format?: string;
}

export interface PlaceholderInfo {
  tag: string;
  location: string;
  sheet: string;
  address: string;
}

export interface SheetInfo {
  name: string;
  rowCount: number;
  colCount: number;
  usedRange: string;
  cells: CellInfo[];
  mergedCells: string[];
}

export interface NamedRange {
  name: string;
  ref: string;
  scope?: string;
}

export interface SpreadsheetInventory {
  filename: string;
  sheetCount: number;
  sheets: SheetInfo[];
  namedRanges: NamedRange[];
  placeholders: PlaceholderInfo[];
  hasFormulas: boolean;
  hasMacros: boolean;
}

interface ParsedArgs {
  help: boolean;
  verbose: boolean;
  pretty: boolean;
  sheet?: string;
  _: (string | number)[];
}

// === Constants ===
const VERSION = "1.0.0";
const SCRIPT_NAME = "analyze-template";

// Placeholder patterns: {{PLACEHOLDER}} or ${placeholder}
const PLACEHOLDER_REGEX = /\{\{([^}]+)\}\}|\$\{([^}]+)\}/g;

// === Help Text ===
function printHelp(): void {
  console.log(`
${SCRIPT_NAME} v${VERSION} - Extract structure from XLSX templates

Usage:
  deno run --allow-read scripts/${SCRIPT_NAME}.ts <input.xlsx> [options]

Arguments:
  <input.xlsx>     Path to the Excel spreadsheet to analyze

Options:
  -h, --help       Show this help message
  -v, --verbose    Enable verbose output (to stderr)
  --pretty         Pretty-print JSON output (default: compact)
  --sheet <name>   Analyze only specific sheet

Examples:
  # Analyze spreadsheet
  deno run --allow-read scripts/${SCRIPT_NAME}.ts template.xlsx > inventory.json

  # With verbose output
  deno run --allow-read scripts/${SCRIPT_NAME}.ts template.xlsx -v --pretty

  # Analyze specific sheet
  deno run --allow-read scripts/${SCRIPT_NAME}.ts template.xlsx --sheet "Data"
`);
}

// === Utility Functions ===

function getCellType(cell: XLSX.CellObject | undefined): CellInfo["type"] {
  if (!cell) return "empty";
  switch (cell.t) {
    case "s": return "string";
    case "n": return "number";
    case "b": return "boolean";
    case "d": return "date";
    case "e": return "error";
    default: return "empty";
  }
}

function getCellValue(cell: XLSX.CellObject | undefined): string | number | boolean | null {
  if (!cell) return null;
  if (cell.v === undefined || cell.v === null) return null;
  return cell.v;
}

function decodeRange(range: string): { startRow: number; startCol: number; endRow: number; endCol: number } {
  const decoded = XLSX.utils.decode_range(range);
  return {
    startRow: decoded.s.r,
    startCol: decoded.s.c,
    endRow: decoded.e.r,
    endCol: decoded.e.c,
  };
}

function findPlaceholders(
  text: string,
  sheet: string,
  address: string
): PlaceholderInfo[] {
  const placeholders: PlaceholderInfo[] = [];
  let match;

  const regex = new RegExp(PLACEHOLDER_REGEX.source, "g");
  while ((match = regex.exec(text)) !== null) {
    placeholders.push({
      tag: match[0],
      location: `${sheet}!${address}`,
      sheet,
      address,
    });
  }

  return placeholders;
}

// === Core Logic ===

export async function analyzeSpreadsheet(
  xlsxPath: string,
  options: { verbose?: boolean; sheetFilter?: string } = {}
): Promise<SpreadsheetInventory> {
  const { verbose = false, sheetFilter } = options;

  // Read the XLSX file
  const data = await Deno.readFile(xlsxPath);
  const workbook = XLSX.read(data, { type: "array", cellFormula: true, cellStyles: true });

  const filename = basename(xlsxPath);

  if (verbose) {
    console.error(`Analyzing: ${filename}`);
    console.error(`Sheets: ${workbook.SheetNames.join(", ")}`);
  }

  const sheets: SheetInfo[] = [];
  const allPlaceholders: PlaceholderInfo[] = [];
  let hasFormulas = false;

  // Process each sheet
  for (const sheetName of workbook.SheetNames) {
    if (sheetFilter && sheetName !== sheetFilter) {
      continue;
    }

    const worksheet = workbook.Sheets[sheetName];
    if (!worksheet) continue;

    const ref = worksheet["!ref"];
    if (!ref) {
      // Empty sheet
      sheets.push({
        name: sheetName,
        rowCount: 0,
        colCount: 0,
        usedRange: "",
        cells: [],
        mergedCells: [],
      });
      continue;
    }

    const range = decodeRange(ref);
    const cells: CellInfo[] = [];

    // Iterate through all cells in range
    for (let row = range.startRow; row <= range.endRow; row++) {
      for (let col = range.startCol; col <= range.endCol; col++) {
        const address = XLSX.utils.encode_cell({ r: row, c: col });
        const cell = worksheet[address] as XLSX.CellObject | undefined;

        if (cell && cell.v !== undefined) {
          const cellInfo: CellInfo = {
            address,
            row: row + 1,  // 1-indexed for display
            col: col + 1,
            value: getCellValue(cell),
            type: getCellType(cell),
          };

          if (cell.f) {
            cellInfo.formula = cell.f;
            hasFormulas = true;
          }

          if (cell.z) {
            cellInfo.format = cell.z;
          }

          cells.push(cellInfo);

          // Check for placeholders in string values
          if (cellInfo.type === "string" && typeof cellInfo.value === "string") {
            const placeholders = findPlaceholders(cellInfo.value, sheetName, address);
            allPlaceholders.push(...placeholders);
          }
        }
      }
    }

    // Get merged cells
    const mergedCells: string[] = [];
    if (worksheet["!merges"]) {
      for (const merge of worksheet["!merges"]) {
        const startCell = XLSX.utils.encode_cell(merge.s);
        const endCell = XLSX.utils.encode_cell(merge.e);
        mergedCells.push(`${startCell}:${endCell}`);
      }
    }

    sheets.push({
      name: sheetName,
      rowCount: range.endRow - range.startRow + 1,
      colCount: range.endCol - range.startCol + 1,
      usedRange: ref,
      cells,
      mergedCells,
    });

    if (verbose) {
      console.error(`Sheet "${sheetName}": ${cells.length} cells, ${mergedCells.length} merged regions`);
    }
  }

  // Get named ranges
  const namedRanges: NamedRange[] = [];
  if (workbook.Workbook?.Names) {
    for (const name of workbook.Workbook.Names) {
      namedRanges.push({
        name: name.Name,
        ref: name.Ref,
        scope: name.Sheet !== undefined ? workbook.SheetNames[name.Sheet] : undefined,
      });
    }
  }

  // Check for macros
  const hasMacros = workbook.vbaraw !== undefined;

  if (verbose) {
    console.error(`Named ranges: ${namedRanges.length}`);
    console.error(`Placeholders found: ${allPlaceholders.length}`);
    console.error(`Has formulas: ${hasFormulas}`);
    console.error(`Has macros: ${hasMacros}`);
  }

  return {
    filename,
    sheetCount: sheets.length,
    sheets,
    namedRanges,
    placeholders: allPlaceholders,
    hasFormulas,
    hasMacros,
  };
}

// === Main CLI Handler ===
async function main(args: string[]): Promise<void> {
  const parsed = parseArgs(args, {
    boolean: ["help", "verbose", "pretty"],
    string: ["sheet"],
    alias: { help: "h", verbose: "v" },
    default: { verbose: false, pretty: false },
  }) as ParsedArgs;

  if (parsed.help) {
    printHelp();
    Deno.exit(0);
  }

  const positionalArgs = parsed._.map(String);

  if (positionalArgs.length === 0) {
    console.error("Error: No input file provided\n");
    printHelp();
    Deno.exit(1);
  }

  const inputPath = positionalArgs[0];

  try {
    const inventory = await analyzeSpreadsheet(inputPath, {
      verbose: parsed.verbose,
      sheetFilter: parsed.sheet,
    });

    const output = parsed.pretty
      ? JSON.stringify(inventory, null, 2)
      : JSON.stringify(inventory);
    console.log(output);
  } catch (error) {
    console.error(
      "Error:",
      error instanceof Error ? error.message : String(error)
    );
    Deno.exit(1);
  }
}

// === Entry Point ===
if (import.meta.main) {
  main(Deno.args);
}

#!/usr/bin/env -S deno run --allow-read --allow-write

/**
 * generate-from-template.ts - Generate XLSX from existing templates
 *
 * Modifies existing Excel templates using placeholder replacement.
 * Finds and replaces tagged content (e.g., {{TITLE}}, ${date}) in cells
 * across all sheets. Preserves formulas, formatting, and structure.
 *
 * Usage:
 *   deno run --allow-read --allow-write scripts/generate-from-template.ts <template.xlsx> <spec.json> <output.xlsx>
 *
 * Options:
 *   -h, --help       Show help
 *   -v, --verbose    Enable verbose output
 *
 * Permissions:
 *   --allow-read: Read template and specification files
 *   --allow-write: Write output XLSX file
 */

import { parseArgs } from "jsr:@std/cli@1.0.9/parse-args";
import { basename } from "jsr:@std/path@1.0.8";
// deno-lint-ignore no-explicit-any
import * as XLSX from "npm:xlsx@0.18.5" as any;

// === Types ===

export interface TextReplacement {
  /** The tag to find and replace (e.g., "{{TITLE}}" or "${date}") */
  tag: string;
  /** The replacement value */
  value: string | number | boolean;
  /** Optional: only apply to specific sheets */
  sheets?: string[];
  /** Optional: only apply to specific cell range (e.g., "A1:D10") */
  range?: string;
}

export interface CellUpdate {
  /** Sheet name */
  sheet: string;
  /** Cell address (e.g., "A1") */
  address: string;
  /** New value */
  value: string | number | boolean | null;
  /** Optional formula (without = sign) */
  formula?: string;
}

export interface TemplateSpec {
  /** Text replacements to apply */
  textReplacements?: TextReplacement[];
  /** Direct cell updates */
  cellUpdates?: CellUpdate[];
  /** Sheets to include (all if omitted) */
  includeSheets?: string[];
  /** Sheets to exclude */
  excludeSheets?: string[];
}

interface ParsedArgs {
  help: boolean;
  verbose: boolean;
  _: (string | number)[];
}

// === Constants ===
const VERSION = "1.0.0";
const SCRIPT_NAME = "generate-from-template";

// === Help Text ===
function printHelp(): void {
  console.log(`
${SCRIPT_NAME} v${VERSION} - Generate XLSX from existing templates

Usage:
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts <template.xlsx> <spec.json> <output.xlsx>

Arguments:
  <template.xlsx>  Path to the template Excel file
  <spec.json>      Path to JSON specification for replacements
  <output.xlsx>    Path for output Excel file

Options:
  -h, --help       Show this help message
  -v, --verbose    Enable verbose output

Specification Format:
  {
    "textReplacements": [
      { "tag": "{{TITLE}}", "value": "Q4 2024 Report" },
      { "tag": "{{DATE}}", "value": "2024-12-15" },
      { "tag": "\${author}", "value": "Finance Team", "sheets": ["Summary"] }
    ],
    "cellUpdates": [
      { "sheet": "Data", "address": "B5", "value": 1250000 },
      { "sheet": "Data", "address": "C5", "formula": "B5*1.1" }
    ]
  }

Examples:
  # Replace text in template
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts \\
    template.xlsx replacements.json output.xlsx

  # With verbose output
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts \\
    template.xlsx replacements.json output.xlsx -v
`);
}

// === Utility Functions ===

function escapeRegExp(string: string): string {
  return string.replace(/[.*+?^${}()|[\]\\]/g, "\\$&");
}

function getCellType(value: unknown): string {
  if (typeof value === "string") return "s";
  if (typeof value === "number") return "n";
  if (typeof value === "boolean") return "b";
  if (value instanceof Date) return "d";
  return "s";
}

function isInRange(address: string, range: string): boolean {
  const decoded = XLSX.utils.decode_range(range);
  const cell = XLSX.utils.decode_cell(address);
  return (
    cell.r >= decoded.s.r &&
    cell.r <= decoded.e.r &&
    cell.c >= decoded.s.c &&
    cell.c <= decoded.e.c
  );
}

// === Core Logic ===

interface ReplacementStats {
  replacements: number;
  updates: number;
}

export async function generateFromTemplate(
  templatePath: string,
  spec: TemplateSpec,
  outputPath: string,
  options: { verbose?: boolean } = {}
): Promise<ReplacementStats> {
  const { verbose = false } = options;

  // Read template
  const templateData = await Deno.readFile(templatePath);
  const workbook = XLSX.read(templateData, {
    type: "array",
    cellFormula: true,
    cellStyles: true,
    cellNF: true,
  });

  if (verbose) {
    console.error(`Loaded template: ${basename(templatePath)}`);
    console.error(`Sheets: ${workbook.SheetNames.join(", ")}`);
  }

  const textReplacements = spec.textReplacements || [];
  const cellUpdates = spec.cellUpdates || [];

  if (verbose) {
    console.error(`Text replacements: ${textReplacements.length}`);
    console.error(`Cell updates: ${cellUpdates.length}`);
  }

  let totalReplacements = 0;
  let totalUpdates = 0;

  // Determine which sheets to process
  let sheetsToProcess = [...workbook.SheetNames];
  if (spec.includeSheets && spec.includeSheets.length > 0) {
    sheetsToProcess = sheetsToProcess.filter((s) =>
      spec.includeSheets!.includes(s)
    );
  }
  if (spec.excludeSheets && spec.excludeSheets.length > 0) {
    sheetsToProcess = sheetsToProcess.filter(
      (s) => !spec.excludeSheets!.includes(s)
    );
  }

  // Process each sheet
  for (const sheetName of sheetsToProcess) {
    const worksheet = workbook.Sheets[sheetName];
    if (!worksheet) continue;

    const ref = worksheet["!ref"];
    if (!ref) continue;

    if (verbose) {
      console.error(`Processing sheet: ${sheetName}`);
    }

    let sheetReplacements = 0;

    // Process text replacements
    for (const key of Object.keys(worksheet)) {
      if (key.startsWith("!")) continue;

      const cell = worksheet[key];
      if (!cell || cell.t !== "s" || typeof cell.v !== "string") continue;

      let text = cell.v;
      const originalText = text;

      for (const replacement of textReplacements) {
        // Check sheet filter
        if (replacement.sheets && !replacement.sheets.includes(sheetName)) {
          continue;
        }

        // Check range filter
        if (replacement.range && !isInRange(key, replacement.range)) {
          continue;
        }

        // Perform replacement
        if (text.includes(replacement.tag)) {
          const regex = new RegExp(escapeRegExp(replacement.tag), "g");
          const matches = text.match(regex);
          if (matches) {
            sheetReplacements += matches.length;
          }
          text = text.replace(regex, String(replacement.value));
        }
      }

      if (text !== originalText) {
        // If the entire cell was a placeholder and replacement is a number, use number type
        if (
          originalText.trim() === textReplacements.find((r) => originalText.includes(r.tag))?.tag &&
          typeof textReplacements.find((r) => originalText.includes(r.tag))?.value === "number"
        ) {
          cell.t = "n";
          cell.v = Number(text);
        } else {
          cell.v = text;
        }
        // Clear any cached value
        delete cell.w;
      }
    }

    if (verbose && sheetReplacements > 0) {
      console.error(`  ${sheetReplacements} replacements`);
    }
    totalReplacements += sheetReplacements;
  }

  // Process direct cell updates
  for (const update of cellUpdates) {
    const worksheet = workbook.Sheets[update.sheet];
    if (!worksheet) {
      if (verbose) {
        console.error(`Warning: Sheet "${update.sheet}" not found`);
      }
      continue;
    }

    if (update.formula) {
      worksheet[update.address] = {
        t: "n",
        f: update.formula,
      };
    } else if (update.value !== null && update.value !== undefined) {
      worksheet[update.address] = {
        t: getCellType(update.value),
        v: update.value,
      };
    } else {
      // Clear the cell
      delete worksheet[update.address];
    }

    totalUpdates++;
  }

  if (verbose) {
    console.error(`Total replacements: ${totalReplacements}`);
    console.error(`Total updates: ${totalUpdates}`);
  }

  // Write output file
  const xlsxData = XLSX.write(workbook, {
    type: "buffer",
    bookType: "xlsx",
    compression: true,
  });

  await Deno.writeFile(outputPath, new Uint8Array(xlsxData));

  if (verbose) {
    console.error(`Wrote ${outputPath}`);
  }

  return { replacements: totalReplacements, updates: totalUpdates };
}

// === Main CLI Handler ===
async function main(args: string[]): Promise<void> {
  const parsed = parseArgs(args, {
    boolean: ["help", "verbose"],
    alias: { help: "h", verbose: "v" },
    default: { verbose: false },
  }) as ParsedArgs;

  if (parsed.help) {
    printHelp();
    Deno.exit(0);
  }

  const positionalArgs = parsed._.map(String);

  if (positionalArgs.length < 3) {
    console.error(
      "Error: template.xlsx, spec.json, and output.xlsx are required\n"
    );
    printHelp();
    Deno.exit(1);
  }

  const templatePath = positionalArgs[0];
  const specPath = positionalArgs[1];
  const outputPath = positionalArgs[2];

  try {
    // Read specification
    const specText = await Deno.readTextFile(specPath);
    const spec = JSON.parse(specText) as TemplateSpec;

    await generateFromTemplate(templatePath, spec, outputPath, {
      verbose: parsed.verbose,
    });

    console.log(`Created: ${outputPath}`);
  } catch (error) {
    console.error(
      "Error:",
      error instanceof Error ? error.message : String(error)
    );
    Deno.exit(1);
  }
}

// === Entry Point ===
if (import.meta.main) {
  main(Deno.args);
}

#!/usr/bin/env -S deno run --allow-read --allow-write

/**
 * generate-scratch.ts - Create XLSX from scratch using JSON specification
 *
 * Creates Excel spreadsheets programmatically from a JSON specification
 * using the xlsx library. Supports sheets, cells, formulas, formatting, and charts.
 *
 * Usage:
 *   deno run --allow-read --allow-write scripts/generate-scratch.ts <spec.json> <output.xlsx>
 *
 * Options:
 *   -h, --help       Show help
 *   -v, --verbose    Enable verbose output
 *
 * Permissions:
 *   --allow-read: Read specification file
 *   --allow-write: Write output XLSX file
 */

import { parseArgs } from "jsr:@std/cli@1.0.9/parse-args";
// deno-lint-ignore no-explicit-any
import * as XLSX from "npm:xlsx@0.18.5" as any;

// === Types ===

export interface CellSpec {
  /** Cell address (e.g., "A1", "B2") */
  address: string;
  /** Cell value */
  value: string | number | boolean | null;
  /** Formula (without = sign) */
  formula?: string;
  /** Number format (e.g., "0.00", "#,##0", "yyyy-mm-dd") */
  format?: string;
  /** Cell type override */
  type?: "string" | "number" | "boolean" | "date";
}

export interface RowSpec {
  /** Row number (1-indexed) */
  row: number;
  /** Cell values starting from column A */
  values: (string | number | boolean | null)[];
  /** Optional formulas for cells (same index as values) */
  formulas?: (string | undefined)[];
}

export interface ColumnSpec {
  /** Column letter (e.g., "A", "B", "AA") */
  col: string;
  /** Column width in characters */
  width?: number;
  /** Column hidden */
  hidden?: boolean;
}

export interface MergeSpec {
  /** Start cell address */
  start: string;
  /** End cell address */
  end: string;
}

export interface SheetSpec {
  /** Sheet name */
  name: string;
  /** Individual cell specifications */
  cells?: CellSpec[];
  /** Row-based data (alternative to cells) */
  rows?: RowSpec[];
  /** 2D array data starting at A1 (alternative to cells/rows) */
  data?: (string | number | boolean | null)[][];
  /** Column specifications */
  columns?: ColumnSpec[];
  /** Merged cell ranges */
  merges?: MergeSpec[];
  /** Freeze panes at this cell (e.g., "A2" freezes first row) */
  freezePane?: string;
  /** Auto-filter range (e.g., "A1:D10") */
  autoFilter?: string;
  /** Tab color (hex, no #) */
  tabColor?: string;
}

export interface SpreadsheetSpec {
  /** Workbook title (metadata) */
  title?: string;
  /** Author (metadata) */
  author?: string;
  /** Company (metadata) */
  company?: string;
  /** Sheets to create */
  sheets: SheetSpec[];
}

interface ParsedArgs {
  help: boolean;
  verbose: boolean;
  _: (string | number)[];
}

// === Constants ===
const VERSION = "1.0.0";
const SCRIPT_NAME = "generate-scratch";

// === Help Text ===
function printHelp(): void {
  console.log(`
${SCRIPT_NAME} v${VERSION} - Create XLSX from scratch using JSON specification

Usage:
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts <spec.json> <output.xlsx>

Arguments:
  <spec.json>      Path to JSON specification file
  <output.xlsx>    Path for output Excel file

Options:
  -h, --help       Show this help message
  -v, --verbose    Enable verbose output

Specification Format:
  {
    "title": "Sales Report",
    "sheets": [
      {
        "name": "Data",
        "data": [
          ["Product", "Price", "Quantity", "Total"],
          ["Widget A", 10.99, 100, null],
          ["Widget B", 24.99, 50, null]
        ],
        "cells": [
          { "address": "D2", "formula": "B2*C2" },
          { "address": "D3", "formula": "B3*C3" }
        ],
        "columns": [
          { "col": "A", "width": 15 },
          { "col": "B", "width": 10 }
        ]
      }
    ]
  }

Examples:
  # Generate spreadsheet
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts spec.json output.xlsx

  # With verbose output
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts spec.json output.xlsx -v
`);
}

// === Utility Functions ===

function colNameToIndex(colName: string): number {
  let index = 0;
  for (let i = 0; i < colName.length; i++) {
    index = index * 26 + (colName.charCodeAt(i) - 64);
  }
  return index - 1;
}

function getCellType(value: unknown, specType?: string): string {
  if (specType) {
    switch (specType) {
      case "string": return "s";
      case "number": return "n";
      case "boolean": return "b";
      case "date": return "d";
    }
  }
  if (typeof value === "string") return "s";
  if (typeof value === "number") return "n";
  if (typeof value === "boolean") return "b";
  if (value instanceof Date) return "d";
  return "s";
}

// === Core Logic ===

export async function generateFromSpec(
  spec: SpreadsheetSpec,
  outputPath: string,
  options: { verbose?: boolean } = {}
): Promise<void> {
  const { verbose = false } = options;

  // Create workbook
  const workbook = XLSX.utils.book_new();

  // Set workbook properties
  if (spec.title || spec.author || spec.company) {
    workbook.Props = {
      Title: spec.title,
      Author: spec.author,
      Company: spec.company,
    };
  }

  if (verbose) {
    console.error(`Creating workbook with ${spec.sheets.length} sheet(s)`);
  }

  // Process each sheet
  for (const sheetSpec of spec.sheets) {
    if (verbose) {
      console.error(`Processing sheet: ${sheetSpec.name}`);
    }

    // deno-lint-ignore no-explicit-any
    let worksheet: any;

    // Create worksheet from data array
    if (sheetSpec.data && sheetSpec.data.length > 0) {
      worksheet = XLSX.utils.aoa_to_sheet(sheetSpec.data);
      if (verbose) {
        console.error(`  Created from ${sheetSpec.data.length} rows of data`);
      }
    } else {
      worksheet = {};
    }

    // Process row specifications
    if (sheetSpec.rows) {
      for (const rowSpec of sheetSpec.rows) {
        for (let colIdx = 0; colIdx < rowSpec.values.length; colIdx++) {
          const value = rowSpec.values[colIdx];
          const formula = rowSpec.formulas?.[colIdx];
          const address = XLSX.utils.encode_cell({ r: rowSpec.row - 1, c: colIdx });

          if (formula) {
            worksheet[address] = { t: "n", f: formula };
          } else if (value !== null && value !== undefined) {
            worksheet[address] = {
              t: getCellType(value),
              v: value,
            };
          }
        }
      }
      if (verbose) {
        console.error(`  Processed ${sheetSpec.rows.length} row specs`);
      }
    }

    // Process individual cell specifications
    if (sheetSpec.cells) {
      for (const cellSpec of sheetSpec.cells) {
        // deno-lint-ignore no-explicit-any
        const cell: any = {};

        if (cellSpec.formula) {
          cell.t = "n";
          cell.f = cellSpec.formula;
        } else if (cellSpec.value !== null && cellSpec.value !== undefined) {
          cell.t = getCellType(cellSpec.value, cellSpec.type);
          cell.v = cellSpec.value;
        }

        if (cellSpec.format) {
          cell.z = cellSpec.format;
        }

        worksheet[cellSpec.address] = cell;
      }
      if (verbose) {
        console.error(`  Applied ${sheetSpec.cells.length} cell specs`);
      }
    }

    // Calculate the used range
    const range = { s: { r: 0, c: 0 }, e: { r: 0, c: 0 } };
    let hasData = false;

    for (const key of Object.keys(worksheet)) {
      if (key.startsWith("!")) continue;
      hasData = true;
      const cell = XLSX.utils.decode_cell(key);
      range.e.r = Math.max(range.e.r, cell.r);
      range.e.c = Math.max(range.e.c, cell.c);
    }

    if (hasData) {
      worksheet["!ref"] = XLSX.utils.encode_range(range);
    }

    // Apply column specifications
    if (sheetSpec.columns) {
      worksheet["!cols"] = [];
      for (const colSpec of sheetSpec.columns) {
        const colIdx = colNameToIndex(colSpec.col);
        while (worksheet["!cols"].length <= colIdx) {
          worksheet["!cols"].push({});
        }
        worksheet["!cols"][colIdx] = {
          wch: colSpec.width,
          hidden: colSpec.hidden,
        };
      }
      if (verbose) {
        console.error(`  Applied ${sheetSpec.columns.length} column specs`);
      }
    }

    // Apply merged cells
    if (sheetSpec.merges && sheetSpec.merges.length > 0) {
      worksheet["!merges"] = sheetSpec.merges.map((merge) => ({
        s: XLSX.utils.decode_cell(merge.start),
        e: XLSX.utils.decode_cell(merge.end),
      }));
      if (verbose) {
        console.error(`  Applied ${sheetSpec.merges.length} merge specs`);
      }
    }

    // Apply freeze pane
    if (sheetSpec.freezePane) {
      const freezeCell = XLSX.utils.decode_cell(sheetSpec.freezePane);
      worksheet["!freeze"] = {
        xSplit: freezeCell.c,
        ySplit: freezeCell.r,
        topLeftCell: sheetSpec.freezePane,
      };
    }

    // Apply auto-filter
    if (sheetSpec.autoFilter) {
      worksheet["!autofilter"] = { ref: sheetSpec.autoFilter };
    }

    // Add sheet to workbook
    XLSX.utils.book_append_sheet(workbook, worksheet, sheetSpec.name);
  }

  // Write output file
  const xlsxData = XLSX.write(workbook, {
    type: "buffer",
    bookType: "xlsx",
    compression: true,
  });

  await Deno.writeFile(outputPath, new Uint8Array(xlsxData));

  if (verbose) {
    console.error(`Wrote ${outputPath}`);
  }
}

// === Main CLI Handler ===
async function main(args: string[]): Promise<void> {
  const parsed = parseArgs(args, {
    boolean: ["help", "verbose"],
    alias: { help: "h", verbose: "v" },
    default: { verbose: false },
  }) as ParsedArgs;

  if (parsed.help) {
    printHelp();
    Deno.exit(0);
  }

  const positionalArgs = parsed._.map(String);

  if (positionalArgs.length < 2) {
    console.error("Error: Both spec.json and output.xlsx are required\n");
    printHelp();
    Deno.exit(1);
  }

  const specPath = positionalArgs[0];
  const outputPath = positionalArgs[1];

  try {
    const specText = await Deno.readTextFile(specPath);
    const spec = JSON.parse(specText) as SpreadsheetSpec;

    await generateFromSpec(spec, outputPath, {
      verbose: parsed.verbose,
    });

    console.log(`Created: ${outputPath}`);
  } catch (error) {
    console.error(
      "Error:",
      error instanceof Error ? error.message : String(error)
    );
    Deno.exit(1);
  }
}

// === Entry Point ===
if (import.meta.main) {
  main(Deno.args);
}