Can we add a formula in a DataTable's export to Excel?-CodePudding

I need to add a formula to an Excel sheet that is exported from DataTables. I am trying the code written below

{
    extend: 'excelHtml5',
    exportOptions: { orthogonal: 'export' },
            
    customizeData: function(data){
      //Add a row.
      var desc = [
        ['','TOTAL','','','=sum(E1:E15)']
      ];
      // data.body.unshift(data.header);
      for (var i = 0; i < desc.length; i  ) {
        data.body.push(desc[i]);
      };
  }
},

But it adds the formula as text and it works as a formula only after key focus on that cell.

CodePudding user response：

Your approach causes the Excel (xlsx) file to store the formula as a string (as you have noted).

Specifically, the DataTables Excel export stores it as an "inline string" using the t="inlineStr" attribute. This needs to be changed to t="str" for a formula. The actual text of the formula needs to be contained in a <f> tag, also (

Final Notes

When I run this in a web page, the downloaded Excel file is automatically opened in read-only mode (as a safeguard). At this point, you cannot see the formula.

You have to choose to open the file for editing for the formula to be visible.

That is a safety feature of the version of Excel I am using.

You can adjust these settings if needed (but I recommend keeping them as they are, for security):