yudao-admin-yiqun/yudao-ui-admin-vue3/src/utils/reportUtil.ts

import { buildFileUrl } from "@/utils";
import { urlToBase64 } from "@/utils/filt";
import GC from "@grapecity-software/spread-sheets";

/**
 * 将值安全转为数组（兼容 JSON 字符串和已解析的数组）
 */
function ensureArray(val: any): any[] {
  if (Array.isArray(val)) return val
  if (typeof val === 'string' && val.trim()) {
    try {
      const parsed = JSON.parse(val)
      return Array.isArray(parsed) ? parsed : []
    } catch (e) {
      return []
    }
  }
  return []
}

/**
 * 修改报表配置
 * @param sheet 报表页
 * @param res 可编辑字段
 * @param imgCol 图片字段
 * @param isPdf 是否pdf
 */
export const editReport = async (sheet, res?, imgCol?, isPdf: boolean = false) => {

  sheet.suspendPaint()
  try {
    for (let i = 0; i < sheet.getRowCount(); i++) {
      for (let j = 0; j < sheet.getColumnCount(); j++) {
        const cell = sheet.getCell(i, j);
        if (sheet.getBindingPath(i, j) && cell.value()) {
          // 值的后缀为jpg、png
          const cellValue = cell.value();

          if (typeof cellValue === 'string' && (cellValue.endsWith('.jpg') || cellValue.endsWith('.png'))) {
            if (!cellValue.includes(',')) {
              const fileUrl = buildFileUrl(cell.value())
              const base64 = await urlToBase64(fileUrl)
              let x = 0, y = 0;
              for (let col = 0; col < j; col++) {
                x += sheet.getColumnWidth(col);
              }
              for (let row = 0; row < i; row++) {
                y += sheet.getRowHeight(row);
              }
              // 拿到合并单元格的宽度和高度
              let colCount = 1, rowCount = 1
              if (sheet.getSpans(new GC.Spread.Sheets.Range(i, j, 1, 1)).length > 0) {
                colCount = sheet.getSpans(new GC.Spread.Sheets.Range(i, j, 1, 1))[0].colCount
                rowCount = sheet.getSpans(new GC.Spread.Sheets.Range(i, j, 1, 1))[0].rowCount
              }
              let columnWidth = 0
              for (let k = 0; k < colCount; k++) {
                columnWidth += sheet.getColumnWidth(k + j);
              }
              let rowHeight = 0
              for (let k = 0; k < rowCount; k++) {
                rowHeight += sheet.getRowHeight(k + i);
              }
              // 边框宽度
              const a = cell.borderLeft()?.style | 1
              const b = cell.borderTop()?.style | 1
              const c = cell.borderRight()?.style | 1
              const d = cell.borderBottom()?.style | 1
              console.log(cellValue)
              const addPictureShape = sheet.shapes.addPictureShape(cellValue, base64, x + a, y + b, columnWidth - c, rowHeight - d);
              addPictureShape.allowMove(false)
              addPictureShape.allowResize(false)
              addPictureShape.allowRotate(false)
              addPictureShape.isLocked(true)
            } else {
              // 多个图片
              const imgArr: string[] = []
              for (const item of cellValue.split(',')) {
                const fileUrl = buildFileUrl(item)
                const base64 = await urlToBase64(fileUrl)
                imgArr.push(base64)
              }
              // 图片位置
              let x = 0, y = 0;
              for (let col = 0; col < j; col++) {
                x += sheet.getColumnWidth(col);
              }
              for (let row = 0; row < i; row++) {
                y += sheet.getRowHeight(row);
              }
              // 拿到合并单元格的宽度和高度
              let colCount = 1, rowCount = 1
              if (sheet.getSpans(new GC.Spread.Sheets.Range(i, j, 1, 1)).length > 0) {
                colCount = sheet.getSpans(new GC.Spread.Sheets.Range(i, j, 1, 1))[0].colCount
                rowCount = sheet.getSpans(new GC.Spread.Sheets.Range(i, j, 1, 1))[0].rowCount
              }
              let columnWidth = 0
              for (let k = 0; k < colCount; k++) {
                columnWidth += sheet.getColumnWidth(k + j);
              }
              let rowHeight = 0
              for (let k = 0; k < rowCount; k++) {
                rowHeight += sheet.getRowHeight(k + i);
              }
              // 边框宽度
              const a = cell.borderLeft()?.style | 1
              const b = cell.borderTop()?.style | 1
              const c = cell.borderRight()?.style | 1
              const d = cell.borderBottom()?.style | 1
              const imgW = (columnWidth - c - a) / imgArr.length
              let imgX = x + a
              for (const base64 of imgArr) {
                const addPictureShape = sheet.shapes.addPictureShape(cellValue, base64, imgX, y + b, imgW, rowHeight - d)
                addPictureShape.allowMove(false)
                addPictureShape.allowResize(false)
                addPictureShape.allowRotate(false)
                addPictureShape.isLocked(true)
                imgX += imgW
              }
            }
          }
          if (imgCol && imgCol.includes(sheet.getBindingPath(i, j))) {
            if (cellValue && cellValue.startsWith('[') && cellValue.endsWith(']')) {
              cell.value(null)
              const imgArr = JSON.parse(cellValue)
              for (const item of imgArr) {
                const fileUrl = buildFileUrl(item.url)
                const base64 = await urlToBase64(fileUrl)
                sheet.shapes.addPictureShape(JSON.stringify(item), base64, item.x, item.y, item.width, item.height)
              }
            }
          }
        }
        if (sheet.getRange(i, j).cellType() && sheet.getRange(i, j).cellType().typeName === '5') {
          if (cell.value() != 'true') {
            cell.value(null)
          } else {
            cell.value(true)
          }
        }
      }
    }

    // 填充浮动图片
    const text = sheet.getDataSource().rT.Illustration;
    if (text) {
      const illustration = ensureArray(text)
      const remainingItems: any[] = []
      for (const item of illustration) {
        if (item.sheet === sheet.name()) {
          const fileUrl = buildFileUrl(item.url)
          const base64 = await urlToBase64(fileUrl)
          sheet.shapes.addPictureShape(JSON.stringify(item), base64, item.x, item.y, item.width, item.height)
        } else {
          remainingItems.push(item)
        }
      }
      sheet.getDataSource().rT.Illustration = JSON.stringify(remainingItems)
    }
    if (isPdf) {
      return
    }
    const spreadNS = GC.Spread.Sheets;
    const cfs = sheet.conditionalFormats
    const style = new spreadNS.Style();
    style.backColor = "#FFF895";
    const arr: any[] = []
    const cellRange = sheet.getRange(0, 0, sheet.getRowCount(), sheet.getColumnCount());
    cellRange.allowEditInCell(false);
    for (let i = 0; i < sheet.getRowCount(); i++) {
      for (let j = 0; j < sheet.getColumnCount(); j++) {
        if (sheet.getBindingPath(i, j) && res && res.includes(sheet.getBindingPath(i, j))) {
          setTimeout(() => {
            sheet.getCell(i, j).allowEditInCell(true)
          }, 1)
          // 一个个设置背景颜色太费时间了
          // setTimeout(() => {
          //   sheet.getRange(i, j, 1, 1).backColor("#FFF895")
          // }, 1)
          arr.push(new spreadNS.Range(i, j, 1, 1))
        }
      }
    }
    // 优化性能版
    // 设置可编辑背景
    cfs.addSpecificTextRule(
      spreadNS.ConditionalFormatting.ComparisonOperators.greaterThanOrEqualsTo,
      null,
      style,
      arr
    );
    // 设置必填
    // cfs.addCellValueRule(
    //   spreadNS.ConditionalFormatting.ComparisonOperators.equalsTo,
    //   null,null,
    //   style,
    //    arr
    // );
  } finally {
    sheet.resumePaint()
    sheet.repaint()
  }
}


/**
 * 添加复制事件
 * @param spread 工作簿
 */
export const handleCopy = (spread) => {
  let bindingPathRec = new Map();
  spread.bind(GC.Spread.Sheets.Events.ClipboardPasting, function (_e, info) {
    const cellRange = info.cellRange;
    bindingPathRec = new Map()
    // 边界检查
    if (
      !info.sheet ||
      !cellRange ||
      cellRange.rowCount <= 0 ||
      cellRange.colCount <= 0
    ) {
      console.error("Invalid fill range or sheet reference.");
      return;
    }

    try {
      // 处理 pasteData.text，去除末尾的制表符
      let processedText = info.pasteData.text;
      if (typeof processedText === 'string') {
        processedText = processedText.replace(/\t+$/, '');
      }
      for (let i = 0; i < cellRange.rowCount; i++) {
        for (let j = 0; j < cellRange.colCount; j++) {
          const path = info.sheet.getBindingPath(cellRange.row + i, cellRange.col + j);
          const compositeKey = `${cellRange.row + i},${cellRange.col + j}`;
          bindingPathRec.set(compositeKey, {
            row: cellRange.row + i,
            col: cellRange.col + j,
            path: path,
            text: processedText
          });
        }
      }
    } catch (getErr) {
      console.error("Error getting binding path:", getErr);
    }
  });

  spread.bind(GC.Spread.Sheets.Events.ClipboardPasted, function (_e, info) {
    bindingPathRec.forEach(value => {
      info.sheet.setBindingPath(value.row, value.col, value.path)
      info.sheet.setValue(value.row, value.col, value.text)
    })
  })
  let bindingPathRecMap = new Map();
  spread.bind(GC.Spread.Sheets.Events.DragFillBlock, function (_e, info) {
    console.log(info)
    const fillRange = info.fillRange;
    bindingPathRecMap = new Map()
    // 边界检查
    if (
      !info.sheet ||
      !fillRange ||
      fillRange.rowCount <= 0 ||
      fillRange.colCount <= 0
    ) {
      console.error("Invalid fill range or sheet reference.");
      return;
    }

    try {
      for (let row = fillRange.row; row < fillRange.row + fillRange.rowCount; row++) {
        for (let col = fillRange.col; col < fillRange.col + fillRange.colCount; col++) {
          // 获取当前单元格的bindingPath
          const path = info.sheet.getBindingPath(row, col);

          // Matthew：注意，没有path时也不能跳过，因为绑定路径为undefined也是一种绑定路径
          // 如果path存在，则记录；否则，跳过该单元格
          // 使用复合键存储信息
          const compositeKey = `${row},${col}`;
          bindingPathRecMap.set(compositeKey, {
            row: row,
            col: col,
            path: path,
          });
        }
      }

    } catch (getErr) {
      console.error("Error getting binding path:", getErr);
    }
  });

  spread.bind(GC.Spread.Sheets.Events.DragFillBlockCompleted, function (_e, info) {
    bindingPathRecMap.forEach(value => {
      info.sheet.setBindingPath(value.row, value.col, value.path)
    })
  })
}

/**
 * 从 sheet 数据源提取所有字段值（兼容 table 绑定字段）
 */
export const collectSheetDataSource = (sheet): Record<string, any> => {
  console.log(sheet.getDataSource())
  if(!sheet.getDataSource()) return {}
  const rT = sheet.getDataSource().rT
  const result: Record<string, any> = { ...rT }

  const tables = sheet.tables?.all() || []
  for (const table of tables) {
    const tableBindingPath = table.bindingPath()
    if (tableBindingPath) {
      const tableData = rT[tableBindingPath]
      if (Array.isArray(tableData)) {
        result[tableBindingPath] = tableData
      }
    }
  }
  return result
}

/**
 * 将数据源值序列化为后端存储格式（对象/数组 → JSON 字符串）
 */
export const normalizeValueForStorage = (val: any): any => {
  if (val === null || val === undefined) return val
  if (typeof val === 'object') return JSON.stringify(val)
  return val
}

/**
 * 递归遍历整个 dataSource，将所有值序列化
 */
export const serializeDataSourceForStorage = (dataSource: Record<string, any>): Record<string, any> => {
  const result: Record<string, any> = {}
  for (const key in dataSource) {
    result[key] = normalizeValueForStorage(dataSource[key])
  }
  return result
}

/**
 * 收集 sheet 中的形状（浮动图片）数据，合并到 dataSource 中
 */
export const collectShapesIntoDataSource = (sheet, dataSource: Record<string, any>) => {
  sheet.shapes.all().forEach(shape => {
    if (shape.name() && shape.name().startsWith('{') && shape.name().endsWith('}')) {
      try {
        const data = JSON.parse(shape.name())
        data.x = shape.x()
        data.y = shape.y()
        data.width = shape.width()
        data.height = shape.height()

        const existing = dataSource[data.path]
        if (existing !== undefined && existing !== null) {
          const arr = ensureArray(existing)
          arr.push(data)
          dataSource[data.path] = arr
        } else {
          dataSource[data.path] = [data]
        }
      } catch (e) {
        // 忽略解析失败的形状
      }
    }
  })
}