Saving data in a loop using xlswrite

I am trying to use xslwrite to save data in a loop.

What I would like it to do is write the data in one row in Sheet1, then for i+1 write the data in the next row of Sheet1. I have tried to use the help, but I maybe was not understanding.

Answer 1

[Walter Roberson]

Doing that is very inefficient, especially if you are using R2015a or earlier. You would be better off doing one of the following:

• collect all of the data in a cell array, and then do a single xlswrite(); or
• if you are using MS Windows with Excel installed, use a COM object to connect to Excel and issue the updates more directly; there are ways to query the selection so you might not have to keep track yourself of how far you have written; or
• if you are using MS Windows with Excel installed and a version up to R2015a, use xlswrite1() from the File Exchange. You will need to keep track yourself of how far you have written

[Andrew]

I have an old version of Matlab R2011b. The loop is needed to collect the data and process some statistics on each file for thousands of files. Each time the loop completes the same sequence and puts out bins of data in each row.

As for the COM Object, I am not sure how that is done either? I will check out the xlswrite1().

Is this even the most efficient way to do this? Maybe csvwrite is better?

[Walter Roberson]

Just before the loop, allocate a cell array column vector with as many entries as there are files to process. In the loop, instead of writing the data with xlswrite(), store the data in the next available slot in the cell array. After the loop, you can construct a single giant cell array by using vertcat(TheCell{:}) and it would be that array that you would xlswrite() all at the same time.

csvwrite() is only for numeric arrays.

However if it is acceptable to produce a .csv file instead of a spreadsheet, then you can use fopen() and fprintf() and finally fclose() to write the data.

Supposing you have a cell array C of mixed data type; for the purpose of illustration suppose the first column is string, the second is numeric decimal and the third is numeric floating point, then you can use something like

fmt = '%s,%d,%g\n';

Ct = C.';
fprintf(fid, fmt, Ct{:});

This illustrates two tricks: That you need to transpose the data before writing it, and that you need to use cell array expansion to create the arguments to fprintf() when you are writing out information of mixed data types.