koc-loop/lib/recovery-workbook.ts

import { strToU8, zipSync } from "fflate";

export type RecoveryWorkbookImage = {
  bytes: Uint8Array;
  contentType: string;
  width?: number | null;
  height?: number | null;
  description: string;
};

export type RecoveryWorkbookRow = {
  cells: Array<string | number | null>;
  images: Array<{
    column: number;
    image: RecoveryWorkbookImage;
  }>;
  hyperlinks?: Array<{
    column: number;
    url: string;
  }>;
};

type WorkbookOptions = {
  sheetName: string;
  headers: string[];
  columnWidths: number[];
  rows: RecoveryWorkbookRow[];
};

const XML_HEADER = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
const IMAGE_ROW_HEIGHT = 126;

function cleanXmlText(value: unknown) {
  return String(value ?? "")
    .replace(/[\u0000-\u0008\u000B\u000C\u000E-\u001F]/g, "")
    .slice(0, 32767)
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&apos;");
}

function columnName(index: number) {
  let value = index + 1;
  let result = "";
  while (value > 0) {
    value -= 1;
    result = String.fromCharCode(65 + (value % 26)) + result;
    value = Math.floor(value / 26);
  }
  return result;
}

function safeSheetName(value: string) {
  const cleaned = value.replace(/[\\/?*\[\]:]/g, " ").trim();
  return (cleaned || "数据回收").slice(0, 31);
}

function safeHyperlink(value: string) {
  try {
    const url = new URL(value);
    return ["http:", "https:"].includes(url.protocol) ? url.toString() : "";
  } catch {
    return "";
  }
}

function imageFormat(contentType: string, bytes: Uint8Array) {
  const normalized = contentType.toLowerCase();
  if (normalized.includes("png") || (bytes[0] === 0x89 && bytes[1] === 0x50)) {
    return { extension: "png", contentType: "image/png" };
  }
  if (
    normalized.includes("gif") ||
    (bytes[0] === 0x47 && bytes[1] === 0x49 && bytes[2] === 0x46)
  ) {
    return { extension: "gif", contentType: "image/gif" };
  }
  if (
    normalized.includes("webp") ||
    String.fromCharCode(...bytes.slice(8, 12)) === "WEBP"
  ) {
    return { extension: "webp", contentType: "image/webp" };
  }
  return { extension: "jpg", contentType: "image/jpeg" };
}

function imageDimensions(image: RecoveryWorkbookImage) {
  if (image.width && image.height) {
    return { width: image.width, height: image.height };
  }
  const bytes = image.bytes;
  if (bytes.length >= 24 && bytes[0] === 0x89 && bytes[1] === 0x50) {
    const view = new DataView(bytes.buffer, bytes.byteOffset, bytes.byteLength);
    return { width: view.getUint32(16), height: view.getUint32(20) };
  }
  if (
    bytes.length >= 10 &&
    bytes[0] === 0x47 &&
    bytes[1] === 0x49 &&
    bytes[2] === 0x46
  ) {
    const view = new DataView(bytes.buffer, bytes.byteOffset, bytes.byteLength);
    return { width: view.getUint16(6, true), height: view.getUint16(8, true) };
  }
  if (bytes.length >= 4 && bytes[0] === 0xff && bytes[1] === 0xd8) {
    let offset = 2;
    while (offset + 9 < bytes.length) {
      if (bytes[offset] !== 0xff) {
        offset += 1;
        continue;
      }
      const marker = bytes[offset + 1];
      const length = (bytes[offset + 2] << 8) + bytes[offset + 3];
      if (
        [0xc0, 0xc1, 0xc2, 0xc3, 0xc5, 0xc6, 0xc7, 0xc9, 0xca, 0xcb, 0xcd, 0xce, 0xcf].includes(
          marker,
        )
      ) {
        return {
          width: (bytes[offset + 7] << 8) + bytes[offset + 8],
          height: (bytes[offset + 5] << 8) + bytes[offset + 6],
        };
      }
      if (!length) break;
      offset += length + 2;
    }
  }
  return { width: 4, height: 3 };
}

function imageDisplaySize(image: RecoveryWorkbookImage) {
  const dimensions = imageDimensions(image);
  const scale = Math.min(160 / dimensions.width, 150 / dimensions.height, 1);
  return {
    width: Math.max(28, Math.round(dimensions.width * scale)),
    height: Math.max(28, Math.round(dimensions.height * scale)),
  };
}

function inlineCell(reference: string, value: unknown, style: number) {
  return `<c r="${reference}" t="inlineStr" s="${style}"><is><t xml:space="preserve">${cleanXmlText(value)}</t></is></c>`;
}

function numberCell(reference: string, value: number, style: number) {
  return `<c r="${reference}" s="${style}"><v>${Number.isFinite(value) ? value : 0}</v></c>`;
}

export function buildRecoveryWorkbook(options: WorkbookOptions) {
  const sheetName = safeSheetName(options.sheetName);
  const imageEntries = options.rows.flatMap((row, rowIndex) =>
    row.images.map((item) => ({ ...item, row: rowIndex + 1 })),
  );
  const hyperlinkEntries = options.rows.flatMap((row, rowIndex) =>
    (row.hyperlinks ?? [])
      .map((item) => ({
        ...item,
        row: rowIndex + 2,
        url: safeHyperlink(item.url),
      }))
      .filter((item) => item.url),
  );
  const lastColumn = columnName(Math.max(0, options.headers.length - 1));
  const lastRow = Math.max(1, options.rows.length + 1);
  const headerCells = options.headers
    .map((value, index) => inlineCell(`${columnName(index)}1`, value, 1))
    .join("");
  const dataRows = options.rows
    .map((row, rowIndex) => {
      const number = rowIndex + 2;
      const imageColumns = new Set(row.images.map((item) => item.column));
      const hyperlinkColumns = new Set(
        (row.hyperlinks ?? []).map((item) => item.column),
      );
      const cells = options.headers
        .map((_, columnIndex) => {
          const reference = `${columnName(columnIndex)}${number}`;
          const value = row.cells[columnIndex] ?? "";
          if (imageColumns.has(columnIndex)) {
            return inlineCell(reference, value || "见图", 4);
          }
          return typeof value === "number"
            ? numberCell(reference, value, 3)
            : inlineCell(
                reference,
                value,
                hyperlinkColumns.has(columnIndex) ? 5 : 2,
              );
        })
        .join("");
      const rowHeight = row.images.length > 0 ? IMAGE_ROW_HEIGHT : 42;
      return `<row r="${number}" ht="${rowHeight}" customHeight="1">${cells}</row>`;
    })
    .join("");
  const columns = options.headers
    .map((_, index) => {
      const width = Math.min(70, Math.max(8, options.columnWidths[index] ?? 14));
      return `<col min="${index + 1}" max="${index + 1}" width="${width}" customWidth="1"/>`;
    })
    .join("");

  const drawingXml = imageEntries.length
    ? `${XML_HEADER}<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">${imageEntries
        .map((entry, index) => {
          const size = imageDisplaySize(entry.image);
          const width = size.width * 9525;
          const height = size.height * 9525;
          return `<xdr:oneCellAnchor><xdr:from><xdr:col>${entry.column}</xdr:col><xdr:colOff>57150</xdr:colOff><xdr:row>${entry.row}</xdr:row><xdr:rowOff>57150</xdr:rowOff></xdr:from><xdr:ext cx="${width}" cy="${height}"/><xdr:pic><xdr:nvPicPr><xdr:cNvPr id="${index + 1}" name="${cleanXmlText(entry.image.description || `图片${index + 1}`)}"/><xdr:cNvPicPr><a:picLocks noChangeAspect="1"/></xdr:cNvPicPr></xdr:nvPicPr><xdr:blipFill><a:blip r:embed="rId${index + 1}"/><a:stretch><a:fillRect/></a:stretch></xdr:blipFill><xdr:spPr><a:prstGeom prst="rect"><a:avLst/></a:prstGeom></xdr:spPr></xdr:pic><xdr:clientData/></xdr:oneCellAnchor>`;
        })
        .join("")}</xdr:wsDr>`
    : "";
  const drawingRelationships = imageEntries.length
    ? `${XML_HEADER}<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">${imageEntries
        .map((entry, index) => {
          const format = imageFormat(entry.image.contentType, entry.image.bytes);
          return `<Relationship Id="rId${index + 1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image${index + 1}.${format.extension}"/>`;
        })
        .join("")}</Relationships>`
    : "";

  const imageFormats = new Map<string, string>();
  for (const entry of imageEntries) {
    const format = imageFormat(entry.image.contentType, entry.image.bytes);
    imageFormats.set(format.extension, format.contentType);
  }
  const imageContentTypes = [...imageFormats.entries()]
    .map(([extension, contentType]) => `<Default Extension="${extension}" ContentType="${contentType}"/>`)
    .join("");
  const contentTypes = `${XML_HEADER}<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"/>${imageContentTypes}<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/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>${imageEntries.length ? '<Override PartName="/xl/drawings/drawing1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>' : ""}<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/></Types>`;
  const hyperlinkRelationshipOffset = imageEntries.length ? 2 : 1;
  const hyperlinksXml = hyperlinkEntries.length
    ? `<hyperlinks>${hyperlinkEntries
        .map(
          (entry, index) =>
            `<hyperlink ref="${columnName(entry.column)}${entry.row}" r:id="rId${hyperlinkRelationshipOffset + index}"/>`,
        )
        .join("")}</hyperlinks>`
    : "";
  const worksheet = `${XML_HEADER}<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="A1:${lastColumn}${lastRow}"/><sheetViews><sheetView workbookViewId="0"><pane xSplit="3" ySplit="1" topLeftCell="D2" activePane="bottomRight" state="frozen"/></sheetView></sheetViews><sheetFormatPr defaultRowHeight="18"/><cols>${columns}</cols><sheetData><row r="1" ht="30" customHeight="1">${headerCells}</row>${dataRows}</sheetData><autoFilter ref="A1:${lastColumn}${lastRow}"/>${hyperlinksXml}${imageEntries.length ? '<drawing r:id="rId1"/>' : ""}</worksheet>`;

  const sheetRelationships = [
    ...(imageEntries.length
      ? [
          '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing1.xml"/>',
        ]
      : []),
    ...hyperlinkEntries.map(
      (entry, index) =>
        `<Relationship Id="rId${hyperlinkRelationshipOffset + index}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="${cleanXmlText(entry.url)}" TargetMode="External"/>`,
    ),
  ];

  const files: Record<string, Uint8Array> = {
    "[Content_Types].xml": strToU8(contentTypes),
    "_rels/.rels": strToU8(`${XML_HEADER}<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"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/></Relationships>`),
    "docProps/app.xml": strToU8(`${XML_HEADER}<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Application>KOC LOOP</Application></Properties>`),
    "docProps/core.xml": strToU8(`${XML_HEADER}<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dc:creator>KOC LOOP</dc:creator><cp:lastModifiedBy>KOC LOOP</cp:lastModifiedBy><dcterms:created xsi:type="dcterms:W3CDTF">${new Date().toISOString()}</dcterms:created></cp:coreProperties>`),
    "xl/workbook.xml": strToU8(`${XML_HEADER}<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="${cleanXmlText(sheetName)}" sheetId="1" r:id="rId1"/></sheets></workbook>`),
    "xl/_rels/workbook.xml.rels": strToU8(`${XML_HEADER}<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/styles" Target="styles.xml"/></Relationships>`),
    "xl/styles.xml": strToU8(`${XML_HEADER}<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="4"><font><sz val="10"/><name val="Arial"/></font><font><b/><color rgb="FFFFFFFF"/><sz val="10"/><name val="Arial"/></font><font><color rgb="FF153B31"/><sz val="10"/><name val="Arial"/></font><font><u/><color rgb="FF1F8F6B"/><sz val="10"/><name val="Arial"/></font></fonts><fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FF1F8F6B"/><bgColor indexed="64"/></patternFill></fill></fills><borders count="2"><border><left/><right/><top/><bottom/><diagonal/></border><border><left style="thin"><color rgb="FFDDE5E1"/></left><right style="thin"><color rgb="FFDDE5E1"/></right><top style="thin"><color rgb="FFDDE5E1"/></top><bottom style="thin"><color rgb="FFDDE5E1"/></bottom><diagonal/></border></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="6"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/><xf numFmtId="0" fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1" applyAlignment="1"><alignment vertical="top" wrapText="1"/></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf><xf numFmtId="0" fontId="3" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1" applyAlignment="1"><alignment vertical="top" wrapText="1"/></xf></cellXfs><cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles></styleSheet>`),
    "xl/worksheets/sheet1.xml": strToU8(worksheet),
  };
  if (sheetRelationships.length) {
    files["xl/worksheets/_rels/sheet1.xml.rels"] = strToU8(`${XML_HEADER}<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">${sheetRelationships.join("")}</Relationships>`);
  }
  if (imageEntries.length) {
    files["xl/drawings/drawing1.xml"] = strToU8(drawingXml);
    files["xl/drawings/_rels/drawing1.xml.rels"] = strToU8(drawingRelationships);
    imageEntries.forEach((entry, index) => {
      const format = imageFormat(entry.image.contentType, entry.image.bytes);
      files[`xl/media/image${index + 1}.${format.extension}`] = entry.image.bytes;
    });
  }
  return zipSync(files, { level: 1 });
}