How do I write a large multi-dimensional cell array into Excel?

Hi,

I have a 155X56 cell array, where each cell in the array is a ~17,100X 1 cell. (Ranges from 17102-17301 rows).

First off, using xlswrite or xlswrite3 just gives me empty Excel sheets, so I am pretty sure I am not using xlswrite correctly with my cell.

I just used xlswrite('Testdata.xls',fulldata) where fulldata is the 155X56 cell- I think I need to turn that into a matrix or something? I saw an answer to a similar question that had: X=[{'A','B','C','D'};A,B,C,D] xlswrite('X.xls',X) where A-D were column vectors, but I don't know how to apply that here.

Secondly, how do I know how whether this amount of data is too large for Excel to handle?

Thank you! K

Answer 1

[dpb]

You would need to write the underlying array(s), yes. As it appears you have a fixed number of columns but variable number of rows, vertical concatenation will work.

But, the 2nd part is the rub --

https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f

says row/column limits are

Worksheet size 1,048,576 rows by 16,384 columns and I get that

>> 1048576>156*17200
ans =
   0
>> 156*17200
ans =
   2683200
>> 156*17200/1048576
ans =
  2.5589
>>

You could only write to multiple sheets. Then, of course, you've got the overall memory limits of 2GB if 32-bit and paging may kill you in 64-bit depending on the system memory/configuration.

>> 156*56*17200*8/1024^3
ans =
  1.1195
>>

It's a little over 1 GB total so w/ a decent 64-bit machine/Excel probably would have sufficient overall memory but still have the row limit.

Why would you want to put such into Excel, anyway? Why not do whatever in Matlab where can handle it essentially transparently as array or at worst cell array of arrays?

[K]

Have to give it to our statistical people who want it in Excel. Trust me, I asked them if .mat would work!

[K]

And yes, I've got it running now to write each ~17,000X56 array to a new sheet. Should be done by about midnight tonight if I start running it now!

[dpb]

And then how are the "statistical people" going to deal with 155 sheets? What a nightmare?

Don't they have SAS or R or anything useful???