Best Xlsx read and write option

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]

https://www.mathworks.com/help/matlab/ref/sheetnames.html

[Mini Me]

I think that only give the name of the sheets

[Walter Roberson]

yes but that gives you enough information to know which sheet names to use as arguments for the other functions.

There are no functions that work on all sheets at the same time. If I recall correctly what I have seen using the ActiveX interface my memory is that even Excel cannot work with all sheets at the same time.

Answer 1

[Walter Roberson]

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:

1. any sheet that is going to be updated is more efficient as a separate file, so that there is less binary file to update
2. 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
3. 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.
4. if the text field contents are chosen from a fixed list, use categorical indices as that represents less updating