How to write to excel file faster (writetable, writecell, writematrix or others)

I've went through many topics over the years and here is what I found out recently:

• writetable & co are faster than the old xlswrite (or alternatives I found on matlab central)
• writetable & co are still ultra slow when writing to excel. Not to an unbearable level but if the data size is big enough and you have to write tens or hundreds of file (1-100 MB each), you can spend minutes or even hours waiting.

A few tips I found to speed up the write speed to about 4x or 5x is:

• 'AutoFitWidth' = false ...this is true by default and disabling it allowed me to achieve a 2-3x faster write speed (when 'UseExcel' = false, otherwise even more)
• 'UseExcel' = true ...this is false by default and I thought it was a good thing to avoid opening up an Excel instance but you can actually squeeze another 2-3x by switching this to true

A weird interaction is that 'AutoFitWidth' = true is especially punishing when 'UseExcel' = false and this is exactly the default combination. When 'UseExcel' is true, at least in my case, I can get the full 4-5x boost even by leaving AutoFitWidth enabled.

Now, since even with this trick the write speed is still quite low, I was wondering, is there a lower level way to write excel output? Like using fprintf and somehow still saving to excel file? I know it's a bad habit to save to Excel but it still comes in handy when you have to export data for customers, etc. Sharing an Excel file instead of a .txt or whatever usually is usually a big plus in terms of handling for the customer so it would be nice to have a faster way to programmatically write matlab calculated stuff into excel files.

[Jon]

I am also struggling with MATLAB writematrix being very slow when writing .xlsx files. I'm wondering if there are any further developments on this topic. I tried the tweaks/tips you have identified, regarding AutoFitWidth and UseExcel, but didn't see any appreciable improvement. Writing to .csv's has acceptable performance for my application, and the end users I am creating the files for can easily import .csv's into Excel (which is where they need to work with the data). The trouble is there is no mechanism then for creating sheets. So it is somewhat awkward to have a .csv for every sheet. Bottom line, I would really like to be able to have a fast writematrix for creating .xlsx files. By the way, I also tried creating .xlsb (binary) files thinking that might help, but didn't notice an appreciable improvement.

[Davide Battini]

No clue sorry. I didn't have to mass write .xlsx files since then and didn't test anymore after finding what you can see here.

I was on 2021a so the situation might be a little different on 2021b or 2022a (not sure though).

Generally speaking I'd advise AutoFitWidth = false and making as many calls as you can with UseExcel = true since the first call is always the slowest one.

The only other solution would be saving to .csv from matlab and then coding up a macro or some VB lines to autoimport .csv files to excel and create all the sheets you need. The overall execution time would certainly be faster but setting up the code is definitely more tedious. Maybe you could also make the call of the excel VB code from Matlab and still do all your stuff with a single call but I never tried it honestly.

Answer 1

[Davide Battini]

clear all
opts={'WriteMode', 'replacefile'};
for i = 1:25; varNames{i} = char(randi([33 126],1,20+randi(20)));end
x = array2table(rand(15000, 25));
x.Properties.VariableNames = varNames;
tic; writetable(x, 'test1.xlsx', 'UseExcel', false, 'AutoFitWidth', false, opts{:}); toc
% Elapsed time is 2.8 seconds.   % First call
tic; writetable(x, 'test2.xlsx', 'UseExcel', false, 'AutoFitWidth', false, opts{:}); toc
% Elapsed time is 2.7 seconds.   % Second call

clear all
opts={'WriteMode', 'replacefile'};
for i = 1:25; varNames{i} = char(randi([33 126],1,20+randi(20)));end
x = array2table(rand(15000, 25));
x.Properties.VariableNames = varNames;
tic; writetable(x, 'test1.xlsx', 'UseExcel', true, 'AutoFitWidth', false, opts{:}); toc
% Elapsed time is 4.7 seconds.   % First call
tic; writetable(x, 'test2.xlsx', 'UseExcel', true, 'AutoFitWidth', false, opts{:}); toc
% Elapsed time is 2.0 seconds.   % Second call

clear all
opts={'WriteMode', 'replacefile'};
for i = 1:25; varNames{i} = char(randi([33 126],1,20+randi(20)));end
x = array2table(rand(15000, 25));
x.Properties.VariableNames = varNames;
tic; writetable(x, 'test1.xlsx', 'UseExcel', false, 'AutoFitWidth', true, opts{:}); toc
% Elapsed time is 3.7 seconds.   % First call
tic; writetable(x, 'test2.xlsx', 'UseExcel', false, 'AutoFitWidth', true, opts{:}); toc
% Elapsed time is 3.6 seconds.   % Second call

clear all
opts={'WriteMode', 'replacefile'};
for i = 1:25; varNames{i} = char(randi([33 126],1,20+randi(20)));end
x = array2table(rand(15000, 25));
x.Properties.VariableNames = varNames;
tic; writetable(x, 'test1.xlsx', 'UseExcel', true, 'AutoFitWidth', true, opts{:}); toc
% Elapsed time is 4.8 seconds.   % First call
tic; writetable(x, 'test2.xlsx', 'UseExcel', true, 'AutoFitWidth', true, opts{:}); toc
% Elapsed time is 2.0 seconds.   % Second call

tic; save('test3.mat', 'x'); toc
% Elapsed time is 0.12 seconds.

I changed your bench code a bit because there was almost no difference (also the table had 1 col only).

I guess we can't see the same boost I see on my real data. Probably there is even more difference when creating sheets and appending data as I'm doing. Also I can tell you that the higher difference the slower the process. On my real data I was on 30-40s cycle time (in which I write a couple of sheets and make 4 write calls) with default options and got to 5-10s per cycle with non default options.

Taking into consideration the second calls it's basically only a 2x from default to non default opts.

PS: i completely agree on the formats. I just came into this because of the "need" of sharing with a customer and I always wonder about code optimization (even though I'm not a software engineer and I can be quite bad at it)

[Jan]

Thanks for this useful explanation.

Answer 2

[Jan]

Thanks for sharing this useful information.

Do I understand correctly that you suggest:

writetabele(data, 'UseExcel', true, 'AutoFitWidth', false)

AutoFitWidth did not exist in R2018b, so it is newer. Some timings on my Windows machine:

x = table(rand(1000, 100));
tic; writetable(x, 'test1.xlsx', 'UseExcel', true); toc
% Elapsed time is 1.795150 seconds.   % First call
tic; writetable(x, 'test2.xlsx', 'UseExcel', true); toc
% Elapsed time is 0.667419 seconds.   % Second call
tic; writetable(x, 'test3.xlsx', 'UseExcel', false); toc
% Elapsed time is 1.489082 seconds.   % Same time for repeated calls
tic; save('test4.mat', 'x'); toc
% Elapsed time is 0.027679 seconds.

The Excel file formats .xls and .xlsx are not efficient in terms of read and write access. There are many binary formats, which outperform the Excel types.

I personally hate Excel and the file formats are one of the reasons for this.

Could you please post some timings with including 'AutoFitWidth', false ?