Export to excel with format text for column B,C and D

don2

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

colin

You would use orthogonal data data for that, as demonstrated by this example from this thread,

Colin

don2

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?

                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 c[r^="B"], row c[r^="C"], row c[r^="D"]', sheet).attr('t', 's'); //.attr('s', 50);
                    },
                    messageTop: "List of SIMs"
                }
            ]

allan

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

don2

I have commented out lines #12 & 13, the problem doesn't occur, any idea?

allan

It means your customisation is causing invalid Excel XML to be created.

attr('t', 's');

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