rui_sonika
Reputation: 45
remove columns in export to excel javascript
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>Upvotes: 0
Views: 1872
Answers (1)
Ross
Reputation: 126
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);Upvotes: 1
Related Questions
- Hide column while exporting to excel from html table using javascript
- Exporting table data to XLSX, but remove columns from export using an array
- Delete column in excel file using ExcelJS
- Delete specific column when importing to excel
- Exclude column when Exporting table in javascript
- Exclude table columns while exporting it into Excel in JavaScript
- MERGE column when export excel using javascript?
- Exclude Specific Columns when exporting data from HTML table to excel
- export html to excel excluding some cells or columns
- How to remove column during export report in csv?