Dynamically change column in excel worksheet inside a for loop

Question

Damith 2014-9-25

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/156190-dynamically-change-column-in-excel-worksheet-inside-a-for-loop

编辑： Damith 2014-9-26

Hi,

Can somebody help me how I can modify/include the follwing code to dynamically write output to Excel file inside a for loop starting from the cell "C9" to advance upto 200 columns?

for i=1:200
     Data1{:,i}=prcp(RowID(i,1):RowID(i,2),5);
     xlswrite('test.xls',Data1{1,i},'Zone01','??') 
end

Thanks in advance,

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

请先登录，再进行评论。

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

Answer 1

Image Analyst 2014-9-25

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/156190-dynamically-change-column-in-excel-worksheet-inside-a-for-loop#answer_152896

ExcelDemo.m

Oh my gosh. You definitely don't want to do that, unless you have lots of time to wait or have solid state drives (faster than hard drives by a lot). Launching Excel 200 times, tossing data into it 200 times, saving the file 200 times, and shutting down Excel 200 times will take a very long time. You want to use ActiveX anytime you need to call xlswrite more than about 4 or 5 times. Attached is a demo. With ActiveX, you launch once, toss data in as many times as you want lightning fast, save it once and shut it down once.

6 个评论
显示 4更早的评论隐藏 4更早的评论

Damith 2014-9-25

编辑：Damith 2014-9-25

在 MATLAB Online 中打开

Thanks guys for all your valuable comments.

I modified the following code considering the above comments and it's faster. But, the,

Excel = actxserver ('Excel.Application');

File='<path>\test.xls'; 
  if ~exist(File,'file') 
      ExcelWorkbook = Excel.workbooks.Add; 
      ExcelWorkbook.SaveAs(File,1); 
      ExcelWorkbook.Close(false); 
  end 
  invoke(Excel.Workbooks,'Open',File);
    for i=1:10
         Data1{:,i}=prcp(RowID(i,1):RowID(i,2),5);  
         xlswrite1('test.xls',Data1{1,i},'test','C9:L18271')
    end
    invoke(Excel.ActiveWorkbook,'Save'); 
    Excel.Quit 
    Excel.delete 
    clear Excel

But, when I open the test.xls I see the output below. It's writing the 10th columns output for all the columns. How can I fix this? Where and what I am doing wrong in the code above. The number of rows in each cell of "Data1" cell array alternatively changes from 18262 to 18263.

Any help is appreciated.

0  0  0  0  0  0  0  0  0
0  0  0  0  0  0  0  0  0
0  0  0  0  0  0  0  0  0
0  0  0  0  0  0  0  0  0
0  0  0  0  0  0  0  0  0
0  0  0  0  0  0  0  0  0
03  0.03  0.03  0.03  0.03  0.03  0.03  0.03  0.03  0.03
58  0.58  0.58  0.58  0.58  0.58  0.58  0.58  0.58  0.58
93  1.93  1.93  1.93  1.93  1.93  1.93  1.93  1.93  1.93
0  0  0  0  0  0  0  0  0
26  18.26  18.26  18.26  18.26  18.26  18.26  18.26  18.26  18.26
.
.

Image Analyst 2014-9-25

在 MATLAB Online 中打开

Is RowID integers, and prcp an array of doubles? And why are you using a cell array for Data1 rather than just a regular old double array? Also, you need to derive the cell reference for Excel. You're stuffing all of these arrays into the same location. Maybe you should just do

for i=1:10
  row1 = RowID(i,1);
  row2 = RowID(i,2);
  Data1 = prcp(row1:row2,5);  % Col5 between row1 and row2
  cellReference = sprintf('C%d:C%d', row1, row2);
  xlswrite1('test.xls', Data1, 'test', cellReference)
end

Damith 2014-9-26

编辑：Damith 2014-9-26

在 MATLAB Online 中打开

Yes RowIDs are integers and prcp an array of doubles.

I tried your code above and it does not work.

The reason why I am storing in cell array is when you read from the RowIDs the size of the double arrays are different meaning number of rows are alternatively changing from 18262 to 18263. So, I am storing in "Data1" cell array.

I modified your code above (see below).

for i=1:10
    row1 = RowID(i,1);
    row2 = RowID(i,2);
    Data1{:,i}=prcp(row1:row2,5);     xlswrite1('test.xls',Data1{1,i},'test','C9:L18271')
end

Why the code above does not write cell arrays of "Data1" from 1-10 but rather writes only 10th cell array for all columns from "C9"?

Any idea?

请先登录，再进行评论。

Dynamically change column in excel worksheet inside a for loop

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

回答（1 个）

6 个评论
显示 4更早的评论隐藏 4更早的评论

另请参阅

类别

标签

Community Treasure Hunt

Dynamically change column in excel worksheet inside a for loop

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

回答（1 个）

6 个评论 显示 4更早的评论隐藏 4更早的评论

另请参阅

类别

标签

Community Treasure Hunt

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

6 个评论
显示 4更早的评论隐藏 4更早的评论