Convert cell array to excel - problem with nested cells

Hello!

I really need to write this data from a cell array into an excel file (you can find it attached or in the printscreen). I've used

xlswrite('data.xls', M2)

and

writecell(M2, 'data.xls')

and nothing works because I have nested cells and all with different sizes, some even empty. How can this work?

I need data to be separated like this in the excel:

line1 = M{1,1}

line 2 = M{2,1}

line 3 = M{3,1}

line 4 = M{1,2}

line 5 = M{2,2}

line 6 = M{3,2}

...and so on...

Please help!

[dpb]

You'll have to unnest them first.

Probably an explicit loop or loops will be needed.

BUT--

You can NOT put an array into a single cell in Excel if that is what you're trying to do.

[Melissa Jones]

Could you help out with those loops then, please?

Answer 1

[dpb]

Try something like

 l=0;
 for i=1:size(M2,1)
   for j=1:size(M2,2)
     l=l+1;
     writecell(M2(j,i),'M2.xls','Range',"A"+l);
   end
 end

[Melissa Jones]

It doesent run, gives an error

'Error using writecell (line 146) 
Unable to write to file 'M2.xls'. '

I also didnt understand your logic... could you try to explain it or redo it please?

[dpb]

Rearranged from the one-liner at the command line to multiple lines -- missed closing parenthesis.

It just iterates over the cell array by row, column as per request...

[Melissa Jones]

I noticed but it still doesn’t work. Also in your solution i = 1:size(M2,1) should be i=size(M2,2) and j should be j=1:size(M2,1).

Even with this correction it still doesent work, or are you able to run it? (the .mat is uploaded)

[dpb]

Seems to work here at command line...

>> l=0;for i=1:size(M2,2),for j=1:size(M2,1);l=l+1;writecell(M2(j,i),'M2.xls','Range',"A"+l);end,end
>> winopen('M2.xls')
>> MC=readcell('M2.xls');
>> MC(:,1:8)
ans =
  22×8 cell array
    {'s'       }    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}
    {[     989]}    {[      256]}    {[      593]}    {[ 253.5000]}    {[ 125.2500]}    {[ 615.7500]}    {[      269]}    {[ 316.5000]}
    {[378.5000]}    {[ 141.7500]}    {[ 110.7500]}    {[ 272.2500]}    {[ 436.5000]}    {[ 290.7500]}    {[ 204.5000]}    {[      410]}
    {'s'       }    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}
    {[516.5000]}    {[      328]}    {[      229]}    {[      459]}    {[ 162.7500]}    {[ 222.2500]}    {[ 247.7500]}    {[ 153.5000]}
    {[ 99.2500]}    {[ 155.2500]}    {[ 182.5000]}    {[       96]}    {[ 189.5000]}    {[ 122.7500]}    {[ 196.7500]}    {[ 192.5000]}
    {'s'       }    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}
    {[     483]}    {[ 615.5000]}    {[ 357.2500]}    {[      303]}    {[ 326.2500]}    {[ 381.2500]}    {[ 316.7500]}    {[ 209.7500]}
    {[101.2500]}    {[      215]}    {[ 558.7500]}    {[  96.7500]}    {[ 148.5000]}    {[ 169.7500]}    {[  94.7500]}    {[ 219.5000]}
    {'s'       }    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}
    {[566.2500]}    {[      597]}    {[ 149.2500]}    {[ 484.7500]}    {[      615]}    {[       63]}    {[ 110.2500]}    {[ 159.2500]}
    {[721.5000]}    {[  97.5000]}    {[  97.2500]}    {[  87.7500]}    {[      281]}    {[  79.5000]}    {[  82.2500]}    {[ 189.7500]}
    {'s'       }    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}
    {[     181]}    {[       94]}    {[      146]}    {[      130]}    {[ 297.7500]}    {[ 509.2500]}    {[  82.2500]}    {[  78.5000]}
    {[117.2500]}    {[ 137.7500]}    {[ 135.5000]}    {[ 147.2500]}    {[  99.7500]}    {[  86.5000]}    {[  75.7500]}    {[      121]}
    {'s'       }    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}
    {[253.2500]}    {[ 332.2500]}    {[ 265.7500]}    {[ 309.7500]}    {[ 619.2500]}    {[      834]}    {[      521]}    {[ 374.7500]}
    {[178.2500]}    {[      545]}    {[      238]}    {[ 163.2500]}    {[ 194.5000]}    {[ 786.2500]}    {[ 131.7500]}    {[ 275.5000]}
    {'s'       }    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}
    {[     252]}    {[ 355.5000]}    {[ 311.2500]}    {[ 218.5000]}    {[      279]}    {[ 186.7500]}    {[      270]}    {[      160]}
    {[297.2500]}    {[  95.7500]}    {[ 327.2500]}    {[ 357.5000]}    {[ 228.7500]}    {[ 221.2500]}    {[ 201.7500]}    {[      341]}
    {'s'       }    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}    {1×1 missing}
>> 

[Melissa Jones]

I had to save data to excel file like this:

cellReference = sprintf('A%d', l);
xlswrite('timings.xlsx',a, 'TABLE', cellReference)

As you were suggesting gives me an error, dont know why. Thanks very much, though!

[dpb]

Well, you've never posted an error or the exact code you tried, so how are we to know what happened?

As noted above, it works w/ R2020b; I can't think what is in the above that has changed terribly recently, but it does use the new(ish) string class overloaded + operator, but that's been around for a while by now.

The above makes it look like that perhaps is the issue; if so replace

...,'Range',"A"+l)

with

,'Range',sprintf('A%d', l));

writecell was introduced in R2019a; if your version predates that then use

writetable(cell2table(M2(j,i)),'M2.xls','Range',sprintf('A%d', l));

as all writecell is is a wrapper around writetable

I will note that if the size of the cell array gets to be large, the above will likely become exceedingly slow and may, even, fail to complete owing to the opening/closing of the Excel file on every cell write operation -- I have run into this on some complex spreadsheets I've been working on for a local nonprofit that need to update disparate locations within the workbook for a few hundred locations.

In that case, the solution will be to build the composite cell array in memory first instead of writing each cell, then make one call to write after have done.

You might also consider how you're constructing the cell array to begin with and not create the nested version at all but build it linearly instead.

[dpb]

" the solution will be to build the composite cell array in memory first..."

Alternatively, and actually the one I implemented above is to use the FEX submission from Nick Oatley @

https://www.mathworks.com/matlabcentral/fileexchange/69745-xlswriteex

which will create a persistent COM object at the beginning of the sequence of writes you want to make and then let you close it when done.

This will make probably an order of magnitude difference in speed and is also not prone to the issues I outlined above if the size of M gets large.

Answer 2

[Walter Roberson]

You have several options:

• write each cell entry as text, perhaps using M2s = cellfun(@mat2str, M2, 'uniform', 0) . If you do that, then Excel will not be able to interpret the results as numeric
• Provided that you do not have any natural "nan" entries, convert each entry into a column and append a nan to it, and combine the entries. You might need to pad each column to the same length using nan. So the first column would contain 52 double, then a nan, then 34 double, then a nan, then 6 nan (longest column would be 92 entries)
• find the widest column and pad each of them out to that size. Then rearrange the entries into continuous columns. Your widest is 74 columns, so you would pad each of the 1 x N out to be 1 x 72, using NaN as your padding; then you would rearrange to a cell containing a letter, then 72 cells with numeric values, then another 72 cells of numeric values, and each column would have that length.
• expand the first column into 52 columns (of 3 rows), expand the second column into 74 columns, the third into 60 columns, and so on, with the number of new colums needed being equal to the widest row in that column. You might need to put in a new row that indicates which original column each entry belonged to.

[Melissa Jones]

The first way seemed the simplest.

But Isnt there a way to put each number in a separate cell in excel, disposed as I mention in the original question?