writematrix command is very slow when the excel file has formulas

Hello everybody.

I am using the writematrix command to write outputs in an excel file and it is really really slow.

I have tried to remove all the formulas on the excel file and the time drops from ~2 min to 6 seconds, that is totally acceptable given the number of data I am writing.

The file I want to use has two sheets containing formulas, one has 30x10 formulas, while the other one has 800x20 formulas plus two columns of ~20000 cells and some charts.

Is there any way to have this speed writing in a file with formulas?

• I have tried to use the 'UseExcel' statement and set it manually to 0, although it is like this by default and indeed the time did not change.
• I have tried to turn off the automatic calculation in Excel, and the time dropped of some seconds.
• I have tried using the function xlswrite but it is even slower.
• I have tried removing just the charts (that use those formulas) and the time did not change.
• I have tried removing one of the two sheets including formulas (the bigger one) and the time dropped to ~6 seconds.

So, it seems the speed is affected by the presence of formulas and especially their amount. Even when the automatic calculation is disabled, it seems that the calculations are performed at each writing or something like that, slowing a lot the process.

I would like to know if there is a way to disable the calculations while writing data when it is not needed to perform those calculations.

As a temporary workaround I am using an excel file without formulas and then copy-paste the formula sheets.

Thanks in advance to everyone that will take time to contribute.

[Joe Vinciguerra]

Are you able to attatch your files and data?

[dpb]

• I have tried to use the 'UseExcel' statement and set it manually to 0, although it is like this by default and indeed the time did not change.

I suspect it will be better with '1' than '0' -- then it can use more native stuff. I don't know that this will help, but I wouldn't be surprised if it does.

The biggest thing is how are you calling writematrix? Are you building the whole sheet (or at least sizable fractions of it) in memory and then calling writematrix only once (or at least only a few times), or are you calling it to write each and every cell/row? The latter will definitely not be a way to go at it.

All the MATLAB spreadsheet functions open/close the workbook and create/destroy the ActX engine on every call; a lot of overhead if try to call them in deep loops.

One workaround without going all the way to writing ActX yourself is the <FEX submission xlswrite1> that removes the overhead of the multiple open/close cycle. You create the ActX engine and open the file externally first, then do the work on the file, then close the workbook and destroy the engine when done.