html 导出页面数据至表格Excel

冷不防 2023-09-27 23:46 53阅读 0赞

**简介：**

将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：