filter and export excel

montoyam

I have a button where I would like to first apply a search to a table and then export it to excel. Is that possible? Right now I have them as two separate buttons just to test.

                {
                    text: 'filter', 
                    action: function (e, dt, node, config) {
                        InvoiceListingTable.search('7/1/2020');
                        InvoiceListingTable.draw();
                    }
                },
                {
                    extend: 'excelHtml5',
                    text: 'Auditor spreadsheet',
                    exportOptions: {
                        columns: '.excelExport'
                    },
                    customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('c[r=A1] t', sheet).text('ITC/Telecom Billing ' + AsOfCookie);
                        //get column A and assign '50' to the s tag (50=left align)
                        $('row c[r^="A"]', sheet).attr('s', '50');   //https://datatables.net/reference/button/excelHtml5
                    }
                }

allan

Yes - see the buttons.buttons.action page, specifically the last example on that page, to be able to call the built in button actions from a custom function.

Allan

montoyam

Nice. But, can I add my customization to that?

                    customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('c[r=A1] t', sheet).text('ITC/Telecom Billing ' + AsOfCookie);
                        //get column A and assign '50' to the s tag (50=left align)
                        $('row c[r^="A"]', sheet).attr('s', '50');   //https://datatables.net/reference/button/excelHtml5
                        $('row c[r^="B"]', sheet).attr('s', '50');  
                        $('row c[r^="D"]', sheet).attr('s', '64');  
                        $('row c[r^="E"]', sheet).attr('s', '64');  
                    }

and...

                    exportOptions: {
                        columns: '.excelExport'
                    },

kthorngren

Did you try adding the buttons.buttons.action to your button? Seems to work here:
http://live.datatables.net/yogegosu/1/edit

Kevin

montoyam

oooh, I see. I thought I was getting rid of the extend: 'excelHtml5' and just having 'regular' button with an action that then ran the excel action.

thank you for clarifying, yes, that code worked perfectly.