Why do writematrix et. al. have unexpected behavoir, e.g. they alter Excel formatting?

When writing to an existing Excel file, writematrix and its siblings alter extant formatting in the following ways:

1) Column widths are changed

2) Merged cells are unmerged

This behavior is unexpected and frustrating. See attached example code and Excel files.

Furthermore, it would be nice if after writing the data out writematrix would allow the Excel file to recompute all cells before saving the file. In the attached example, a random matrix is written to Columns A:D. Column E sums Columns A:D. When Column E is read into Matlab its contents have not been updated. The user must open Excel, prompting the cells to recompute, then save the file.

These issues occur in 2020a and 2020b.

Solutions that do not require the user to employ ActiveX would be much appreciated. I the user expect the built-in write functions to take care of that. I am not trying to do anything fancy, I merely want to write out data.

The deprecated xlswrite did not have these issues. Its primary problem was that it created a new instance of EXCEL.EXE if one did not exist but then failed to close it. Please avoid that problem when addressing these.

Answer 1

[Chris]

Please see this related link:

https://www.mathworks.com/matlabcentral/answers/676588-writematrix-resizing-column-widths#answer_564558

If I set 'AutoFitWidth', to false, the column width does not change. I don't have Windows or Excel to check, but I would expect that adding

'UseExcel', true, 'PreserveFormat', true

would address the merged cell issue.

So, to recap, the entire function call would look something like:

writematrix(A, fout, 'Range', range_out1, ...
    'AutoFitWidth', false, 'UseExcel', true, 'PreserveFormat', true);

[Chris]

If excel persists, use

system('taskkill /F /IM EXCEL.EXE');

https://www.mathworks.com/matlabcentral/answers/163755-how-to-close-an-excel-file

[James]

The addition of

'AutoFitWidth', false, 'UseExcel', true, 'PreserveFormat', true 

to the writematrix calls now perserves column width, but merged cells are still unmerged. Also, Excel calculations are still not updated before the file is saved. I have scripts that write input data to Excel. The script is expecting Excel to update its calculations before the script reads the results and continues. writematrix does not allow the calculations to update while the deprecated xlswrite did. The Excel calculations make use of an add-in not replicated in MATLAB.

A EXCEL.EXE process is left running by writematrix. The call to system you suggest does kill it. I would expect writematrix to clean up after itself but apparently not.

[Chris]

@James you may receive a more satisfactory response if you submit a bug report/feature request with MathWorks (if you haven't already). I've found them quite responsive.

[James]

After a second look, I notice that

'UseExcel', true

prompts Excel to update calculations. Thank you for that. I am submitting a suggestion request.

[Simon Skillen]

I am using 'AutoFitWidth', false, 'UseExcel', true, 'PreserveFormat', true with Matlab R2021a and merged cells become unmerged. Any ideas where to alter my code?

[Walter Roberson]

writecell() is the only one documented to preserve formatting and formulas.

The write*() routines leave the excel connection active for performance reasons. The performance hit of creating and destroying the connection each time is quite noticeable.