Export to excel with format text for column B,C and D
Export to excel with format text for column B,C and D
don2
Posts: 27Questions: 15Answers: 0
I'm trying to do export to excel, but I need to format text for column B, C and D, the data starts at row 4, I have tried the following:
dom: '<"card-header border-bottom p-1"<"head-label"><"dt-action-buttons text-end"B>><"d-flex justify-content-between align-items-center mx-0 row"<"col-sm-12 col-md-6"l><"col-sm-12 col-md-6"f>>t<"d-flex justify-content-between mx-0 row"<"col-sm-12 col-md-6"i><"col-sm-12 col-md-6"p>>',
buttons: [
{
text: 'Export',
className: 'btn btn-outline-success',
extend: 'excelHtml5',
title: 'SIMs',
exportOptions: {
columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13],
},
customize: function (xlsx)
{
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row b[r^="B"]', sheet).attr('s', '2');
$('row b[r^="C"]', sheet).attr('s', '2');
$('row b[r^="D"]', sheet).attr('s', '2');
},
messageTop: "List of SIMs"
}
]
but no luck
Answers
You would use orthogonal data data for that, as demonstrated by this example from this thread,
Colin
Hi Colin,
Thank you for your reply, I have tried below, excel file generated but it looks like corrupted!, it asked me to recover the excel (see enclosed screenshot)
what I missed?
Does that happen if you comment out lines 12 and 13 in the above?
The error you are seeing isn't one I've come across with our exported data in a long time now. The old TableTools library used to have that problem sometimes, but ever since we retired that and started using Buttons, it has been fine.
Allan
I have commented out lines #12 & 13, the problem doesn't occur, any idea?
It means your customisation is causing invalid Excel XML to be created.
is probably the issue. That marks the text in that field as a shared string but you aren't adding shared strings in your customisation. You might fine this page useful if you are going to work with the generated XML.
Allan