why xlswrite/xlsread is not recommended for use

Hello,

I am being advised not to use xlswrite/xlsread for reading/writing mixed data to an excel file (xlsx).

I found an alternative way out for reading but I have a hard time to find an alternative to write.

Questions:

1. why is not avaisable to use xlsread/xlswrite
2. is xlsread/xlswrite being elliminated in latest versions of Matlab?
3. if the answer to Q2 is positive, what is the latest version one can still use it?

I have an excel fiel with two sheets.

I need to read mixed data from defined ranges form sheet1 and write mixed data to defined ranges in sheet2

In the file attached, I need to read from sheet "MAIN" the following cells

C6:C12,C14:F17, G6:H6.

In the sheet "COST_FUNCTION" I need to write a line of 7 strings A1:H1, followed by lines of numbers A2:H2, A3:H3, etc

What would be the best way to do it?

Thank you

Answer 1

[dpb]

See xlsread for the official word from TMW (The Mathworks). From the supporting link there is the following

Advantages [of readXXXX family new functions] over the xlsread function:

• Better cross-platform support and performance
• Automatic detection of data format and types
• Ability to use import options to control the data import process, including the handling of errors and missing data

There's no real danger of xlsread/write being actually removed; there's too much historic code for that to be practical option open to TMW, at least any time "real soon now"...eventually, it might be, but I'd be very surprised it it actually ever were physically removed from the distributed product.

In most cases, they're really correct in that readtable is a better mousetrap -- xlsread gave three separate outputs as it couldn't handle the mixed data types so prevalent with spreadsheets; the table data class can. You may want to consider changing your coding style to make use of the features of the table over using separate arrays; there's much built in to like; little to dislike other than it may be somewhat of a performance hit for really large tables, but they have to be really large to see much impact in my experience.

In general, even with xlsread it's better to read the sheet and the select the subset of data wanted than it is to make multiple calls with disparate ranges.

The best way to write the data out is probably to create a table of the contents to write and use writetable -- writematrix and friends end up converting the passed arrays through array/cell2table and calling it, anyway.

[dpb]

That's a real mess for the MAIN sheet -- you've got stuff all mixed up all over the place as to what kind of data is and who...not much to be done but to just pick 'n choose the pieces you want. Show us how you had read it before and what doing with the result and can probably either reproduce or streamline with replacement routines, depending on just what that might look like.

Certainly you can create and write the second page directly with a table in its entirety or whatever pieces of it are computed fresh or replaced in an existing sheet -- that's a "drop-dead simple" and ideal application for a table and writetable as would be then reading it back again later with readtable.

>> [n,t,r]=xlsread('EXCEL_20220803_0921_20220720_1006_Sigfried_with_Bed_of_Nails.xlsx','MAIN','C6:H14');
>> whos n t r
  Name      Size            Bytes  Class     Attributes

  n         9x5               360  double              
  r         9x6              6476  cell                
  t         9x6              6132  cell                
>> c=readcell('EXCEL_20220803_0921_20220720_1006_Sigfried_with_Bed_of_Nails.xlsx','Sheet','MAIN','Range','C6:H14');
>> whos c
  Name      Size            Bytes  Class    Attributes

  c         9x6              6180  cell               

>>

illustrates a typical issue with xlsread -- there isn't always a 1:1 match of the array sizes returned between the numeric and nonnumeric resutls arrays -- hence one doesn't always know "who's who iin the zoo" as to which pieces of data go with each other. The "raw" r array returns the data for both but then must convert to numeric those that are supposed to be.

For comparison, the result from readcell is the same as the raw from xlsread except it will have the <ismissing> indicator for empty cells in the spreadsheet that are returned as NaN by xlsread. This can be an advantage as well in parsing the pieces from that result as there is a distinguishing characteristic to use to tell the difference between the empty cell and one that is not convertible to a number. The two are otherwise interchangeable.

>> m=readmatrix('EXCEL_20220803_0921_20220720_1006_Sigfried_with_Bed_of_Nails.xlsx','Sheet','MAIN','Range','C6:H14');
>> whos m
  Name      Size            Bytes  Class     Attributes

  m         9x6               432  double              

>> 

has the same result as the numeric portion of xlsread except it maintains the overall size commensurate with the read region and is, thereby consistent in where things are located in the returned array with respect to their position in the spreadsheet. This is a slight difference in result, but overall an advantage to using the results.

The only real disadvantage of the newer routines or the advantage of xlsread over the newer is that it does have the optional return arguments that lets one have the three data types after one call to read the file whereas one has two calls with the readcell/readmatrix combination. For oddball sheets such as your MAIN, one could choose to stick with xlsread for that reason, but I'd suggest to look more carefully at how you use the data to see if can't be more efficient in processing in memory from the cell array first.