Write data on to Excel

I want to write the following data onto excel, in a way which the first answer (M(: , : , 1)) will be at the range E to G and the next answer (M(: , : , 2)) will be at the range I to K, so 4 column space between each.The sample data is below, is there a easier to do this as sometimes there might be more than 6 data sets.

X = ones(6).*(1:6);
Y = 2*ones(6).*(1:6);
Z = 3*ones(6).*(1:6);
M = permute(cat(3,X,Y,Z), [1 3 2]);
M= reshape(M, 6, []);

[darova]

Is this correct?

[Avishka G]

yes the first 3 columns of the matrix should write on to EFG

[darova]

Your ccode then look ok. Why don't you want it?

[Image Analyst]

What code? Easier way to do what? I see no code calling xlswrite() or writematrix(). Do you have any code that actually tries to write to Excel?

[Avishka G]

I used xls write but i can only copy all the data in M into sheet without keeping space between the 3 columns.

Answer 1

[Guillaume]

If I understood correctly:

xlswrite(somefile, reshape([M, nan(size(M, 1), 1, size(M, 3))], 6, []), '', 'E1')

basically pad your 3D matrix with a column of NaNs. NaNs result in empty cells in the output spreadsheet.

[Avishka G]

works perfectly.... thanks alot!

[Avishka G]

another question if were too pad NaNs, to 4 columns between each data sets for the following data:

L=[0,70,100,150,200]

How do I do just the padding for that?

[Guillaume]

The second input to NaN is the number of pad columns, so:

xlswrite(somefile, reshape([M, nan(size(M, 1), 4, size(M, 3))], 6, []), '', 'E1')

[Avishka G]

I tried to do the same for variable L:

reshape([L, nan( size (L ,1), 4, size (L, 3))], 1, [])

so I get the ans,

0 70 100 150 200 NaN NaN NaN NaN

[Guillaume]

So, yes you get four columns of NaN which will end up as blank columns in excel. What's the problem?

[Avishka G]

so I wanted to get my ans like;

0 NaN NaN NaN NaN 70 NaN NaN NaN NaN 100 NaN NaN NaN NaN 150 NaN NaN NaN NaN 200 NaN NaN NaN NaN

where all the NaN is between the numbers, do you know the code for it?

[Guillaume]

"do you know the code for it?"

Depends on what's L. If L is a column vector, the above will work as desired.

[Avishka G]

I used this code but still dont get the answer i want;

L=[0,70,100,150,200].';

N=reshape([L, nan(size(L,1),1,size(L,3))], 1, [])

[Guillaume]

That's a different arrangement than in your initial question where each row of the input ended up on a different row of excel whereas you want it all in one row.

L = [0, 70, 100, 150, 200];
N = reshape([L; nan(4, size(L, 2))], 1, [])

This time, we're putting the nans below L and reshaping into a vector.