tihao-ai/server/utils/file-parser.ts

import { inflateRawSync } from 'node:zlib';

/**
 * 简易文件解析工具
 * 支持从上传的 Excel/Word 文件中提取文本内容
 * 注意：完整实现需要 xlsx / mammoth 等库，此处提供基础文本提取
 */

/**
 * 从文件 Buffer 提取可读文本
 * 对于 .docx 文件提取 XML 内的文本节点
 * 对于 .xlsx 文件提取 sharedStrings 中的文本
 */
export function extractTextFromBuffer(buffer: Buffer, fileName: string): string {
  const ext = fileName.substring(fileName.lastIndexOf('.')).toLowerCase();
  console.log(`[FileParser] 文件: ${fileName}, 扩展名: ${ext}, buffer大小: ${buffer.length} bytes`);

  let result: string;
  if (ext === '.docx') {
    result = extractDocxText(buffer);
  } else if (ext === '.xlsx' || ext === '.xls') {
    result = extractXlsxText(buffer);
  } else {
    // Fallback: try to read as plain text
    result = buffer.toString('utf-8').slice(0, 50000);
  }

  console.log(`[FileParser] 提取文本长度: ${result.length} 字符`);
  console.log(`[FileParser] 文本前300字: ${result.substring(0, 300)}`);
  return result;
}

export interface WorkbookSheetTable {
  name: string;
  rows: string[][];
}

export function extractWorkbookTablesFromBuffer(buffer: Buffer): WorkbookSheetTable[] {
  const entries = listZipEntries(buffer);
  if (entries.length === 0) return [];

  const sharedStrings = parseSharedStrings(readZipEntry(buffer, 'xl/sharedStrings.xml'));
  const sheetNames = parseWorkbookSheetNames(readZipEntry(buffer, 'xl/workbook.xml'));
  return entries
    .filter((entry) => /^xl\/worksheets\/sheet\d+\.xml$/.test(entry.name))
    .sort((a, b) => a.name.localeCompare(b.name))
    .map((entry, index) => ({
      name: sheetNames[index] || `Sheet${index + 1}`,
      rows: parseWorksheetRows(readZipEntry(buffer, entry.name), sharedStrings),
    }))
    .filter((sheet) => sheet.rows.length > 0);
}

/**
 * 从 .docx (ZIP 内的 XML) 提取纯文本
 * 简易实现：查找 <w:t> 标签内容
 */
function extractDocxText(buffer: Buffer): string {
  const documentXml = readZipEntry(buffer, 'word/document.xml');
  const raw = documentXml ? documentXml.toString('utf-8') : buffer.toString('utf-8');
  const textParts: string[] = [];

  // Extract text between <w:t> tags
  const regex = /<w:t[^>]*>([^<]*)<\/w:t>/g;
  let match: RegExpExecArray | null;
  while ((match = regex.exec(raw)) !== null) {
    if (match[1]) {
      textParts.push(match[1]);
    }
  }

  if (textParts.length > 0) {
    return textParts.join(' ');
  }

  // Fallback: extract any readable text segments
  return extractReadableText(buffer);
}

/**
 * 从 .xlsx (ZIP 内的 sharedStrings.xml) 提取文本
 */
function extractXlsxText(buffer: Buffer): string {
  const entries = listZipEntries(buffer);
  if (entries.length === 0) {
    return extractReadableText(buffer);
  }

  const sharedStrings = parseSharedStrings(readZipEntry(buffer, 'xl/sharedStrings.xml'));
  const sheetEntries = entries
    .filter((entry) => /^xl\/worksheets\/sheet\d+\.xml$/.test(entry.name))
    .sort((a, b) => a.name.localeCompare(b.name));

  const sheetTexts = sheetEntries
    .map((entry, index) => parseWorksheetText(readZipEntry(buffer, entry.name), sharedStrings, index + 1))
    .filter(Boolean);

  return sheetTexts.length > 0 ? sheetTexts.join('\n\n') : extractReadableText(buffer);
}

/**
 * 从二进制数据提取可读文本段
 */
function extractReadableText(buffer: Buffer): string {
  const text = buffer.toString('utf-8');
  // Filter to only printable characters and common Chinese
  const readable = text.replace(/[^\u4e00-\u9fff\u0020-\u007e\u00a0-\u00ff\n\r\t]/g, ' ');
  // Collapse whitespace
  return readable.replace(/\s+/g, ' ').trim().slice(0, 30000);
}

interface ZipEntry {
  name: string;
  method: number;
  compressedSize: number;
  localHeaderOffset: number;
}

function listZipEntries(buffer: Buffer): ZipEntry[] {
  const entries: ZipEntry[] = [];
  let offset = 0;
  while (offset < buffer.length - 46) {
    if (buffer.readUInt32LE(offset) !== 0x02014b50) {
      offset += 1;
      continue;
    }

    const method = buffer.readUInt16LE(offset + 10);
    const compressedSize = buffer.readUInt32LE(offset + 20);
    const fileNameLength = buffer.readUInt16LE(offset + 28);
    const extraLength = buffer.readUInt16LE(offset + 30);
    const commentLength = buffer.readUInt16LE(offset + 32);
    const localHeaderOffset = buffer.readUInt32LE(offset + 42);
    const name = buffer.toString('utf-8', offset + 46, offset + 46 + fileNameLength);

    entries.push({ name, method, compressedSize, localHeaderOffset });
    offset += 46 + fileNameLength + extraLength + commentLength;
  }
  return entries;
}

function readZipEntry(buffer: Buffer, entryName: string): Buffer | null {
  const entry = listZipEntries(buffer).find((item) => item.name === entryName);
  if (!entry) return null;

  const localOffset = entry.localHeaderOffset;
  if (buffer.readUInt32LE(localOffset) !== 0x04034b50) return null;

  const fileNameLength = buffer.readUInt16LE(localOffset + 26);
  const extraLength = buffer.readUInt16LE(localOffset + 28);
  const dataStart = localOffset + 30 + fileNameLength + extraLength;
  const compressed = buffer.subarray(dataStart, dataStart + entry.compressedSize);

  if (entry.method === 0) return compressed;
  if (entry.method === 8) return inflateRawSync(compressed);
  return null;
}

function parseSharedStrings(xmlBuffer: Buffer | null): string[] {
  if (!xmlBuffer) return [];
  const xml = xmlBuffer.toString('utf-8');
  const strings: string[] = [];
  const itemRegex = /<si\b[^>]*>([\s\S]*?)<\/si>/g;
  let itemMatch: RegExpExecArray | null;
  while ((itemMatch = itemRegex.exec(xml)) !== null) {
    strings.push(extractXmlText(itemMatch[1]));
  }
  return strings;
}

function parseWorksheetText(xmlBuffer: Buffer | null, sharedStrings: string[], sheetIndex: number): string {
  const rows = parseWorksheetRows(xmlBuffer, sharedStrings)
    .map((row) => row.map((value) => value.trim()).filter(Boolean));
  const textRows = rows.filter((row) => row.length > 0).map((row) => row.join(' | '));
  return textRows.length > 0 ? `Sheet${sheetIndex}\n${textRows.join('\n')}` : '';
}

function parseWorksheetRows(xmlBuffer: Buffer | null, sharedStrings: string[]): string[][] {
  if (!xmlBuffer) return [];
  const xml = xmlBuffer.toString('utf-8');
  const rows: string[][] = [];
  const rowRegex = /<row\b[^>]*>([\s\S]*?)<\/row>/g;
  let rowMatch: RegExpExecArray | null;

  while ((rowMatch = rowRegex.exec(xml)) !== null) {
    const values: string[] = [];
    const cellRegex = /<c\b([^>]*)>([\s\S]*?)<\/c>/g;
    let cellMatch: RegExpExecArray | null;
    while ((cellMatch = cellRegex.exec(rowMatch[1])) !== null) {
      const attrs = cellMatch[1];
      const body = cellMatch[2];
      const ref = attrs.match(/\br="([A-Z]+\d+)"/)?.[1] || '';
      const colIndex = ref ? columnNameToIndex(ref.replace(/\d+/g, '')) : values.length;
      const type = attrs.match(/\bt="([^"]+)"/)?.[1];
      const rawValue = body.match(/<v[^>]*>([\s\S]*?)<\/v>/)?.[1] || '';
      let value = '';

      if (type === 's') {
        value = sharedStrings[Number(rawValue)] || '';
      } else if (type === 'inlineStr') {
        value = extractXmlText(body);
      } else {
        value = decodeXml(rawValue);
      }

      values[colIndex] = value.trim();
    }
    if (values.some(Boolean)) rows.push(values.map((value) => value || ''));
  }

  return rows;
}

function parseWorkbookSheetNames(xmlBuffer: Buffer | null): string[] {
  if (!xmlBuffer) return [];
  const xml = xmlBuffer.toString('utf-8');
  const names: string[] = [];
  const sheetRegex = /<sheet\b[^>]*\bname="([^"]+)"/g;
  let match: RegExpExecArray | null;
  while ((match = sheetRegex.exec(xml)) !== null) {
    names.push(decodeXml(match[1]));
  }
  return names;
}

function columnNameToIndex(name: string): number {
  let result = 0;
  for (const char of name) {
    result = result * 26 + (char.charCodeAt(0) - 64);
  }
  return Math.max(0, result - 1);
}

function extractXmlText(xml: string): string {
  const parts: string[] = [];
  const textRegex = /<t\b[^>]*>([\s\S]*?)<\/t>/g;
  let textMatch: RegExpExecArray | null;
  while ((textMatch = textRegex.exec(xml)) !== null) {
    parts.push(decodeXml(textMatch[1]));
  }
  return parts.join('').trim();
}

function decodeXml(value: string): string {
  return value
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&quot;/g, '"')
    .replace(/&apos;/g, "'")
    .replace(/&amp;/g, '&');
}