Can you export a table and format a cell to use a formula using orthogonal data?

Dzyann

I am starting to work with the datatables.net export to excel feature. So far I managed to make it work but now I am trying to format some cells to be exported as an excel formula and I have some issues.

I use this basic configuration for the Excel button (it winds up using the html5 excel feature)

 buttons: [
            {
                extend: 'excel',
                exportOptions: {
                    orthogonal: 'export'
                }
            }
        ]

Then I configure the render function of the column like this:

"render": function (data, type, row) {                  
                            if (type === 'export')
                                return '=3+' + 4;
                            return "whatever";
                }

As a test I am returning a simple "formula", basically: =3+4
In excel it should be resolved to 7, however it is shown as plain text until I edit the cell and press enter.
I feel I am missing something really simple. Is there a way to export this data and make excel interpret it automatically as a formula?

Thanks in advance!

kthorngren

I feel I am missing something really simple. Is there a way to export this data and make excel interpret it automatically as a formula?

Unfortunately not simple but possible. You will need to have an understanding of how the XML is generated for an Excel spreadsheet then convert the inline string in Datatables to a formula format for the export. Before you venture into the world of exporting formulas take a look at this thread
https://datatables.net/forums/discussion/41778/excel-export-and-newlines

It took a few hours of experimenting to figure out what to do. Hopefully that thread will have enough info to get you started.

Please post your results or questions.

Kevin

Dzyann

Hi Kevin,
Thanks for that link! I reviewed it and changing the code to:

"render": function (data, type, row) {                 
                            if (type === 'export')
                                return '=3+' + 4;
                            return "whatever";
                }

And the buttons options to:

buttons: [
            {
                extend: 'excel',
                exportOptions: {
                    orthogonal: 'export',
                },
                customize: function (xlsx) {
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    var col = $('col', sheet);                   
                    $('row c[r^="H"]', sheet).each(function ( index, element) {
                        if (index !== 0) {                            
                            //change the type to `str` which is a formula
                            $(this).attr('t', 'str');
                            //append the concat formula
                            $(this).append('<f>' + $('is t', this).text() + '</f>');
                            //remove the inlineStr
                            $('is', this).remove();
                        }                        
                    });
                }
            }
        ]

You were right I needed to understand better the xml structure.
Thanks!