hyreal-gen-pic/server/services/xlsx.js

'use strict';

/**
 * 最小 xlsx 读取器（零依赖）
 * ----------------------------------------------------------------------------
 * xlsx 是 ZIP 容器 + XML。这里只做一件事：把第一个工作表的单元格读成二维数组，
 * 供解析器使用。够用即可，不做通用电子表格库。
 *
 * 支持：共享字符串（t="s"）、内联字符串（inlineStr）、缓存值（t="str"）、数字、布尔。
 * 不支持：.xls、公式计算（读缓存值）、样式/颜色、图表、合并单元格语义
 *        （合并区只取左上角，其余为空）、多工作表遍历（只读第一个）。
 */

const zlib = require('zlib');

const EOCD_SIG = 0x06054b50;
const CEN_SIG = 0x02014b50;
const LOC_SIG = 0x04034b50;

function fail(message, cause) {
  const err = new Error(message);
  err.code = 'XLSX_PARSE_FAILED';
  if (cause) err.cause = cause;
  return err;
}

// ---------- ZIP ----------

/** 从尾部找中央目录结束记录（EOCD 可能带注释，最多回退 65557 字节） */
function findEocd(buf) {
  const stop = Math.max(0, buf.length - 65557);
  for (let i = buf.length - 22; i >= stop; i--) {
    if (buf.readUInt32LE(i) === EOCD_SIG) return i;
  }
  return -1;
}

function readCentralDirectory(buf) {
  const eocd = findEocd(buf);
  if (eocd < 0) throw fail('不是有效的 zip/xlsx 文件');
  const count = buf.readUInt16LE(eocd + 10);
  const cdOffset = buf.readUInt32LE(eocd + 16);
  if (cdOffset <= 0 || cdOffset >= buf.length) throw fail('zip 中央目录偏移非法');
  const entries = new Map();
  let p = cdOffset;
  for (let i = 0; i < count; i++) {
    if (p + 46 > buf.length) throw fail('zip 中央目录被截断');
    if (buf.readUInt32LE(p) !== CEN_SIG) throw fail('zip 中央目录签名不符');
    const method = buf.readUInt16LE(p + 10);
    const compSize = buf.readUInt32LE(p + 20);
    const nameLen = buf.readUInt16LE(p + 28);
    const extraLen = buf.readUInt16LE(p + 30);
    const commentLen = buf.readUInt16LE(p + 32);
    const localOffset = buf.readUInt32LE(p + 42);
    const name = buf.toString('utf8', p + 46, p + 46 + nameLen);
    entries.set(name, { method, compSize, localOffset });
    p += 46 + nameLen + extraLen + commentLen;
  }
  return entries;
}

function readEntry(buf, entries, name) {
  const entry = entries.get(name);
  if (!entry) return null;
  const p = entry.localOffset;
  if (p + 30 > buf.length || buf.readUInt32LE(p) !== LOC_SIG) throw fail('zip 本地文件头损坏');
  // 本地头的 name/extra 长度以本地头为准（中央目录里可能带额外字段）
  const nameLen = buf.readUInt16LE(p + 26);
  const extraLen = buf.readUInt16LE(p + 28);
  const start = p + 30 + nameLen + extraLen;
  const end = start + entry.compSize;
  if (end > buf.length) throw fail('zip 条目被截断');
  const raw = buf.subarray(start, end);
  if (entry.method === 0) return raw;
  if (entry.method === 8) {
    try {
      return zlib.inflateRawSync(raw);
    } catch (e) {
      throw fail('zip 解压失败', e);
    }
  }
  throw fail(`不支持的 zip 压缩方式：${entry.method}`);
}

// ---------- XML ----------

function decodeXml(text) {
  return String(text).replace(/&(#x?[0-9a-fA-F]+|lt|gt|amp|quot|apos);/g, (whole, ent) => {
    switch (ent) {
      case 'lt': return '<';
      case 'gt': return '>';
      case 'amp': return '&';
      case 'quot': return '"';
      case 'apos': return "'";
      default: break;
    }
    if (ent[0] === '#') {
      const code = ent[1] === 'x' || ent[1] === 'X'
        ? parseInt(ent.slice(2), 16)
        : parseInt(ent.slice(1), 10);
      return Number.isFinite(code) ? String.fromCodePoint(code) : whole;
    }
    return whole;
  });
}

function attrOf(attrs, name) {
  const m = new RegExp(`\\b${name}="([^"]*)"`).exec(attrs || '');
  return m ? m[1] : '';
}

function parseSharedStrings(xml) {
  const out = [];
  if (!xml) return out;
  const siRe = /<si\b[^>]*?(?:\/>|>([\s\S]*?)<\/si>)/g;
  let m;
  while ((m = siRe.exec(xml))) {
    const inner = m[1] || '';
    let text = '';
    const tRe = /<t\b[^>]*>([\s\S]*?)<\/t>/g;
    let t;
    while ((t = tRe.exec(inner))) text += decodeXml(t[1]);
    out.push(text);
  }
  return out;
}

function columnIndex(ref) {
  const m = /^([A-Za-z]+)/.exec(ref || '');
  if (!m) return -1;
  let n = 0;
  for (const ch of m[1].toUpperCase()) n = n * 26 + (ch.charCodeAt(0) - 64);
  return n - 1;
}

function parseCell(attrs, inner, shared) {
  const type = attrOf(attrs, 't');
  if (type === 'inlineStr') {
    let text = '';
    const tRe = /<t\b[^>]*>([\s\S]*?)<\/t>/g;
    let t;
    while ((t = tRe.exec(inner))) text += decodeXml(t[1]);
    return text === '' ? null : text;
  }
  const v = /<v\b[^>]*>([\s\S]*?)<\/v>/.exec(inner);
  if (!v) return null;
  const raw = decodeXml(v[1]);
  if (type === 's') {
    const idx = Number(raw);
    return Number.isInteger(idx) && idx >= 0 && idx < shared.length ? shared[idx] : null;
  }
  if (type === 'str') return raw === '' ? null : raw;
  if (type === 'b') return raw === '1';
  if (type === 'e') return null;
  if (raw === '') return null;
  const num = Number(raw);
  return Number.isFinite(num) ? num : raw;
}

function parseSheet(xml, shared) {
  const rows = [];
  const rowRe = /<row\b([^>]*?)(?:\/>|>([\s\S]*?)<\/row>)/g;
  let m;
  while ((m = rowRe.exec(xml))) {
    const rAttr = attrOf(m[1], 'r');
    const rowIdx = rAttr ? Number(rAttr) - 1 : rows.length;
    const cells = [];
    const cellRe = /<c\b([^>]*?)(?:\/>|>([\s\S]*?)<\/c>)/g;
    let c;
    while ((c = cellRe.exec(m[2] || ''))) {
      const ci = columnIndex(attrOf(c[1], 'r'));
      if (ci < 0) continue;
      cells[ci] = parseCell(c[1], c[2] || '', shared);
    }
    for (let i = 0; i < cells.length; i++) if (cells[i] === undefined) cells[i] = null;
    rows[rowIdx] = cells;
  }
  const out = [];
  for (let i = 0; i < rows.length; i++) {
    const row = rows[i] || [];
    for (let j = 0; j < row.length; j++) if (row[j] === undefined) row[j] = null;
    out.push(row);
  }
  return out;
}

/** 工作表路径：优先读 workbook.xml + rels，失败则回退到第一个 sheetN.xml */
function resolveFirstSheet(buf, entries) {
  const workbook = readEntry(buf, entries, 'xl/workbook.xml');
  const rels = readEntry(buf, entries, 'xl/_rels/workbook.xml.rels');
  if (workbook && rels) {
    const sheetTag = /<sheet\b([^>]*)\/?>/.exec(workbook.toString('utf8'));
    const rid = sheetTag ? attrOf(sheetTag[1], 'r:id') : '';
    if (rid) {
      const relRe = /<Relationship\b([^>]*)\/?>/g;
      let m;
      const relsText = rels.toString('utf8');
      while ((m = relRe.exec(relsText))) {
        if (attrOf(m[1], 'Id') === rid) {
          let target = attrOf(m[1], 'Target');
          if (!target) break;
          target = target.replace(/^\/?xl\//, '').replace(/^\//, '');
          return { path: 'xl/' + target, name: '' };
        }
      }
    }
  }
  const names = [...entries.keys()]
    .filter((n) => /^xl\/worksheets\/sheet\d+\.xml$/.test(n))
    .sort((a, b) => Number(/(\d+)/.exec(a)[1]) - Number(/(\d+)/.exec(b)[1]));
  if (!names.length) throw fail('xlsx 中没有工作表');
  return { path: names[0], name: '' };
}

/**
 * 读取第一个工作表
 * @param {Buffer} buffer
 * @returns {{ sheets: Array<{ name: string, rows: Array<Array<string|number|boolean|null>> }> }}
 */
function readXlsx(buffer) {
  if (!Buffer.isBuffer(buffer) || buffer.length < 22) throw fail('文件内容为空或不是 xlsx');
  const entries = readCentralDirectory(buffer);
  const sheetRef = resolveFirstSheet(buffer, entries);
  const sheetXml = readEntry(buffer, entries, sheetRef.path);
  if (!sheetXml) throw fail('未找到工作表数据');
  const sharedXml = readEntry(buffer, entries, 'xl/sharedStrings.xml');
  const shared = parseSharedStrings(sharedXml ? sharedXml.toString('utf8') : '');
  let rows;
  try {
    rows = parseSheet(sheetXml.toString('utf8'), shared);
  } catch (e) {
    if (e.code === 'XLSX_PARSE_FAILED') throw e;
    throw fail('工作表 XML 解析失败', e);
  }
  return { sheets: [{ name: sheetRef.name || 'Sheet1', rows }] };
}

module.exports = { readXlsx };