Pass from multidimensional array to spreadsheet

0
votes

I have a multidimensional array which of the form:

Multi dimensional array with 5 rows and variable number of columns

where each row represents a sheet and each column represents a type of variable.

What I want to do is export the data into a spreadsheet where each row gets stored in a sheet with its columns. (Maybe it could be easier separate the sim multidimensional array into five matrices and then export those together to a spreadsheet) but I don't know if it's the most efficient way.

Here is a image of the spreadsheet:

5 sheets with variable numbers of columns

Below is my code, which basically reads the sheets from a Excel and make a simulation of 1,000 cases for each type of data:

sim={};
for k = 1 : 5
sheet = xlsread('CB.xlsx', k);
[m, n] = size(sheet)
for i = 1 : n
[f,x] = ecdf(sheet(:,[i]));
[f, dup] = unique(f);
x = x(dup);
randomValues = rand(1, 1000);
sim{k,i} = round(interp1(f,x, randomValues));
end
end

trying to use Mikhail_Sam'answer I got the follow problems:

`sheet = 1
for i = 1:5
for j = 1:5
xlswrite('filename.xls',sim(:,i,j),sheet,strcat(char(64+j),int2str(1)))
end
sheet = sheet+1;
 end`

but it says "Index exceeds matrix dimensions", then I tried this code sheet = 1 for i = 1:5 for j = 1:size(sheet) xlswrite('filename2.xls',sim{i,j},sheet,'A1:E1000'); end sheet = sheet+1; end This code write the file ith the 5 sheets but it is filled with the first 5 numbers of each row of the multidimensional array. Then I tried to apply num2cell and run your code but it gives "index exceeds matrix dimension" again

2
excelmatlabmatrixmultidimensional-array

2 Answers

1
votes

I don't know about 'the most efficient way', but I did it this way: for example I create 3x3x3 matrix and try to write it to excel:

sheet = 1
for i = 1:3
for j = 1:3
xlswrite(filename,x(:,i,j),sheet,strcat(char(64+j),int2str(1)));
end
sheet = sheet+1;
end

And I got what you want - 3 sheets, and data in rows. replace 3 at your dimensions. But I have one weak place - I don't remember numeric mechanism in multidimensional arrays - is i is a row in your example, or j... Anyway it's work just take a look at data and replace i - j - : if necessary.

Hope, it helps :)

1
votes

Finally I figure out how pass from multidimensional array to each spreadsheet. I decided to arrange the data in the same multidimensional array to then pass the data to a spreadshe, using A{k}=transpose(vertcat(simulado{k,:})) and then apply a for loop to add the data to each sheet separately.

 sheet = 1
 for i = 1:5
 xlswrite('filename.xls',A{1,i},sheet);
 sheet = sheet+1;
 end`