Workbook and Worksheet Management

/**
 * Create a new empty workbook
 * @returns Empty WorkBook object ready for sheet addition
 */
function book_new(): WorkBook;

/**
 * Add worksheet to existing workbook
 * @param workbook - Target workbook
 * @param worksheet - Worksheet to add
 * @param name - Optional sheet name (auto-generated if not provided)
 * @param roll - If true, replace existing sheet with same name
 */
function book_append_sheet(
  workbook: WorkBook, 
  worksheet: WorkSheet, 
  name?: string, 
  roll?: boolean
): void;

/**
 * Set worksheet visibility in workbook
 * @param workbook - Target workbook
 * @param sheet - Sheet index or name
 * @param visibility - Visibility level (0=visible, 1=hidden, 2=very hidden)
 */
function book_set_sheet_visibility(
  workbook: WorkBook, 
  sheet: number | string, 
  visibility: number
): void;

import { utils, writeFile } from "xlsx";

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

// Create multiple worksheets
const salesData = [
  ["Month", "Revenue", "Expenses"],
  ["Jan", 10000, 7500],
  ["Feb", 12000, 8000],
  ["Mar", 11500, 7800]
];

const employeeData = [
  ["Name", "Department", "Salary"],
  ["Alice", "Sales", 50000],
  ["Bob", "Engineering", 75000],
  ["Charlie", "Marketing", 55000]
];

// Create worksheets
const salesSheet = utils.aoa_to_sheet(salesData);
const employeeSheet = utils.aoa_to_sheet(employeeData);
const summarySheet = utils.aoa_to_sheet([
  ["Company Summary"],
  ["Total Employees", 3],
  ["Total Revenue", 33500]
]);

// Add sheets to workbook with custom names
utils.book_append_sheet(workbook, salesSheet, "Sales Data");
utils.book_append_sheet(workbook, employeeSheet, "Employees");
utils.book_append_sheet(workbook, summarySheet, "Summary");

// Set sheet visibility
utils.book_set_sheet_visibility(workbook, "Summary", 0);     // Visible
utils.book_set_sheet_visibility(workbook, "Employees", 1);   // Hidden
utils.book_set_sheet_visibility(workbook, 0, 0);            // Sales Data visible (by index)

// Replace existing sheet
const updatedSalesSheet = utils.aoa_to_sheet([
  ["Month", "Revenue", "Expenses", "Profit"],
  ["Jan", 10000, 7500, 2500],
  ["Feb", 12000, 8000, 4000]
]);
utils.book_append_sheet(workbook, updatedSalesSheet, "Sales Data", true); // roll=true replaces

// Save workbook
writeFile(workbook, "company-data.xlsx");

// Access workbook structure
console.log(workbook.SheetNames);  // ["Sales Data", "Employees", "Summary"]
console.log(Object.keys(workbook.Sheets));  // Sheet objects

const consts: {
  /** Visible sheet (default) */
  SHEET_VISIBLE: 0;
  /** Hidden sheet (can be unhidden by user) */
  SHEET_HIDDEN: 1;
  /** Very hidden sheet (requires programmatic access to unhide) */
  SHEET_VERYHIDDEN: 2;
};

import { utils } from "xlsx";

const workbook = utils.book_new();
const worksheet = utils.aoa_to_sheet([["Secret Data"], ["Confidential"]]);
const publicSheet = utils.aoa_to_sheet([["Public Data"], ["Everyone can see"]]);

utils.book_append_sheet(workbook, publicSheet, "Public");
utils.book_append_sheet(workbook, worksheet, "Secret");

// Use constants for clarity
utils.book_set_sheet_visibility(workbook, "Public", utils.consts.SHEET_VISIBLE);
utils.book_set_sheet_visibility(workbook, "Secret", utils.consts.SHEET_VERYHIDDEN);

// Hidden sheet won't appear in normal Excel interface
// Very hidden sheet requires VBA or programmatic access to unhide

/**
 * Add JSON data to existing worksheet
 * @param ws - Target worksheet
 * @param data - Array of objects to add
 * @param opts - Addition options
 * @returns Modified worksheet
 */
function sheet_add_json<T>(ws: WorkSheet, data: T[], opts?: SheetJSONOpts): WorkSheet;

/**
 * Add array data to existing worksheet
 * @param ws - Target worksheet
 * @param data - 2D array to add
 * @param opts - Addition options
 * @returns Modified worksheet
 */
function sheet_add_aoa<T>(ws: WorkSheet, data: T[][], opts?: SheetAOAOpts): WorkSheet;

/**
 * Add DOM table data to existing worksheet (browser only)
 * @param ws - Target worksheet
 * @param data - HTML table element
 * @param opts - Addition options
 * @returns Modified worksheet
 */
function sheet_add_dom(ws: WorkSheet, data: any, opts?: Table2SheetOpts): WorkSheet;

interface SheetJSONOpts extends JSON2SheetOpts, OriginOption {}

interface SheetAOAOpts extends AOA2SheetOpts, OriginOption {}

import { utils } from "xlsx";

// Start with existing worksheet
const initialData = [
  ["Product", "Q1 Sales"],
  ["Widget", 1000],
  ["Gadget", 1500]
];
const worksheet = utils.aoa_to_sheet(initialData);

// Add more data as JSON (appending columns)
const additionalData = [
  { "Q2 Sales": 1200, "Q3 Sales": 1400 },
  { "Q2 Sales": 1600, "Q3 Sales": 1800 }
];

utils.sheet_add_json(worksheet, additionalData, {
  origin: "C1",      // Start at column C
  skipHeader: false  // Include headers
});

// Add more rows as array data
const newProducts = [
  ["Tool", 800, 900, 1000],
  ["Accessory", 400, 450, 500]
];

utils.sheet_add_aoa(worksheet, newProducts, {
  origin: "A4"  // Start at row 4
});

// Result worksheet now contains:
// A1: Product    B1: Q1 Sales  C1: Q2 Sales  D1: Q3 Sales
// A2: Widget     B2: 1000      C2: 1200      D2: 1400
// A3: Gadget     B3: 1500      C3: 1600      D3: 1800
// A4: Tool       B4: 800       C4: 900       D4: 1000
// A5: Accessory  B5: 400       C5: 450       D5: 500

// Add data at specific locations with gaps
const summaryData = [
  { "Summary": "Total", "Amount": 8150 }
];

utils.sheet_add_json(worksheet, summaryData, {
  origin: "A7"  // Skip row 6, add summary at row 7
});

// Create complex layouts by adding data at different origins
const headerData = [["SALES REPORT 2023"]];
utils.sheet_add_aoa(worksheet, headerData, {
  origin: "A1"  // Insert title above existing data (overwrites)
});

// Shift existing data down and insert title
const reportWorksheet = utils.aoa_to_sheet([["SALES REPORT 2023"], []]);
utils.sheet_add_aoa(reportWorksheet, initialData, { origin: "A3" });
utils.sheet_add_json(reportWorksheet, additionalData, { origin: "C3", skipHeader: false });

/**
 * Set array formula on a range of cells
 * @param ws - Target worksheet
 * @param range - Cell range for the array formula
 * @param formula - Formula expression
 * @param dynamic - Whether this is a dynamic array formula
 * @returns Modified worksheet
 */
function sheet_set_array_formula(
  ws: WorkSheet, 
  range: Range | string, 
  formula: string, 
  dynamic?: boolean
): WorkSheet;

import { utils } from "xlsx";

// Create worksheet with data
const data = [
  ["Values", "Multiplier", "Results"],
  [10, 2, ""],
  [20, 3, ""],
  [30, 4, ""],
  [40, 5, ""]
];
const worksheet = utils.aoa_to_sheet(data);

// Set array formula to calculate results
utils.sheet_set_array_formula(
  worksheet,
  "C2:C5",                    // Range for results
  "A2:A5*B2:B5",             // Formula: multiply values by multipliers
  false                       // Traditional array formula
);

// Dynamic array formula (Excel 365)
const dynamicSheet = utils.aoa_to_sheet([
  ["Source Data"],
  [1], [2], [3], [4], [5]
]);

utils.sheet_set_array_formula(
  dynamicSheet,
  "C1",                       // Single cell (dynamic arrays expand automatically)
  "A2:A6*2",                 // Formula: double all values
  true                        // Dynamic array formula
);

// Complex array formulas
const salesData = [
  ["Product", "Price", "Quantity", "Revenue"],
  ["Widget", 10, 100, ""],
  ["Gadget", 15, 80, ""],
  ["Tool", 25, 60, ""]
];
const salesSheet = utils.aoa_to_sheet(salesData);

// Array formula for revenue calculation
utils.sheet_set_array_formula(
  salesSheet,
  "D2:D4",
  "B2:B4*C2:C4"  // Price * Quantity for each row
);

// Conditional array formula
utils.sheet_set_array_formula(
  salesSheet,
  "E2:E4",
  'IF(D2:D4>1000,"High","Low")'  // Mark high/low revenue
);

const workbook = utils.book_new();
utils.book_append_sheet(workbook, worksheet, "Array Formulas");
utils.book_append_sheet(workbook, salesSheet, "Sales Calculations");

import { utils, writeFile } from "xlsx";

// Create workbook with metadata
const workbook = utils.book_new();

// Set workbook properties
workbook.Props = {
  Title: "Quarterly Sales Report",
  Subject: "Q2 2023 Sales Analysis",
  Author: "Sales Department",
  Manager: "Sales Manager",
  Company: "Example Corp",
  Category: "Sales Reports",
  Keywords: "sales, quarterly, revenue",
  Comments: "Generated from CRM data",
  CreatedDate: new Date()
};

// Create summary worksheet with calculations
const summaryData = [
  ["Quarterly Sales Summary"],
  [],
  ["Metric", "Q1", "Q2", "Q3", "Q4", "Total"],
  ["Revenue", 100000, 120000, 110000, 130000, "=SUM(B4:E4)"],
  ["Expenses", 75000, 85000, 80000, 90000, "=SUM(B5:E5)"],
  ["Profit", "=B4-B5", "=C4-C5", "=D4-D5", "=E4-E5", "=F4-F5"]
];

const summarySheet = utils.aoa_to_sheet(summaryData);
utils.book_append_sheet(workbook, summarySheet, "Summary");

// Create detailed data sheets
const q1Data = [
  ["Q1 Sales Detail"],
  ["Date", "Product", "Amount"],
  ["2023-01-15", "Widget", 25000],
  ["2023-02-10", "Gadget", 35000],
  ["2023-03-05", "Tool", 40000]
];

const q2Data = [
  ["Q2 Sales Detail"], 
  ["Date", "Product", "Amount"],
  ["2023-04-12", "Widget", 30000],
  ["2023-05-18", "Gadget", 40000],
  ["2023-06-22", "Tool", 50000]
];

utils.book_append_sheet(workbook, utils.aoa_to_sheet(q1Data), "Q1 Detail");
utils.book_append_sheet(workbook, utils.aoa_to_sheet(q2Data), "Q2 Detail");

// Set sheet visibility - hide detail sheets by default
utils.book_set_sheet_visibility(workbook, "Q1 Detail", utils.consts.SHEET_HIDDEN);
utils.book_set_sheet_visibility(workbook, "Q2 Detail", utils.consts.SHEET_HIDDEN);

// Add custom properties
workbook.Custprops = {
  "Report Period": "Q2 2023",
  "Data Source": "CRM System",
  "Generated By": "Automated Report System",
  "Last Updated": new Date().toISOString()
};

// Save with metadata
writeFile(workbook, "quarterly-report.xlsx");

console.log("Created workbook with:", workbook.SheetNames.length, "sheets");
console.log("Sheet names:", workbook.SheetNames);
console.log("Properties:", workbook.Props?.Title);

import { utils } from "xlsx";

function createMultiSheetReport(dataByCategory: Record<string, any[]>) {
  const workbook = utils.book_new();
  const summaryData: any[][] = [["Category", "Count", "Total Value"]];

  // Create a sheet for each category
  Object.entries(dataByCategory).forEach(([category, data]) => {
    // Create individual category sheet
    const worksheet = utils.json_to_sheet(data);
    utils.book_append_sheet(workbook, worksheet, category);

    // Collect summary data
    const count = data.length;
    const total = data.reduce((sum, item) => sum + (item.value || 0), 0);
    summaryData.push([category, count, total]);
  });

  // Create summary sheet
  const summarySheet = utils.aoa_to_sheet(summaryData);
  utils.book_append_sheet(workbook, summarySheet, "Summary");

  // Move summary to first position by recreating workbook
  const reorderedWorkbook = utils.book_new();
  utils.book_append_sheet(reorderedWorkbook, summarySheet, "Summary");
  
  Object.entries(dataByCategory).forEach(([category]) => {
    utils.book_append_sheet(reorderedWorkbook, workbook.Sheets[category], category);
  });

  return reorderedWorkbook;
}

// Usage
const salesByRegion = {
  "North": [
    { product: "Widget", sales: 1000, value: 10000 },
    { product: "Gadget", sales: 800, value: 12000 }
  ],
  "South": [
    { product: "Widget", sales: 1200, value: 12000 },
    { product: "Tool", sales: 600, value: 15000 }
  ],
  "East": [
    { product: "Gadget", sales: 900, value: 13500 },
    { product: "Tool", sales: 700, value: 17500 }
  ]
};

const reportWorkbook = createMultiSheetReport(salesByRegion);
console.log("Created report with sheets:", reportWorkbook.SheetNames);
// Output: ["Summary", "North", "South", "East"]