如何用JavaScript将页面多个HTML表格合并导出至Excel？

问题背景 #

仅掌握基础HTML知识，对JavaScript了解甚少。当前内联网使用Tridion系统，需要通过代码块组件实现HTML表格导出至Excel功能，但现有JavaScript代码仅能导出单个表格，而页面存在多个格式一致的表格，求能将所有表格合并导出至同一份Excel的JavaScript代码。

原HTML结构 #

<button onclick="exportTableToExcel('Dept1')">Download To Excel</button> 
<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
  </tr>
  </thead>
  <tbody>
  <tr id="Dept1">
    <td>Ted</td>
    <td>Sam</td>    
    <td>Sally</td>
    <td>John</td>
  </tr>
   </tbody>
</table>

<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
  </tr>
  </thead>
  <tbody>
  <tr id="Dept2">
    <td>Bird</td>
    <td>Cat</td>    
    <td>Dog</td>
    <td>Bunny</td>
  </tr>
   </tbody>
</table>

<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
  </tr>
  </thead>
  <tbody>
  <tr id="Dept3">
    <td>Star</td>
    <td>Sun</td>    
    <td>Moon</td>
    <td>Cloud</td>
  </tr>
   </tbody>
</table>

原JavaScript代码（仅支持单个表格导出） #

<script>
function exportTableToExcel(dept1, filename = ''){
    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(dept1);
    var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');
    
    filename = filename?filename+'.xls':'Departments';
    
    downloadLink = document.createElement("a");
    document.body.appendChild(downloadLink);
    
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTML], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
        downloadLink.download = filename;
        downloadLink.click();
    }
}
</script>

解决方案：合并所有表格导出的代码 #

修改后的HTML（更新按钮事件） #

<button onclick="exportAllTablesToExcel()">Download All Tables To Excel</button> 
<!-- 以下表格结构保留原内容，注意修正未闭合的<td>标签 -->
<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
  </tr>
  </thead>
  <tbody>
  <tr id="Dept1">
    <td>Ted</td>
    <td>Sam</td>    
    <td>Sally</td>
    <td>John</td>
  </tr>
   </tbody>
</table>

<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
  </tr>
  </thead>
  <tbody>
  <tr id="Dept2">
    <td>Bird</td>
    <td>Cat</td>    
    <td>Dog</td>
    <td>Bunny</td>
  </tr>
   </tbody>
</table>

<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
  </tr>
  </thead>
  <tbody>
  <tr id="Dept3">
    <td>Star</td>
    <td>Sun</td>    
    <td>Moon</td>
    <td>Cloud</td>
  </tr>
   </tbody>
</table>

修改后的JavaScript（支持多表格合并导出） #

<script>
function exportAllTablesToExcel(filename = '') {
    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    // 创建临时合并表格
    var mergedTable = document.createElement('table');
    mergedTable.border = "1";
    mergedTable.width = "100%";
    mergedTable.cellSpacing = "2";
    mergedTable.cellPadding = "3";

    // 获取页面所有表格
    var allTables = document.querySelectorAll('table');
    if (allTables.length === 0) return;

    // 取第一个表格的表头作为合并表格的表头
    var firstTableHead = allTables[0].querySelector('thead');
    if (firstTableHead) {
        mergedTable.appendChild(firstTableHead.cloneNode(true));
    }

    // 合并所有表格的tbody内容
    var mergedTbody = document.createElement('tbody');
    allTables.forEach(table => {
        var tbody = table.querySelector('tbody');
        if (tbody) {
            var rows = tbody.querySelectorAll('tr');
            rows.forEach(row => {
                mergedTbody.appendChild(row.cloneNode(true));
            });
        }
    });
    mergedTable.appendChild(mergedTbody);

    // 处理表格HTML格式
    var tableHTML = mergedTable.outerHTML.replace(/ /g, '%20');
    
    // 设置导出文件名
    filename = filename ? filename + '.xls' : 'AllDepartments';
    
    // 创建下载链接并触发下载
    downloadLink = document.createElement("a");
    document.body.appendChild(downloadLink);
    
    if (navigator.msSaveOrOpenBlob) {
        var blob = new Blob(['\ufeff', tableHTML], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob(blob, filename);
    } else {
        downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
        downloadLink.download = filename;
        downloadLink.click();
    }
    
    // 清理临时元素
    document.body.removeChild(downloadLink);
}
</script>

使用说明 #

1. 将原按钮的onclick事件替换为exportAllTablesToExcel()
2. 替换原JavaScript代码为上述新代码
3. 确保原HTML中所有<td>标签都已正确闭合，避免导出格式异常

修改后点击按钮，即可将页面上所有表格的内容合并到单个Excel文件中，表头仅保留一次，所有表格的数据行依次排列。

内容的提问来源于stack exchange，提问作者shimkoko