Export to Excel without having to copy from TSV export

[cfmitrah]

We are having an option TSV export in pivottable.js that just displays the table values in the text area. Instead is there any possible way to directly export to excel without showing the scrambled details to the user? That way the user will only see the table default view not the text area.

By this way the user will see the values that are selected and just exports the data that are seen there.

[nicolaskruchten]

This is not a feature of this library, but can likely be implemented with https://github.com/sheetjs

[paulovieira]

An alternative to using sheetjs (which is a heavy dependency) would be to show the native "save as" dialog to the user (which can be easily done with the file-saver module - https://github.com/eligrey/FileSaver.js ). It would still be tsv, but the user wouldn't have to see it.

[PRINCEHR]

Guys, You can try this code

// In body

Export Table Data To Excel File

<script type="text/javascript">
  function exportTableToExcel(tableID, filename = ''){
var downloadLink;
var dataType = 'application/vnd.ms-excel';
var tableSelect = document.getElementById(tableID);
var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');

filename = filename?filename+'.xls':'excel_data.xls';
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>

//In pivot.js

pivot.js link in which can be used in which modification is already made
modified pivot js
After Inline No:955
result.setAttribute("id", "testTable");

Hope this will help you guys

[kalimuthu123]

i tried this but when the data is large it fails

[ernestina]

This is not a feature of this library, but can likely be implemented with https://github.com/sheetjs

thank you, sheetjs is very helpfull. i just need to add:
result.id = "data-table";
after result = document.createElement("table"); result.className = "pvtTable";
in pivot.js to match sheetjs function (they hadvar elt = document.getElementById('data-table'); in that function) and done!

[BasselFouad]

Hey can you please explain how you used sheetjs to export to an excel file ?

[ernestina]

Hey can you please explain how you used sheetjs to export to an excel file ?

1. install & implement sheetjs on your UI scripts (check on sheetjs manual on github)
2. from pivottable js, open pivot.js, search line result = document.createElement("table"); result.className = "pvtTable";
3. add line result.id = "data-table";
4. in your UI (e.g. excel button download) don't forget to add id "data-table" in your table e.g. <table id="data-table">

i hope those are easy enough to understand

[amankhan25]

You can simply use TableToExcel.js library

example code: if you are using container with div id="Wrapper" and click button class="generate"

$(function() {
$("button.generate").click(function(e) {
TableToExcel.convert(document.getElementById("Wrapper"), {
name: "Report.xlsx",
sheet: {
name: "Sheet 1"
}
});
});

});

[in2uitions]

I used the built in TSV export to get the data and then used a modified version of PRINCEHR solution above to get the resulted textarea as a csv
function delay(time) {
return new Promise(resolve => setTimeout(resolve, time));
}
async function TableToCSV() {
$(".pvtRenderer").val("TSV Export").change();
await delay(5000).then(() => console.log('ran after 1 second1 passed'));
filename = "Database" + "{{database.name}}" + "Indicators" + formatDate(Date())
var downloadLink;
var dataType = 'application/csv';
var data = $("textarea").val().replaceAll('#', ' ')

    filename = filename ? filename + '.csv' : 'csv_data.csv';
    downloadLink = document.createElement("a");
    document.body.appendChild(downloadLink);

    if (navigator.msSaveOrOpenBlob) {
        var blob = new Blob(['\ufeff', data], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob(blob, filename);
    } else {
        downloadLink.href = 'data:' + dataType + ', ' + data;
        downloadLink.download = filename;
        downloadLink.click();
    }
}

[jazo2212]

Chicos, pueden probar este código.

// En cuerpo

Exportar datos de tabla a un archivo de Excel

<script type="text/javascript">
  function exportTableToExcel(tableID, filename = ''){
var downloadLink;
var dataType = 'application/vnd.ms-excel';
var tableSelect = document.getElementById(tableID);
var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');

filename = filename?filename+'.xls':'excel_data.xls';
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>

//En pivot.js

Enlace pivot.js en el que se puede utilizar en el que ya se realizó la modificación pivot js modificado después del número de línea: 955 result.setAttribute("id", "testTable");

Espero que esto les ayude chicos

Hola, el detalle que tiene es que los valores numéricos los exporta como texto, la idea es que sean numérico para tener la opción de poder seguir haciendo cálculos ya en la hoja Excel