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

11 次查看（过去 30 天）

显示更早的评论

James 2021-10-30

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1575428-why-do-writematrix-et-al-have-unexpected-behavoir-e-g-they-alter-excel-formatting

评论： Walter Roberson 2022-5-27

采纳的回答： Chris

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Chris 2021-10-30

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1575428-why-do-writematrix-et-al-have-unexpected-behavoir-e-g-they-alter-excel-formatting#answer_820408

编辑：Chris 2021-10-30

在 MATLAB Online 中打开

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

6 个评论
显示 4更早的评论隐藏 4更早的评论

Simon Skillen 2022-5-27

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 2022-5-27

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.

请先登录，再进行评论。