前端常见的文件操作

记录一下工作中用到的文件操作

文件的预览

微软的在线预览功能

注意: 文件需要能够公开访问

1
2
3

// 前缀: https://view.officeapps.live.com/op/view.aspx?src=
// url: 文件地址
window.open(`https://view.officeapps.live.com/op/view.aspx?src=${encodeURIComponent(url)}`);

使用微软的在线预览功能预览文件
- Word https://view.officeapps.live.com/op/view.aspx?src=https://cdn.zxiaosi.com/hexo/office/temp.docx
- Excel https://view.officeapps.live.com/op/view.aspx?src=https://cdn.zxiaosi.com/hexo/office/temp.xlsx
- PPT https://view.officeapps.live.com/op/view.aspx?src=https://cdn.zxiaosi.com/hexo/office/temp.pptx
Edge 浏览器可以直接预览的文件

kkFileView 部署文件预览服务

独立部署，向外提供http预览服务（外部系统只需要访问本项目预览接口并传入需要预览文件的url，就可以打开预览页面）
在线体验地址

WebOffice 开放平台收费

HTML2Canvas + JsPDF 导出PDF

官网链接

HTML2Canvas 将HTML转换为图片
JsPDF 将图片转换为PDF

代码示例

工具类 exportPdf.ts

import html2canvas from "html2canvas";
import jsPDF from "jspdf";

/**
 * 参考:
 *  - https://juejin.cn/post/7138370283739545613
 *  - https://gitee.com/jseven68/vue-pdf2
 */

interface OutPutPDFParams {
  /** 页眉dom元素 */
  header?: HTMLElement;
  /** 页脚dom元素 */
  footer?: HTMLElement;
  /** 需要转换的dom根节点 */
  element: HTMLElement;
  /** 一页pdf的内容宽度，0-592.28 */
  contentWidth?: number;
  /** pdf文件名 */
  filename?: string;
}

interface CanvasData {
  width: number;
  height: number;
  data: string;
}

/** A4纸的宽度 */
const A4_WIDTH = 592.28;

/** A4纸的高度 */
const A4_HEIGHT = 841.89;

/**
 * 将元素转化为canvas元素
 * @param element 需要转化的元素
 * @param width 内容宽度
 * @returns 返回canvas元素的宽度、高度、转化后的图片Data
 */
async function toCanvas(element: HTMLElement, width: number): Promise<CanvasData> {
  // canvas元素
  const canvas = await html2canvas(element, {
    // allowTaint: true, // 允许渲染跨域图片
    scale: window.devicePixelRatio * 2, // 通过 放大 增加清晰度
    useCORS: true, // 允许跨域
  });

  // 获取canavs转化后的宽度
  const canvasWidth = canvas.width;

  // 获取canvas转化后的高度
  const canvasHeight = canvas.height;

  // 高度转化为PDF的高度
  const height = (width / canvasWidth) * canvasHeight;

  // 转化成图片Data
  const canvasData = canvas.toDataURL("image/jpeg", 1.0);

  return { width, height, data: canvasData };
}

/**
 * 添加页眉
 * @param header 页眉元素
 * @param pdf jsPDF实例
 * @param contentWidth 内容宽度
 */
async function addHeader(header: HTMLElement, pdf: jsPDF, contentWidth: number) {
  const { height: headerHeight, data: headerData } = await toCanvas(header, contentWidth);
  pdf.addImage(headerData, "JPEG", 0, 0, contentWidth, headerHeight);
}

/**
 * 添加页脚
 * @param pageSize 页数
 * @param pageNo 当前页码
 * @param footer 页脚元素
 * @param pdf jsPDF实例
 * @param contentWidth 内容宽度
 */
async function addFooter(pageSize: number, pageNo: number, footer: HTMLElement, pdf: jsPDF, contentWidth: number) {
  // 页码元素，类名这里写死了
  const pageNoDom = footer.querySelector(".pdf-footer-page") as HTMLElement;
  const pageSizeDom = footer.querySelector(".pdf-footer-page-count") as HTMLElement;
  if (pageNoDom) pageNoDom.innerText = pageNo + "";
  if (pageSizeDom) pageSizeDom.innerText = pageSize + "";

  const { height: footerHeight, data: footerData } = await toCanvas(footer, contentWidth);
  pdf.addImage(footerData, "JPEG", 0, A4_HEIGHT - footerHeight, contentWidth, footerHeight);
}

/**
 * 截取图片添加到pdf中
 * @param _x x坐标
 * @param _y y坐标
 * @param pdf jsPDF实例
 * @param data 图片data
 * @param width 宽度
 * @param height 高度
 */
function addImage(_x: number, _y: number, pdf: jsPDF, data: string, width: number, height: number) {
  pdf.addImage(data, "JPEG", _x, _y, width, height);
}

/**
 * 添加空白遮挡
 * @param x x 与 页面左边缘的坐标
 * @param y y 与 页面上边缘的坐标
 * @param width 填充宽度
 * @param height 填充高度
 * @param pdf jsPDF实例
 */
function addBlank(x: number, y: number, width: number, height: number, pdf: jsPDF) {
  pdf.setFillColor(255, 255, 255);
  pdf.rect(x, y, Math.ceil(width), Math.ceil(height), "F");
}

/**
 * 获取元素距离网页顶部的距离
 * @param element dom元素
 * @returns 距离顶部的高度
 */
function getElementTop(element: any) {
  let actualTop = element.offsetTop;
  let current = element.offsetParent;

  // 通过遍历offsetParant获取距离顶端元素的高度值
  while (current && current !== null) {
    actualTop += current.offsetTop;
    current = current.offsetParent;
  }

  return actualTop;
}

/**
 * 生成pdf(A4多页pdf截断问题， 包括页眉、页脚 和 上下左右留空的护理)
 * - 内容与页眉、页脚之间留空留白的高度 - baseY
 * - 强制分页的标记点 - split-page
 * - 自动分页标记 - divide-inside
 * @param props {@link OutPutPDFParams}
 */
export async function outputPDF({ element, contentWidth = 550, footer, header, filename = "temp.pdf" }: OutPutPDFParams) {
  // 如果 dom根节点 不存在, 则直接返回
  if (!(element instanceof HTMLElement)) return;

  // jsPDFs实例
  const pdf = new jsPDF({ unit: "pt", format: "a4", orientation: "p" });

  /** 根元素距离网页顶部的距离 */
  const elementTop = getElementTop(element) || 0;

  /** 页眉元素的高度 */
  let headerHeight = 0;

  /** 页脚元素的高度 */
  let footerHeight = 0;

  if (header) {
    // 页眉元素 经过转换后在PDF的高度
    const headerCanvas = await toCanvas(header, contentWidth);
    headerHeight = headerCanvas.height;
  }

  if (footer) {
    // 页脚元素 经过转换后在PDF页面的高度
    const footerCanvas = await toCanvas(footer, contentWidth);
    footerHeight = footerCanvas.height;
  }

  // 距离 PDF 左边的距离, / 2 表示居中 - 左右边距
  const baseX = (A4_WIDTH - contentWidth) / 2;

  // 距离 PDF 页眉和页脚的间距, 留白留空
  const baseY = 15;

  // 除去页眉、页脚、还有内容与两者之间的间距后, 每页内容的实际高度
  const originalPageHeight = A4_HEIGHT - headerHeight - footerHeight - 2 * baseY;

  // 元素在网页页面的宽度
  const elementWidth = element.offsetWidth;

  // PDF内容宽度 和 在HTML中宽度 的比, 用于将 元素在网页的高度 转化为 PDF内容内的高度, 将 元素距离网页顶部的高度  转化为 距离Canvas顶部的高度
  const rate = contentWidth / elementWidth;

  // 每一页的分页坐标, PDF高度， 初始值为根元素距离顶部的距离
  const pages = [0];

  /**
   * 普通元素 - 位置更新方法
   * @param top 元素距离顶部的高度
   */
  function updateNormalElPos(top: number) {
    const prevTop = pages.length > 0 ? pages[pages.length - 1] : 0;

    // 当前距离顶部高度 - 上一个分页点的高度 大于 正常一页的高度, 则需要载入分页点
    if (top - prevTop > originalPageHeight) pages.push(prevTop + originalPageHeight);
  }

  /**
   * 存在可能跨页的元素 - 位置更新方法
   * @param elHeight 元素高度
   * @param top 元素距离顶部的高度
   */
  function updatePos(elHeight: number, top: number) {
    const prevTop = pages.length > 0 ? pages[pages.length - 1] : 0;

    // 同 updateNormalElPos 方法
    if (top - prevTop >= originalPageHeight) {
      pages.push(prevTop + originalPageHeight);
      return;
    }

    // 当前距离顶部高度 + 元素自身高度 - 上一个分页点的高度 大于 一页内容的高度, 则证明元素跨页
    // top != prevTop 这个条件是防止多个元素嵌套情况下，他们 top 是一样的
    if (top + elHeight - prevTop > originalPageHeight && top !== prevTop) {
      pages.push(top);
      return;
    }
  }

  /**
   * 对于富文本元素，观察所得段落之间都是以<p> / <img> 元素相隔，因此不需要进行深度遍历 (仅针对个人遇到的情况)
   */
  function traversingEditor(nodes: NodeListOf<ChildNode>) {
    // 遍历子节点
    for (let i = 0; i < nodes.length; ++i) {
      const one = nodes[i] as HTMLElement;
      const { offsetHeight } = one;
      const offsetTop = getElementTop(one);
      const top = (contentWidth / elementWidth) * offsetTop;
      updatePos((contentWidth / elementWidth) * offsetHeight, top);
    }
  }

  /** 遍历正常的元素节点 */
  function traversingNodes(nodes: NodeListOf<ChildNode>) {
    for (let i = 0; i < nodes.length; ++i) {
      /** 当前节点 */
      const one = nodes[i] as HTMLElement;

      /** 分页标记 - 强制分页的标记点 */
      const isSplit = one.classList && one.classList.contains("split-page");

      /** 自动分页标记 - 根据元素高度自动判断是否需要分页 */
      const isDivideInside = one.classList && one.classList.contains("divide-inside");

      /** 终点元素 - 图片元素作为深度终点, 不再继续深入 */
      const isIMG = one.tagName === "IMG";

      /** 终点元素 - table的每一行元素也作为深度终点, 不再继续深入, 自定义(antd table 组件) */
      const isTableCol = one.classList && one.classList.contains("ant-table-row");

      /** 特殊元素 - 富文本元素 */
      const isEditor = one.classList && one.classList.contains("editor");

      // 对需要处理分页的元素, 计算是否跨界, 若跨界, 则直接将顶部位置作为分页位置, 进行分页, 且子元素不需要再进行判断
      const { offsetHeight } = one;

      // 计算出距离顶部最终高度 【减去根元素距离网页顶部的高度】
      const offsetTop = getElementTop(one) - elementTop;

      // dom转换后距离顶部的高度 => 转换成canvas高度
      const top = rate * offsetTop;

      if (isSplit) {
        pages.push(top); // 将当前高度作为分页位置
        traversingNodes(one.childNodes); // 执行深度遍历操作
        continue;
      }

      if (isDivideInside) {
        updatePos(rate * offsetHeight, top); // 执行位置更新操作
        traversingNodes(one.childNodes); // 执行深度遍历操作
        continue;
      }

      if (isTableCol || isIMG) {
        // dom高度转换成生成pdf的实际高度
        // 代码不考虑dom定位、边距、边框等因素, 需在dom里自行考虑, 如将box-sizing设置为border-box
        updatePos(rate * offsetHeight, top);
        continue;
      }

      if (isEditor) {
        updatePos(rate * offsetHeight, top); // 执行位置更新操作
        traversingEditor(one.childNodes); // 遍历富文本节点
        continue;
      }

      // 对于普通元素, 则判断是否高度超过分页值, 并且深入
      updateNormalElPos(top); // 执行位置更新操作
      traversingNodes(one.childNodes); // 遍历子节点
    }

    return;
  }

  // 深度遍历节点的方法
  traversingNodes(element.childNodes);

  // 一页的高度， 转换宽度为一页元素的宽度
  const { width, height, data } = await toCanvas(element, contentWidth);

  // 可能会存在遍历到底部元素为深度节点，可能存在最后一页位置未截取到的情况
  // if (pages[pages.length - 1] + originalPageHeight < height) {
  //   pages.push(pages[pages.length - 1] + originalPageHeight);
  // }

  // 根据分页位置 开始分页
  for (let i = 0; i < pages.length; ++i) {
    console.log(`%c共${pages.length}页, 生成第${i + 1}页`, "color: yellow");

    // 向 PDF 中添加 canvas 图片 (dom元素)
    addImage(baseX, baseY + headerHeight - pages[i], pdf, data, width, height);

    // 将 内容 与 页眉之间留空留白的部分进行遮白处理
    addBlank(0, headerHeight, A4_WIDTH, baseY, pdf);

    // 将 内容 与 页脚之间留空留白的部分进行遮白处理
    addBlank(0, A4_HEIGHT - baseY - footerHeight, A4_WIDTH, baseY, pdf);

    // 对于除最后一页外，对 内容 的多余部分进行遮白处理
    if (i < pages.length - 1) {
      // 获取当前页面需要的内容部分高度
      const imageHeight = pages[i + 1] - pages[i];

      // 对多余的内容部分进行遮白
      addBlank(0, baseY + imageHeight + headerHeight, A4_WIDTH, A4_HEIGHT - imageHeight, pdf);
    }

    // 添加页眉
    if (header) await addHeader(header, pdf, A4_WIDTH);

    // 添加页脚
    if (footer) await addFooter(pages.length, i + 1, footer, pdf, A4_WIDTH);

    // 若不是最后一页，则分页
    if (i !== pages.length - 1) {
      // 增加分页
      pdf.addPage();
    }
  }

  return pdf.save(filename);
}

使用示例

import { Button } from "antd";
import styles from "./index.module.less";
import { outputPDF } from "../../utils/exportPdf";

const Index = () => {
  /** 下载 */
  const handleDownload = async () => {
    const element = document.getElementById("pdfContent");

    if (element) {
      try {
        await outputPDF({ element, contentWidth: 560, filename: "temp.pdf" });
      } catch (error) {
        console.log(error);
      }
    }
  };

  return (
    <div className={styles.page}>
      <Button type="primary" onClick={handleDownload}>
        下载
      </Button>

      {/* 不设置宽度, 默认以 父元素的的宽度 为 PDF宽度   */}
      <div className={styles.pdf}>
        <div id="pdfContent" className={styles.pdfContent}>
          <div>我是内容</div>
          <img src="" alt="" crossOrigin="anonymous" />
           {/* 图片一定要加 crossOrigin 参数 */}
           {/* <img src="" alt="" crossOrigin="anonymous" /> */}
        </div>
      </div>
    </div>
  );
};

export default Index;

功能演示

Fortune-Sheet + ExcelJS 导入导出Excel

官网链接

代码示例

工具类 enum.ts

/** 边框样式枚举 */
export const borderStyleMap = {
  "1": "thin", // 细边框
  "2": "hair", // 虚线边框
  "3": "dotted", // 点状边框
  "4": "dashed", // 虚线边框
  "5": "dashDot", // 点划线边框
  "6": "dashDotDot", // 双点划线边框
  "7": "double", // 双边框
  "8": "medium", // 中等边框
  "9": "mediumDashed", // 中等虚线边框
  "10": "mediumDashDot", // 中等点划线边框
  "11": "mediumDashDotDot", // 中等双点划线边框
  "12": "slantDashDot", // 斜线点划线边框
  "13": "thick", // 粗边框
};

/** 水平对齐枚举 */
export const verticalAlignmentMap = {
  "0": "middle", // 居中对齐
  "1": "top", // 顶部对齐
  "2": "bottom", // 底部对齐
};

/** 垂直对齐枚举 */
export const horizontalAlignmentMap = {
  "0": "center", // 居中对齐
  "1": "left", // 左对齐
  "2": "right", // 右对齐
};

/** 文本换行枚举 */
export const wrapTextMap = {
  "1": false, // 溢出
  "2": true, // 自动换行
};

/** 文本角度枚举 */
export const textRotationMap = {
  "1": 45, // 向上倾斜
  "2": -45, // 向下倾斜
  "3": "vertical", // 竖排文字
  "4": 90, // 向上90°
  "5": -90, // 向下90°
};

/**
 * 根据key获取value
* @param map 枚举对象
* @param key 枚举key
*/
export function getValueByKey(map: Record<string, any>, key: any): any {
  return map[key];
}

/**
 * 据value获取key
* @param map 枚举对象
* @param value 枚举value
*/
export function getKeyByValue(map: Record<string, any>, value: any): any {
  return Object.keys(map).find((key) => map[key] === value);
}

工具类 tool.ts

/** 将单元格坐标转为对应的行列索引 */
export const handleCellToRowCol = (cell: string) => {
  // 将字母部分转换为大写
  const colIndex = cell.substring(0, 1).toUpperCase();
  // 提取数字部分并转换为数字
  const row = parseInt(cell.substring(1));
  // 将列转换为从 1 开始的数字，A 对应 1，B 对应 2，依此类推
  const col = colIndex.charCodeAt(0) - "A".charCodeAt(0) + 1;
  return { row, col };
};

/**
 * 转换内部链接引用
 * @param reference - 内部链接引用
 */
export const handleConvertReference = (reference: string) => {
  // 使用正则表达式分隔工作表名称和单元格范围
  const regex = /^(.*)!(.*)$/;
  const match = reference.match(regex);

  if (match) {
    let sheetName = match[1];
    const cellRange = match[2];

    // 检查工作表名称是否已经用单引号包裹
    if (!sheetName.startsWith("'")) {
      sheetName = `${sheetName}`;
    }

    // 在原始引用前添加 #
    const convertedReference = cellRange ? `#${sheetName}!${cellRange}` : `#${sheetName}`;

    return convertedReference;
  } else {
    // 如果输入格式不正确，返回原始引用
    return reference;
  }
};

/**
 * 将 ARGB 颜色值转换为 RGB 格式
 */
function handleRgbToHex(r, g, b) {
  return ((1 << 24) + (r << 16) + (g << 8) + b).toString(16).slice(1);
}

/**
 * 将 RGB 颜色值转换为 ARGB 格式
 */
export function handleColorToARGB(color: string, defaultColor?: string) {
  if (!color) return defaultColor || undefined;

  let hex = "";

  // 如果颜色值是十六进制格式
  if (color?.startsWith("#")) {
    hex = color?.slice(1);
  }
  // 如果颜色值是 RGB 格式
  else if (color?.startsWith("rgb")) {
    const rgbValues = color?.match(/\d+/g);
    hex = handleRgbToHex(Number(rgbValues?.[0]), Number(rgbValues?.[1]), Number(rgbValues?.[2]));
  }

  // 补全颜色值长度为 6 位
  if (hex?.length === 3) {
    hex = hex.replace(/(.)/g, "$1$1");
  }

  // 将 ARGB 值返回
  return "FF" + hex?.toUpperCase();
}

/**
 * 将 ARGB 颜色值转换为 RGB 格式
 */
export function handleArgbToHex(argb?: string) {
  if (!argb) return undefined;
  return argb.replace("FF", "#");
}

/**
 * 处理类型
 * @param type 类型
 * @param v1 计算值1
 * @param v2 计算值2
 */
export const handleOperator = (type: string, v1?: any, v2?: any) => {
  switch (type) {
    case "between":
      return `介于${v1}和${v2}之间`;
    case "notBetween":
      return `不介于${v1}和${v2}之间`;
    case "equal":
      return `等于${v1}`;
    case "notEqual":
      return `不等于${v1}`;
    case "greaterThan":
      return `大于${v1}`;
    case "lessThan":
      return `小于${v1}`;
    case "greaterThanOrEqual":
      return `大于等于${v1}`;
    case "lessThanOrEqual":
      return `小于等于${v1}`;
    default:
      return "";
  }
};

/** 获取最大值 */
export const handleMaxValue = (...numbers: any[]) => {
  const filteredNumbers = numbers.filter((num) => typeof num === "number");
  if (filteredNumbers.length === 0) return 0; // 没有有效的数值参数
  return Math.max(...filteredNumbers);
};

工具类 exportExcel.ts

import { Sheet, Cell, SheetConfig } from "@fortune-sheet/core";
import ExcelJS, { Alignment, Border, BorderStyle, Borders, CellModel, CellValue, DataValidation, Fill, Font, Workbook, Worksheet, WorksheetView } from "exceljs";
import { handleConvertReference, handleColorToARGB, handleOperator } from "./tool";
import { borderStyleMap, getValueByKey, horizontalAlignmentMap, textRotationMap, verticalAlignmentMap, wrapTextMap } from "./enum";

/**
 * excel 单元格默认的宽度和高度 是 8.43 和 15
 * - https://support.microsoft.com/zh-cn/office/%E6%9B%B4%E6%94%B9%E5%88%97%E5%AE%BD%E5%92%8C%E8%A1%8C%E9%AB%98-72f5e3cc-994d-43e8-ae58-9774a0905f46?ns=excel&version=21&syslcid=2052&uilcid=2052&appver=zxl210&helpid=20926&ui=zh-cn&rs=zh-cn&ad=cn
 *
 * fortune-sheet 单元格默认的宽度和高度 是 73 和 19
 * - https://ruilisi.github.io/fortune-sheet-docs/zh/guide/sheet.html#%E5%88%9D%E5%A7%8B%E5%8C%96%E9%85%8D%E7%BD%AE
 */

/** excel 默认列宽 */
const excelColWidth = 8.43;

/** excel 默认行高 */
const excelRowHeight = 15;

/** fortune-sheet 默认列宽 */
const defaultColWidth = 73;

/** fortune-sheet 默认行高 */
const defaultRowHeight = 19;

/** fortune-sheet 默认行数 */
const defaultRows = 36;

/** fortune-sheet 默认列数 */
const defaultColumns = 18;

/**
 * 处理行高、列宽、隐藏行、隐藏列
 */
const handleColumnRowLen = (worksheet: Worksheet, config: Sheet["config"]) => {
  if (!config) return worksheet;

  const { columnlen, rowlen, rowhidden, colhidden } = config;
  const conversionWidth = defaultColWidth / excelColWidth;
  const conversionHeight = defaultRowHeight / excelRowHeight;

  // 列长度
  if (columnlen) {
    const columns = Object.keys(columnlen);
    columns.forEach((column) => {
      worksheet.getColumn(Number(column) + 1).width = (columnlen?.[column] ?? 0) / conversionWidth;
    });
  }

  // 行高度
  if (rowlen) {
    const rows = Object.keys(rowlen);
    rows.forEach((row) => {
      worksheet.getRow(Number(row) + 1).height = (rowlen?.[row] ?? 0) / conversionHeight;
    });
  }

  // 隐藏行
  if (rowhidden) {
    const rows = Object.keys(rowhidden);
    rows.forEach((row) => {
      worksheet.getRow(Number(row) + 1).hidden = true;
    });
  }

  // 隐藏列
  if (colhidden) {
    const columns = Object.keys(colhidden);
    columns.forEach((column) => {
      worksheet.getColumn(Number(column) + 1).hidden = true;
    });
  }

  return worksheet;
};

/**
 * 处理图片: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%9B%BE%E7%89%87
 * - excelJs 不会处理已变化单元格的宽度 https://github.com/exceljs/exceljs/issues/2730
 * @param workbook 工作簿
 * @param worksheet 工作表
 * @param sheetData 工作表数据
 */
const handleImages = (workbook: Workbook, worksheet: Worksheet, sheetData: Sheet) => {
  const { row, column, images, config } = sheetData;

  if (!images) return worksheet;

  const { colhidden, columnlen, rowhidden, rowlen } = config || {};

  const columnObj = { ...colhidden, ...columnlen }; // 列对象
  const rowObj = { ...rowhidden, ...rowlen }; // 行对象

  const rowLen = row || defaultRows; // 行数
  const columnLen = column || defaultColumns; // 列数

  for (let i = 0; i < images?.length; i++) {
    const { src, height, width, left, top } = images?.[i] || {};

    let col = 0; // 图片所在列 0~1 表示一个单元格
    let row = 0; // 图片所在行 0~1 表示一个单元格
    let restLeft = left; // 剩余宽度
    let restTop = top; // 剩余高度

    for (let i = 0; i <= rowLen; i++) {
      const minuend = columnObj[i] || defaultColWidth;
      if (restLeft - minuend > 0) {
        restLeft -= minuend;
        col++;
      } else {
        col += restLeft / minuend;
        break;
      }
    }

    for (let i = 0; i <= columnLen; i++) {
      const minuend = rowObj[i] || defaultRowHeight;
      if (restTop - minuend > 0) {
        restTop -= minuend;
        row++;
      } else {
        row += restTop / minuend;
        break;
      }
    }

    if (col === 0) col = left / defaultColWidth; // 如果不存在自定义列和隐藏列, 设置列默认值

    if (row === 0) row = top / defaultRowHeight; // 如果不存在自定义行和隐藏行, 设置行默认值

    const imageId = workbook.addImage({ base64: src, extension: "png" });
    worksheet.addImage(imageId, { tl: { col, row }, ext: { width, height }, editAs: "absolute" });
  }

  return worksheet;
};

/**
 * 处理边框: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E8%BE%B9%E6%A1%86
 * @param worksheet 工作表
 * @param borderInfo 边框信息
 */
const handleBorder = (worksheet: Worksheet, borderInfo: any[] = []) => {
  if (!borderInfo) return worksheet;

  borderInfo?.forEach((border) => {
    let realBorder = {} as Partial<Borders>;
    const { row, column } = border?.range?.[0] || {};

    const style = getValueByKey(borderStyleMap, border?.style) as BorderStyle;

    switch (border?.borderType) {
      case "border-left" /* 左框线 */:
      case "border-right" /* 右框线 */:
      case "border-top" /* 上框线 */:
      case "border-bottom" /* 下框线 */: {
        const key = border?.borderType?.slice?.(7);
        const currentCell = worksheet.getCell(row?.[0] + 1, column?.[0] + 1);
        currentCell.border = { ...currentCell.border, [key]: { style, color: { argb: handleColorToARGB(border?.color) } } };
        break;
      }
      case "border-none" /* 无 */:
      case "border-all" /* 所有 */: {
        const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
        realBorder = { top: borderObj, left: borderObj, bottom: borderObj, right: borderObj };
        for (let i = row?.[0]; i <= row?.[1]; i++) {
          for (let j = column?.[0]; j <= column?.[1]; j++) {
            worksheet.getCell(i + 1, j + 1).border = realBorder;
          }
        }
        break;
      }
      case "border-inside" /* 内侧 */: {
        const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;

        // 内侧横线的范围，从 row[0] + 1 到 row[1] - 1 行，从 column[0] 到 column[1] 列
        for (let i = row?.[0] + 1; i <= row?.[1] - 1; i++) {
          for (let j = column?.[0]; j <= column?.[1]; j++) {
            const cell = worksheet.getCell(i + 1, j + 1);
            cell.border = { top: borderObj, bottom: borderObj };
          }
        }

        // 内侧竖线的范围，从 row[0] 到 row[1] 行，从 column[0] + 1 到 column[1] - 1 列
        for (let i = row?.[0]; i <= row?.[1]; i++) {
          for (let j = column?.[0] + 1; j <= column?.[1] - 1; j++) {
            const cell = worksheet.getCell(i + 1, j + 1);
            cell.border = { ...cell.border, left: borderObj, right: borderObj };
          }
        }

        break;
      }
      case "border-outside" /* 外侧 */: {
        const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
        // 外侧边框的范围，从 row[0] 到 row[1] 行，从 column[0] 到 column[1] 列
        for (let i = row?.[0]; i <= row?.[1]; i++) {
          for (let j = column?.[0]; j <= column?.[1]; j++) {
            // 设置外侧边框，仅设置最外侧一圈
            if (i === row[0] || i === row[1] || j === column[0] || j === column[1]) {
              const cell = worksheet.getCell(i + 1, j + 1);
              cell.border = {
                top: i === row[0] ? borderObj : undefined,
                bottom: i === row[1] ? borderObj : undefined,
                left: j === column[0] ? borderObj : undefined,
                right: j === column[1] ? borderObj : undefined,
              };
            }
          }
        }
        break;
      }
      case "border-horizontal" /* 内侧横线 */: {
        const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
        // 内侧横线的范围，从 row[0] + 1 到 row[1] - 1 行，从 column[0] 到 column[1] 列
        for (let i = row?.[0] + 1; i <= row?.[1] - 1; i++) {
          for (let j = column?.[0]; j <= column?.[1]; j++) {
            const cell = worksheet.getCell(i + 1, j + 1);
            cell.border = { top: borderObj, bottom: borderObj };
          }
        }
        break;
      }
      case "border-vertical" /* 内侧竖线 */: {
        const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
        // 内侧竖线的范围，从 row[0] 到 row[1] 行，从 column[0] + 1 到 column[1] - 1 列
        for (let i = row?.[0]; i <= row?.[1]; i++) {
          for (let j = column?.[0] + 1; j <= column?.[1] - 1; j++) {
            const cell = worksheet.getCell(i + 1, j + 1);
            cell.border = { left: borderObj, right: borderObj };
          }
        }
        break;
      }
      case "border-slash" /* 边框斜线 */: {
        const borderObj = { up: false, down: true, style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
        for (let i = row?.[0]; i <= row?.[1]; i++) {
          for (let j = column?.[0]; j <= column?.[1]; j++) {
            const cell = worksheet.getCell(i + 1, j + 1);
            cell.border = { diagonal: borderObj };
          }
        }
        break;
      }
      default:
        break;
    }
  });

  return worksheet;
};

/**
 * 处理冻结视图: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%86%BB%E7%BB%93%E8%A7%86%E5%9B%BE
 * @param worksheet Worksheet 工作表
 * @param frozen Sheet["frozen"] 冻结视图
 */
const handleFrozen = (worksheet: Worksheet, frozen: Sheet["frozen"]): Worksheet["views"] => {
  if (frozen) {
    const { type, range } = frozen;
    const column_focus = Number(range?.column_focus || -1) + 1;
    const row_focus = Number(range?.row_focus || -1) + 1;
    const otherView: Partial<WorksheetView> = worksheet.views[0];

    switch (type) {
      case "rangeColumn":
        return [{ ...otherView, state: "frozen", xSplit: column_focus, ySplit: 0, style: undefined }];
      case "rangeRow":
        return [{ ...otherView, state: "frozen", xSplit: 0, ySplit: row_focus, style: undefined }];
      case "both":
        return [{ ...otherView, state: "frozen", xSplit: column_focus, ySplit: row_focus, style: undefined }];
      default:
        return worksheet.views;
    }
  } else {
    return worksheet.views;
  }
};

/**
 * 处理自动筛选器: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E8%87%AA%E5%8A%A8%E7%AD%9B%E9%80%89%E5%99%A8
 * - excelJs 没有 filter 参数
 * @param filter_select 自动筛选器
 */
const handleAutoFilter = (filter_select: Sheet["filter_select"]): Worksheet["autoFilter"] => {
  if (filter_select && Object.keys(filter_select).length > 0) {
    const { row, column } = filter_select;
    return {
      from: { row: row[0], column: column[0] },
      to: { row: row[1], column: column[1] },
    };
  } else {
    return undefined;
  }
};

/**
 * 处理公式值: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%85%AC%E5%BC%8F%E5%80%BC
 * @param f 公式
 * @param v 值
 */
const handleFormula = (f: string, v: string | number | boolean | undefined) => {
  return { formula: f, result: v };
};

/**
 * 处理超链接: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E8%B6%85%E9%93%BE%E6%8E%A5%E5%80%BC
 * @param hyperlink 超链接
 * @param r 行
 * @param c 列
 * @param v 真实值
 */
const handleHyperlink = (hyperlink: Sheet["hyperlink"], r: number, c: number, v: any): CellValue => {
  const hl = hyperlink?.[r + "_" + c];

  switch (hl?.linkType) {
    case "webpage" /** 网页跳转 */:
      return { text: v, hyperlink: hl?.linkAddress, tooltip: hl?.linkAddress };
    case "cellrange" /** 文件内跳转 */:
      return { text: v, hyperlink: handleConvertReference(hl?.linkAddress) };
    case "sheet" /** 工作表跳转 */:
      return { text: v, hyperlink: handleConvertReference(hl?.linkAddress + "!A1") };
  }
};

/**
 * 处理正常值
 * @param ct 单元格类型
 * @param m 显示值
 */
const handleNormalValue = (ct: Cell["ct"], m: any) => {
  // 对数字类型的值特殊处理
  return ct?.fa === "General" && ct?.t === "n" ? Number(m) : m || null;
};

/**
 * 处理数字格式: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E6%95%B0%E5%AD%97%E6%A0%BC%E5%BC%8F
 * @param ct 单元格类型
 */
const handleNumFmt = (ct: Cell["ct"]) => {
  return ct?.fa || "";
};

/**
 * 处理字体: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%AD%97%E4%BD%93
 * @param cell 单元格
 */
const handleFont = (cell: Cell): Partial<Font> => {
  const { ff, fs, bl, it, un, cl, fc } = cell;

  return {
    // @ts-ignore
    name: ff,
    size: fs || 10,
    bold: bl === 1, // 加粗
    italic: it === 1, // 斜体
    underline: un === 1, // 下划线
    strike: cl === 1, // 删除线
    color: { argb: handleColorToARGB(fc!, "FF000000") }, // 颜色
  };
};

/**
 * 处理背景色: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%A1%AB%E5%85%85
 * @param bg 背景色
 */
const handleBackground = (bg: string): Fill => {
  return { type: "pattern", pattern: "solid", fgColor: { argb: handleColorToARGB(bg, "FFFFFFFF") } };
};

/**
 * 处理对齐方式: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%AF%B9%E9%BD%90
 * @param cell 单元格
 */
const handleAlignment = (cell: Cell): Partial<Alignment> => {
  const { vt, ht, tb, rt } = cell;

  const alignment: Partial<Alignment> = {
    vertical: undefined,
    horizontal: "left",
    textRotation: 0,
  };

  switch (tb + "") {
    case "0" /* 截断 */:
      alignment.horizontal = "fill";
      break;
    case "1" /* 溢出 */:
    case "2" /* 自动换行 */:
      alignment.wrapText = getValueByKey(wrapTextMap, vt + "");
      break;
  }

  alignment.vertical = getValueByKey(verticalAlignmentMap, vt + ""); // 垂直对齐

  alignment.horizontal = getValueByKey(horizontalAlignmentMap, ht + ""); // 水平对齐

  alignment.textRotation = getValueByKey(textRotationMap, rt + ""); // 文本角度

  return alignment;
};

/**
 * 处理合并单元格: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%90%88%E5%B9%B6%E5%8D%95%E5%85%83%E6%A0%BC
 * @param worksheet  工作表
 * @param merge 合并单元格信息
 * @param r 行
 * @param c 列
 */
const handleMergeCells = (worksheet: Worksheet, merge: SheetConfig["merge"], r: number, c: number) => {
  const mergeObj = merge?.[r + "_" + c];
  if (mergeObj) {
    const { r: merge_r, c: merge_c, rs: merge_rs, cs: merge_cs } = mergeObj;
    worksheet.mergeCells(merge_r + 1, merge_c + 1, merge_r + merge_rs, merge_c + merge_cs);
  }

  return worksheet;
};

/**
 * 处理注释/批注: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%8D%95%E5%85%83%E6%A0%BC%E6%B3%A8%E9%87%8A
 * @param ps 批注
 */
const handleNote = (ps: Cell["ps"]): string | CellModel["comment"] => {
  // exceljs 不支持注释换行 x.x
  const texts = ps?.value?.split("<br>").map((_) => ({ text: _ }));
  return { texts: texts, editAs: "twoCells" };
};

/**
 * 数据验证: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E6%95%B0%E6%8D%AE%E9%AA%8C%E8%AF%81
 * @param dataVerification 数据验证
 * @param r 行
 * @param c 列
 * @param v 值
 */
const handleDataValidation = (dataVerification: Sheet["dataVerification"], r: number, c: number): DataValidation => {
  const dv = dataVerification?.[r + "_" + c];

  switch (dv?.type) {
    case "dropdown" /* 下拉框 */: {
      return {
        type: "list",
        formulae: [`"${dv?.value1}"`],
        allowBlank: true,
        showInputMessage: dv?.hintShow,
        // promptTitle: "提示",
        prompt: dv?.hintValue,
        showErrorMessage: dv?.prohibitInput,
        // errorTitle: "错误",
        error: "你选择的不是下拉列表中的选项",
      };
    }
    case "number_integer" /* 整数 */: {
      const tip = handleOperator(dv?.type2, dv?.value1, dv?.value2);

      return {
        type: "whole",
        operator: dv?.type2,
        formulae: [Number(dv?.value1), Number(dv?.value2)],
        allowBlank: true,
        showInputMessage: dv?.hintShow,
        prompt: dv?.hintValue,
        showErrorMessage: dv?.prohibitInput,
        error: `你输入的不是${tip}的整数`,
      };
    }
    case "number_decimal" /* 小数 */: {
      const tip = handleOperator(dv?.type2, dv?.value1, dv?.value2);

      return {
        type: "decimal",
        operator: dv?.type2,
        formulae: [Number(dv?.value1), Number(dv?.value2)],
        allowBlank: true,
        showInputMessage: dv?.hintShow,
        prompt: dv?.hintValue,
        showErrorMessage: dv?.prohibitInput,
        error: `你输入的不是${tip}的小数`,
      };
    }
    case "text_length" /* 文本长度 */: {
      const tip = handleOperator(dv?.type2, dv?.value1, dv?.value2);

      return {
        type: "textLength",
        operator: dv?.type2,
        formulae: [Number(dv?.value1), Number(dv?.value2)],
        allowBlank: true,
        showInputMessage: dv?.hintShow,
        prompt: dv?.hintValue,
        showErrorMessage: dv?.prohibitInput,
        error: `你输入的不是长度${tip}的文本`,
      };
    }
    case "date" /* 日期 */: {
      const tip = handleOperator(dv?.type2, dv?.value1, dv?.value2);

      return {
        type: "date",
        operator: dv?.type2,
        formulae: [new Date(dv?.value1), new Date(dv?.value2)],
        allowBlank: true,
        showInputMessage: dv?.hintShow,
        prompt: dv?.hintValue,
        showErrorMessage: dv?.prohibitInput,
        error: `你输入的不是${tip}的日期`,
      };
    }
    case "number" /* 数字 */:
    case "text_content" /** 文本内容 */:
    case "validity" /* 有效性 */:
    case "checkbox" /* 复选框 */:
    default:
      // 这里处理的都是 exceljs 不支持的类型 - x.x
      return { type: "custom", formulae: [], allowBlank: true };
  }
};

/**
 * 下载Excel
 * @param workbook ExcelJS.Workbook
 * @param fileName 文件名
 */
export const handleDownloadExcel = async (workbook: Workbook, fileName: string = "temp.xlsx") => {
  // 将工作簿转换为二进制数据
  const buffer = await workbook.xlsx.writeBuffer();
  const blob = new Blob([buffer], { type: "application/octet-stream" });
  const url = window.URL.createObjectURL(blob);

  // 创建一个 a 标签
  const link = document.createElement("a");
  link.href = url;
  link.setAttribute("download", fileName);
  document.body.appendChild(link);
  link.click();

  // 释放资源
  window.URL.revokeObjectURL(url);
  document.body.removeChild(link);
};

/**
 * 导出Excel
 * - 问题1: 列宽和行高改变时会影响图片的位置
 * - 问题2: excelJs 没有 filter 参数
 * @param workbookData 工作簿数据
 * @param fileName 文件名
 */
export const handleExportExcel = async (workbookData: Sheet[], fileName: string) => {
  // 创建一个工作簿
  const workbook = new ExcelJS.Workbook();

  // 工作表排序
  const newWorkbookData = [...workbookData]?.sort((a, b) => (a.order ?? 0) - (b.order ?? 0));

  newWorkbookData.map((sheetData) => {
    const { name, hide, zoomRatio = 1, data, config, frozen, filter_select, dataVerification, hyperlink }: Sheet = sheetData!;

    let worksheet = workbook.addWorksheet(name, {
      // 工作表隐藏: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%B7%A5%E4%BD%9C%E8%A1%A8%E7%8A%B6%E6%80%81
      state: hide === 1 ? "hidden" : "visible",
      // 视图百分比: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%B7%A5%E4%BD%9C%E8%A1%A8%E8%A7%86%E5%9B%BE
      views: [{ zoomScale: zoomRatio * 100 }],
      properties: {
        /**
        * 列宽是自动计算的, 行高是15
        * https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%B7%A5%E4%BD%9C%E8%A1%A8%E5%B1%9E%E6%80%A7
        */
        defaultColWidth: excelColWidth,
        defaultRowHeight: excelRowHeight,
      },
    });

    // 处理行高、列宽、隐藏行、隐藏列
    worksheet = handleColumnRowLen(worksheet, config);

    // 处理图片
    worksheet = handleImages(workbook, worksheet, sheetData);

    // 处理边框
    worksheet = handleBorder(worksheet, config?.borderInfo);

    // 处理冻结视图
    worksheet.views = handleFrozen(worksheet, frozen);

    // 处理筛选器
    worksheet.autoFilter = handleAutoFilter(filter_select);

    // 处理数据
    data?.forEach((row, r) => {
      const newRow = row.map((cell, c) => {
        if (!cell) return null;

        const { f, m, v, ct, hl, bg, ps } = cell; // 单元格

        const currentCell = worksheet.getCell(r + 1, c + 1); // 获取单元格

        // currentCell.model.type = 1; // 默认类型
        // currentCell.model.text = m ? m + "" : undefined; // 默认文本
        // currentCell.model.value = v; // 默认值

        if (f) currentCell.value = handleFormula(f, v); // 公式值
        else if (hl) currentCell.value = handleHyperlink(hyperlink, r, c, v); // 超链接
        else currentCell.value = handleNormalValue(ct, m || v); // 正常值

        if (ct) currentCell.numFmt = handleNumFmt(ct); // 数字格式

        if (cell) currentCell.font = handleFont(cell); // 字体

        if (bg) currentCell.fill = handleBackground(bg); // 背景色

        if (cell) currentCell.alignment = handleAlignment(cell); // 对齐方式

        if (ps) currentCell.note = handleNote(ps); // 注释

        if (dataVerification) currentCell.dataValidation = handleDataValidation(dataVerification, r, c); // 数据验证

        if (config?.merge) worksheet = handleMergeCells(worksheet, config?.merge, r, c); // 合并单元格
      });

      worksheet.addRow(newRow);
    });
  });

  // 下载Excel
  await handleDownloadExcel(workbook, fileName);
};

工具类 importExcel.ts

import ExcelJS, { Worksheet } from "exceljs";
import { Cell, CellWithRowAndCol, Sheet, SheetConfig } from "@fortune-sheet/core";
import { handleArgbToHex, handleCellToRowCol, handleMaxValue } from "./tool";
import { borderStyleMap, getKeyByValue, horizontalAlignmentMap, textRotationMap, wrapTextMap } from "./enum";

/** excel 默认列宽 */
const excelColWidth = 8.43;

/** excel 默认行高 */
const excelRowHeight = 15;
// const excelRowHeight = 14.25;

/** fortune-sheet 默认列宽 */
const defaultColWidth = 73;

/** fortune-sheet 默认行高 */
const defaultRowHeight = 19;

/** fortune-sheet 默认行数 */
const defaultRows = 36;

/** fortune-sheet 默认列数 */
const defaultColumns = 18;

/**
 * 导入Excel
 * - 问题1: 列宽和行高改变时会影响图片的位置
 * - 问题2: excelJS 获取不到工作表内的超链接
 * - 问题3: fortune-sheet 数字格式(百分比、货币等)不生效
 * - 问题4: fortune-sheet 斜线边框方法不生效
 * @param file 文件
 */
export const handleImportExcel = async (file: File) => {
  try {
    // 初始化数据
    const excelData: Sheet[] = [];

    // 读取Excel文件
    const buffer = await file.arrayBuffer();
    const workbook = new ExcelJS.Workbook();
    const worksheets = await workbook.xlsx.load(buffer);

    // 遍历所有的工作表
    worksheets.eachSheet((worksheet: Worksheet) => {
      const { name, rowCount, columnCount, state, views, autoFilter } = worksheet;

      // 声明变量
      let frozen = {} as Sheet["frozen"]; // 冻结区域
      const filter_select = {} as { row: number[]; column: number[] }; // 筛选
      const config = {} as SheetConfig; // 配置
      const images = [] as Sheet["images"]; // 图片
      const hyperlink = {} as Record<string, { linkType: string; linkAddress: string }>; // 超链接
      const calcChain = [] as Sheet["calcChain"]; // 公式链
      const dataVerification = {} as any; // 数据验证
      const celldata = [] as CellWithRowAndCol[]; // 单元格数据

      // 最大行数和列数
      const maxRow = handleMaxValue(rowCount, defaultRows);
      const maxCol = handleMaxValue(columnCount, defaultColumns);

      // 是否隐藏
      const hide = state === "visible" ? 0 : 1;

      // 缩放比例
      const zoomRatio = (views[0]?.zoomScale || 100) / 100;

      // 冻结区域 - 控制台会报错: https://github.com/ruilisi/fortune-sheet/issues/434
      if (views[0].state === "frozen") {
        const { xSplit = 0, ySplit = 0 } = views[0]; // 选中区域
        if (xSplit > 0 && ySplit > 0) {
          frozen = { type: "both", range: { row_focus: ySplit - 1, column_focus: xSplit - 1 } };
        } else if (xSplit === 0) {
          frozen = { type: "rangeRow", range: { row_focus: ySplit - 1, column_focus: 0 } };
        } else if (ySplit > 0) {
          frozen = { type: "rangeColumn", range: { row_focus: 0, column_focus: xSplit - 1 } };
        }
      }

      // 自动筛选器
      if (autoFilter) {
        filter_select.row = [autoFilter.from.row, autoFilter.to.row];
        filter_select.column = [autoFilter.from.col, autoFilter.to.col];
      }

      // 处理列宽、隐藏列
      for (let colNumber = 1; colNumber <= maxCol; colNumber++) {
        const curCol = worksheet.getColumn(colNumber);
        if (curCol.width) {
          if (config.columnlen === undefined) config.columnlen = {};
          config.columnlen[colNumber - 1] = curCol.width * (defaultColWidth / excelColWidth);
        }

        if (curCol.hidden) {
          if (config.colhidden === undefined) config.colhidden = {};
          config.colhidden[colNumber - 1] = 0;
        }
      }

      // 处理行高、隐藏行
      for (let rowNumber = 1; rowNumber <= maxRow; rowNumber++) {
        const curRow = worksheet.getRow(rowNumber);
        if (curRow.height) {
          if (config.rowlen === undefined) config.rowlen = {};
          config.rowlen[rowNumber - 1] = curRow.height * (defaultRowHeight / excelRowHeight);
        }

        if (curRow.hidden) {
          if (config.rowhidden === undefined) config.rowhidden = {};
          config.rowhidden[rowNumber - 1] = 0;
        }
      }

      // 处理图片: excelJs 不会处理已变化单元格的宽度 https://github.com/exceljs/exceljs/issues/2730
      const sheetImages = worksheet.getImages();
      if (sheetImages && sheetImages.length > 0) {
        sheetImages.map((item) => {
          const { imageId, range } = item;

          // 获取图像的数据
          const image = workbook.getImage(Number(imageId));

          // 图片的base64
          const base64 = `data:${image.type}/${image.extension};base64,${image.buffer?.toString("base64")}`;

          // 获取图像的左上角和右下角的单元格位置
          const topLeftCell = range.tl;
          const bottomRightCell = range.br;

          // 获取单元格的行高和列宽，并计算图像的宽度和高度
          let realWidth = 0;
          let realHeight = 0;

          // 图片在单元格内
          if (Math.floor(topLeftCell.row) === Math.floor(bottomRightCell.row)) {
            const curRow = Math.floor(topLeftCell.row) + 1;
            const height = Number(worksheet.getRow(curRow).height || 0) * (defaultRowHeight / excelRowHeight) || defaultRowHeight;
            realHeight = height * (bottomRightCell.row - topLeftCell.row);
          } else {
            for (let row = Math.floor(topLeftCell.row); row < Math.floor(bottomRightCell.row) + 1; row++) {
              const height = worksheet.getRow(row).height * (defaultRowHeight / excelRowHeight) || defaultRowHeight;
              if (row < topLeftCell.row) {
                realHeight += height * (topLeftCell.row - row);
              } else if (row > bottomRightCell.row) {
                realHeight += height * (1 - (row - bottomRightCell.row));
              } else {
                realHeight += height;
              }
            }
          }

          if (Math.floor(topLeftCell.col) === Math.floor(bottomRightCell.col)) {
            const curCol = Math.floor(topLeftCell.col) + 1;
            const width = Number(worksheet?.getColumn(curCol)?.width || 0) * (defaultColWidth / excelColWidth) || defaultColWidth;
            realWidth = width * (bottomRightCell.col - topLeftCell.col);
          } else {
            for (let col = topLeftCell.col; col < bottomRightCell.col; col++) {
              const width = Number(worksheet?.getColumn(col)?.width || 0) * (defaultColWidth / excelColWidth) || defaultColWidth;
              if (col < topLeftCell.col) {
                realWidth += width * (topLeftCell.col - col);
              } else if (col > bottomRightCell.col) {
                realWidth += width * (1 - (col - bottomRightCell.col));
              } else {
                realWidth += width;
              }
            }
          }

          let realLeft = 0; // 计算图像的左边距
          let realTop = 0; // 计算图像的上边距
          for (let col = 1; col < topLeftCell.col + 1; col++) {
            const left = Number(worksheet?.getColumn(col)?.width || 0) * (defaultColWidth / excelColWidth) || defaultColWidth;
            if (col < topLeftCell.col) {
              realLeft += left;
            } else {
              realLeft += left * (1 - (col - topLeftCell.col));
            }
          }
          for (let row = 1; row < topLeftCell.row + 1; row++) {
            const top = worksheet.getRow(row).height * (defaultRowHeight / excelRowHeight) || defaultRowHeight;
            if (row < topLeftCell.row) {
              realTop += top;
            } else {
              realTop += top * (1 - (row - topLeftCell.row));
            }
          }

          images?.push({
            id: imageId,
            src: base64,
            width: realWidth,
            height: realHeight,
            left: realLeft,
            top: realTop,
          });
        });
      }

      // 设置单元格
      worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
        row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
          if (cell.type !== 0) console.log("cell", cell.model);

          const currentCell = {} as CellWithRowAndCol;
          currentCell.r = Number(rowNumber) - 1;
          currentCell.c = Number(colNumber) - 1;
          currentCell.v = {} as Cell;

          const { type, value, font, formula, result, numFmt, alignment, fill, note, dataValidation, border } = cell;

          switch (type) {
            case 0 /** Null */:
              currentCell.v.v = null;
              break;
            case 1 /** Merge */: {
              // 起始点
              const { row: start_r, col: start_c } = handleCellToRowCol(cell.model.master);
              // 当前点
              const { row: end_r, col: end_c } = handleCellToRowCol(cell.address);

              // 合并单元格 - 1.设置mc
              const mc = { r: start_r - 1, c: start_c - 1 };
              currentCell.v.mc = mc;

              // 合并单元格 - 2.设置merge
              const merge = { r: start_r - 1, c: start_c - 1, rs: end_r - start_r + 1, cs: end_c - start_c + 1 };
              config.merge = { ...config.merge, [`${start_r - 1}_${start_c - 1}`]: merge };

              break;
            }
            case 2 /** Number */:
              currentCell.v.ht = 2; // 右对齐
              currentCell.v.v = value + "";
              currentCell.v.ct = { fa: numFmt || "General", t: "n" };
              break;
            case 3 /** String */:
              currentCell.v.v = value + "";
              currentCell.v.ct = { fa: "General", t: "s" };
              break;
            case 4 /** Date */:
              currentCell.v.ct = { fa: numFmt || "General", t: "d" };
              break;
            case 5 /** Hyperlink */:
              currentCell.v.v = value?.text;
              hyperlink[`${currentCell.r}_${currentCell.c}`] = {
                linkType: "webpage",
                linkAddress: value?.hyperlink,
              };
              break;
            case 6 /** Formula */:
              currentCell.v.ht = 2; // 右对齐
              currentCell.v.f = "=" + formula;
              currentCell.v.v = result;
              currentCell.v.ct = { fa: "General", t: "n" };
              // 等待excel加载完成后, 手动计算公式 - ref.current?.calculateFormula();
              // calcChain?.push({ r: currentCell.r, c: currentCell.c });
              break;
          }

          // 边框
          if (border && Object.keys(border).length > 0) {
            if (config.borderInfo === undefined) config.borderInfo = [];

            const borderKeys = Object.keys(border) || [];
            if (borderKeys.includes("left")) {
              config.borderInfo?.push({
                rangeType: "range",
                borderType: "border-left",
                style: getKeyByValue(borderStyleMap, border.left?.style || "thin"),
                color: handleArgbToHex(border.left?.color?.argb || "FF000000"),
                range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
              });
            }

            if (borderKeys.includes("right")) {
              config.borderInfo?.push({
                rangeType: "range",
                borderType: "border-right",
                style: getKeyByValue(borderStyleMap, border.right?.style || "thin"),
                color: handleArgbToHex(border.right?.color?.argb || "FF000000"),
                range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
              });
            }

            if (borderKeys.includes("top")) {
              config.borderInfo?.push({
                rangeType: "range",
                borderType: "border-top",
                style: getKeyByValue(borderStyleMap, border.top?.style || "thin"),
                color: handleArgbToHex(border.top?.color?.argb || "FF000000"),
                range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
              });
            }

            if (borderKeys.includes("bottom")) {
              config.borderInfo?.push({
                rangeType: "range",
                borderType: "border-bottom",
                style: getKeyByValue(borderStyleMap, border.bottom?.style || "thin"),
                color: handleArgbToHex(border.bottom?.color?.argb || "FF000000"),
                range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
              });
            }

            // 斜线 fortune-sheet 不生效
            if (borderKeys.includes("diagonal")) {
              config.borderInfo?.push({
                rangeType: "range",
                borderType: "border-slash",
                style: getKeyByValue(borderStyleMap, border.diagonal?.style || "thin"),
                color: handleArgbToHex(border.diagonal?.color?.argb || "FF000000"),
                range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
              });
            }
          }

          // 字体
          if (font) {
            if (font.name) currentCell.v.ff = font.name;
            if (font.size) currentCell.v.fs = font.size;
            if (font.bold) currentCell.v.bl = 1;
            if (font.italic) currentCell.v.it = 1;
            if (font.underline) currentCell.v.un = 1;
            if (font.strike) currentCell.v.cl = 1;
            if (font.color) currentCell.v.fc = handleArgbToHex(font.color.argb);
          }

          // 背景色
          if (fill && fill.type === "pattern") {
            currentCell.v.bg = handleArgbToHex(fill?.fgColor?.argb);
          }

          // 对齐方式
          if (alignment) {
            // 水平对齐
            if (alignment.horizontal) {
              switch (alignment.horizontal) {
                case "left" /* 左对齐 */:
                case "center" /* 居中对齐 */:
                case "right" /* 右对齐 */:
                  currentCell.v.ht = Number(getKeyByValue(horizontalAlignmentMap, alignment.horizontal));
                  break;
                case "fill" /* 截断 */:
                  currentCell.v.tb = "0";
                  break;
              }
            }

            // 垂直对齐
            if (alignment.vertical) {
              currentCell.v.vt = Number(getKeyByValue(horizontalAlignmentMap, alignment.vertical));
            }

            // 自动换行
            if (alignment.wrapText) {
              currentCell.v.tb = getKeyByValue(wrapTextMap, alignment.wrapText);
            }

            // 文本角度
            if (alignment.textRotation) {
              currentCell.v.rt = Number(getKeyByValue(textRotationMap, alignment.textRotation));
            }
          }

          // 注释
          if (note) {
            const noteVal = note?.texts ? note.texts.map((_) => _.text).join("") : note || null;
            currentCell.v.ps = {
              left: null,
              top: null,
              width: null,
              height: null,
              value: noteVal,
              isShow: false,
            };
          }

          // 数据验证
          if (dataValidation) {
            const { type, formulae, showInputMessage, prompt, showErrorMessage, operator } = dataValidation;
            switch (type) {
              case "list" /** 下拉列表 */:
                dataVerification[`${currentCell.r}_${currentCell.c}`] = {
                  type: "dropdown",
                  value1: JSON.parse(formulae?.[0]),
                  checked: false,
                  hintShow: showInputMessage,
                  hintValue: prompt,
                  prohibitInput: showErrorMessage,
                };
                break;
              case "whole" /** 整数 */:
                dataVerification[`${currentCell.r}_${currentCell.c}`] = {
                  type: "number_integer",
                  type2: operator,
                  value1: formulae?.[0] + "",
                  value2: formulae?.[1] + "",
                  hintShow: showInputMessage,
                  hintValue: prompt,
                  prohibitInput: showErrorMessage,
                };
                break;
              case "decimal" /** 小数 */:
                dataVerification[`${currentCell.r}_${currentCell.c}`] = {
                  type: "number_decimal",
                  type2: operator,
                  value1: formulae?.[0] + "",
                  value2: formulae?.[1] + "",
                  hintShow: showInputMessage,
                  hintValue: prompt,
                  prohibitInput: showErrorMessage,
                };
                break;
              case "textLength" /** 文本长度 */:
                dataVerification[`${currentCell.r}_${currentCell.c}`] = {
                  type: "text_length",
                  type2: operator,
                  value1: formulae?.[0] + "",
                  value2: formulae?.[1] + "",
                  hintShow: showInputMessage,
                  hintValue: prompt,
                  prohibitInput: showErrorMessage,
                };
                break;
              case "date" /** 日期 */:
                dataVerification[`${currentCell.r}_${currentCell.c}`] = {
                  type: "date",
                  type2: operator,
                  value1: formulae?.[0] + "",
                  value2: formulae?.[1] + "",
                  hintShow: showInputMessage,
                  hintValue: prompt,
                  prohibitInput: showErrorMessage,
                };
                break;
            }
          }

          celldata.push(currentCell);
        });
      });

      // 处理单元格
      const mergeKeys = Object.keys(config.merge || {});
      mergeKeys.forEach((key: any) => {
        const [r, c] = key.split("_");
        const cell = celldata.find((item) => item.r === Number(r) && item.c === Number(c)) as CellWithRowAndCol;
        cell.v.mc = config.merge?.[key];
        celldata.push(cell);
      });

      excelData.push({
        name,
        row: maxRow,
        column: maxCol,
        hide,
        zoomRatio,
        frozen,
        filter_select,
        config,
        images,
        hyperlink,
        calcChain,
        dataVerification,
        celldata,
      });
    });

    return excelData;
  } catch (e) {
    console.error("报错~", e);
    return false;
  }
};