前端excel表格解析为json，并模仿excel显示

前端环境：elementUI  vue2   <style lang="scss" scoped>

页面效果

• jsondata为mock数据，为方便调试其内容可清空，
• 首行（字母坐标）随数据内容自动变化，首列也是一样，模拟excel 坐标的样式

<template>
  <div class="mainBox">
    <div class="excel-uploader">
      <el-upload action="" :before-upload="handleFileUpload" :show-file-list="false">
        <el-button size="small" type="primary">导入表格</el-button>
      </el-upload>
      <!-- <div v-if="jsonData.length" class="json-preview">
        <h3>解析后的 JSON 数据：</h3>
        <pre>{{ jsonData }}</pre>
      </div> -->
    </div>
    <div class="excel-table-container">
      <table class="excel-table">
        <thead>
          <tr>
            <th></th>
            <th v-for="(header, index) in headers" :key="index" class="editable-cell">{{ header }}</th>
          </tr>
        </thead>
        <tbody>
          <tr v-for="(row, rowIndex) in rows" :key="rowIndex">
            <th class="editable-cell indexno">{{ 1 + rowIndex }}</th>
            <td v-for="(cell, cellIndex) in row" :key="cellIndex" class="editable-cell"
              :class="{ selected: selectedCell.row === rowIndex && selectedCell.col === cellIndex }"
              @click="selectCell(rowIndex, cellIndex)">
              <span>{{ cell.value }}</span>
            </td>
          </tr>
        </tbody>
      </table>
    </div>
</div>
</template>

<script>
import * as XLSX from 'xlsx';
import {
  saveTemplate,
} from "@/api/template";
export default {
  data() {
    return {
      headers: [],// ['A', 'B', 'C', 'D', 'E']
      rows: [
        ['A1', 'B1', 'CQ', '', ''],
        ['非货币性资产交换', 'B2', '', '', ''],
        // Add more rows as needed
      ],
      selectedCell: { row: 0, col: 0 },
      isEditingCell: false,
      jsonData: [
        {
          "A1": {
            "value": "项目",
            "formula": null
          },
          "B1": {
            "value": "会计确认的处置收入",
            "formula": null
          },
          "C1": {
            "value": "处置收入调增",
            "formula": null
          },
          "D1": {
            "value": "处置收入调减",
            "formula": null
          },
          "E1": {
            "value": "税收计算的处置收入",
            "formula": null
          },
          "F1": {
            "value": "处置投资的账面价值",
            "formula": null
          },
          "G1": {
            "value": "处置投资的计税基础",
            "formula": null
          },
          "H1": {
            "value": "会计确认的处置所得或损失",
            "formula": null
          },
          "I1": {
            "value": "税收计算的处置所得",
            "formula": null
          },
          "J1": {
            "value": "纳税调整金额",
            "formula": null
          }
        },
        {
          "A2": {
            "value": "交易性金融资产",
            "formula": null
          },
          "E2": {
            "value": 0,
            "formula": "B2+C2-D2"
          },
          "H2": {
            "value": 0,
            "formula": "B2-F2"
          },
          "I2": {
            "value": 0,
            "formula": "E2-G2"
          },
          "J2": {
            "value": 0,
            "formula": "I2-H2"
          }
        },
        {
          "A3": {
            "value": "可供出售金融资产",
            "formula": null
          }
        },
        {
          "A4": {
            "value": "持有至到期投资",
            "formula": null
          }
        },
        {
          "A5": {
            "value": "衍生工具",
            "formula": null
          }
        },
        {
          "A6": {
            "value": "交易性金融负债",
            "formula": null
          }
        },
        {
          "A7": {
            "value": "长期股权投资",
            "formula": null
          },
          "E7": {
            "value": 0,
            "formula": "B7+C7-D7"
          },
          "H7": {
            "value": 0,
            "formula": "B7-F7"
          },
          "I7": {
            "value": 0,
            "formula": "E7-G7"
          },
          "J7": {
            "value": 0,
            "formula": "I7-H7"
          }
        },
        {
          "A8": {
            "value": "短期投资",
            "formula": null
          },
          "E8": {
            "value": 0,
            "formula": "B8+C8-D8"
          },
          "H8": {
            "value": 0,
            "formula": "B8-F8"
          },
          "I8": {
            "value": 0,
            "formula": "E8-G8"
          },
          "J8": {
            "value": 0,
            "formula": "I8-H8"
          }
        },
        {
          "A9": {
            "value": "长期债券投资",
            "formula": null
          }
        },
        {
          "A10": {
            "value": "其他",
            "formula": null
          }
        },
        {
          "A11": {
            "value": "合计",
            "formula": null
          },
          "B11": {
            "value": 0,
            "formula": "SUM(B2:B10)"
          },
          "E11": {
            "value": 0,
            "formula": "SUM(E2:E10)"
          }
        }
      ],

    };
  },
  created() {
    this.transformJsonToTableData();
  },
  methods: {
    selectCell(row, col) {
      console.log("dff", row, col);

      this.selectedCell = { row, col };
      this.startEditing();
    },
    startEditing() {
      this.isEditingCell = true;
    },
    stopEditing() {
      this.isEditingCell = false;
    },
    isEditing(row, col) {
      return this.isEditingCell && this.selectedCell.row === row && this.selectedCell.col === col;
    },
    // excel 解析

    // handleFileUpload(file) {//如果只要 坐标和值用这个，{A1:"值"}
    //       const reader = new FileReader();

    //       reader.onload = (e) => {
    //         const data = new Uint8Array(e.target.result);
    //         const workbook = XLSX.read(data, { type: 'array' });

    //         // 假设我们只解析第一个工作表
    //         const firstSheetName = workbook.SheetNames[0];
    //         const worksheet = workbook.Sheets[firstSheetName];

    //         // 将工作表转换为坐标格式的 JSON
    //         const jsonData = this.sheetToJsonWithCoordinates(worksheet);
    //         this.jsonData = jsonData;
    //       };

    //       reader.readAsArrayBuffer(file);

    //       // 阻止上传动作，因为我们只是读取文件内容
    //       return false;
    //     },
    //     sheetToJsonWithCoordinates(worksheet) {
    //       const range = XLSX.utils.decode_range(worksheet['!ref']);
    //       const result = [];

    //       for (let row = range.s.r; row <= range.e.r; row++) {
    //         const rowData = {};
    //         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];
    //           if (cell && cell.v !== undefined) {
    //             rowData[cellAddress] = cell.v;
    //           }
    //         }
    //         result.push(rowData);
    //       }

    //       return result;
    //     }
    //   },

    handleFileUpload(file) {
      const reader = new FileReader();

      reader.onload = (e) => {
        const data = new Uint8Array(e.target.result);
        const workbook = XLSX.read(data, { type: 'array' });

        // 假设我们只解析第一个工作表
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        // 将工作表转换为包含坐标、样式和公式的 JSON
        const jsonData = this.sheetToJsonWithCoordinatesStylesAndFormulas(worksheet);
        this.jsonData = jsonData;
      };

      reader.readAsArrayBuffer(file);

      // 阻止上传动作，因为我们只是读取文件内容
      this.transformJsonToTableData()
      return false;
    },
    sheetToJsonWithCoordinatesStylesAndFormulas(worksheet) {
      const range = XLSX.utils.decode_range(worksheet['!ref']);
      const result = [];

      for (let row = range.s.r; row <= range.e.r; row++) {
        const rowData = {};
        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];
          if (cell && cell.v !== undefined) {
            rowData[cellAddress] = {
              value: cell.v,
              formula: cell.f || null, // 如果单元格有公式，则包含公式信息
              // style:this.getCellStyle(cell.s, rowData[cellAddress],cell)
            };
          }
        }
        result.push(rowData);
      }
      return result;
    },
    transformJsonToTableData() {
      // Determine the maximum column index needed
      let maxColIndex = 0;
      this.jsonData.forEach(data => {
        const keys = Object.keys(data);
        keys.forEach(key => {
          const colIndex = key.charCodeAt(0) - 'A'.charCodeAt(0);
          if (colIndex > maxColIndex) {
            maxColIndex = colIndex;
          }
        });
      });

      // Generate headers based on the maximum column index
      this.headers = Array.from({ length: maxColIndex + 1 }, (_, i) => String.fromCharCode(65 + i));

      // Initialize rows based on the maximum number of columns
      this.rows = this.jsonData.map(data => {
        const row = Array(this.headers.length).fill({ value: '' });
        const keys = Object.keys(data);
        keys.forEach(key => {
          const colIndex = key.charCodeAt(0) - 'A'.charCodeAt(0);
          if (colIndex < row.length) {
            row[colIndex] = data[key];
          }
        });
        return row;
      });
    },
  },
  mounted() {
    this.ruleForm.createUserName = this.name
  }
};
</script>

<style scoped lang="stylus">
.excel-uploader {
  margin-bottom :12px;
  .json-preview {
    padding: 10px;
    border: 1px solid #ebeef5;
    border-radius: 4px;
    background-color: #f5f7fa;
 
    pre {
      white-space: pre-wrap; /* 保留换行符 */
      word-wrap: break-word; /* 长单词换行 */
    }
  }
}
.excel-table-container {
  width: 100%;
  overflow-x: auto;
}
 
.excel-table {
  width: 100%;
  border-collapse: collapse;
 
  th, td { 
    border: 1px solid #E6E8EA;
    padding: 8px;
    text-align: left;
    min-width: 100px;
  }
 
  th {
   background-color: #F4F6F8;
    min-width: 40px;

  }
  .editable-cell {
    
    &:hover {
      background-color: #e0e0e0;
      cursor: pointer;
    }
 
    &.selected {
      border: 2px solid #165DFF;
    }
  }
}
</style>