xlsx-export.js

gistfile1.txt

import JSZip from 'https://cdn.skypack.dev/jszip';

function columnLetter(n) {
  let s = '';
  while (n > 0) {
    const m = (n - 1) % 26;
    s = String.fromCharCode(65 + m) + s;
    n = Math.floor((n - 1) / 26);
  }
  return s;
}

class Sheet {
  constructor(name = 'Sheet1') {
    this.name = name;
    this.data = [];
  }
  addRow(row) {
    this.data.push(row);
    return this;
  }
}

class Workbook {
  constructor() {
    this.sheets = [];
  }
  sheet(name) {
    const sht = new Sheet(name);
    this.sheets.push(sht);
    return sht;
  }
  addSheets(arr) {
    this.sheets.push(...arr);
    return this;
  }
  async save(filename = 'workbook.xlsx') {
    const zip = new JSZip();
    // [Content_Types].xml
    zip.file('[Content_Types].xml', contentTypesXml(this.sheets.length));
    // root rels
    zip.folder('_rels').file('.rels', rootRelsXml());
    // workbook and its rels
    const xl = zip.folder('xl');
    xl.file('workbook.xml', workbookXml(this.sheets));
    xl.folder('_rels').file('workbook.xml.rels', workbookRelsXml(this.sheets));
    // worksheets
    const wsFolder = xl.folder('worksheets');
    this.sheets.forEach((s, i) => {
      wsFolder.file(`sheet${i + 1}.xml`, sheetXml(s.data));
    });
    const blob = await zip.generateAsync({ type: 'blob' });
    downloadBlob(blob, filename);
  }
}

// XML generators
function contentTypesXml(count) {
  const overrides = Array.from({ length: count }, (_, i) =>
    `  <Override PartName="/xl/worksheets/sheet${i + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`
  ).join('\n');
  return `<?xml version="1.0" encoding="UTF-8"?>
<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"/>
${overrides}
</Types>`;
}

function rootRelsXml() {
  return `<?xml version="1.0" encoding="UTF-8"?>
<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>`;
}

function workbookXml(sheets) {
  const list = sheets.map((s, i) =>
    `    <sheet name="${s.name}" sheetId="${i + 1}" r:id="rId${i + 1}"/>`
  ).join('\n');
  return `<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheets>
${list}
  </sheets>
</workbook>`;
}

function workbookRelsXml(sheets) {
  const rels = sheets.map((_, i) =>
    `  <Relationship Id="rId${i + 1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet${i + 1}.xml"/>`
  ).join('\n');
  return `<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
${rels}
</Relationships>`;
}

function sheetXml(data) {
  const rows = data.map((r, i) => {
    const cells = r.map((v, j) => {
      const ref = `${columnLetter(j + 1)}${i + 1}`;
      if (typeof v === 'number') {
        return `      <c r="${ref}"><v>${v}</v></c>`;
      }
      const text = String(v).replace(/&/g, '&amp;').replace(/</g, '&lt;');
      return `      <c r="${ref}" t="inlineStr"><is><t>${text}</t></is></c>`;
    }).join('\n');
    return `    <row r="${i + 1}">
${cells}
    </row>`;
  }).join('\n');
  return `<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheetData>
${rows}
  </sheetData>
</worksheet>`;
}

function downloadBlob(blob, name) {
  const url = URL.createObjectURL(blob);
  const a = document.createElement('a');
  a.href = url;
  a.download = name;
  document.body.appendChild(a);
  a.click();
  document.body.removeChild(a);
  URL.revokeObjectURL(url);
}

export { Workbook, Sheet };