create one cell in excel after one iteration

Hi guys,

this is the code:

M = cell(28,1);
for i=1:3;
  M{i} =  UM(i,:);
  IPV = M{i,1}(1);
  IB = M{i,1}(2);
  IWP = M{i,1}(3);
  IK = M{i,1}(4);
  IWS = M{i,1}(5);    
    for p = 1:2;
   Bundesland=p;

     OptimierungPV_B_BANDU;

     Kapitalwert(1,1)=num2cell('ZFW%d', i);

       if Bundesland == 1;
      xlswrite('testsheet.xlsx', ...
      Kapitalwert,...
      'Kapitalwerte','A1:A28');
      elseif Bundesland == 2;
      xlswrite('testsheet.xlsx', ...
      Kapitalwert, ...
        'Kapitalwerte','B1:B28');
     end;
     end;
  end;
    end;

After each iteration in the script "Optimierung_.." the value of ZFW is calculated by the script. this value should be written into a excel file, depending on "Bundesland".

when i run the code, i receive this error:

Subscripted assignment dimension mismatch.

Error in Kapitalwertvergleich_28_Moeglichkeiten (line 28)
   Kapitalwert(1,1)=num2cell('ZFW%d', i);

thanks for your support.

Answer 1

[Cedric]

What are you trying to achieve with the line?

 Kapitalwert(1,1)=num2cell('ZFW%d', i);

Type

 doc num2cell

you'll see that it doesn't work like S/FPRINTF. It just takes a numeric array and converts it to cell array. You might want to do something like the following (I guess):

 Kapitalwert{1,1} = sprintf('ZFW%d', i) ;

(note the curly brackets for addressing the content of the cell and not the cell itself).

Another point is that it is generally a bad idea to export to XLS(X) in a loop, as this is a time consuming operation. We usually build a full cell array for export purpose in the loop, and we export it in one shot after the loop.

[Cedric]

But wait, with 'ZFW%d' you are not trying to build a string actually, but to access the content of the variable ZFW ??

Assuming that ZFW is computed elsewhere, depends on p, is a column vector, and has the same size for all p (e.g. 28), I would do something like that..

 for p = 1 : 2

    % .. some code for computing ZFW.

    if p == 1
       xlsContent = num2cell( ZFW ) ;
    else
       xlsContent = [xlsContent, num2cell( ZFW )] ;
    end
 end

 xlswrite( 'testsheet.xlsx', xlsContent ) ;

[Michael]

hey cedric, thanks a lot for your help. you improved it already a lot and understood me completely with your assumptions.

so now i got the first row of my matrix 28x2. but only the first row is fullfilled. i need 28. it does not take another ZFW in another iteration. here is the update:

M = cell(28,1);
for i=1:3;
  M{i} =  UM(i,:);
  IPV = M{i,1}(1);
  IB = M{i,1}(2);
  IWP = M{i,1}(3);
  IK = M{i,1}(4);
  IWS = M{i,1}(5);    
    for Bundesland = 1:2;
    OptimierungPV_B_BANDU;
   if Bundesland == 1;
    xlsContent =num2cell(ZFW);
    else Bundesland == 2;
    xlsContent =[xlsContent, num2cell(ZFW)];
   end;
   end;
  end;
  xlswrite( 'testsheet.xlsx', xlsContent );

[Cedric]

You're welcome!

What I still don't understand though is that in your initial code, you were writing at A1:A28 and B1:B28 for all i. This means that each iteration of the outer loop overwrites previous values. Also, if ZFW doesn't depend on p, why did you implement this loop?

If you wanted to increment columns in the Excel file and ZFW were depending on both i and p, you could implement the following.. (I am trying to show different approaches, so you can pick what is relevant to your case):

 xlsContent = {} ;

 for ii = 1 : 3           % Don't use i, it's for complex notation.
    M{ii} = ...
    ...
    for p = 1 : 2
       ...
       xlsContent = [xlsContent, num2cell(ZFW)] ;
    end
 end

 xlswrite( 'testsheet.xlsx', xlsContent );

[Michael]

column A1:28 was supposed to be for ZFW computed with p=1 and

column B1:28 was supposed to be for ZFW computed with p=2.

the code should do the following: in the inner loop it computes a ZFW depending on p and puts it in the two cells A1 and B1.

then for each iteration, it should go down and generating two columns untils it ends up on line 28 with the last computation.

But still, only the first row is filled by two ZFW values, the others are still empty aber the computation.

[Cedric]

But what iterates until 28? And what is the outer loop for? The way you designed the first code made me think that ZFW was already a 1x28 vector; is it not the case?

[Michael]

the outer loop should iterate until 28(i put 3 for not loosing so much time every time i run "Optimierung.."). cell array M puts different constraints (horizontally) as input parameters as you can see in the following:

[1700 200 800 150 4]
[1700 0 0 0 0]
[0 200 0 0 0]
[0 0 800 0 0]
[0 0 0 150 0]
[0 0 0 0 4]
[1700 200 0 0 0]
[1700 0 800 0 0]
[1700 0 0 150 0]
[1700 0 0 0 4]
[0 200 800 0 0]
[0 200 0 150 0]
[0 200 0 0 4]
[0 0 800 150 0]
[0 0 800 0 4]
[0 0 0 150 4]
[1700 200 800 0 0]
[1700 200 0 150 0]
[1700 200 0 0 4]
[0 200 800 150 0]
[0 200 800 0 4]
[0 0 800 150 4]
[0 200 800 150 4]
[1700 0 800 150 4]
[1700 200 0 150 4]
[1700 200 800 0 4]
[1700 200 800 150 0]
[0 0 0 0 0]

these different inputs generate different outputs ZFW. No, in the first code and still now, ZFW is only a number.

[Cedric]

Ok, then try the following..

 nRows = 28 ;
 nCols = 2 ;
 xlsContent = cell( nRows, nCols ) ;               % Prealloc.

 for rId = 1 : nRows
    M{rId} = ...
    ...
    for cId = 1 : nCols
       ...
       xlsContent{rId,cId} = ZFW ;
    end
 end

 xlswrite( 'testsheet.xlsx', xlsContent );

[Michael]

hi cedric,

it might work. I am not completely convinced of the computed results. thatswhy i want to test the results.

My question: i want to extract more variables (1) than ZFW from the script and want to proof if the program took the right constraints(2; cell array like shown above). The results differs from each iteration, but they differ only in two numerical values.

i tried this code, but it did not work.

 nRows = 3 ;
 nCols = 2 ;
xlsContent=cell( nRows, nCols );
M = cell(28,1);
for rId=1:nRows
  M{rId} =  UM(rId,:);
  IPV = M{rId,1}(1);
  IB = M{rId,1}(2);
  IWP = M{rId,1}(3);
  IK = M{rId,1}(4);
  IWS = M{rId,1}(5);    
   for Bundesland = 1:nCols
       OptimierungPV_B_WP_mitWaermespeicher_Bandu_14;

    xlsContent{rId,Bundesland}(1,1) = ZFW;
    end;
  xlsContent{rId}(1,3) = IPV;
  xlsContent{rId}(1,4) = IB;
  xlsContent{rId}(1,5) = IWP;
  xlsContent{rId}(1,6) = IK;
  xlsContent{rId}(1,7) = IWS;
end;
xlswrite( 'testsheet.xlsx', xlsContent, 'Kapitalwerte');

the values of IPV, IB, etc are not shown in xlsContent nor in the excel sheet.

thanks so much for your support.

[Cedric]

It cannot work. XLSWRITE can only export numeric arrays, or cell arrays with scalar or string content. The cell array xlsContent has the following structure

 Row\Col     1        2
   1     [       ][       ]
   2     [       ][       ]
   3     [       ][       ]

Where each [ ] represents a cell. In MATLAB, cells can contain any type/class of data of any size. But XLSWRITE will only work with cells which contain scalars (one number per cell) and strings. You probably make several mistakes in the code above: this line for example

 xlsContent{rId}(1,3) = IPV;

indexes xlsContent with only a row Id, which is incorrect for a 2D cell array (it is actually technically possible: it is called linear indexing, but it is certainly not what you want). Then the second index (1,3) builds a numeric array withing the cell, which is probably not what you want, and is not supported then by XLSWRITE.

In the expression

 IWS = M{rId,1}(5);

the part M{rId,1} is addressing the content (curly brackets address the content of cells) of cell on row rId and column 1, and the second index (5) is addressing the 5th element of the content, .. which is probably not what you want.

So I think that your general approach is correct, but that you need to train on simpler cases with numeric arrays and cell arrays. To illustrate..

 >> C = cell(3, 2) ;               % Build 3x2 cell array.
 >> class(C)
 ans =
      cell

 >> C{2,1} = 6                     % Store scalar 6 as content of cell 2,1.
 C = 
     []    []
    [6]    []
     []    []

 >> class( C{2,1} )                % Check that content is numeric/double.
 ans =
      double

 >> class( C(2,1) )                % Now element 2,1 of C is a cell!
 ans =
      cell

here you can see that parentheses are for block-indexing elements of arrays, whereas curly brackets are getting the content of cells.

 >> C{3,2} = [10:12]                 % Store numeric array in cell 3,2.
 C = 
     []              []
    [6]              []
     []    [1x3 double]

At this point, C could not be exported anymore with XLSWRITE.

 >> C{3,2}(1)        
 ans =
    10

C{3,2} addresses content of cell 3,2 (1) addresses element 1 of content (which is a numeric array). Same for other elements.

 >> C{3,2}(2)
 ans =
    11

 >> C{3,2}(3)
 ans =
    12

So my advice is: use SIZE, CLASS, DISP, etc, to get more insights about the objects that you are dealing with in your code, and then it will be clear how to build an appropriate cell array for exporting with XLSWRITE.

[Michael]

thanks for this little teaching. it helped me.

but i can not change the variable Bundesland, because in the script there 2 cases. If i extend the second loop to 3 or 4, it will not help me because Bundesland is restricted from 1 to 2.

So how can i solve the problem ?

[Cedric]

If I understand well, Bundesland is the column index, so there is no need to increase it. You have the following nested loop

 for rId = 1 : nRows
    ...
    for Bundesland = 1 : nCols
       ...
       xlsContent{rId,Bundesland} = ZFW ;
    end
 end

which will compute

 xlsContent{1,1} = ZFW
 xlsContent{1,2} = ZFW
 xlsContent{2,1} = ZFW
 xlsContent{2,2} = ZFW
 xlsContent{3,1} = ZFW
 xlsContent{3,2} = ZFW
 xlsContent{4,1} = ZFW
 xlsContent{4,2} = ZFW
 ...

in that order, as the column loop (Bundesland) is the inner loop. At this point, it would be interesting to start using the debugger to observe what happens (so you can control each step of the execution). For that, place your cursor on the line

 nRows = 28 ;

and press F12 for setting a break point at this location. If your code starts with a "clear all" statement, comment it out as it would clear the break point. Run your code (Run arrow or by pressing F5), you'll see that it will stop at the break point and a green arrow will appear indicating where the program is standing; the command window prompt will also change from >> to K>>. Then press F10 to move forward (or F11 if you want to enter scripts of functions which are called, but at this point I'd say stick to F10). Each time you press F10, the program moves forward and you can display variables' content in the command window. At any moment you can ask MATLAB to finish the execution (or go to the next break point) by pressing F5 again, or interrupt the debugging session by pressing Shift+F5. Using the debugger, you'll see how the empty cell is built initially and then what happens during the execution, step by step. In particular, you'll be able to see how the cell array is filled in.

Answer 2

[Michael]

ok thanks, you are right. i want the value ZFW to be put into an excelfile after each iteration by this line:

Kapitalwert(1,1)=num2cell('ZFW%d', i);

now, i changed the code according to your suggestions: but then i do have the following problem, that i can not distignuish anymore. the if call does not work anymore.

M = cell(28,1);
for i=1:3;
  M{i} =  UM(i,:);
  IPV = M{i,1}(1);
  IB = M{i,1}(2);
  IWP = M{i,1}(3);
  IK = M{i,1}(4);
  IWS = M{i,1}(5);    
    for p = 1:2;
   Bundesland=p;

     OptimierungPV_B_BANDU;

     Kapitalwert{1,1}=sprintf('ZFW%d', i);

     end;
 end;

if Bundesland == 1;
      xlswrite('testsheet.xlsx', ...
      Kapitalwert,...
      'Kapitalwerte','A1:A28');
      elseif Bundesland == 2;
      xlswrite('testsheet.xlsx', ...
      Kapitalwert, ...
        'Kapitalwerte','B1:B28'); 
end;

[Cedric]

See my comment under my answer.