wisdomcity-office/src/components/view_file/components/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 'handsontable/i18n'
import _ from 'lodash'
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)
          .change((data) => {
            console.log('数据已更改:', data)
          })
      } catch (error) {
        console.error('加载Excel数据失败:', error)
      } 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) {
          console.error('转换工作表数据失败:', error)
          data[sheetIndex] = {
            name: sheet._name || 'Sheet1',
            rows: {},
            cols: {}
          }
        }
      })
      return data
    },

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

      Object.values(sheet._merges).forEach((merge) => {
        try {
          const { top, left, bottom, right } = merge
          sheetData.merges.push(
            `${this.columnIndexToLetter(
              left - 1
            )}${top}:${this.columnIndexToLetter(right - 1)}${bottom}`
          )
        } catch (e) {
          console.warn('处理合并单元格出错:', e)
        }
      })
    },

    // 处理行和单元格数据
    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) {
            console.warn(
              '处理单元格时出错:',
              error,
              '位置:',
              rowIndex + 1,
              colIndex + 1
            )
            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) {
            console.warn('日期转换错误:', 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) {
        console.warn('获取单元格文本出错:', 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) {
        console.warn('应用色调调整出错:', 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) {
        console.error('Color conversion error:', 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) {
        console.error('解析主题颜色出错:', 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>