html 导出页面数据至表格Excel

最新推荐文章于 2024-08-01 13:23:22 发布

王家五哥

最新推荐文章于 2024-08-01 13:23:22 发布

阅读量1.1k

点赞数 1

文章标签： excel html powerpoint

本文链接：https://blog.csdn.net/qq_36521848/article/details/130762838

版权

简介：

将html中的表格下载下来，使用js组件：xlsx.full.min.js

完整示例：

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>导出表格到 Excel 文件</title>
  </head>
  <body>
    <table id="myTable">
      <thead>
        <tr>
          <th>姓名</th>
          <th>年龄</th>
          <th>分数</th>
          <th>分数2</th>
          <th>分数3</th>
          <th>分数4</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>张三</td>
          <td>20</td>
          <td>88.5</td>
          <td>5292.55999999999556</td>
          <td>174.57999999999998</td>
        </tr>
        <tr>
          <td>李四</td>
          <td>21</td>
          <td>92.0</td>
		  <td>5292.55999999999557</td>
		  <td>0601012023041453</td>
        </tr>
        <tr>
          <td>王五</td>
          <td>22</td>
          <td>85.5</td>
		  <td>5292.55999999999558</td>
		  <td>1098556414760812544</td>
        </tr>
      </tbody>
    </table>

    <button onclick="exportToExcel('myTable', [], [])">导出 Excel</button>

    <script src="https://cdn.bootcdn.net/ajax/libs/xlsx/0.16.9/xlsx.full.min.js"></script>
    <script>
      function exportToExcel(tableId, excludeRows, excludeColumns) {
		  debugger;
        // 获取表格
        var table = document.getElementById(tableId);

        // 获取表头和数据行
        var thead = table.querySelector('thead');
        var tbody = table.querySelector('tbody');

        // 获取表头中的列名
        var columns = [];
        var ths = thead.querySelectorAll('th');
        for (var i = 0; i < ths.length; i++) {
          columns.push(ths[i].textContent);
        }

        // 获取数据行中的单元格数据
        var data = [];
        var trs = tbody.querySelectorAll('tr');
        for (var i = 0; i < trs.length; i++) {
          var tds = trs[i].querySelectorAll('td');
          var row = [];
          for (var j = 0; j < tds.length; j++) {
            row.push(tds[j].textContent);
          }
          data.push(row);
        }

        // 排除指定的行和列
        for (var i = excludeRows.length - 1; i >= 0; i--) {
          data.splice(excludeRows[i], 1);
        }
        for (var i = excludeColumns.length - 1; i >= 0; i--) {
          columns.splice(excludeColumns[i], 1);
          for (var j = 0; j < data.length; j++) {
            data[j].splice(excludeColumns[i], 1);
          }
        }

        // 创建一个工作簿
        var workbook = XLSX.utils.book_new();

        // 创建一个工作表
        var worksheet = XLSX.utils.aoa_to_sheet([columns].concat(data));

        // 将单元格格式设置为文本
        var range = XLSX.utils.decode_range(worksheet['!ref']);
        for (var R = range.s.r; R <= range.e.r; ++R) {
          for (var C = range.s.c; C <= range.e.c; ++C) {
            var cellAddress = { c: C, r: R };
            var cell = worksheet[XLSX.utils.encode_cell(cellAddress)];
			// if(cell.z!=undefined){
				try{
					if (!cell || !cell.s) continue;
					    cell.s.border = {
					      top: { style: 'thin', color: { rgb: '000000' } },
					      bottom: { style: 'thin', color: { rgb: '000000' } },
					      left: { style: 'thin', color: { rgb: '000000' } },
					      right: { style: 'thin', color: { rgb: '000000' } }
					    };
				}catch(e){
					//TODO handle the exception
				}
				try{
					cell.z = '@';
				}catch(e){
					//TODO handle the exception
				}
			// }
          }
        }

        // 将工作表添加到工作簿
        XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

        // 将工作簿导出为 XLSX 文件
        var wbout = XLSX.write(workbook, { bookType: 'xlsx', type: 'binary' });

        // 将二进制数据转换为 Blob 对象
        var blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' });

        // 下载文件
        var link = document.createElement('a');
        link.href = window.URL.createObjectURL(blob);
        link.download = 'export.xlsx';
        document.body.appendChild(link);
        link.click();
      }

      // 将字符串转换为 ArrayBuffer 对象
      function s2ab(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i < s.length; i++) {
          view[i] = s.charCodeAt(i) & 0xff;
        }
        return buf;
      }
    </script>
  </body>
</html>

xlsx.full.min.js：