前端本地文件获取excel表格内容，并渲染在页面

淇淇包.

已于 2024-12-19 14:21:34 修改

阅读量1.1k

点赞数 5

文章标签： excel html5 jquery

于 2024-12-19 14:13:57 首次发布

本文链接：https://blog.csdn.net/weixin_45183187/article/details/144584161

版权

插件：SheetJs ，如若想要了解更多，可参考官网：SheetJS 中文网

效果图：

方法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], '职业类别表.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();

        // 监听滚动事件，在接近底部时加载更多数据
        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 = ['大分类', '中分类', '小分类', '代码', '细类', '职业类别'];
    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 = ['大分类', '中分类', '小分类', '代码', '细类', '职业类别'];
      headers.forEach(header => {
        if (!mergeInfo[header]) {
          mergeInfo[header] = [];
          currentMergeCounts[header] = 0;
        }

        // 如果当前行缺少该字段，则认为它是前一行的延续
        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 = ['大分类', '中分类', '小分类', '代码', '细类', '职业类别'];

    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>