Dynamically change column in excel worksheet inside a for loop

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,

Answer 1

[Image Analyst]

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.

[Image Analyst]

Or else create your cell array in advance and then call xlswrite() once when you're done rather than as each cell is created.

[Damith]

Thanks.

I am not intended to open 200 worksheets in a Excel file but rather start from C9 cell in one worksheet (Zone1). The reason why I stored in a cell array (Data1) is that each cell has different number of rows.

Any idea?

[Guillaume]

Each time you call xlswrite, it starts Excel, opens the excel file, writes to it, closes the file and shuts down excel. You're doing that 200 times, that's going to take a long time.

[Damith]

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  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
0.58  0.58  0.58  0.58  0.58  0.58  0.58  0.58  0.58  0.58
1.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  0
18.26  18.26  18.26  18.26  18.26  18.26  18.26  18.26  18.26  18.26
.
.

[Image Analyst]

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]

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?