excel forumula calculation when spreadsheet opens

kdubs

I was playing with functions and hyper links in tables and trying to export them to excel.

I came across this question: https://datatables.net/forums/discussion/42097

that lead me to try to find how to get them to auto evaluate.

it occurred to me that in python I'm using XLSXwritter and the forumals work there, so what are they doing?

I found this tidbit:

that lead me to this: https://stackoverflow.com/questions/18355691/set-xlsx-to-recalculate-formulae-on-open

the good part is this:

so the question is do you set

<calcPr fullCalcOnLoad="1"/>

kthorngren

I don't see that property in xl/workbook.xml. You can view the spreadsheet XML by unzipping the file the download XLSX file. I created a simple export that uses SUM() in one of the columns. The SUM() function executes when opening the exported file.
https://live.datatables.net/ninukebu/1/edit

I don't have Excel but opened it in Numbers on the Mac. Does the function automatically execute for you in Excel?

I updated the test case to add <calcPr fullCalcOnLoad="1"/> to the workbook node in xl/workbook.xml. Not sure if that is the correct place. Does this do what you want?
https://live.datatables.net/rahipuvo/1/edit

Kevin

allan

Yup, as always, Kevin is spot on. The answer is no, I don't set fullCalcOnLoad at all. You can see the general XML structure for the spreadsheet that is created here.

Allan

kdubs

it seems to work with or without that property being set for your simple example. let me do some more testing with my code. it may take me a day or two to reply, but I am very interested in getting this to work.

rf1234

I don't have Excel but opened it in Numbers on the Mac. Does the function automatically execute for you in Excel?

Works like a charm in Excel! I wasn't aware that this is possible at all. Could be very useful for some of my Excel exports as well. Thanks for this, Kevin!

As always Excel converts everything for the respective regional Excel version in use. So SUM is being converted to SUMME in my case.

Roland

kdubs

does datatable use XLSXwritter?

kthorngren

No it uses the JSZip library that you are loading. See the excelHtml5 docs for more details.

Kevin

kdubs

thanks. your example confused me.

kthorngren

If you need help customizing the Excel output then please post a simple test case that has an example of the data you want in the SS. My example is simplistic to show how to create functions. The specifics of wha you need to do is dependent on your specific data and how you want Excel to handle it. Feel free to update my test case with your example data.

Kevin

kdubs

if i create a cell with =SUM(b2:c2) why doesn't work? what is that conversion step that you are doing? what is that "<f>" doing?

kthorngren

if i create a cell with =SUM(b2:c2) why doesn't work?

Without seeing what you are doing its impossible to say what the problem might be. Please post a simple test case showing what you are trying to do so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

what is that conversion step that you are doing?

I'm not sure what you mean by conversion step. Here is a snippet of the test case code I posted:

        customize: function( xlsx ) {
          var sheet = xlsx.xl.worksheets['sheet1.xml'];

          $('row c[r^="D"]', sheet).each(function(i) {
            if (i > 0 ) {
              
              var row = i + 2;

              //change the type to `str` which is a formula
              $(this).attr('t', 'str');

              //append the concat formula
              $(this).append('<f>SUM(B' + row + ':C' + row + ')</f>');

              //remove the value
              $('v', this).remove();

              //remove the inlineStr
              $('is', this).remove();
              console.log( $( this).html() )
              
            }
          })
        },          

What part of this code are you asking about.

what is that "<f>" doing?

It's a formula element. See this Microsoft Open XML doc for more details about formulas.

The export buttons, using JSZip, is meant for basic export capability. It can be very complex to customize the output beyond the basics. Possibly creating a Custom Button and using SheetJS might be a better alternative for your case.

Kevin

kthorngren

I built a simplistic SheetJS example that shows one way of creating hyperlinks (which I think you asked about).
https://live.datatables.net/cayuguri/1/edit

It's loosely related to their Export example. I used the Hyperlinks doc to learn how to create hyperlinks and added a loop for column A to create them.

This si the only Datatables related code in the custom button:

            // Get row data
            const data = dt.rows().data().toArray();
            
            // Get row header titles
            const titles = dt.columns().titles().toArray();

It uses rows().data() to get all the rows and columns().titles() to get the header titles. toArray() is used to convert them from Datatables API results to standard Javascript arrays.

The rest of the code is SheetJS specific Javascript code. Use their docs and support options to learn how to use SheetjS. We can help with any Datatables questions. Note that my SheetJS example likely won't work out of the box with your specific data. If you go this route make sure to build a simple test case with examples of your data and custom button code so we can see what you are doing.

Kevin

kdubs

here's my example. I was trying to write =HYPERLINK into the excel doc.
I can see now that the customize function can do a lot more than the exportOption can.

https://live.datatables.net/ninukebu/5/edit

I think with your example I can get what I want.

kthorngren

That looks like a good start. I'm thinking SheetJS might be a bit easier as it looks like that is needed is to build the proper object for each element type then SheetJS will build the XML Using customize will require you to build the XML structure.

Kevin

allan

I'm wondering if I should perhaps have a basic SheetJS button which can then be more readily customised than the excelHtml5 button type...

Allan

kdubs

in a case like this it would be useful.