<template>
  <!-- 上傳按鈕 -->
  <el-upload
    action=""
    :http-request="_export"
    :auto-upload="true"
    accept=".xls,.xlsx"
  >
    <el-button size="small" type="primary">導(dǎo)入</el-button>
  </el-upload>
  <!-- 預(yù)覽區(qū)域 -->
  <ExcelPreview :file="file" showFormulas />
</template>
<script>
export default {
  data() {
    return { file: null };
  },
  methods: {
    _export({ file }) {
      this.file = file;   // 直接塞 File 實(shí)例
    }
  }
}
</script>

//`handleFileResponse` 已幫你寫好，**無(wú)需改動(dòng)**。
axios.get('/api/excel').then(res => {
  // 統(tǒng)一入口，支持 Blob / ArrayBuffer / Base64 / JSON 嵌套
  this.handleFileResponse(res.data);
});

// 統(tǒng)一處理文件響應(yīng)
      handleFileResponse(data) {
        try {
          console.log('開始處理文件響應(yīng)，數(shù)據(jù)類型:', typeof data);
          
          // 如果已經(jīng)是ArrayBuffer，直接使用
          if (data instanceof ArrayBuffer) {
            console.log('檢測(cè)到ArrayBuffer數(shù)據(jù)，直接使用');
            this.file = data;
            this.$message({ message: '數(shù)據(jù)解析成功', type: 'success' });
            return;
          }
          
          // 如果是Blob，轉(zhuǎn)換為ArrayBuffer
          if (data instanceof Blob) {
            console.log('檢測(cè)到Blob數(shù)據(jù)，轉(zhuǎn)換為ArrayBuffer');
            this.convertBlobToArrayBuffer(data);
            return;
          }
          
          // 如果是字符串，嘗試解析為base64
          if (typeof data === 'string') {
            console.log('檢測(cè)到字符串?dāng)?shù)據(jù)，嘗試解析為base64');
            this.convertStringToArrayBuffer(data);
            return;
          }
          
          // 如果是對(duì)象，嘗試提取文件數(shù)據(jù)
          if (typeof data === 'object' && data !== null) {
            console.log('檢測(cè)到對(duì)象數(shù)據(jù)，嘗試提取文件數(shù)據(jù)');
            this.extractFileDataFromObject(data);
            return;
          }
          
          throw new Error('不支持的數(shù)據(jù)格式');
        } catch (error) {
          console.error('文件響應(yīng)處理失敗:', error);
         
           this.$message({ message: '文件格式不支持', type: 'success' });
        }
      },
      
      
         // 轉(zhuǎn)換Blob為ArrayBuffer
      convertBlobToArrayBuffer(blob) {
        console.log('開始轉(zhuǎn)換Blob，大小:', blob.size, '類型:', blob.type);
        const reader = new FileReader();
        reader.onload = event => {
          console.log('FileReader讀取完成');
          this.file = event.target.result; // ArrayBuffer
         this.$message({ message: '數(shù)據(jù)解析成功', type: 'success' });
        };
        reader.onerror = error => {
          console.error('FileReader讀取失敗:', error);
          this.$message.error('文件讀取失敗');
        };
        reader.readAsArrayBuffer(blob);
      },
      
        // 轉(zhuǎn)換字符串為ArrayBuffer
      convertStringToArrayBuffer(stringData) {
        try {
          let base64Data = stringData;
          
          // 如果是data URL格式，提取base64部分
          if (stringData.startsWith('data:')) {
            const parts = stringData.split(',');
            if (parts.length >= 2) {
              base64Data = parts[1];
            }
          }
          
          // 解碼base64
          const binaryString = atob(base64Data);
          const bytes = new Uint8Array(binaryString.length);
          for (let i = 0; i < binaryString.length; i++) {
            bytes[i] = binaryString.charCodeAt(i);
          }
          
          console.log('base64轉(zhuǎn)換完成，數(shù)據(jù)大小:', bytes.length);
          this.file = bytes.buffer;
        this.$message({ message: '數(shù)據(jù)解析成功', type: 'success' });
        } catch (error) {
          console.error('字符串轉(zhuǎn)換失敗:', error);
          this.$message.error('文件格式不支持');
        }
      },
      
        // 從對(duì)象中提取文件數(shù)據(jù)
      extractFileDataFromObject(obj) {
        // 嘗試常見(jiàn)的文件數(shù)據(jù)字段
        const possibleFields = ['fileData', 'data', 'content', 'file', 'excel', 'xlsx'];
        
        for (const field of possibleFields) {
          if (obj[field] && typeof obj[field] === 'string') {
            console.log(`從對(duì)象字段 ${field} 提取數(shù)據(jù)`);
            this.convertStringToArrayBuffer(obj[field]);
            return;
          }
        }
        
        // 如果沒(méi)有找到字符串字段，嘗試其他類型
        if (obj.data instanceof Blob) {
          this.convertBlobToArrayBuffer(obj.data);
          return;
        }
        
        if (obj.data instanceof ArrayBuffer) {
          this.file = obj.data;
          this.$message({ message: '數(shù)據(jù)解析成功', type: 'success' });
          return;
        }
        
        throw new Error('對(duì)象中未找到有效的文件數(shù)據(jù)');
      },

<template>
  <!-- excle預(yù)覽插件 傳入文件即可 -->
  <div class="excel-preview-container">
    <div v-if="data.length > 0">
      <!-- 表格信息 -->
      <table class="excel-table">
        <tbody>
          <tr v-for="(row, rowIndex) in data" :key="rowIndex">
            <template v-for="(value, colIndex) in row">
              <td
                v-if="!isCellMerged(rowIndex, colIndex)"
                :key="colIndex"
                :colspan="getColspan(rowIndex, colIndex)"
                :rowspan="getRowspan(rowIndex, colIndex)"
                :class="{
                  'formula-cell': cellFormulas[`${rowIndex},${colIndex}`],
                  'error-cell': isErrorValue(value),
                  'empty-cell': isEmptyValue(value),
                }"
              >
                <div
                  v-if="
                    cellFormulas[`${rowIndex},${colIndex}`] &&
                    showFormulas &&
                    isEmptyValue(value)
                  "
                  class="formula-indicator"
                >
                  <span class="formula-icon">?</span>
                  <span class="formula-text"
                    >={{ cellFormulas[`${rowIndex},${colIndex}`] }}</span
                  >
                </div>
                <span v-else>{{
                  formatCellValue(value, rowIndex, colIndex)
                }}</span>
              </td>
            </template>
          </tr>
        </tbody>
      </table>
    </div>
    <div v-else class="empty-message">請(qǐng)導(dǎo)入數(shù)據(jù)</div>
  </div>
</template>
<script>
  import * as XLSX from 'xlsx';
  // const FormulaParser = require('hot-formula-parser').Parser;
  // var FormulaParser = new formulaParser.Parser();
  var FormulaParser = require('hot-formula-parser').Parser;
  var parser = new FormulaParser();
  export default {
    name: 'ExcelPreview',
    props: {
      file: { type: [File, ArrayBuffer, null], default: null },
      showFormulas: { type: Boolean, default: false },
    },
    data() {
      return {
        data: [],
        mergedCells: {},
        cellFormulas: {},
        cellFormats: {}, // 存儲(chǔ)單元格格式信息
        cellDisplay: {}, // 存儲(chǔ)單元格顯示值（如帶%）
        sheetDataDict: {},
        currentSheetName: '',
      };
    },
    watch: {
      file: {
        immediate: true,
        handler(val) {
          if (!val) {
            this.resetData();
            return;
          }
          if (val instanceof File) {
            const reader = new FileReader();
            reader.onload = event => {
              this.processExcelData(event.target.result);
            };
            reader.readAsArrayBuffer(val);
          } else if (val instanceof ArrayBuffer) {
            this.processExcelData(val);
          }
        },
      },
    },
    methods: {
      resetData() {
        this.data = [];
        this.mergedCells = {};
        this.cellFormulas = {};
        this.cellFormats = {};
        this.cellDisplay = {};
        this.sheetDataDict = {};
        this.currentSheetName = '';
      },
      processExcelData(arrayBuffer) {
        console.log(parser, 'parser', arrayBuffer);
        try {
          const fileData = new Uint8Array(arrayBuffer);
          const workbook = XLSX.read(fileData, {
            type: 'array',
            cellFormula: true,
            cellHTML: false,
            cellDates: true,
            sheetStubs: true,
          });
          // 構(gòu)建所有sheet的數(shù)據(jù)字典
          const sheetDataDict = {};
          workbook.SheetNames.forEach(sheetName => {
            const ws = workbook.Sheets[sheetName];
            const range = XLSX.utils.decode_range(ws['!ref'] || 'A1:A1');
            // 直接使用單元格地址映射，避免空行問(wèn)題
            const sheetData = {};
            // 遍歷所有單元格，只保存有數(shù)據(jù)的單元格
            for (let r = range.s.r; r <= range.e.r; r++) {
              for (let c = range.s.c; c <= range.e.c; c++) {
                const addr = XLSX.utils.encode_cell({ r, c });
                const cell = ws[addr];
                if (cell && cell.v !== undefined) {
                  sheetData[addr] = cell.v;
                }
              }
            }
            sheetDataDict[sheetName] = {
              cells: sheetData,
              range: range,
              worksheet: ws,
            };
            console.log(`工作表 ${sheetName} 數(shù)據(jù):`, sheetData);
          });
          this.sheetDataDict = sheetDataDict;
          // 默認(rèn)展示第一個(gè)sheet
          const sheetName = workbook.SheetNames[0];
          this.currentSheetName = sheetName;
          const worksheet = workbook.Sheets[sheetName];
          const range = XLSX.utils.decode_range(worksheet['!ref'] || 'A1:A1');
          console.log('主工作表范圍:', range);
          console.log('主工作表名稱:', sheetName);
          console.log('主工作表范圍詳情:', {
            startRow: range.s.r,
            endRow: range.e.r,
            startCol: range.s.c,
            endCol: range.e.c,
          });
          const newData = [];
          const formulas = {};
          const cellFormats = {}; // 存儲(chǔ)單元格格式信息
          const cellDisplay = {}; // 存儲(chǔ)單元格顯示值
          parser.on('callCellValue', (cellCoord, done) => {
            const sheet = cellCoord.sheet || sheetName;
            const row = cellCoord.row.index;
            const col = cellCoord.column.index;
            // 使用單元格地址來(lái)獲取值，確保位置正確
            const addr = XLSX.utils.encode_cell({ r: row, c: col });
            const value =
              sheetDataDict[sheet] && sheetDataDict[sheet].cells[addr];
            console.log(
              `callCellValue: sheet=${sheet}, row=${row}, col=${col}, addr=${addr}, value=${value}`
            );
            done(value !== undefined ? value : null);
          });
          parser.on('callRangeValue', (startCellCoord, endCellCoord, done) => {
            const sheet = startCellCoord.sheet || sheetName;
            const startRow = startCellCoord.row.index;
            const endRow = endCellCoord.row.index;
            const startCol = startCellCoord.column.index;
            const endCol = endCellCoord.column.index;
            const values = [];
            for (let r = startRow; r <= endRow; r++) {
              const row = [];
              for (let c = startCol; c <= endCol; c++) {
                const addr = XLSX.utils.encode_cell({ r, c });
                const value =
                  sheetDataDict[sheet] && sheetDataDict[sheet].cells[addr];
                row.push(value !== undefined ? value : null);
              }
              values.push(row);
            }
            console.log(
              `callRangeValue: sheet=${sheet}, range=${startRow}:${endRow},${startCol}:${endCol}, values=`,
              values
            );
            done(values);
          });
          // 重新構(gòu)建主工作表數(shù)據(jù)，保持原始結(jié)構(gòu)包括空行
          console.log('開始構(gòu)建主工作表數(shù)據(jù)...');
          for (let row = range.s.r; row <= range.e.r; row++) {
            const rowData = [];
            console.log(`處理第 ${row} 行`);
            for (let col = range.s.c; col <= range.e.c; col++) {
              const cellAddress = XLSX.utils.encode_cell({ r: row, c: col });
              const cell = worksheet[cellAddress];
              // === 這里是調(diào)試代碼 ===
              if (cell) {
                console.log(
                  `cell ${cellAddress} 格式z:`,
                  cell.z,
                  '顯示w:',
                  cell.w,
                  '值v:',
                  cell.v,
                  '公式f:',
                  cell.f
                );
              }
              // =====================
              console.log(`處理單元格 ${cellAddress}: row=${row}, col=${col}`);
              let value = '';
              if (cell) {
                if (cell.f) {
                  formulas[`${row},${col}`] = cell.f;
                  console.log(`發(fā)現(xiàn)公式: ${cellAddress} = ${cell.f}`);
                }
                // 存儲(chǔ)單元格格式信息
                if (cell.z) {
                  cellFormats[`${row},${col}`] = cell.z;
                }
                // 存儲(chǔ)單元格顯示值
                if (cell.w) {
                  cellDisplay[`${row},${col}`] = cell.w;
                }
                // 優(yōu)先用Excel保存的值，否則自動(dòng)計(jì)算
                if (cell.v !== undefined && cell.v !== null) {
                  value = cell.v;
                } else if (cell.f) {
                  // 自動(dòng)計(jì)算公式
                  console.log(`開始計(jì)算公式: ${cell.f}`);
                  const res = parser.parse('=' + cell.f);
                  value = res.error ? `#ERROR` : res.result;
                  console.log(`公式計(jì)算結(jié)果: ${value}`);
                } else if (cell.t === 'z') {
                  value = '';
                }
                if (cell.t === 'd' && value instanceof Date) {
                  value = this.formatDate(value);
                }
                if (cell.t === 'b') {
                  value = cell.v ? 'TRUE' : 'FALSE';
                }
                if (cell.t === 'e') {
                  value = `${cell.w || 'ERROR!'}`;
                }
              }
              // 保持空單元格為空字符串
              rowData.push(value);
            }
            // 保持空行
            newData.push(rowData);
            console.log(`第 ${row} 行數(shù)據(jù):`, rowData);
          }
          this.data = newData;
          this.cellFormulas = formulas;
          this.cellFormats = cellFormats; // 保存格式信息
          this.cellDisplay = cellDisplay; // 保存顯示值
          this.getMergedCells(worksheet);
          // 只過(guò)濾右側(cè)空列，保留空行
          this.data = this.filterEmptyColumns(this.data);
        } catch (error) {
          console.error('Excel解析失敗:', error, error && error.stack);
          this.$message && this.$message.error('文件解析失敗，請(qǐng)檢查文件格式');
        }
      },
      // 只過(guò)濾右側(cè)空列，保留空行
      filterEmptyColumns(data) {
        if (!data || data.length === 0) return data;
        // 找到每行最后一個(gè)非空單元格的列索引
        const maxCols = Math.max(
          ...data.map(row => {
            for (let i = row.length - 1; i >= 0; i--) {
              if (row[i] !== '' && row[i] !== null && row[i] !== undefined) {
                return i + 1;
              }
            }
            return 0;
          })
        );
        // 只截取到最后一個(gè)非空列，保留所有行
        return data.map(row => row.slice(0, maxCols));
      },
      formatDate(date) {
        if (!(date instanceof Date)) return date;
        const year = date.getFullYear();
        const month = String(date.getMonth() + 1).padStart(2, '0');
        const day = String(date.getDate()).padStart(2, '0');
        return `${year}-${month}-${day}`;
      },
      formatCellValue(value, row, col) {
        // 優(yōu)先用 cellDisplay 的顯示值（Excel里看到的內(nèi)容）
        const display = this.cellDisplay[`${row},${col}`];
        if (typeof display === 'string' && display.trim() !== '') {
          return display;
        }
        // 優(yōu)先用 cellFormulas 的百分號(hào)顯示
        const formulaDisplay = this.cellFormulas[`${row},${col}`];
        if (
          typeof formulaDisplay === 'string' &&
          formulaDisplay.includes('%')
        ) {
          return formulaDisplay;
        }
        if (
          this.cellFormulas[`${row},${col}`] &&
          (value === '' || value === null || value === undefined)
        ) {
          return this.showFormulas ? '' : '';
        }
        // 獲取單元格格式
        const format = this.cellFormats[`${row},${col}`];
        if (typeof value === 'number') {
          // 處理百分比格式
          if (format && format.includes('%')) {
            // 解析格式中的小數(shù)位數(shù)
            let digits = 2; // 默認(rèn)2位小數(shù)
            const match = format.match(/0\.(0+)%/);
            if (match) {
              digits = match[1].length;
            } else if (format.match(/0%/)) {
              digits = 0;
            } else if (format.match(/0\.0%/)) {
              digits = 1;
            }
            // 計(jì)算百分比，使用更高精度避免浮點(diǎn)數(shù)問(wèn)題
            const percentageValue = value * 100;
            // 根據(jù)格式要求格式化
            if (digits === 0) {
              // 整數(shù)百分比
              return Math.round(percentageValue) + '%';
            } else {
              // 小數(shù)百分比，使用更高精度計(jì)算
              let percentage = percentageValue.toFixed(Math.max(digits, 6));
              // 去除末尾的無(wú)效零，但保留有效的小數(shù)位
              if (digits > 0) {
                // 先去除末尾的 .0
                percentage = percentage.replace(/\.0+$/, '');
                // 再去除末尾的無(wú)效零，但保留至少 digits 位小數(shù)
                const parts = percentage.split('.');
                if (parts.length > 1) {
                  const decimal = parts[1];
                  const significantDigits = Math.min(
                    digits,
                    decimal.replace(/0+$/, '').length
                  );
                  if (significantDigits > 0) {
                    percentage =
                      parts[0] + '.' + decimal.substring(0, significantDigits);
                  } else {
                    percentage = parts[0];
                  }
                }
              }
              return percentage + '%';
            }
          }
          // 處理其他數(shù)字格式 - 讀取Excel中的格式設(shè)置
          if (format) {
            // 解析Excel格式中的小數(shù)位數(shù)
            let digits = null;
            // 匹配不同的數(shù)字格式模式
            if (format.match(/0\.(0+)/)) {
              // 格式如 "0.00" 或 "0.000"
              const match = format.match(/0\.(0+)/);
              digits = match[1].length;
            } else if (format.match(/#\.(0+)/)) {
              // 格式如 "#.00" 或 "#.000"
              const match = format.match(/#\.(0+)/);
              digits = match[1].length;
            } else if (format.match(/0\.0/)) {
              // 格式如 "0.0" (1位小數(shù))
              digits = 1;
            } else if (format.match(/0/)) {
              // 格式如 "0" (整數(shù))
              digits = 0;
            }
            // 調(diào)試信息
            console.log(
              `單元格 ${row},${col}: 格式="${format}", 小數(shù)位數(shù)=${digits}, 原始值=${value}`
            );
            // 根據(jù)Excel格式要求格式化
            if (digits !== null) {
              if (digits === 0) {
                return Math.round(value).toString();
              } else {
                return value.toFixed(digits);
              }
            }
          }
          // 如果沒(méi)有特定格式，保持原始精度
          if (Number.isInteger(value)) return value;
          // 保持原始精度，不強(qiáng)制截?cái)?
          const valueStr = value.toString();
          // 如果是科學(xué)計(jì)數(shù)法，轉(zhuǎn)換為普通小數(shù)
          if (valueStr.includes('e') || valueStr.includes('E')) {
            return value.toFixed(10).replace(/\.?0+$/, '');
          }
          // 去除末尾的無(wú)效零，但保留有效的小數(shù)位
          return valueStr.replace(/\.?0+$/, '');
        }
        return value;
      },
      isEmptyValue(value) {
        return value === '' || value === null || value === undefined;
      },
      isErrorValue(value) {
        return typeof value === 'string' && value.startsWith('#');
      },
      getMergedCells(worksheet) {
        this.mergedCells = {};
        if (worksheet['!merges']) {
          worksheet['!merges'].forEach(merge => {
            const s = merge.s;
            const e = merge.e;
            for (let r = s.r; r <= e.r; r++) {
              for (let c = s.c; c <= e.c; c++) {
                this.mergedCells[r + ',' + c] = {
                  start: r === s.r && c === s.c,
                  colspan: e.c - s.c + 1,
                  rowspan: e.r - s.r + 1,
                };
              }
            }
          });
        }
      },
      isCellMerged(row, col) {
        const cell = this.mergedCells[row + ',' + col];
        return cell && !cell.start;
      },
      getColspan(row, col) {
        const cell = this.mergedCells[row + ',' + col];
        return cell && cell.colspan ? cell.colspan : 1;
      },
      getRowspan(row, col) {
        const cell = this.mergedCells[row + ',' + col];
        return cell && cell.rowspan ? cell.rowspan : 1;
      },
    },
  };
</script>
<style scoped lang="scss">
  .excel-preview-container {
    overflow-x: auto;
    border: 1px solid #e0e0e0;
    border-radius: 10px;
    box-shadow: 0 4px 12px rgba(0, 0, 0, 0.06);
    background: white;
    height: 100%;
    position: relative;
    margin: 0 auto;
    // 響應(yīng)式設(shè)計(jì)
    @media (max-width: 768px) {
      border-radius: 5px;
      box-shadow: 0 2px 8px rgba(0, 0, 0, 0.1);
    }
  }
  .table-info {
    padding: 10px 15px;
    background: #f8f9fa;
    border-bottom: 1px solid #e0e0e0;
    display: flex;
    gap: 20px;
    font-size: 12px;
    color: #666;
  }
  .info-item {
    display: flex;
    align-items: center;
    gap: 5px;
  }
  .info-item::before {
    content: '?';
    color: #007bff;
    font-weight: bold;
  }
  .excel-table {
    width: 100%;
    border-collapse: collapse;
    font-size: 13px;
    table-layout: fixed; // 固定表格布局，提高性能
    th,
    td {
      padding: 8px 12px;
      text-align: center;
      vertical-align: middle;
      border: 1px solid #e0e0e0;
      min-width: 80px;
      max-width: 200px; // 限制最大寬度
      position: relative;
      transition: background-color 0.2s;
      background: white;
      word-wrap: break-word; // 允許文字換行
      overflow: hidden;
      text-overflow: ellipsis; // 超出顯示省略號(hào)
    }
    tr:nth-child(even) td {
      background-color: #f9fbfd;
    }
    tr:hover td {
      background-color: #f0f7ff;
    }
    // 第一行作為表頭樣式
    tr:first-child td {
      background-color: #f5f7fa;
      font-weight: 600;
      color: #2c3e50;
      border-bottom: 2px solid #ddd;
    }
    // 響應(yīng)式設(shè)計(jì)
    @media (max-width: 768px) {
      font-size: 12px;
      th,
      td {
        padding: 6px 8px;
        min-width: 60px;
        max-width: 120px;
      }
    }
  }
  .formula-cell::after {
    content: '?';
    position: absolute;
    top: 4px;
    right: 4px;
    font-size: 10px;
    color: #2c80c5;
    font-weight: bold;
  }
  .error-cell {
    background-color: #fff0f0 !important;
    color: #e74c3c;
    font-weight: 500;
  }
  .empty-message {
    height: 100%;
    display: flex;
    align-items: center;
    justify-content: center;
    flex-direction: column;
    color: #a0aec0;
    background: #fafafa;
    border-radius: 8px;
  }
  .empty-icon {
    font-size: 72px;
    margin-bottom: 25px;
    color: #cbd5e0;
  }
  .empty-text {
    font-size: 22px;
    margin-bottom: 15px;
    font-weight: 500;
  }
  .empty-subtext {
    font-size: 16px;
    color: #909399;
    max-width: 500px;
    text-align: center;
    line-height: 1.6;
  }
  .empty-cell {
    background-color: #fafafa !important;
    color: #ccc;
    font-style: italic;
  }
  .empty-cell::after {
    content: '—';
    color: #ddd;
    font-style: normal;
  }
</style>