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

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.

Solution

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 (see the OpenXML reference documentation).

The following code takes the raw DataTables data provided by the customizeData() function, and modifies the way it is stored in Excel:

Note - this code uses my sample data, shown below in the full demo.

customize: function ( xlsx ) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  var cell = $('row c[r=D8]', sheet);
  $(cell).attr( 't', 'str' );
  $(cell).append('<f>' + $('is t', cell).text() + '</f>');
  $('is', cell).remove();
}

The above code uses jQuery to manipulate the XML used for storing the Excel structures.

I have simplified the logic to hard-code the cell D8 where the formula is placed. You may have to alter that for your specific data needs.

Before the customize function is used, the cell content would have been stored as a plain inline string:

<c t="inlineStr" r="D8">
    <is>
       <t xml:space="preserve">=sum(D3:D7)</t>
    </is>
 </c>

After the customize function is added, the cell content is stored as a formula:

<c t="str" r="D8">
    <f>=sum(D3:D7)</f>
</c>

The demo code (I don't think this actually runs in a Stack Snippet - the Excel file cannot be downloaded here):

$(document).ready(function() {

  var table = $('#example').DataTable({
    dom: 'Brftip',
    buttons: [{
      extend: 'excelHtml5',

      customizeData: function(data) {
        var desc = [
          ['', 'TOTAL', '', '=sum(D3:D7)']
        ];
        for (var i = 0; i < desc.length; i++) {
          data.body.push(desc[i]);
        };
      },

      customize: function(xlsx) {    
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        var cell = $('row c[r=D8]', sheet);
        console.log(cell);
        $(cell).attr('t', 'str');
        console.log(cell);
        $(cell).append('<f>' + $('is t', cell).text() + '</f>');
        console.log(cell);
        $('is', cell).remove();
        console.log(cell);
      }

    }]
  });

});

<!doctype html>
<html>

<head>
  <meta charset="UTF-8">
  <title>Demo</title>

  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.1/css/jquery.dataTables.css" />
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/2.3.3/css/buttons.dataTables.css" />

  <script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.js"></script>
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/1.13.1/js/jquery.dataTables.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.3.3/js/dataTables.buttons.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.3.3/js/buttons.html5.js"></script>

  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

  <div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
      <thead>
        <tr>
          <th>Name</th>
          <th>Position</th>
          <th>Office</th>
          <th>Age</th>
          <th>Start date</th>
          <th>Salary</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>Tiger Nixon</td>
          <td>System Architect</td>
          <td>Edinburgh</td>
          <td>61</td>
          <td>2011/04/25</td>
          <td>$320,800</td>
        </tr>
        <tr>
          <td>Garrett Winters</td>
          <td>Accountant</td>
          <td>Tokyo</td>
          <td>63</td>
          <td>2011/07/25</td>
          <td>$170,750</td>
        </tr>
        <tr>
          <td>Ashton Cox</td>
          <td>Junior Technical Author</td>
          <td>San Francisco</td>
          <td>66</td>
          <td>2009/01/12</td>
          <td>$86,000</td>
        </tr>
        <tr>
          <td>Michael Bruce</td>
          <td>Javascript Developer</td>
          <td>Singapore</td>
          <td>29</td>
          <td>2011/06/27</td>
          <td>$183,000</td>
        </tr>
        <tr>
          <td>Donna Snider</td>
          <td>Customer Support</td>
          <td>New York</td>
          <td>27</td>
          <td>2011/01/25</td>
          <td>$112,000</td>
        </tr>
      </tbody>
    </table>

  </div>

</body>

</html>

The customizeData function (which you already have) operates on the raw data provided by DataTables.

Then the new customize function operates on the target Excel file (the underlying OpenXML structures), to convert a string to a formula.

The end result (in edit mode):

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):