- any sheet that is going to be updated is more efficient as a separate file, so that there is less binary file to update
- if you can place reasonable limits on the size of text fields, it can be more efficient to use binary files with fixed length fields
- in some cases where the portability of xlsx is needed but efficiency is also needed, and a lot of scattered read/write needs to be done, sometimes it pays to convert the xlsx to binary files that get operated on, and convert the updated binary files back afterwards.
- if the text field contents are chosen from a fixed list, use categorical indices as that represents less updating
Best Xlsx read and write option
2 views (last 30 days)
I am working with a lot of data. I have a .xlsx workbook with multiple spreadsheets full of data (100000 rows) that contains text strings and numeric data.
I tried importdata , it did not read the text strings and only output the numeric values but it allows me to see each sheet in the workbook.
I tried readtable, although it works well and output all the data, I dont have access to all sheets and have to specify which one. It would not be too efficient because I willbe comparing values and text strings from one sheet to another.
xlsread is not recommmended.
My goal is to open the xlsx file and read values from it, write to it and also filter some columns using numeric values.
Walter Roberson on 10 Jun 2020
xlsx format is not an efficient one. Portable, yes, but not efficient.
An xlsx file is a zip file containing a directory of XML files. There are some overhead files in the directory, and there is one XML file for each worksheet. The overhead files usually all need to be decompressed, and the worksheet specific file needs to be decompressed. When you make a change to a worksheet, the new XML needs to be generated and compressed and stored back, which will typically not fit into the existing storage, so the zip as a whole would commonly need to be updated.
This update of the zip file would be done each time you writetable() or xlswrite(), so for efficiency it is usually better to write the entire revised sheet than to write into pieces at a time. There is no "open worksheet", "close worksheet" at that level, so the routines cannot buffer changes.
... and this all implies a few things: