How to write to excel file faster (writetable, writecell, writematrix or others)
146 次查看(过去 30 天)
显示 更早的评论
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.
2 个评论
Jon
2022-2-7
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.
回答(2 个)
Jan
2021-5-30
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 ?
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!