`src/export/xlsx.js`

242 lines
'use strict';
/**
 * Pure-Node minimal XLSX writer — Open XML SpreadsheetML.
 *
 * An .xlsx file is a ZIP (PKWARE) containing Open XML parts:
 *   [Content_Types].xml
 *   _rels/.rels
 *   xl/workbook.xml
 *   xl/_rels/workbook.xml.rels
 *   xl/worksheets/sheet1.xml
 *   xl/sharedStrings.xml
 *
 * This implementation:
 *   - Produces one sheet per call.
 *   - Stores strings in the shared string table (type="s") for small file size.
 *   - Stores numbers as inline numbers (type="n").
 *   - Does NOT support formulas, styles, or multi-sheet workbooks (not needed here).
 *   - Reuses zip.js (also pure Node, STORE method).
 *
 * Usage:
 *   const buf = toXlsxBuffer(rows, { columns: ['name','score'] });
 *   fs.writeFileSync('out.xlsx', buf);
 *
 * @module src/export/xlsx
 */

const { ZipWriter } = require('./zip');

/**
 * Escape XML special characters.
 * @param {string} str
 * @returns {string}
 */
function xmlEscape(str) {
  return String(str)
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&apos;')
    // Strip control characters (not valid XML 1.0 except tab/CR/LF)
    .replace(/[\x00-\x08\x0B\x0C\x0E-\x1F]/g, '');
}

/**
 * Convert a 1-based column index to Excel letter notation (1=A, 26=Z, 27=AA).
 * @param {number} n
 * @returns {string}
 */
function colLetter(n) {
  let s = '';
  while (n > 0) {
    const rem = (n - 1) % 26;
    s = String.fromCharCode(65 + rem) + s;
    n = Math.floor((n - 1) / 26);
  }
  return s;
}

/**
 * Build the shared strings table XML and index map.
 * Returns { xml, index } where index is a Map<string, number>.
 *
 * @param {Object[]} rows
 * @param {string[]} columns
 * @returns {{ xml: string, index: Map<string,number>, count: number }}
 */
function buildSharedStrings(rows, columns) {
  const index = new Map();
  const strings = [];

  function intern(val) {
    if (val === null || val === undefined) return;
    const s = String(val);
    if (typeof val === 'number' && isFinite(val)) return; // kept inline
    if (!index.has(s)) {
      index.set(s, strings.length);
      strings.push(s);
    }
  }

  // Intern column headers
  for (const col of columns) intern(col);

  // Intern all string cell values
  for (const row of rows) {
    for (const col of columns) {
      const v = row[col];
      if (typeof v !== 'number' || !isFinite(v)) {
        intern(v === null || v === undefined ? '' : String(v));
      }
    }
  }

  const total = strings.length;
  const items = strings.map((s) => `<si><t xml:space="preserve">${xmlEscape(s)}</t></si>`).join('');
  const xml =
    `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n` +
    `<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="${total}" uniqueCount="${total}">` +
    items +
    `</sst>`;

  return { xml, index, count: total };
}

/**
 * Build sheet1.xml — the worksheet XML.
 *
 * @param {Object[]} rows
 * @param {string[]} columns
 * @param {Map<string,number>} ssIndex - shared string index
 * @returns {string}
 */
function buildSheetXml(rows, columns, ssIndex) {
  const totalRows = rows.length + 1; // +1 for header

  /**
   * Build a cell reference like "A1".
   * @param {number} col  1-based column index
   * @param {number} row  1-based row index
   */
  const ref = (col, row) => `${colLetter(col)}${row}`;

  /** Build a cell element. */
  function cellEl(colIdx, rowIdx, value) {
    const r = ref(colIdx, rowIdx);

    if (value === null || value === undefined || value === '') {
      // Empty string shared string
      const si = ssIndex.get('');
      if (si !== undefined) {
        return `<c r="${r}" t="s"><v>${si}</v></c>`;
      }
      return `<c r="${r}"/>`;
    }

    if (typeof value === 'number' && isFinite(value)) {
      return `<c r="${r}" t="n"><v>${value}</v></c>`;
    }

    const s = String(value);
    const si = ssIndex.get(s);
    if (si !== undefined) {
      return `<c r="${r}" t="s"><v>${si}</v></c>`;
    }
    // Fallback: inline string (shouldn't happen if buildSharedStrings ran first)
    return `<c r="${r}" t="inlineStr"><is><t>${xmlEscape(s)}</t></is></c>`;
  }

  // Build row XML strings
  const rowEls = [];

  // Header row (row 1)
  const headerCells = columns.map((col, i) => cellEl(i + 1, 1, col)).join('');
  rowEls.push(`<row r="1">${headerCells}</row>`);

  // Data rows
  for (let ri = 0; ri < rows.length; ri++) {
    const rowIdx = ri + 2;
    const cells = columns.map((col, ci) => cellEl(ci + 1, rowIdx, rows[ri][col])).join('');
    rowEls.push(`<row r="${rowIdx}">${cells}</row>`);
  }

  // Dimension ref e.g. "A1:E5"
  const dimRef =
    totalRows > 0 && columns.length > 0
      ? `A1:${colLetter(columns.length)}${totalRows}`
      : 'A1';

  return (
    `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n` +
    `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"` +
    ` xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">` +
    `<dimension ref="${dimRef}"/>` +
    `<sheetData>` +
    rowEls.join('') +
    `</sheetData>` +
    `</worksheet>`
  );
}

/** Static Open XML relationship + content-type parts. */
const CONTENT_TYPES_XML =
  `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n` +
  `<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">` +
  `<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>` +
  `<Default Extension="xml" ContentType="application/xml"/>` +
  `<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>` +
  `<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>` +
  `<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>` +
  `</Types>`;

const RELS_XML =
  `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n` +
  `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` +
  `<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>` +
  `</Relationships>`;

const WORKBOOK_XML =
  `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n` +
  `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"` +
  ` xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">` +
  `<sheets><sheet name="Sheet1" sheetId="1" r:id="rId1"/></sheets>` +
  `</workbook>`;

const WORKBOOK_RELS_XML =
  `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n` +
  `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` +
  `<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>` +
  `<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>` +
  `</Relationships>`;

/**
 * Convert an array of row objects to an XLSX Buffer.
 *
 * @param {Object[]} rows
 * @param {Object} [opts]
 * @param {string[]} [opts.columns]  - column order; defaults to Object.keys(rows[0])
 * @returns {Buffer}
 */
function toXlsxBuffer(rows, opts = {}) {
  const safeRows = rows || [];
  const columns =
    opts.columns ||
    (safeRows.length > 0 ? Object.keys(safeRows[0]) : []);

  const { xml: ssXml, index: ssIndex } = buildSharedStrings(safeRows, columns);
  const sheetXml = buildSheetXml(safeRows, columns, ssIndex);

  const zip = new ZipWriter();
  zip.addEntry('[Content_Types].xml', CONTENT_TYPES_XML);
  zip.addEntry('_rels/.rels', RELS_XML);
  zip.addEntry('xl/workbook.xml', WORKBOOK_XML);
  zip.addEntry('xl/_rels/workbook.xml.rels', WORKBOOK_RELS_XML);
  zip.addEntry('xl/worksheets/sheet1.xml', sheetXml);
  zip.addEntry('xl/sharedStrings.xml', ssXml);

  return zip.finalize();
}

module.exports = { toXlsxBuffer, colLetter, xmlEscape };