前端本地文件獲取excel表格內容并渲染在頁面的方法

更新時間：2025年03月15日 08:41:47 作者：淇淇包.

這篇文章主要介紹了前端本地文件獲取excel表格內容并渲染在頁面的方法,主要利用SheetJS插件用于處理Excel文件,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

效果圖：

方法1： XLSX.readFile(filename, opts) 讀取指定文件并生成 SheetJS 工作簿對象

如果表格內容較少，體驗較好

如果表格內容很多，幾千行，則不推薦，數據量過大會導致頁面卡死，體驗差

代碼：

<!DOCTYPE html>
<html lang="cn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>可在線展示Excel的js插件</title>
    <style>
        html,body {
      width: 100%;
      height: 100%;
      padding: 0;
      margin: 0;
    }
    .container {
      overflow: auto;
      width: 100%;
      height: 100%;
    }
    table {
      width: 100%;
      border-collapse: collapse;
    }
    th, td {
      border: 1px solid #ddd;
      padding: 8px;
    }
    th {
      background-color: #f2f2f2;
      text-align: left;
    }
    </style>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
</head>
<body>
    <div class="container" id="container">

    </div>
    <script>
        var nContainer = document.getElementById('container');  
        var xlsx = new Plus.Xlsx(nContainer);
        xlsx.readFile('本地文件路徑');
    </script>
</body>
</html>

方法2：XLSX.read(data, opts) 解析文件數據并生成 SheetJS 工作簿對象

可獲取固定的數據格式，并處理懶加載方式，從而緩解數據量過大卡死現象

代碼：

<!DOCTYPE html>
<html lang="cn">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>可在線展示Excel的 js插件</title>
  <style>
    html,body {
      width: 100%;
      height: 100%;
      padding: 0;
      margin: 0;
    }
    .container {
      overflow: auto;
      width: 100%;
      height: 100%;
    }
    table {
      width: 100%;
      border-collapse: collapse;
    }
    th, td {
      border: 1px solid #ddd;
      padding: 8px;
    }
    th {
      background-color: #f2f2f2;
      text-align: left;
    }
  </style>
  <script type="text/javascript" src="/jquery-3.6.0.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
</head>
<body>
<div id="excel-data" class="container"></div>
<script type="text/javascript">
  const excelData = $('#excel-data');
  let allData = [];
  let loadedRows = 0;
  const pageSize = 50;

  let mergeInfo = {};
  let currentMergeCounts = {};
  async function loadStaticFile(filePath) {
    try {
      const response = await fetch(filePath);
      const blob = await response.blob();
      const file = new File([blob], '職業(yè)類別表.xls', { type: blob.type });
      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];
        allData = XLSX.utils.sheet_to_json(worksheet);

        // 初始化表格和合并信息
        initializeTable();
        calculateMergeInfo(allData);
        loadMoreRows();

        // 監(jiān)聽滾動事件，在接近底部時加載更多數據
        excelData.on('scroll', handleScroll);
      };

      reader.readAsArrayBuffer(file);
    } catch (error) {
      console.error('加載文件失敗:', error);
    }
  }
  loadStaticFile('/view/zhiyefenlei01_new.xls');

  function initializeTable() {
    let jobHtml = "<table border='1' cellpadding='5' cellspacing='0'>";
    // 構建表頭
    const headers = ['大分類', '中分類', '小分類', '代碼', '細類', '職業(yè)類別'];
    jobHtml += `<thead><tr>${headers.map(h => `<th>${h}</th>`).join('')}</tr></thead>`;
    jobHtml += "<tbody>";
    excelData.html(jobHtml + "</tbody></table>");
  }

  function calculateMergeInfo(data) {
    mergeInfo = {};
    currentMergeCounts = {};
    data.forEach((row, rowIndex) => {
      const headers = ['大分類', '中分類', '小分類', '代碼', '細類', '職業(yè)類別'];
      headers.forEach(header => {
        if (!mergeInfo[header]) {
          mergeInfo[header] = [];
          currentMergeCounts[header] = 0;
        }

        // 如果當前行缺少該字段，則認為它是前一行的延續(xù)
        if (row[header] === undefined || row[header] === '') {
          currentMergeCounts[header]++;
        } else {
          // 如果有未處理的合并項，更新之前的 rowspan
          if (currentMergeCounts[header] > 0) {
            const lastIndex = rowIndex - currentMergeCounts[header] - 1;
            mergeInfo[header][lastIndex] = currentMergeCounts[header] + 1;
            currentMergeCounts[header] = 0;
          }
          mergeInfo[header].push(0);
        }
      });

      // 處理最后一組合并項
      if (rowIndex === data.length - 1) {
        Object.keys(currentMergeCounts).forEach(header => {
          if (currentMergeCounts[header] > 0) {
            const lastIndex = rowIndex - currentMergeCounts[header];
            mergeInfo[header][lastIndex] = currentMergeCounts[header] + 1;
          }
        });
      }
    });
  }

  function generateMergedRow(row, rowIndex) {
    let rowHtml = "<tr>";
    const headers = ['大分類', '中分類', '小分類', '代碼', '細類', '職業(yè)類別'];

    headers.forEach(header => {
      if (row[header] !== undefined && row[header] !== '') {
        const rowspan = mergeInfo[header][rowIndex] || 1;
        rowHtml += `<td ${rowspan > 1 ? `rowspan="${rowspan}"` : ''}>${row[header]}</td>`;
      } else {
        // 跳過重復的單元格
        rowHtml += '';
      }
    });

    rowHtml += "</tr>";
    return rowHtml;
  }

  function loadMoreRows() {
    const dataSlice = allData.slice(loadedRows, loadedRows + pageSize);
    if (dataSlice.length === 0) return;

    let $tbody = $('#excel-data table tbody');
    dataSlice.forEach((row, rowIndex) => {
      $tbody.append(generateMergedRow(row, loadedRows + rowIndex));
    });

    loadedRows += dataSlice.length;
  }

  function handleScroll() {
    const $container = $(this);
    const scrollTop = $container.scrollTop();
    const scrollHeight = $container[0].scrollHeight;
    const clientHeight = $container.height();

    // 當滾動到底部附近時加載更多數據
    if (scrollTop + clientHeight >= scrollHeight - 50) {
      loadMoreRows();
    }
  }
</script>
</body>
</html>