create one cell in excel after one iteration

Question

Michael 2013-10-15

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/90262-create-one-cell-in-excel-after-one-iteration

评论： Cedric 2013-10-17

采纳的回答： Cedric

在 MATLAB Online 中打开

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Cedric 2013-10-15

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/90262-create-one-cell-in-excel-after-one-iteration#answer_99727

编辑：Cedric 2013-10-15

在 MATLAB Online 中打开

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.

11 个评论
显示 9更早的评论隐藏 9更早的评论

Michael 2013-10-16

编辑：Michael 2013-10-16

在 MATLAB Online 中打开

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 2013-10-16

在 MATLAB Online 中打开

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
   [       ][       ]
   [       ][       ]
   [       ][       ]

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 2013-10-17

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 2013-10-17

在 MATLAB Online 中打开

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 2013-10-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/90262-create-one-cell-in-excel-after-one-iteration#answer_99729

在 MATLAB Online 中打开

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;

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Cedric 2013-10-15

See my comment under my answer.

请先登录，再进行评论。

create one cell in excel after one iteration

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

11 个评论
显示 9更早的评论隐藏 9更早的评论

更多回答（1 个）

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

Community Treasure Hunt

create one cell in excel after one iteration

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

11 个评论 显示 9更早的评论隐藏 9更早的评论

更多回答（1 个）

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

11 个评论
显示 9更早的评论隐藏 9更早的评论

1 个评论
显示 -1更早的评论隐藏 -1更早的评论