More efficient way to export output to Excel

Hello,

I am currently following this method to export the results to one Excel file:

   xlswrite('C:\Users\...\Graph.xlsx',{'A' 'B' 'C' 'D'},'Sheet1','A1');
   xlswrite('C:\Users\...\Graph.xlsx',PriceA','Sheet1','A2');
   xlswrite('C:\Users\...\Graph.xlsx',PriceB','Sheet1','A2');
   .
   .
   etc

There must be a more efficient way that opens the excel file only once, but I am not able to locate it. It is not really logical to write every time separately to the same file.

Any assistance is appreciated.

Thanks

Answer 1

[Azzi Abdelmalek]

EDIT

header={'A' 'B' 'C' };
priceA=[1 2 3]';
priceB=[4 5 6 8 9 1 0 12 15 20]';
priceC=[7 8 9 10]';
max_n=10;
tr=@(price)[num2cell(price) ;repmat({[]},max_n-numel(price),1)]
priceA=tr(priceA)
priceB=tr(priceB)
priceC=tr(priceC)
M=[header;[priceA priceB priceC]]
xlswrite('C:\Users\...\Graph.xlsx',M)

[AND]

Many thanks Azzi for your assistance, but this method isn't working because the size of each array, in my case, is not the same. For instance:

PriceA=[1 2 3]'
PriceB=[5 5]'
PriceC=[6 8 9 0 5]'

I even have vector of text values, that would go under column D, such as:

Description={'Ended' 'Executed' 'Failed' 'Terminated'}'

To summarize, an example of the data is something like this:

header={'A' 'B' 'C' 'D'}
PriceA=[1 2 3]'
PriceB=[5 5]'
PriceC=[6 8 9 0 5]'
Description={'Ended' 'Executed' 'Failed' 'Terminated'}'

I just wonder if there is an alternative method to mine that would do the trick.

Thanks again Azzi and regards.

[Iain]

Azzi's method does the trick with a few mods, eg:

 PriceA = num2cell(PriceA);
 PriceA(end+1:maximum_column_length) = {};

[AND]

Thanks lain,I tried adding this part but it is not working, and I am getting a bunch of different errors with every alter I make.

[Azzi Abdelmalek]

Post the sizes of your arrays and the error message

[AND]

After altering Lain's proposal, I was back to the initial problem: "Error using horzcat Dimensions of matrices being concatenated are not consistent."

If I am able to handle exporting the example below at once, the issue will be solved. The sizes in this example are 3, 2, 5, and 4.

header={'A' 'B' 'C' 'D'}
PriceA=[1 2 3]'
PriceB=[5 5]'
PriceC=[6 8 9 0 5]'
Description={'Ended' 'Executed' 'Failed' 'Terminated'}'

Thanks

[Azzi Abdelmalek]

Look at Edited Answer

[Azzi Abdelmalek]

In your case

PriceA=[1 2 3]'
PriceB=[5 5]'
PriceC=[6 8 9 0 5]'
Description={'Ended' 'Executed' 'Failed' }
max_n=5;
tr=@(price)[num2cell(price) ;repmat({[]},max_n-numel(price),1)]
PriceA=tr(PriceA)
PriceB=tr(PriceB)
PriceC=tr(PriceC)
M=[Description;[PriceA PriceB PriceC]]
xlswrite('C:\Users\...\Graph.xlsx',M)

[AND]

Dear Azzi, The issue is partially solved, as I am not able to get the text arrays in shape. If I use:

Description={'Ended' 'Executed' 'Failed' 'Terminated'}'
I get  {1x1 cell} which exports empty cells.

If I use:

Description=['Ended' 'Executed' 'Failed' 'Terminated']'
I get each letter in one cell

I wonder if you think it is possible to combine text & numbers in the exported file? Thanks

[Azzi Abdelmalek]

ok, you can add an array of empty cell

PriceA=[1 2 3]'
PriceB=[5 5]'
PriceC=[6 8 9 0 5]'
PriceD=[];
Description={'Ended' 'Executed' 'Failed' 'Terminal' }
max_n=5;
tr=@(price)[num2cell(price) ;repmat({[]},max_n-numel(price),1)]
PriceA=tr(PriceA)
PriceB=tr(PriceB)
PriceC=tr(PriceC)
PriceD=tr(PriceD)
M=[Description;[PriceA PriceB PriceC PriceD]]
%xlswrite('C:\Users\...\Graph.xlsx',M)

The Cell array M to export is

   'Ended'    'Executed'    'Failed'    'Terminal'
    [    1]    [       5]    [     6]            []
    [    2]    [       5]    [     8]            []
    [    3]            []    [     9]            []
         []            []    [     0]            []
         []            []    [     5]            []

[AND]

I really think this is going far beyond I expected, and I apologize for that, but the output I am looking for is below (kindly note that I can achieve this with my multi-write initial method which is not convenient):

A  B  C  D
1  4  7  Ended
2  5  8  Executed
3  6  9  Failed
  8  10  Terminal
  9    
  1    
  0    
  12    
  15    
  20

Thanks for your time, much appreciated.

[Iain]

If you get your data so that:

 data = {'A', 'B', 'C', 'D';
         1 ,   4,    7, 'Ended'; % and so on, until:
        };

Then you can write "data" to excel with a single xlswrite.

Oh, and I'm not Lain.

[Azzi Abdelmalek]

Ok, we add a function trc for cell array of string

header={'A' 'B' 'C' 'D'}
A=[1 2 3]'
B=[4 5 6 8 9 1 0 12 15 20]'
C=[7 8 9 10]'
D={'Ended' 'Executed' 'Failed' 'Terminal' }'
max_n=10;
tr=@(price)[num2cell(price) ;repmat({[]},max_n-numel(price),1)]
trc=@(price)[price;repmat({[]},max_n-numel(price),1)]
A=tr(A)
B=tr(B)
C=tr(C)
D=trc(C)
M=[header;[A B C D]]
xlswrite('C:\Users\...\Graph.xlsx',M)

[AND]

Dear Azzi. It didn't work, as D is just copying the numerical values in C.

I will however choose your answer as accepted, for the effort you put, and for it was partially solved.

Many thanks for your time and regards-

[Azzi Abdelmalek]

Sorry, there is a mistake. write D=trc(D) instead of D=trc(C)

[AND]

Thanks Azzi- It is working perfectly now.

Best of Regards//

Answer 2

[Shashank Prasanna]

You can use the com interface directly. XLSWRITE does this each time when you call it.

Here is an example:

http://www.mathworks.com/help/matlab/matlab_external/example-reading-excel-spreadsheet-data.html

More about COM Interface:

http://www.mathworks.com/help/matlab/using-com-objects-in-matlab.html

[AND]

Thanks for your answer Shashank, but my issue is a little different.

Regards-

[Shashank Prasanna]

You said your issue is the following. Quote:

There must be a more efficient way that opens the excel file only once, but I am not able to locate it. It is not really logical to write every time separately to the same file.

What I gave you does exactly that.

Regards,