ai-chatbot/lib/openclaw-chat/spreadsheet-markdown.ts

import Papa from "papaparse";

export const OPENCLAW_SPREADSHEET_MAX_FILES = 3;
export const OPENCLAW_SPREADSHEET_MAX_BYTES = 10 * 1024 * 1024;
export const OPENCLAW_SPREADSHEET_MAX_ROWS_PER_SHEET = 200;
export const OPENCLAW_SPREADSHEET_MAX_COLS_PER_SHEET = 30;
export const OPENCLAW_SPREADSHEET_MAX_SHEETS_PER_FILE = 5;
export const OPENCLAW_SPREADSHEET_MAX_MARKDOWN_CHARS = 60_000;

const SUPPORTED_EXTENSIONS = new Set(["csv", "tsv", "xlsx"]);

export type OpenClawSpreadsheetFileSummary = {
  fileName: string;
  sheetCount: number;
  rowCount: number;
  truncated: boolean;
};

export type OpenClawSpreadsheetMarkdownResult = {
  markdown: string;
  files: OpenClawSpreadsheetFileSummary[];
  truncated: boolean;
};

type WorksheetMarkdown = {
  markdown: string;
  rowCount: number;
  truncated: boolean;
};

async function createExcelWorkbook() {
  const exceljs = await import("exceljs");
  const WorkbookConstructor =
    exceljs.Workbook ||
    (exceljs as unknown as {
      default?: { Workbook?: typeof exceljs.Workbook };
    }).default?.Workbook;

  if (!WorkbookConstructor) {
    throw new Error("Excel 解析器加载失败");
  }

  return new WorkbookConstructor();
}

function getSpreadsheetExtension(fileName: string) {
  return String(fileName || "")
    .split(".")
    .pop()
    ?.toLowerCase()
    .trim() || "";
}

export function isOpenClawSpreadsheetFileName(fileName: string) {
  return SUPPORTED_EXTENSIONS.has(getSpreadsheetExtension(fileName));
}

function getSafeFileName(fileName: string) {
  return String(fileName || "未命名表格")
    .replace(/[\\/\u0000-\u001f]/g, "_")
    .trim()
    .slice(0, 160) || "未命名表格";
}

function normalizeSpreadsheetCellValue(value: unknown): string {
  if (value == null) {
    return "";
  }
  if (value instanceof Date) {
    return Number.isNaN(value.getTime()) ? "" : value.toISOString();
  }
  if (typeof value === "string") {
    return value;
  }
  if (typeof value === "number" || typeof value === "boolean") {
    return String(value);
  }
  if (typeof value !== "object") {
    return String(value);
  }

  const cellObject = value as {
    error?: unknown;
    formula?: unknown;
    hyperlink?: unknown;
    result?: unknown;
    richText?: Array<{ text?: unknown }>;
    text?: unknown;
  };

  if (Array.isArray(cellObject.richText)) {
    return cellObject.richText
      .map((part) => normalizeSpreadsheetCellValue(part.text))
      .join("");
  }
  if (cellObject.result != null) {
    return normalizeSpreadsheetCellValue(cellObject.result);
  }
  if (cellObject.text != null) {
    return normalizeSpreadsheetCellValue(cellObject.text);
  }
  if (cellObject.hyperlink != null) {
    return normalizeSpreadsheetCellValue(cellObject.hyperlink);
  }
  if (cellObject.error != null) {
    return normalizeSpreadsheetCellValue(cellObject.error);
  }
  if (cellObject.formula != null) {
    return `=${normalizeSpreadsheetCellValue(cellObject.formula)}`;
  }

  return "";
}

function normalizeMarkdownCell(value: unknown) {
  const normalized = normalizeSpreadsheetCellValue(value)
    .replace(/\r\n/g, "\n")
    .replace(/\r/g, "\n")
    .replace(/\n+/g, "<br>")
    .replace(/[ \t\u00a0]+/g, " ")
    .trim();

  return normalized.replace(/\|/g, "\\|");
}

function normalizeRows(rows: unknown[][]) {
  const normalizedRows = rows
    .map((row) => row.map((cell) => normalizeMarkdownCell(cell)))
    .filter((row) => row.some((cell) => cell.length > 0));

  let columnCount = 0;
  for (const row of normalizedRows) {
    for (let index = row.length - 1; index >= 0; index -= 1) {
      if (row[index]) {
        columnCount = Math.max(columnCount, index + 1);
        break;
      }
    }
  }

  columnCount = Math.min(columnCount, OPENCLAW_SPREADSHEET_MAX_COLS_PER_SHEET);
  return normalizedRows.map((row) =>
    Array.from({ length: columnCount }, (_, index) => row[index] || "")
  );
}

export function rowsToOpenClawMarkdownTable(rows: unknown[][]) {
  const normalizedRows = normalizeRows(rows);
  if (normalizedRows.length === 0 || normalizedRows[0].length === 0) {
    return "";
  }

  const columnCount = normalizedRows[0].length;
  const firstRow = normalizedRows[0];
  const header = firstRow.map((cell, index) => cell || `列${index + 1}`);
  const bodyRows = normalizedRows.length > 1 ? normalizedRows.slice(1) : [];

  return [
    `| ${header.join(" | ")} |`,
    `| ${Array.from({ length: columnCount }, () => "---").join(" | ")} |`,
    ...bodyRows.map((row) => `| ${row.join(" | ")} |`),
  ].join("\n");
}

function buildWorksheetMarkdown(params: {
  rows: unknown[][];
  sheetName: string;
  totalRows: number;
  totalCols: number;
  truncatedBySheetLimit?: boolean;
}): WorksheetMarkdown {
  const limitedRows = params.rows.slice(0, OPENCLAW_SPREADSHEET_MAX_ROWS_PER_SHEET);
  const markdownTable = rowsToOpenClawMarkdownTable(limitedRows);
  if (!markdownTable) {
    return { markdown: "", rowCount: 0, truncated: false };
  }

  const truncated =
    params.truncatedBySheetLimit === true ||
    params.totalRows > OPENCLAW_SPREADSHEET_MAX_ROWS_PER_SHEET ||
    params.totalCols > OPENCLAW_SPREADSHEET_MAX_COLS_PER_SHEET;
  const visibleRows = Math.min(
    params.totalRows,
    OPENCLAW_SPREADSHEET_MAX_ROWS_PER_SHEET
  );
  const visibleCols = Math.min(
    params.totalCols,
    OPENCLAW_SPREADSHEET_MAX_COLS_PER_SHEET
  );
  const suffix = truncated
    ? `（展示前 ${visibleRows} 行 x ${visibleCols} 列，内容已截断）`
    : `（${params.totalRows} 行 x ${params.totalCols} 列）`;

  return {
    markdown: `### 工作表：${params.sheetName || "Sheet1"}${suffix}\n\n${markdownTable}`,
    rowCount: params.totalRows,
    truncated,
  };
}

async function parseCsvOrTsvFile(file: File, delimiter: "," | "\t") {
  const text = (await file.text()).replace(/^\uFEFF/, "");
  const parsed = Papa.parse<string[]>(text, {
    delimiter,
    skipEmptyLines: "greedy",
  });

  if (parsed.errors.length > 0) {
    throw new Error(
      `${getSafeFileName(file.name)} 解析失败：${parsed.errors[0]?.message || "未知错误"}`
    );
  }

  const rows = parsed.data;
  const totalRows = rows.filter((row) =>
    row.some((cell) => normalizeMarkdownCell(cell).length > 0)
  ).length;
  const totalCols = rows.reduce((max, row) => Math.max(max, row.length), 0);
  const sheet = buildWorksheetMarkdown({
    rows,
    sheetName: delimiter === "\t" ? "TSV" : "CSV",
    totalRows,
    totalCols,
  });
  if (!sheet.markdown) {
    throw new Error(`${getSafeFileName(file.name)} 中没有可读取的表格内容`);
  }

  return {
    markdown: `## 附件表格：${getSafeFileName(file.name)}\n\n${sheet.markdown}`.trim(),
    summary: {
      fileName: getSafeFileName(file.name),
      rowCount: sheet.rowCount,
      sheetCount: sheet.markdown ? 1 : 0,
      truncated: sheet.truncated,
    },
  };
}

async function parseXlsxFile(file: File) {
  const workbook = await createExcelWorkbook();
  const buffer = await file.arrayBuffer();
  await workbook.xlsx.load(buffer);

  const worksheets = workbook.worksheets.filter((worksheet) => {
    if (!worksheet) return false;
    let hasValue = false;
    worksheet.eachRow({ includeEmpty: false }, (row) => {
      row.eachCell({ includeEmpty: false }, (cell) => {
        if (normalizeMarkdownCell(cell.value).length > 0) {
          hasValue = true;
        }
      });
    });
    return hasValue;
  });

  if (worksheets.length === 0) {
    throw new Error(`${getSafeFileName(file.name)} 中没有可读取的表格内容`);
  }

  const parts: string[] = [];
  let rowCount = 0;
  let truncated = worksheets.length > OPENCLAW_SPREADSHEET_MAX_SHEETS_PER_FILE;
  const visibleWorksheets = worksheets.slice(
    0,
    OPENCLAW_SPREADSHEET_MAX_SHEETS_PER_FILE
  );

  for (const worksheet of visibleWorksheets) {
    let totalRows = 0;
    let totalCols = 0;
    worksheet.eachRow({ includeEmpty: false }, (row) => {
      let rowHasValue = false;
      row.eachCell({ includeEmpty: false }, (cell, colNumber) => {
        if (normalizeMarkdownCell(cell.value).length > 0) {
          rowHasValue = true;
          totalCols = Math.max(totalCols, colNumber);
        }
      });
      if (rowHasValue) {
        totalRows += 1;
      }
    });

    const rows: unknown[][] = [];
    for (
      let rowNumber = 1;
      rowNumber <= worksheet.rowCount &&
      rows.length < OPENCLAW_SPREADSHEET_MAX_ROWS_PER_SHEET;
      rowNumber += 1
    ) {
      const row = worksheet.getRow(rowNumber);
      const values = Array.from(
        { length: Math.min(totalCols, OPENCLAW_SPREADSHEET_MAX_COLS_PER_SHEET) },
        (_, index) => row.getCell(index + 1).value
      );
      if (values.some((value) => normalizeMarkdownCell(value).length > 0)) {
        rows.push(values);
      }
    }

    const sheet = buildWorksheetMarkdown({
      rows,
      sheetName: worksheet.name,
      totalRows,
      totalCols,
      truncatedBySheetLimit:
        worksheets.length > OPENCLAW_SPREADSHEET_MAX_SHEETS_PER_FILE,
    });
    if (sheet.markdown) {
      parts.push(sheet.markdown);
      rowCount += sheet.rowCount;
      truncated = truncated || sheet.truncated;
    }
  }

  return {
    markdown: `## 附件表格：${getSafeFileName(file.name)}\n\n${parts.join("\n\n")}`.trim(),
    summary: {
      fileName: getSafeFileName(file.name),
      rowCount,
      sheetCount: visibleWorksheets.length,
      truncated,
    },
  };
}

export async function spreadsheetFileToOpenClawMarkdown(file: File) {
  if (file.size <= 0) {
    throw new Error(`${getSafeFileName(file.name)} 是空文件`);
  }
  if (file.size > OPENCLAW_SPREADSHEET_MAX_BYTES) {
    throw new Error(`${getSafeFileName(file.name)} 超过 10MB，请先拆分或压缩`);
  }

  const extension = getSpreadsheetExtension(file.name);
  if (!SUPPORTED_EXTENSIONS.has(extension)) {
    throw new Error("Claw 目前支持 .xlsx、.csv、.tsv 表格文件");
  }

  if (extension === "csv") {
    return parseCsvOrTsvFile(file, ",");
  }
  if (extension === "tsv") {
    return parseCsvOrTsvFile(file, "\t");
  }
  return parseXlsxFile(file);
}

export async function buildOpenClawSpreadsheetMarkdown(
  files: File[]
): Promise<OpenClawSpreadsheetMarkdownResult> {
  const usableFiles = files.filter((file) => file.size > 0);
  if (usableFiles.length === 0) {
    return { markdown: "", files: [], truncated: false };
  }
  if (usableFiles.length > OPENCLAW_SPREADSHEET_MAX_FILES) {
    throw new Error(`一次最多上传 ${OPENCLAW_SPREADSHEET_MAX_FILES} 个表格文件`);
  }

  const parts: string[] = [];
  const summaries: OpenClawSpreadsheetFileSummary[] = [];
  let truncated = false;

  for (const file of usableFiles) {
    const parsed = await spreadsheetFileToOpenClawMarkdown(file);
    if (parsed.markdown) {
      parts.push(parsed.markdown);
    }
    summaries.push(parsed.summary);
    truncated = truncated || parsed.summary.truncated;
  }

  let markdown = parts.join("\n\n---\n\n").trim();
  if (markdown.length > OPENCLAW_SPREADSHEET_MAX_MARKDOWN_CHARS) {
    markdown = `${markdown.slice(0, OPENCLAW_SPREADSHEET_MAX_MARKDOWN_CHARS)}\n\n> 表格 Markdown 内容过长，已在本系统中截断。`;
    truncated = true;
  }

  return { markdown, files: summaries, truncated };
}

export function buildOpenClawMessageWithSpreadsheetMarkdown(params: {
  message: string;
  spreadsheetMarkdown: string;
}) {
  const prompt = params.message.trim() || "请分析上传的表格内容。";
  if (!params.spreadsheetMarkdown.trim()) {
    return prompt;
  }

  return [
    prompt,
    "以下是用户上传表格转换后的 Markdown 内容，请基于这些表格内容回答：",
    params.spreadsheetMarkdown.trim(),
  ].join("\n\n");
}

export function buildOpenClawDisplayMessage(params: {
  message: string;
  files: OpenClawSpreadsheetFileSummary[];
}) {
  const prompt = params.message.trim() || "请分析上传的表格内容";
  if (params.files.length === 0) {
    return prompt;
  }

  const fileLines = params.files.map((file) => {
    const suffix = file.truncated ? "，已截断" : "";
    return `- ${file.fileName}（${file.sheetCount} 个工作表，${file.rowCount} 行${suffix}）`;
  });

  return `${prompt}\n\n已上传表格：\n${fileLines.join("\n")}`;
}