将HTML可见的表列内容导出到客户端，2010 Excel页面

Export HTML visible table column content to client side, 2010 Excel page

本文关键字：客户端 2010 页面 Excel HTML 更新时间：2023-09-26

使用F12调试，JQuery代码可以跳过隐藏的列单元格，只跳过没有隐藏的单元格，但>最后一条语句，window.open无法在2010 EXCEL页面上显示。以下代码经过简化以解决问题，无法将HTML表导出到2010 Execel

    <body>    
    <table id="myGrid">
     <tr><th style="display:">First Column</th>
     <th  style="display:">Second Column</th>
     <th  style="display:">Third Column</th>
     <th  style="display: none">Forth Column</th>
    </tr>
    <tr><td>  2</td><td>   two</td><td>   deux</td><td style="display: none">     zwei</td></tr>
    <tr><td>  3</td><td> three</td><td>  trois</td><td style="display: none">     drei</td></tr>
    <tr><td>  4</td><td>  four</td><td>quattre</td><td style="display: none">     vier</td></tr>
    <tr><td>  5</td><td>  five</td><td>   cinq</td><td style="display: none">f&uuml;nf</td></tr>
    <tr><td>  6</td><td>   six</td><td>    six</td><td style="display: none">    sechs</td></tr>
    </table>
    <br />
    Test: <input id="ExportExcel" type='submit' value='Export Excel'>
    <script type="text/javascript">
    $(document).ready(function () {
     $('#ExportExcel').click(function () {
        var html;
        var numofRows;
        var gTable = document.getElementById('myGrid');
        numofRows = gTable.rows.length - 1;
        var numofCells;
        var trhtml = "";
        numofCells = gTable.rows[0].cells.length - 1;
        for (r = 0; r <= numofRows; r++) {
            var c = 0;
            var tdhtml = "";
            for (c = 0; c <= numofCells; c++) {
                if (!(gTable.rows[r].cells[c].currentStyle.display == "none")) {
                    var tempstr = gTable.rows[r].cells[c].innerText;
                    tdhtml = tdhtml + "<td>" + gTable.rows[r].cells[c].innerText + "</td>";
                }
            }
            trhtml = trhtml + "<tr>" + tdhtml + "</tr>";
        }
        html = "<table border='1'>" + trhtml + "</table>";
        // MS OFFICE 2003  : data:application/vnd.ms-excel        
        // MS OFFICE 2007  : application/vnd.openxmlformats-officedocument.spreadsheetml.sheet        
        window.open('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,' + encodeURIComponent(html));
       });
     });
    </script>  
    </body>

Ans For the above question:
Here i added class to hidden td 
<table id="myGrid">
     <tr><th style="display:">First Column</th>
     <th  style="display:">Second Column</th>
     <th  style="display:">Third Column</th>
     <th  style="display: none">Forth Column</th>
    </tr>
    <tr><td>  2</td><td>   two</td><td>   deux</td><td class="xyz" style="display: none">     zwei</td></tr>
    <tr><td>  3</td><td> three</td><td>  trois</td><td class="xyz" style="display: none">     drei</td></tr>
    <tr><td>  4</td><td>  four</td><td>quattre</td><td class="xyz" style="display: none">     vier</td></tr>
    <tr><td>  5</td><td>  five</td><td>   cinq</td><td class="xyz" style="display: none">f&uuml;nf</td></tr>
    <tr><td>  6</td><td>   six</td><td>    six</td><td class="xyz" style="display: none">    sechs</td></tr>
    </table>
when click on export add this statement -> $('.xyz').remove();
like this
$('#ExportExcel').click(function () {
$('.xyz').remove();
// add export statements here
});
it will work

导出后，类xyz相关的td不会显示在excel中。

附加的JQuery代码将导出可见的列标题和行单元格内容到客户端Excel；只需复制并粘贴以下代码即可成为Javascript代码的一部分（插入问题代码），并将按钮id更改为ExportExcel2。注：假设：客户端已安装Excel。

   $('#ExportExcel2').click(function () {
        str = "";
        var myTable = document.getElementById('myGrid');
        var rows = myTable.getElementsByTagName('tr');
        var rowCount = myTable.rows.length;
        var colCount = myTable.getElementsByTagName("tr")[0].getElementsByTagName("th").length;
        var ExcelApp = new ActiveXObject("Excel.Application");
        var ExcelWorkbook = ExcelApp.Workbooks.Add();
        var ExcelSheet = ExcelWorkbook.ActiveSheet; //new ActiveXObject("Excel.Sheet"); 
        //ExcelSheet.Application.Visible = true;
        ExcelApp.Visible = true;
        ExcelSheet.Range("A1", "Z1").Font.Bold = true;
        ExcelSheet.Range("A1", "Z1").Font.ColorIndex = 23;
        //Format table headers
        var tarcol = 0;
        for (var i = 0; i < 1; i++) {
            targetCol = 1;
            for (var j = 0; j < colCount; j++) {
                if (!(myTable.getElementsByTagName("tr")[i].getElementsByTagName("th")[j].currentStyle.display == "none")) {
                    str = myTable.getElementsByTagName("tr")[i].getElementsByTagName("th")[j].innerHTML;
                    //ExcelSheet.Cells(i + 1, j + 1).Value = str;
                    ExcelSheet.Cells(i + 1, targetCol).Value = str;
                    targetCol += 1;
                }
            }
            ExcelSheet.Range("A1", "BD1").EntireColumn.AutoFit();
        }
        for (var i = 1; i < rowCount; i++) {
            targetCol = 1;
            for (var k = 0; k < colCount; k++) {
                if (!(myTable.getElementsByTagName("tr")[i].getElementsByTagName("td")[k].currentStyle.display == "none")) {
                    str = rows[i].getElementsByTagName('td')[k].innerHTML;
                    //ExcelSheet.Cells(i + 1, k + 1).Value = myTable.rows[i].cells[k].innerText;
                    ExcelSheet.Cells(i + 1, targetCol).Value = myTable.rows[i].cells[k].innerText;
                    targetCol += 1;
                }
            }
            ExcelSheet.Range("A" + i, "Z" + i).WrapText = true;
            ExcelSheet.Range("A" + 1, "Z" + i).EntireColumn.AutoFit();
        }
        //ExcelSheet.SaveAs("C:''TEST.XLS");
        //ExcelSheet.Application.Quit();
    });