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

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

don2don2 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

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

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

    Colin

  • don2don2 Posts: 27Questions: 15Answers: 0

    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"
                    }
                ]
    
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

  • don2don2 Posts: 27Questions: 15Answers: 0

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

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

Sign In or Register to comment.