审查视图

app/utils/fileExport/export2Excel.ts 4.8 KB
202304001 authored
1
/* eslint-disable */
2
import { UPLOAD_FILE_MAX_LINE } from "@/app/constant";
202304001 authored
3
import * as XLSX from "xlsx";
202304001 authored
4
import { removeDeepThink } from "../deepThink";
202304001 authored
5 6

export function toExcel(content: string) {
202304001 authored
7
  content = removeDeepThink(content);
8 9 10 11 12 13 14 15 16 17 18 19 20 21
  if (hasTable(content)) {
    htmlToExcel(content);
  } else {
    markdownToExcel(content);
  }
}

export function hasTable(htmlContent: string): boolean {
  const parser = new DOMParser();
  const doc = parser.parseFromString(htmlContent, "text/html");
  return doc.querySelector("table") !== null;
}

export function markdownToExcel(content: string) {
202304001 authored
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
  let sheetName = "result"; // 默认表名
  let tableContent = "";

  // 查找标题内容(以 ** 或 ## 或 ### 开头)
  const titleMatch = content.match(/^(#{2,3}|\*\*)\s*(.*)/m);
  if (titleMatch) {
    sheetName = titleMatch[2].trim().replace(/\s+/g, "_"); // 使用标题作为表名
    // 提取表格内容(跳过标题部分)
    tableContent = content.substring(titleMatch[0].length).trim();
  } else {
    tableContent = content;
  }

  // 查找表格的起始位置(第一行以 | 开头且以 | 结尾)
  let tableStartIndex = -1;
  const lines = tableContent.split("\n");
  for (let i = 0; i < lines.length; i++) {
    if (lines[i].trim().startsWith("|") && lines[i].trim().endsWith("|")) {
      tableStartIndex = i;
      break;
202304001 authored
42
    }
202304001 authored
43
  }
202304001 authored
44
202304001 authored
45
  if (tableStartIndex === -1) {
46
    throw new Error("表格内容未找到");
202304001 authored
47 48 49 50 51 52 53 54 55
    return;
  }

  // 查找表格的结束位置(遇到不以 | 开头或者不以 | 结尾的行)
  let tableEndIndex = -1;
  for (let i = tableStartIndex; i < lines.length; i++) {
    if (!lines[i].trim().startsWith("|") || !lines[i].trim().endsWith("|")) {
      tableEndIndex = i;
      break;
202304001 authored
56
    }
202304001 authored
57 58 59 60 61 62 63 64 65 66 67
  }

  if (tableEndIndex === -1) {
    tableEndIndex = lines.length;
  }

  // 提取表格内容
  const tableData = lines
    .slice(tableStartIndex, tableEndIndex)
    .join("\n")
    .trim();
202304001 authored
68
202304001 authored
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
  // 解析表格内容
  const rows = tableData.split("\n");
  const data: any[] = [];
  let headers: string[] = [];

  rows.forEach((row, rowIndex) => {
    // 去掉行首尾的 | 符号,并按 | 分割
    const cells = row
      .replace(/^\s*\|/g, "")
      .replace(/\|\s*$/g, "")
      .split(/\s*\|\s*/);

    // 跳过分隔线(假设分隔线的每个单元格都是由短横线组成)
    if (
      rowIndex > 0 &&
      cells.every(
        (cell) => cell.trim().replace(/-/g, "").length === 0, // 检查单元格内容是否只包含短横线
      )
    ) {
      return;
202304001 authored
89 90
    }
202304001 authored
91 92 93 94 95 96 97 98 99
    if (rowIndex === 0) {
      // 第一行是表头
      headers = cells.map((cell) => cell.trim());
    } else {
      // 数据行
      const rowData: any = {};
      cells.forEach((cell, cellIndex) => {
        if (cellIndex < headers.length) {
          rowData[headers[cellIndex]] = cell.trim();
202304001 authored
100
        }
202304001 authored
101 102
      });
      data.push(rowData);
202304001 authored
103
    }
202304001 authored
104
  });
202304001 authored
105
202304001 authored
106 107 108 109
  // 创建工作表
  const ws = XLSX.utils.json_to_sheet(data);
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, sheetName);
202304001 authored
110
202304001 authored
111 112 113 114 115 116 117
  // 生成文件并下载
  XLSX.writeFile(wb, `${sheetName}.xlsx`);
}

export function getExcelData(file: File): Promise<any[][]> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();
202304001 authored
118
202304001 authored
119 120 121 122 123 124
    reader.onload = (e) => {
      try {
        const data = e.target?.result;
        if (!data) {
          reject(new Error("Failed to read file data"));
          return;
202304001 authored
125
        }
202304001 authored
126 127 128 129 130 131 132 133
        const workbook = XLSX.read(data, { type: "array" });
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        // 使用类型断言将 unknown[] 转换为 any[][]
        const jsonData = XLSX.utils.sheet_to_json(worksheet, {
          header: 1,
        }) as any[][];
134
        resolve(jsonData.slice(0, UPLOAD_FILE_MAX_LINE));
202304001 authored
135 136 137 138 139 140 141
      } catch (error) {
        reject(error);
      }
    };
    reader.onerror = (error) => {
      reject(error);
    };
202304001 authored
142
202304001 authored
143 144 145
    reader.readAsArrayBuffer(file);
  });
}
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171

export function htmlToExcel(htmlContent: string) {
  const parser = new DOMParser();
  const doc = parser.parseFromString(htmlContent, "text/html");
  const table = doc.querySelector("table");
  if (!table) {
    return [];
  }
  const rows = table.querySelectorAll("tr");
  const tableData: string[][] = [];
  // 提取表格数据(包含表头)
  rows.forEach((row) => {
    const cells = row.querySelectorAll("td, th");
    const rowData: string[] = [];
    cells.forEach((cell) => {
      rowData.push(cell.textContent?.trim() || "");
    });
    tableData.push(rowData);
  });
  // 使用二维数组直接创建工作表
  const ws = XLSX.utils.aoa_to_sheet(tableData);
  // 创建工作簿并保存
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
  XLSX.writeFile(wb, "export.xlsx");
}