New Issue with writing table from matlab to excel and equations

I have an issue with excel files created with MATLAB not interpreting euqations any longer

if i use :

writetable(local_output_table,output_path);

and the table has a cell array in the 4th column. In the first cell, there is the string '=D2'.

Before I updated to 2021a, this code would create an excel file that would show the contents of D2. in other words, it would open the excel file as an equation

now, it just shows the plain text of the equation.

If i go into the cell, type a space, and press enter, the cell will start working as an equation

If I use the xlswrite tool and directly write a string to a cell, this will create a cell that works as intended, the equation will try to function the first time I open the file

obviosly, I don't want to have to add a space to hundreds of files just to get the equations to work. What changed that this is now not working?

Answer 1

[Ishaan Mehta]

Hey Keith,

As I understand, you are using "writetable" function to export a table into an Excel sheet with a column containing Excel formulae, but these formulae are not being evaluated automatically upon opening the sheet in Excel. This used to work as expected before updating to MATLAB R2021a.

While working on a similar workflow at my end, I noticed the same issue. It seems that "writetable" no longer uses Excel to write the table to the spreadsheet file by default, which seems to be a possible cause for this behavior. I tried using "writetable" with the "UseExcel" flag, and the result was as desired, with the formalue being evaluated automatically.

Please refer to the following code snippet for reference:

writetable(tableToExport, spreadsheetFileName, 'UseExcel', true);

Note that "UseExcel" flag is only supported for Windows. The below documentation page provides more information about "UseExcel" flag for "writetable":

https://www.mathworks.com/help/matlab/ref/writetable.html#:~:text=UseExcel%20%E2%80%94%20Flag%20to%20start%20instance%20of%20Microsoft%20Excel%20for%20Windows

I hope it helps.