wisdomcity-office/src/components/view_file/vendors/xlsx/Table.vue

<template>
  <div>
    <div ref="spreadsheet" class="spreadsheet-container" id="spreadsheet"></div>
  </div>
</template>

<script>
import Spreadsheet from "x-data-spreadsheet"
import { indexedColors } from "./color"
import zhCN from "x-data-spreadsheet/dist/locale/zh-cn"
import _ from "lodash-es"
import tinycolor from "tinycolor2"
Spreadsheet.locale("zh-cn", zhCN)

export default {
  name: "ExcelViewer",
  props: {
    workbook: Object,
  },
  data() {
    return {
      spreadsheet: null,
      themeColors: [],
    }
  },
  mounted() {
    this.initSpreadsheet()
  },
  watch: {
    workbook() {
      this.parseTheme()
      this.updateTable()
    },
  },
  computed: {
    sheets() {
      if (this.workbook.worksheets) {
        return this.workbook.worksheets.filter((sheet) => sheet._rows.length)
      }
      return []
    },
  },
  methods: {
    initSpreadsheet() {
      // 优化性能配置
      this.spreadsheet = new Spreadsheet(this.$refs.spreadsheet, {
        view: {
          height: () => document.documentElement.clientHeight - 120,
          width: () => document.documentElement.clientWidth - 40,
        },
        mode: "read", // 只读模式
        showToolbar: false,
        showGrid: true,
        showContextmenu: false, // 禁用右键菜单提高性能
        multipleSheets: true,
        rpx: 1, // 减少渲染计算
        row: {
          len: 100, // 限制初始行数
          height: 25, // 固定行高
          autoHeight: true,
        },
        col: {
          len: 26, // 限制初始列数
          width: 50, // 固定列宽
          indexWidth: 60, // 行索引宽度
          minWidth: 30, // 最小列宽
          autoWidth: true, // 自动调整列宽
        },
        style: {
          // 确保默认样式正确
          bgcolor: "#ffffff",
          color: "#333333",
          align: "left",
          valign: "middle",
          textwrap: false,
          strike: false,
          underline: false,
          italic: false,
          bold: false,
          fontSize: 12,
        },
      }).loadData({})

      // 延迟加载数据，避免初始化时卡死
      this.$nextTick(() => {
        this.parseTheme()
        this.updateSpreadsheet()
      })
    },
    updateSpreadsheet() {
      if (!this.spreadsheet) return
      // 显示加载状态
      this.$refs.spreadsheet.classList.add("loading")
      try {
        const data = this.convertWorksheetToData()
        if (this.spreadsheet) {
          this.$refs.spreadsheet.innerHTML = ""
        }
        // 重新创建实例
        this.spreadsheet = new Spreadsheet(this.$refs.spreadsheet, {
          view: {
            height: () => document.documentElement.clientHeight - 120,
            width: () => document.documentElement.clientWidth - 40,
          },
          mode: "read",
          showToolbar: false,
          showGrid: true,
          showContextmenu: false,
          multipleSheets: true,
          rpx: 1,
          row: {
            len: 100,
            height: 25,
            autoHeight: true,
          },
          col: {
            len: 26,
            width: 50,
            indexWidth: 60,
            minWidth: 30,
            autoWidth: true,
          },
        })
          .loadData(data)
      } finally {
        this.$refs.spreadsheet.classList.remove("loading")
      }
    },

    convertWorksheetToData() {
      let data = []
      this.sheets.forEach((sheet) => {
        const sheetIndex = sheet.id
        const sheetData = {
          name: sheet._name,
          freeze: "A1",
          styles: [
            {
              bgcolor: "#ffffff",
              color: "#000000",
              align: "left",
              valign: "middle",
              fontSize: 12,
            },
          ],
          merges: [],
          rows: {},
          cols: {},
        }
        try {
          // 预处理合并单元格
          this.processMerges(sheet, sheetData)
          // 预处理样式缓存，避免重复创建相同样式
          const styleCache = new Map()
          // 处理单元格数据
          this.processRows(sheet, sheetData, styleCache)
          // 处理列宽
          this.processColumns(sheet, sheetData)
          data.push(sheetData)
        } catch (error) {
          data[sheetIndex] = {
            name: sheet._name || "Sheet1",
            rows: {},
            cols: {},
          }
        }
      })
      return data
    },

    // 处理合并单元格
    processMerges(sheet, sheetData) {
      if (!sheet._merges) return

      Object.values(sheet._merges).forEach((merge) => {
        const { top, left, bottom, right } = merge
          sheetData.merges.push(
            `${this.columnIndexToLetter(
              left - 1
            )}${top}:${this.columnIndexToLetter(right - 1)}${bottom}`
          )
      })
    },

    // 处理行和单元格数据
    processRows(sheet, sheetData, styleCache) {
      // 增加最大行数限制，确保所有行都能显示
      const maxRows = Math.min(sheet.rowCount || 50, 100)

      // 预处理所有行，确保即使空行也能被处理
      for (let rowIndex = 0; rowIndex < maxRows; rowIndex++) {
        const row = sheet.getRow(rowIndex + 1)

        // 初始化行数据和行高
        let maxHeight = 25 // 最小行高
        sheetData.rows[rowIndex] = {
          cells: {},
          height: maxHeight,
        }

        // 如果行不存在，创建一个空行
        if (!row || !row.cellCount) {
          continue
        }

        // 处理行中的单元格
        // 使用列数而不是cellCount，确保处理所有可能的单元格
        const maxCols = Math.min(sheet.columnCount || 26, 100)
        for (let colIndex = 0; colIndex < maxCols; colIndex++) {
          try {
            const cell = row.getCell(colIndex + 1)
            if (!cell || (cell.type === "null" && !cell.style)) {
              // 对于空单元格，添加一个默认样式的空单元格
              sheetData.rows[rowIndex].cells[colIndex] = {
                text: "",
                style: 0,
              }
              continue
            }

            // 获取单元格文本和样式
            const { text: cellText, height: cellHeight } =
              this.getCellTextAndHeight(cell)
            const styleIndex = this.getCellStyle(
              cell,
              styleCache,
              sheetData.styles
            )

            // 更新行高
            maxHeight = Math.max(maxHeight, cellHeight)

            // 检查是否是合并单元格的起始单元格
            const mergeInfo = Object.values(sheet._merges || {}).find(
              (m) => m.top === row.number && m.left === colIndex + 1
            )

            // 设置单元格数据
            if (mergeInfo) {
              sheetData.rows[rowIndex].cells[colIndex] = {
                text: cellText,
                style: styleIndex,
                merge: [
                  mergeInfo.bottom - mergeInfo.top,
                  mergeInfo.right - mergeInfo.left,
                ],
              }
            } else {
              sheetData.rows[rowIndex].cells[colIndex] = {
                text: cellText,
                style: styleIndex,
              }
            }
          } catch (error) {

            sheetData.rows[rowIndex].cells[colIndex] = {
              text: "",
              style: 0,
            }
          }
        }

        // 更新行高
        sheetData.rows[rowIndex].height = maxHeight
      }
    },

    // 获取单元格文本和计算高度
    getCellTextAndHeight(cell) {
      let cellText = ""
      try {
        // 处理不同类型的单元格内容
        if (!cell || cell.value === null || cell.value === undefined) {
          cellText = ""
        } else if (
          cell.type === "date" ||
          (cell.numFmt &&
            (cell.numFmt.includes("yy") ||
              cell.numFmt.includes("mm") ||
              cell.numFmt.includes("dd") ||
              cell.numFmt.includes("h") ||
              cell.numFmt.includes("m:s")))
        ) {
          // 处理日期类型或带有日期格式的数字
          try {
            // 检查是否为日期值
            let date
            if (cell.value instanceof Date) {
              date = cell.value
            } else if (typeof cell.value === "number") {
              // Excel日期是从1900年1月1日开始的天数
              // 需要转换为JavaScript日期
              const excelEpoch = new Date(1899, 11, 30) // Excel的起始日期
              date = new Date(
                excelEpoch.getTime() + cell.value * 24 * 60 * 60 * 1000
              )
            } else {
              date = new Date(cell.value)
            }

            if (!isNaN(date.getTime())) {
              // 格式化日期
              const year = date.getFullYear()
              const month = (date.getMonth() + 1).toString().padStart(2, "0")
              const day = date.getDate().toString().padStart(2, "0")

              // 检查是否需要显示时间
              if (
                cell.numFmt &&
                (cell.numFmt.includes("h") || cell.numFmt.includes("s"))
              ) {
                const hour = date.getHours().toString().padStart(2, "0")
                const minute = date.getMinutes().toString().padStart(2, "0")
                const second = date.getSeconds().toString().padStart(2, "0")
                cellText = `${year}/${month}/${day} ${hour}:${minute}:${second}`
              } else {
                cellText = `${year}/${month}/${day}`
              }
            } else {
              // 如果不是有效日期，使用原始文本
              cellText = String(cell.text || cell.value || "")
            }
          } catch (e) {
        
            cellText = String(cell.text || cell.value || "")
          }
        } else if (typeof cell.value === "object" && cell.value !== null) {
          if (cell.value.hyperlink) {
            cellText = "[链接]"
          } else if (cell.value.image) {
            cellText = "[图片]"
          } else if (cell.value.richText) {
            cellText = cell.value.richText
              .map((t) => String(t?.text || ""))
              .join("")
          } else {
            cellText = String(cell.text || "")
          }
        } else if (cell.formula) {
          // 检查公式结果是否可能是日期
          if (
            cell.numFmt &&
            (cell.numFmt.includes("yy") ||
              cell.numFmt.includes("mm") ||
              cell.numFmt.includes("dd"))
          ) {
            try {
              // 尝试将结果转换为日期
              const excelEpoch = new Date(1899, 11, 30)
              const date = new Date(
                excelEpoch.getTime() + cell.result * 24 * 60 * 60 * 1000
              )

              if (!isNaN(date.getTime())) {
                const year = date.getFullYear()
                const month = (date.getMonth() + 1).toString().padStart(2, "0")
                const day = date.getDate().toString().padStart(2, "0")
                cellText = `${year}-${month}-${day}`
              } else {
                cellText = String(cell.result || cell.value || "")
              }
            } catch (e) {
              cellText = String(cell.result || cell.value || "")
            }
          } else {
            cellText =
              cell.result !== undefined && cell.result !== null
                ? String(cell.result)
                : String(cell.value || "")
          }
        } else {
          cellText =
            cell.text !== undefined && cell.text !== null
              ? String(cell.text)
              : cell.value !== undefined && cell.value !== null
              ? String(cell.value)
              : ""
        }
      } catch (e) {
  
        cellText = "[格式错误]"
      }

      // 计算行高
      const lineBreaks = ((cellText || "").match(/\n/g) || []).length + 1
      const chars = [...(cellText || "")]
      const lines = Math.ceil(chars.length / 40)
      const neededHeight = Math.max(lineBreaks, lines) * 20

      return { text: cellText, height: neededHeight }
    },

    // 获取单元格样式
    getCellStyle(cell, styleCache, styles) {
      // 创建样式对象
      const cellStyle = {
        bgcolor: "#ffffff",
        color: "#000000",
        align: cell.alignment?.horizontal || "left",
        valign: cell.alignment?.vertical || "middle",
        fontSize: cell.font?.size || 12,
        textwrap: true,
        bold: cell.font?.bold,
        italic: cell.font?.italic,
        underline: false,
        strike: false,
      }

      // 处理背景色
      if (cell.fill && cell.fill.type === "pattern") {
        const fgColor = cell.fill.fgColor || {}

        // 处理索引颜色
        if (typeof fgColor.indexed === "number") {
          const indexedColor = indexedColors[fgColor.indexed]
          if (indexedColor) {
            cellStyle.bgcolor = `#${indexedColor}`
          }
        }
        // 处理主题颜色
        else if (typeof fgColor.theme === "number") {
          // Excel主题颜色索引映射
          const themeColorMap = {
            0: 1, // 浅色1
            1: 0, // 深色1
            2: 3, // 浅色2
            3: 2, // 深色2
            4: 4, // 强调色1
            5: 5, // 强调色2
            6: 6, // 强调色3
            7: 7, // 强调色4
            8: 8, // 强调色5
            9: 9, // 强调色6
          }

          const mappedIndex =
            themeColorMap[fgColor.theme] !== undefined
              ? themeColorMap[fgColor.theme]
              : fgColor.theme

          if (this.themeColors[mappedIndex]) {
            let color = `#${this.themeColors[mappedIndex]}`

            // 应用色调调整
            if (typeof fgColor.tint === "number" && fgColor.tint !== 0) {
              color = this.applyTint(color, fgColor.tint)
            }

            cellStyle.bgcolor = color
          }
        }
        // 处理RGB颜色
        else if (fgColor.rgb) {
          cellStyle.bgcolor = `#${fgColor.rgb.substring(
            fgColor.rgb.length - 6
          )}`
        }
        // 处理ARGB颜色
        else if (fgColor.argb) {
          const color = this.convertArgbToHex(fgColor.argb)
          if (color) cellStyle.bgcolor = color
        }
      }

      // 处理字体颜色
      if (cell.font && cell.font.color) {
        const fontColor = cell.font.color

        // 处理索引颜色
        if (typeof fontColor.indexed === "number") {
          const indexedColor = indexedColors[fontColor.indexed]
          if (indexedColor) {
            cellStyle.color = `#${indexedColor}`
          }
        }
        // 处理主题颜色
        else if (typeof fontColor.theme === "number") {
          // Excel主题颜色索引映射
          const themeColorMap = {
            0: 1, // 浅色1
            1: 0, // 深色1
            2: 3, // 浅色2
            3: 2, // 深色2
            4: 4, // 强调色1
            5: 5, // 强调色2
            6: 6, // 强调色3
            7: 7, // 强调色4
            8: 8, // 强调色5
            9: 9, // 强调色6
          }

          const mappedIndex =
            themeColorMap[fontColor.theme] !== undefined
              ? themeColorMap[fontColor.theme]
              : fontColor.theme

          if (this.themeColors[mappedIndex]) {
            let color = `#${this.themeColors[mappedIndex]}`

            // 应用色调调整
            if (typeof fontColor.tint === "number" && fontColor.tint !== 0) {
              color = this.applyTint(color, fontColor.tint)
            }

            cellStyle.color = color
          }
        }
        // 处理RGB颜色
        else if (fontColor.rgb) {
          cellStyle.color = `#${fontColor.rgb.substring(
            fontColor.rgb.length - 6
          )}`
        }
        // 处理ARGB颜色
        else if (fontColor.argb) {
          const color = this.convertArgbToHex(fontColor.argb)
          if (color) cellStyle.color = color
        }
      }

      // 使用样式缓存避免重复
      const styleKey = JSON.stringify(cellStyle)
      if (styleCache.has(styleKey)) {
        return styleCache.get(styleKey)
      }

      // 添加新样式
      styles.push(cellStyle)
      const styleIndex = styles.length - 1
      styleCache.set(styleKey, styleIndex)

      return styleIndex
    },
    applyTint(hexColor, tint) {
      try {
        // 将十六进制颜色转换为RGB
        const color = tinycolor(hexColor)
        const rgb = color.toRgb()

        // 根据Excel的色调算法调整颜色
        const adjustColor = (value, tint) => {
          let result
          if (tint < 0) {
            // 负色调值，使颜色变暗
            result = value * (1 + tint)
          } else {
            // 正色调值，使颜色变亮
            result = value + (255 - value) * tint
          }
          return Math.max(0, Math.min(255, Math.round(result)))
        }

        // 应用色调到每个RGB通道
        const r = adjustColor(rgb.r, tint)
        const g = adjustColor(rgb.g, tint)
        const b = adjustColor(rgb.b, tint)

        // 返回调整后的颜色
        return tinycolor({ r, g, b }).toHexString()
      } catch (e) {
      
        return hexColor
      }
    },

    // 处理列宽
    processColumns(sheet, sheetData) {
      const maxCols = Math.min(sheet.columnCount || 26, 50)
      const colWidthCache = new Map()
      const mergedCols = new Set()
      // 收集合并单元格信息
      if (sheet._merges) {
        Object.values(sheet._merges).forEach((merge) => {
          // 记录被合并的列
          for (let col = merge.left; col <= merge.right; col++) {
            mergedCols.add(col - 1)
          }
        })
      }
      // 先收集所有单元格的文本宽度
      sheet.eachRow((row) => {
        for (let colIndex = 0; colIndex < maxCols; colIndex++) {
          try {
            const cell = row.getCell(colIndex + 1)
            if (!cell) continue
            // 安全获取单元格文本
            let cellText = ""
            try {
              if (cell.text !== undefined && cell.text !== null) {
                cellText = String(cell.text)
              } else if (cell.value !== undefined && cell.value !== null) {
                if (typeof cell.value === "object") {
                  cellText = cell.value?.richText
                    ? cell.value.richText
                        .map((t) => String(t?.text || ""))
                        .join("")
                    : cell.value?.hyperlink
                    ? "[链接]"
                    : cell.value?.image
                    ? "[图片]"
                    : ""
                } else {
                  cellText = String(cell.value)
                }
              }
            } catch (e) {
              cellText = ""
            }
            // 计算文本宽度
            if (cellText) {
              // 检查是否是合并单元格的一部分
              const isMerged = Object.values(sheet._merges || {}).some(
                (merge) =>
                  row.number >= merge.top &&
                  row.number <= merge.bottom &&
                  colIndex + 1 >= merge.left &&
                  colIndex + 1 <= merge.right
              )
              // 如果是合并单元格，根据合并的列数调整宽度计算
              if (isMerged) {
                const merge = Object.values(sheet._merges || {}).find(
                  (m) =>
                    row.number >= m.top &&
                    row.number <= m.bottom &&
                    colIndex + 1 >= m.left &&
                    colIndex + 1 <= m.right
                )
                if (merge && colIndex + 1 === merge.left) {
                  // 只为合并单元格的第一列计算宽度
                  const mergeWidth = merge.right - merge.left + 1
                  const textWidth = [...cellText].reduce((width, char) => {
                    return width + (/[\u4e00-\u9fa5]/.test(char) ? 2 : 1)
                  }, 0)
                  // 平均分配到每一列
                  const avgWidth = Math.ceil(textWidth / mergeWidth)
                  colWidthCache.set(
                    colIndex,
                    Math.max(colWidthCache.get(colIndex) || 0, avgWidth)
                  )
                }
              } else {
                // 普通单元格
                const textWidth = [...cellText].reduce((width, char) => {
                  return width + (/[\u4e00-\u9fa5]/.test(char) ? 2 : 1)
                }, 0)

                colWidthCache.set(
                  colIndex,
                  Math.max(colWidthCache.get(colIndex) || 0, textWidth)
                )
              }
            }
          } catch (e) {
            // 忽略错误
          }
        }
      })

      // 设置列宽
      for (let colIndex = 0; colIndex < maxCols; colIndex++) {
        let maxWidth = colWidthCache.get(colIndex) || 0
        // 对于合并单元格的列，限制最大宽度
        if (mergedCols.has(colIndex)) {
          maxWidth = Math.min(maxWidth, 30) // 限制合并列的宽度
        }
        // 设置合理的列宽范围
        const width = Math.max(40, Math.min(300, maxWidth * 9))
        sheetData.cols[colIndex] = {
          width: width,
        }
      }
    },
    // ARGB 转 Hex 颜色方法
    convertArgbToHex(argb) {
      if (!argb) return null
      try {
        const matches =
          /^([0-9a-f]{2})([0-9a-f]{2})([0-9a-f]{2})([0-9a-f]{2})$/i.exec(argb)
        if (!matches) return null
        return `#${matches[2]}${matches[3]}${matches[4]}`
      } catch (e) {
     
        return null
      }
    },
    columnIndexToLetter(index) {
      let temp
      let letter = ""
      // 修复列索引转字母的算法
      if (index < 0) return ""
      do {
        temp = index % 26
        letter = String.fromCharCode(temp + 65) + letter
        index = Math.floor(index / 26) - 1
      } while (index >= 0)
      return letter
    },
    parseTheme() {
      const theme = this.workbook._themes?.theme1
      if (!theme) {
        // Office默认主题颜色
        this.themeColors = [
          "FFFFFF", // 白色 - 浅色1
          "000000", // 黑色 - 深色1
          "EEECE1", // 浅灰 - 浅色2
          "1F497D", // 深灰 - 深色2
          "4F81BD", // 蓝色 - 强调色1
          "C0504D", // 红色 - 强调色2
          "9BBB59", // 绿色 - 强调色3
          "8064A2", // 紫色 - 强调色4
          "4BACC6", // 青色 - 强调色5
          "F79646", // 橙色 - 强调色6
        ]
        return
      }
      try {
        const parser = new DOMParser()
        const doc = parser.parseFromString(theme, "text/xml")
        // 获取颜色方案元素
        const clrScheme = doc.getElementsByTagName("a:clrScheme")[0]
        if (!clrScheme) {
          throw new Error("找不到颜色方案元素")
        }
        // 初始化主题颜色数组
        this.themeColors = []
        const colorElements = Array.from(clrScheme.children)
        // 处理每个颜色元素
        for (const element of colorElements) {
          let colorValue = null
          // 查找颜色定义元素
          const srgbClr = element.getElementsByTagName("a:srgbClr")[0]
          const sysClr = element.getElementsByTagName("a:sysClr")[0]
          if (srgbClr) {
            colorValue = srgbClr.getAttribute("val")
          } else if (sysClr) {
            colorValue =
              sysClr.getAttribute("lastClr") || sysClr.getAttribute("val")
          }
          this.themeColors.push(colorValue || "FFFFFF")
        }
      } catch (error) {
      
        this.themeColors = [
          "FFFFFF", // 白色 - 浅色1
          "000000", // 黑色 - 深色1
          "EEECE1", // 浅灰 - 浅色2
          "1F497D", // 深灰 - 深色2
          "4F81BD", // 蓝色 - 强调色1
          "C0504D", // 红色 - 强调色2
          "9BBB59", // 绿色 - 强调色3
          "8064A2", // 紫色 - 强调色4
          "4BACC6", // 青色 - 强调色5
          "F79646", // 橙色 - 强调色6
        ]
      }
    },
  },
}
</script>

<style scoped>
.spreadsheet-container {
  width: 100%;
  height: calc(100vh - 120px);
  position: relative;
}

.spreadsheet-container.loading::after {
  content: "加载中...";
  position: absolute;
  top: 0;
  left: 0;
  right: 0;
  bottom: 0;
  background: rgba(255, 255, 255, 0.7);
  display: flex;
  align-items: center;
  justify-content: center;
  font-size: 18px;
  z-index: 1000;
}

.sheet-btn {
  border: 1px solid #ccc;
  background-color: #f0f0f0;
  cursor: pointer;
  margin-right: 5px;
  border-radius: 4px;
  padding: 5px 15px;
}

.sheet-btn.active {
  background-color: #4caf50;
  color: white;
  border-color: #388e3c;
}

.btn-group {
  margin-bottom: 10px;
  display: flex;
  flex-wrap: wrap;
  gap: 5px;
  padding: 8px;
  background-color: #f8f8f8;
  border-radius: 4px;
}
</style>