0

Does anybody knows how to achive this? I have my html table with some columns with display none, and with javascript i have a export to excel button...all works well, but e excel file generated i also have columns that are with display none...i give to those columns a class (named notToExport), how can i generated the excel file without those columns?

function exportTableToExcel(teste, filename = 'contacorrente'){

    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(teste);
    var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');
    
    
    // Specify file name
    filename = filename?filename+'.xls':'excel_data.xls';
    
    // Create download link element
    downloadLink = document.createElement("a");
    
    document.body.appendChild(downloadLink);
    
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTML], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        // Create a link to the file
        downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
    
        // Setting the file name
        downloadLink.download = filename;
        
        //triggering the function
        downloadLink.click();
    }
}   
<button onclick="exportTableToExcel('teste')">export to excel</button>

<div class="container">
    <div class="row col-md-12 custyle">
    <table id="teste" class="table table-striped custab">
    <thead>
    <a href="#" class="btn btn-danger btn-xs pull-right" style="display:none;">Ver por reguralizar</a>
        <tr>
            <th class="notToExport" style="display:none;">Email</th>
            <th>date</th>
            <th>old date</th>
            <th>Document</th>
            <th>number doc</th>
            <th>debt</th>
            <th class="notToExport" style="display:none;">debf</th>
            <th>cred</th>
            <th class="notToExport" style="display:none;">credf</th>
            <th class="notToExport" style="display:none;">obs</th>
            <th class="notToExport" style="display:none;">erpid</th>
            <th>result</th>
        </tr>
    </thead>
    <tbody>
        
            <tr>
                <td class="notToExport" style="display:none;">[email protected]</td>
                <td>14-10-1977</td>
                <td>16-10-1977</td>
                <td>document</td>
                <td>123</td>
                <td>245</td>
                <td class="notToExport" style="display:none;">245</td>
                <td>12</td>
                <td class="notToExport" style="display:none;">12</td>
                <td class="notToExport" style="display:none;">hfksdfhjhdsf</td>
                <td class="notToExport" style="display:none;">gsdfgjhfj</td>
                <td>43</td>
                
            </tr>
            <tr>
                <td class="notToExport" style="display:none;">[email protected]</td>
                <td>14-11-1977</td>
                <td>16-11-1977</td>
                <td>document</td>
                <td>23</td>
                <td>25</td>
                <td class="notToExport" style="display:none;">24</td>
                <td>2</td>
                <td class="notToExport" style="display:none;">2</td>
                <td class="notToExport" style="display:none;">hfksdfhjhdsf</td>
                <td class="notToExport" style="display:none;">gsdfgjhfj</td>
                <td>3</td>
                
            </tr>
           <tr>
                <td class="notToExport" style="display:none;">[email protected]</td>
                <td>14-12-1977</td>
                <td>16-12-1977</td>
                <td>document</td>
                <td>3</td>
                <td>2</td>
                <td class="notToExport" style="display:none;">4</td>
                <td>2</td>
                <td class="notToExport" style="display:none;">2</td>
                <td class="notToExport" style="display:none;">hfksdfhjhdsf</td>
                <td class="notToExport" style="display:none;">gsdfgjhfj</td>
                <td>3</td>
                
            </tr>
    </tbody>
    <tfoot>
            <tr>
               <th class="notToExport" style="display:none;">Email</th>
                <th colspan="4">TOTAL</th>
                <th class="notToExport" style="display:none;"></th>
                <th class="notToExport" style="display:none;"></th>
                <th class="notToExport" style="display:none;"></th>
                <th>24</th>
                <th class="notToExport" style="display:none;">debf</th>
                <th>44</th>
                <th class="notToExport" style="display:none;">credf</th>
                <th class="notToExport" style="display:none;">obs</th>
                <th class="notToExport" style="display:none;">erpid</th>
                <th>434</th>
                
            </tr>
         </tfoot>
            
    </table>
    </div>
</div>

1 Answer 1

1

You could use the JS selector "getElementsByClassName" and remove them all from the DOM before exporting your data to Excel.

function exportTableToExcel(teste, filename = 'contacorrente'){

var downloadLink;
var dataType = 'application/vnd.ms-excel';

// Find all items that need to be removed
var elements = document.getElementsByClassName("notToExport");

// Loop through items and remove from the DOM
for(var i=0; i < elements.length; i++)
{
    elements[i].remove();
  
}

var tableSelect = document.getElementById(teste);
var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');


// Specify file name
filename = filename?filename+'.xls':'excel_data.xls';

// Create download link element
downloadLink = document.createElement("a");

document.body.appendChild(downloadLink);

if(navigator.msSaveOrOpenBlob){
    var blob = new Blob(['\ufeff', tableHTML], {
        type: dataType
    });
    navigator.msSaveOrOpenBlob( blob, filename);
}else{
    // Create a link to the file
    downloadLink.href = 'data:' + dataType + ', ' + tableHTML;

    // Setting the file name
    downloadLink.download = filename;
    
    //triggering the function
    downloadLink.click();
}
}  
    <button onclick="exportTableToExcel('teste')">export to excel</button>

    <div class="container">
        <div class="row col-md-12 custyle">
        <table id="teste" class="table table-striped custab">
        <thead>
        <a href="#" class="btn btn-danger btn-xs pull-right" style="display:none;">Ver por reguralizar</a>
            <tr>
                <th class="notToExport" style="display:none;">Email</th>
                <th>date</th>
                <th>old date</th>
                <th>Document</th>
                <th>number doc</th>
                <th>debt</th>
                <th class="notToExport" style="display:none;">debf</th>
                <th>cred</th>
                <th class="notToExport" style="display:none;">credf</th>
                <th class="notToExport" style="display:none;">obs</th>
                <th class="notToExport" style="display:none;">erpid</th>
                <th>result</th>
            </tr>
        </thead>
        <tbody>
            
                <tr>
                    <td class="notToExport" style="display:none;">[email protected]</td>
                    <td>14-10-1977</td>
                    <td>16-10-1977</td>
                    <td>document</td>
                    <td>123</td>
                    <td>245</td>
                    <td class="notToExport" style="display:none;">245</td>
                    <td>12</td>
                    <td class="notToExport" style="display:none;">12</td>
                    <td class="notToExport" style="display:none;">hfksdfhjhdsf</td>
                    <td class="notToExport" style="display:none;">gsdfgjhfj</td>
                    <td>43</td>
                    
                </tr>
                <tr>
                    <td class="notToExport" style="display:none;">[email protected]</td>
                    <td>14-11-1977</td>
                    <td>16-11-1977</td>
                    <td>document</td>
                    <td>23</td>
                    <td>25</td>
                    <td class="notToExport" style="display:none;">24</td>
                    <td>2</td>
                    <td class="notToExport" style="display:none;">2</td>
                    <td class="notToExport" style="display:none;">hfksdfhjhdsf</td>
                    <td class="notToExport" style="display:none;">gsdfgjhfj</td>
                    <td>3</td>
                    
                </tr>
               <tr>
                    <td class="notToExport" style="display:none;">[email protected]</td>
                    <td>14-12-1977</td>
                    <td>16-12-1977</td>
                    <td>document</td>
                    <td>3</td>
                    <td>2</td>
                    <td class="notToExport" style="display:none;">4</td>
                    <td>2</td>
                    <td class="notToExport" style="display:none;">2</td>
                    <td class="notToExport" style="display:none;">hfksdfhjhdsf</td>
                    <td class="notToExport" style="display:none;">gsdfgjhfj</td>
                    <td>3</td>
                    
                </tr>
        </tbody>
        <tfoot>
                <tr>
                   <th class="notToExport" style="display:none;">Email</th>
                    <th colspan="4">TOTAL</th>
                    <th class="notToExport" style="display:none;"></th>
                    <th class="notToExport" style="display:none;"></th>
                    <th class="notToExport" style="display:none;"></th>
                    <th>24</th>
                    <th class="notToExport" style="display:none;">debf</th>
                    <th>44</th>
                    <th class="notToExport" style="display:none;">credf</th>
                    <th class="notToExport" style="display:none;">obs</th>
                    <th class="notToExport" style="display:none;">erpid</th>
                    <th>434</th>
                    
                </tr>
             </tfoot>
                
        </table>
        </div>
    </div>

If you need to retain the original data, clone the table first and then remove the elements from that.

// Create a clone of element with id ddl_1:
let tableSelect = document.getElementById(teste).cloneNode(true);

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.