How to write cell array to excel file?

Hi,

I have a 9x5 cell array matrix. I want to export it as excel format. I presented it named cell matrix.

How can I export this matrix as excel format?

Thanks.

Answer 1

[Dyuman Joshi]

The data you have is stored in a weird manner.

load('cell matrix.mat')
whos

  Name                     Size             Bytes  Class    Attributes

  ans                      1x40                80  char               
  cmdout                   1x33                66  char               
  final_best_p_worker      9x5             169560  cell               

169560 bytes for a 9x5 cell, Hmmm.

final_best_p_worker

final_best_p_worker = 9×5 cell array
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}
    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}    {1×16 cell}

Each cell element consists a 1x16 cell.

final_best_p_worker{1}

ans = 1×16 cell array
  Columns 1 through 11

    {24×1 double}    {17×1 double}    {24×1 double}    {14×1 double}    {17×1 double}    {20×1 double}    {18×1 double}    {17×1 double}    {18×1 double}    {11×1 double}    {10×1 double}

  Columns 12 through 16

    {30×1 double}    {6×1 double}    {6×1 double}    {8×1 double}    {10×1 double}

And then each cell element consists a column vector.

There is one uniformity we can work i.e. the total number of elements for each cell element is same (250), so it is possible to vertically concatenate.

for k=1:numel(final_best_p_worker)
    y(k)=sum(cellfun('length',final_best_p_worker{k}));
end
unique(y)

ans = 250

So final product from each cell element will be 250x1 -

%Vertically concatenating data in each cell
for k=1:numel(final_best_p_worker)
    final_best_p_worker{k} = vertcat(final_best_p_worker{k}{:});
end
final_best_p_worker

final_best_p_worker = 9×5 cell array
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}
    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}    {250×1 double}

After this, you have 3 options how do you want your final data to be stored -

%Option 1
out1 = cell2mat(final_best_p_worker)

out1 = 2250×5

208 211 158 113 50 115 67 250 26 199 133 223 100 140 181 197 196 146 107 130 174 1 117 57 121 41 77 19 16 10 104 198 51 168 4 113 189 93 96 1 70 59 44 108 171 192 121 191 127 63

%Option 2
out2 = horzcat(final_best_p_worker{:})

out2 = 250×45

208 235 57 206 6 205 49 74 14 211 67 149 20 178 238 92 7 159 158 82 225 153 35 66 148 27 29 113 150 4 115 165 65 221 106 193 91 109 59 67 160 7 69 105 20 201 196 232 250 156 186 85 241 75 3 108 153 26 192 140 133 8 4 90 159 180 32 43 179 223 70 236 39 179 235 184 172 193 100 232 3 109 181 212 35 250 83 140 123 236 197 174 232 32 72 148 28 169 10 196 102 231 162 40 100 146 182 20 146 64 188 149 60 54 101 196 221 107 145 66 174 180 109 178 176 62 156 12 230 1 248 16 230 109 156 123 203 51 117 119 240 97 37 74 178 162 12 57 153 163 41 79 207 98 32 211 53 96 231 77 115 144 172 241 66 126 112 189 19 68 226 235 26 61 116 127 208 16 10 154 104 164 27 250 70 248 162 128 145 198 150 203 137 47 248 197 14 150 51 123 163 24 162 185 181 197 210 168 223 185 113 41 202 94 182 28 11 134 11 189 152 221 197 221 197 199 195 9 93 8 69 172 56 223 124 1 15 96 1 15 70 231 189 121 39 186 121 203 155 59 11 193 208 156 208 22 132 4 44 90 203 52 52 131 33 61 154 108 4 119 192 56 234 231 155 92 182 224 157 121 234 58 46 41 56 238 184 75 191 227 147 22 14 56 186 5 62 127 228 53

%Option 3
out3 = vertcat(final_best_p_worker{:})

out3 = 11250×1

208 115 133 197 174 41 104 113 70 192

Choose whichever size you want to save your data as and use that variable as input to xlswrite() -

%As you are working with R2015a version, use xlswrite()
%as writematrix() was introduced in R2019a
xlswrite('matrix.xlsx',array_you_want_to_save)

[Abdullah Türk]

Thanks Dyuman Joshi. It was very clear explanation. You right, it is a weird store type. I have to store every each of column in the {1×16 cell}. Unfortunately, combining numbers in the each {1×16 cell} horizontally or vertically does not work for me. Thanks again. I think, I will use .mat type format in the Workspace.

[Dyuman Joshi]

"Unfortunately, combining numbers in the each {1×16 cell} horizontally or vertically does not work for me."

Why not? As you can see, It works above.

"I think, I will use .mat type format in the Workspace."

How do you obtain the data? From some operation(s)/calculation(s)?

[Dyuman Joshi]

@Abdullah Türk any updates?

[Abdullah Türk]

Dyuman Joshi thank you for your responses. You right, your code work ,but, As I said, I have 12 cell array in {1x16 cell} and each cell array of 12 cell array column has different size of numbers. My goal is to save each column which have different size of number in the 12 cell array separately. But, thanks to your code, I can get a total of 250 grains in any order I want. Thank you again.

[Dyuman Joshi]

I don't understand - How did 12 come here?

You have a 9x5 cell where each element is 1x16 cell. Where did 12 come from?

Answer 2

[Walter Roberson]

writecell() in later releases. In your release you are either going to need to make a bunch of xlswrite calls or else you are going to need to create an activex connection to excel and use the connection to send data.

[Abdullah Türk]

I used writecell() command but MATLAB gives an error like that "Undefined matlab command". As you say, I think, I must use later relesases.

Answer 3

[Diwakar Diwakar]

% Load your cell array
load('cell_matrix.mat', 'final_best_p_worker'); 

% Flatten the cell array into a cell array 
flattened_data = cellfun(@(x) x(:)', final_best_p_worker, 'UniformOutput', false);

% Convert the flattened cell array to a table
table_data = cell2table(flattened_data);

% Define excel file
excel_file = 'output_data.xlsx';

% Use the writetable function to export the table to an Excel file
writetable(table_data, excel_file);

% Display a message indicating the successful export
disp(['Data has been exported to ' excel_file]);

[Abdullah Türk]

Diwakar thanks but the results in the output _data.xlsx are very complex. It seems like it's not clear exactly which cell's numbers are where.