@loui/sheets@0.0.1

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778
import JSZip from "npm:jszip@3.10.1";
import { DOMParser, Element, HTMLDocument } from "npm:xmldom@0.6.0";

export async function parseXLSX(arrayBuffer: ArrayBuffer): Promise<Record<string, unknown[][]>> {
  const zip = await JSZip.loadAsync(arrayBuffer);
  const workbook = await zip.file("xl/workbook.xml")?.async("text");
  const parser = new DOMParser();
  if (!workbook) {
    throw new Error("Workbook not found");
  }

  const workbookDoc = parser.parseFromString(workbook, "application/xml");
  if (!workbookDoc) {
    throw new Error("Workbook not found");
  }
  const sheetNames = [...workbookDoc.getElementsByTagName("sheet")].map(
    (sheet) => ({
      id: sheet.getAttribute("r:id"),
      name: sheet.getAttribute("name")
    })
  );

  const sheets: Record<string, unknown[][]> = {};
  for (const { id, name } of sheetNames) {
    const sheetRels = await zip.file("xl/_rels/workbook.xml.rels")?.async("text");
    if (!sheetRels) {
      throw new Error("Sheet relationships not found");
    }
    const relsDoc = parser.parseFromString(sheetRels, "application/xml");
    if (!relsDoc) {
      throw new Error("Sheet relationships not found");
    }
    const relsEls = relsDoc.getElementsByTagName("Relationship");
    if (!relsEls) {
      throw new Error("Sheet relationships not found");
    }
    const sheetFilePath = [...relsEls].find((rel) => rel.getAttribute("Id") === id)?.getAttribute("Target");
    const sheetContent = await zip.file("xl/" + sheetFilePath)?.async("text");
    if (!sheetContent) {
      throw new Error("Sheet content not found");
    }
    const sheetDoc = parser.parseFromString(sheetContent, "application/xml");
    // if name is not found, use id
    const key = name || id;
    if (key && sheetDoc) {
      sheets[key] = parseSheet(sheetDoc);
    }
  }

  return sheets;
}

export function parseSheet(sheetDoc: HTMLDocument): unknown[][] {
  const rows = [];
  const rowElements = sheetDoc.getElementsByTagName("row");

  for (const rowElement of rowElements) {
    const row = [];
    const cellElements = rowElement.getElementsByTagName("c");

    for (const cellElement of cellElements) {
      const cellValue = parseCell(cellElement);
      row.push(cellValue);
    }

    rows.push(row);
  }

  return rows;
}

export function parseCell(cellElement: Element): string | null {
  const valueElement = cellElement.getElementsByTagName("v")[0];
  if (!valueElement) {
    return null;
  }
  return valueElement.textContent;
}