Is there any way to export table to excel with excelHtml5, with defined type for certain columns?
Is there any way to export table to excel with excelHtml5, with defined type for certain columns?
tjurinec
Posts: 5Questions: 2Answers: 0
I have a table in which one column has mixed values(text and numbers: {12AE15152}, {1531215}). My problem is that MS Excel formats number values as number(aligned on the right, and adds E+ for greater values) but i want it to be shown as simple text.
Here is what I have done so far:
var buttonCommon = {
exportOptions: {
trim: false,
format: {
body: function (data, column, row) {
// Strip $ from salary column to make it numeric
data = data.replace(/<span title=".+\">/, '');
data = data.replace(/<span class="fa fa-check text-success\">/, 'YES');
data = data.replace(/<span class="fa fa-minus text-danger\">/, 'NO');
data = data.replace(/<\/span>/, '');
return data.toString();
}
}
}
};
$("#userA_tbl").dataTable({
dom: 'Bfrtip',
buttons: [
$.extend(true, {}, buttonCommon, {
extend: 'excelHtml5',
title: '<fmt:message key="limit.change.history" />',
text: '<i class="fa fa-file-excel-o"></i>',
titleAttr: 'Excel'}),
$.extend(true, {}, buttonCommon, {
extend: 'pdfHtml5',
title: '<fmt:message key="limit.change.history" />',
orientation: 'landscape',
pageSize: 'A3',
text: '<i class="fa fa-file-pdf-o"></i>',
titleAttr: 'PDF'
})
],
"columnDefs": [
{"type": "html", "targets": "_all"}
]
});
This question has accepted answers - jump to:
This discussion has been closed.
Answers
Currently no - sorry. This is something that I plan to add as a feature in a future version, but at the moment the cell format is on a per cell basis and will be either numeric or string.
Allan
Hi allan, thanks for your response. I was wondering if it was possible to manually set all cells to be typeof String in buttons.html5.js ? Or is it possible in any other way to set all cells to be shown as text in xlsx file?
Sure - remove the number handling here.
Allan
It worked! Thanks a bunch.
@tjurinec Try to add 8203; before the value you want to keep as a simple text in Excel, width & and # before 8
Hi,
I did remove line 659 as suggested by Allan number handling
but it still didn't work. When I open the excel file, a message appear to recover the content (error1.jpg) and after I click Yes, the file loaded with a column that suppose to fix but still remain wrong (error2.jpg). You can refer error3.jpg for the log file. Am I removing the wrong line since @tjurinec got it worked?. I'm using Excel 2007 and my debug code is onesej. Thanks in advance.
Yeah that's the wrong line now. I should have linked to a particular version - sorry. This is the block where Buttons' Excel export will detect numbers. Just add
false ||at the start of the number detection.Allan
Thanks!