Data Import and Export

/**
 * Convert array of objects to worksheet
 * @param data - Array of objects to convert
 * @param opts - Conversion options
 * @returns WorkSheet object
 */
function json_to_sheet<T>(data: T[], opts?: JSON2SheetOpts): WorkSheet;

interface JSON2SheetOpts extends CommonOptions, DateNFOption {
  /** Use specified column order */
  header?: string[];
  /** Skip header row in generated sheet */
  skipHeader?: boolean;
}

import { utils } from "xlsx";

// Basic JSON to worksheet
const data = [
  { Name: "Alice", Age: 25, City: "New York" },
  { Name: "Bob", Age: 30, City: "Los Angeles" },
  { Name: "Charlie", Age: 35, City: "Chicago" }
];
const worksheet = utils.json_to_sheet(data);

// With custom column order
const worksheet2 = utils.json_to_sheet(data, {
  header: ["City", "Name", "Age"]  // Reorder columns
});

// Skip header row and start at specific cell
const worksheet3 = utils.json_to_sheet(data, {
  skipHeader: true,
  origin: "B2"  // Start data at cell B2
});

// With custom date formatting
const salesData = [
  { Date: new Date("2023-01-15"), Product: "Widget", Sales: 100 },
  { Date: new Date("2023-01-16"), Product: "Gadget", Sales: 150 }
];
const worksheet4 = utils.json_to_sheet(salesData, {
  dateNF: "mm/dd/yyyy"  // Custom date format
});

/**
 * Convert 2D array to worksheet
 * @param data - 2D array where each sub-array represents a row
 * @param opts - Conversion options
 * @returns WorkSheet object
 */
function aoa_to_sheet<T>(data: T[][], opts?: AOA2SheetOpts): WorkSheet;

interface AOA2SheetOpts extends CommonOptions, DateNFOption {
  /** Create cell objects for stub cells */
  sheetStubs?: boolean;
}

import { utils } from "xlsx";

// Basic 2D array to worksheet
const arrayData = [
  ["Name", "Age", "City"],           // Header row
  ["Alice", 25, "New York"],         // Data rows
  ["Bob", 30, "Los Angeles"],
  ["Charlie", 35, "Chicago"]
];
const worksheet = utils.aoa_to_sheet(arrayData);

// Mixed data types
const mixedData = [
  ["Product", "Price", "In Stock", "Launch Date"],
  ["Laptop", 999.99, true, new Date("2023-01-01")],
  ["Mouse", 29.99, false, new Date("2023-02-15")],
  ["Keyboard", 79.99, true, new Date("2023-03-01")]
];
const worksheet2 = utils.aoa_to_sheet(mixedData);

// Start at specific location
const worksheet3 = utils.aoa_to_sheet(arrayData, {
  origin: "C3"  // Start array at cell C3
});

// Create sparse worksheets with gaps
const sparseData = [
  ["Q1 Results"],
  [],  // Empty row
  ["Revenue", 100000],
  ["Expenses", 75000],
  [],  // Empty row  
  ["Profit", 25000]
];
const worksheet4 = utils.aoa_to_sheet(sparseData);

/**
 * Convert DOM table element to worksheet (browser only)
 * @param data - HTML table element or table selector
 * @param opts - Conversion options
 * @returns WorkSheet object
 */
function table_to_sheet(data: any, opts?: Table2SheetOpts): WorkSheet;

/**
 * Convert DOM table element to workbook (browser only)
 * @param data - HTML table element or table selector
 * @param opts - Conversion options
 * @returns WorkBook object with single sheet
 */
function table_to_book(data: any, opts?: Table2SheetOpts): WorkBook;

interface Table2SheetOpts extends CommonOptions, DateNFOption, OriginOption, SheetOption {
  /** If true, plaintext parsing will not parse values */
  raw?: boolean;
  /** If >0, read the first sheetRows rows */
  sheetRows?: number;
  /** If true, hidden rows and cells will not be parsed */
  display?: boolean;
}

// Browser environment only
import { utils } from "xlsx";

// Convert table by ID
const tableElement = document.getElementById("data-table");
const worksheet = utils.table_to_sheet(tableElement);

// Convert table with options
const worksheet2 = utils.table_to_sheet(tableElement, {
  raw: true,     // Get raw cell values instead of displayed text
  origin: "A1"   // Start at specific cell
});

// Convert directly to workbook
const workbook = utils.table_to_book(tableElement);

// Process multiple tables
const tables = document.querySelectorAll(".data-table");
const workbook2 = utils.book_new();
tables.forEach((table, index) => {
  const worksheet = utils.table_to_sheet(table);
  utils.book_append_sheet(workbook2, worksheet, `Table${index + 1}`);
});

/**
 * Convert worksheet to array of objects
 * @param worksheet - WorkSheet to convert
 * @param opts - Export options
 * @returns Array of objects representing worksheet rows
 */
function sheet_to_json<T>(worksheet: WorkSheet, opts?: Sheet2JSONOpts): T[];

interface Sheet2JSONOpts extends DateNFOption {
  /** Output format */
  header?: "A" | number | string[];
  /** Override worksheet range */
  range?: any;
  /** Include or omit blank lines in the output */
  blankrows?: boolean;
  /** Default value for null/undefined values */
  defval?: any;
  /** if true, return raw data; if false, return formatted text */
  raw?: boolean;
  /** if true, skip hidden rows and columns */
  skipHidden?: boolean;
  /** if true, return raw numbers; if false, return formatted numbers */
  rawNumbers?: boolean;
}

import { utils, readFile } from "xlsx";

const workbook = readFile("data.xlsx");
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

// Basic JSON export
const jsonData = utils.sheet_to_json(worksheet);
console.log(jsonData);
// [{ Name: "Alice", Age: 25 }, { Name: "Bob", Age: 30 }]

// Use first row as headers
const jsonData2 = utils.sheet_to_json(worksheet, { header: 1 });

// Use column letters as keys
const jsonData3 = utils.sheet_to_json(worksheet, { header: "A" });
// [{ A: "Alice", B: 25 }, { A: "Bob", B: 30 }]

// Custom headers
const jsonData4 = utils.sheet_to_json(worksheet, {
  header: ["FullName", "Years", "Location"]
});

// Include empty rows and cells
const jsonData5 = utils.sheet_to_json(worksheet, {
  blankrows: true,  // Include empty rows
  defval: ""        // Default value for empty cells
});

// Get raw values without formatting
const jsonData6 = utils.sheet_to_json(worksheet, {
  raw: true,        // Get raw numeric values
  dateNF: "mm/dd/yyyy"  // Date format for display
});

// Specific range only
const jsonData7 = utils.sheet_to_json(worksheet, {
  range: "A1:C10"   // Only process first 10 rows, columns A-C
});

/**
 * Convert worksheet to CSV string
 * @param worksheet - WorkSheet to convert
 * @param options - CSV export options
 * @returns CSV string representation
 */
function sheet_to_csv(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;

interface Sheet2CSVOpts extends DateNFOption {
  /** Field Separator ("delimiter") */
  FS?: string;
  /** Record Separator ("row separator") */
  RS?: string;
  /** Remove trailing field separators in each record */
  strip?: boolean;
  /** Include blank lines in the CSV output */
  blankrows?: boolean;
  /** Skip hidden rows and columns in the CSV output */
  skipHidden?: boolean;
  /** Force quotes around fields */
  forceQuotes?: boolean;
  /** if true, return raw numbers; if false, return formatted numbers */
  rawNumbers?: boolean;
}

import { utils, readFile } from "xlsx";

const workbook = readFile("data.xlsx");
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

// Basic CSV export
const csvString = utils.sheet_to_csv(worksheet);
console.log(csvString);
// "Name,Age,City\nAlice,25,New York\nBob,30,Los Angeles"

// Custom separators for European format
const csvEuropean = utils.sheet_to_csv(worksheet, {
  FS: ";",      // Semicolon field separator
  RS: "\r\n"    // Windows line endings
});

// Tab-separated values
const tsvString = utils.sheet_to_csv(worksheet, {
  FS: "\t"      // Tab field separator
});

// Custom formatting
const customCsv = utils.sheet_to_csv(worksheet, {
  FS: "|",              // Pipe separator
  dateNF: "yyyy-mm-dd", // ISO date format
  strip: true           // Remove extra whitespace
});

// Skip empty rows
const compactCsv = utils.sheet_to_csv(worksheet, {
  skipEmpty: true       // Omit empty rows
});

/**
 * Convert worksheet to HTML table
 * @param worksheet - WorkSheet to convert
 * @param options - HTML export options
 * @returns HTML string representation
 */
function sheet_to_html(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;

interface Sheet2HTMLOpts {
  /** TABLE element id attribute */
  id?: string;
  /** Add contenteditable to every cell */
  editable?: boolean;
  /** Header HTML */
  header?: string;
  /** Footer HTML */
  footer?: string;
}

import { utils, readFile } from "xlsx";

const workbook = readFile("data.xlsx");
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

// Basic HTML table
const htmlString = utils.sheet_to_html(worksheet);
console.log(htmlString);
// "<table><tr><td>Name</td><td>Age</td></tr>..."

// Custom table with ID and styling
const styledHtml = utils.sheet_to_html(worksheet, {
  id: "data-table",     // Add table ID
  editable: true        // Make cells editable
});

// Complete HTML document
const fullHtml = `
<!DOCTYPE html>
<html>
<head>
    <title>Data Report</title>
    <style>
        table { border-collapse: collapse; }
        td, th { border: 1px solid #ddd; padding: 8px; }
    </style>
</head>
<body>
    <h1>Report Data</h1>
    ${utils.sheet_to_html(worksheet)}
</body>
</html>
`;

/**
 * Convert worksheet to tab-delimited text
 * @param worksheet - WorkSheet to convert
 * @param options - Export options
 * @returns Tab-delimited string
 */
function sheet_to_txt(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;

/**
 * Convert worksheet to array of formulae strings
 * @param worksheet - WorkSheet to convert
 * @returns Array of formula strings
 */
function sheet_to_formulae(worksheet: WorkSheet): string[];

/**
 * Convert worksheet to DIF (Data Interchange Format)
 * @param worksheet - WorkSheet to convert
 * @param options - Export options
 * @returns DIF format string
 */
function sheet_to_dif(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;

/**
 * Convert worksheet to SYLK (Symbolic Link) format
 * @param worksheet - WorkSheet to convert
 * @param options - Export options
 * @returns SYLK format string
 */
function sheet_to_slk(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;

/**
 * Convert worksheet to ETH format
 * @param worksheet - WorkSheet to convert
 * @param options - Export options
 * @returns ETH format string
 */
function sheet_to_eth(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;

import { utils, readFile } from "xlsx";

const workbook = readFile("data.xlsx");
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

// Tab-delimited text (TSV)
const tsvData = utils.sheet_to_txt(worksheet);

// Extract all formulae from worksheet
const formulae = utils.sheet_to_formulae(worksheet);
console.log(formulae);
// ["A1=SUM(B1:B10)", "C1=AVERAGE(D1:D10)", ...]

// Legacy format exports
const difData = utils.sheet_to_dif(worksheet);      // DIF format
const sylkData = utils.sheet_to_slk(worksheet);     // SYLK format  
const ethData = utils.sheet_to_eth(worksheet);      // ETH format

// Use for system integration
const dataForLegacySystem = utils.sheet_to_dif(worksheet);
// Send to legacy system that requires DIF format

import { utils, readFile, writeFile } from "xlsx";

// Read Excel file → Transform to JSON → Modify → Export to CSV
const workbook = readFile("input.xlsx");
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

// Convert to JSON for processing
const jsonData = utils.sheet_to_json(worksheet);

// Transform data (add calculated fields, filter, etc.)
const transformedData = jsonData
  .filter((row: any) => row.Age >= 21)  
  .map((row: any) => ({
    ...row,
    Category: row.Age < 30 ? "Young" : "Experienced",
    FullName: `${row.FirstName} ${row.LastName}`
  }));

// Convert back to worksheet and export
const newWorksheet = utils.json_to_sheet(transformedData);
const newWorkbook = utils.book_new();
utils.book_append_sheet(newWorkbook, newWorksheet, "Processed");

// Export to multiple formats
writeFile(newWorkbook, "output.xlsx");                    // Excel
const csvOutput = utils.sheet_to_csv(newWorksheet);       // CSV string
const htmlOutput = utils.sheet_to_html(newWorksheet);     // HTML table