Optimising Excel automation

The key to optimising automation is to call the automation server (ie Word or Excel) as little as possible. In Word, this especially means not calling the server inside a loop. This is normally achieved by building a string by concatenation in a loop, and then passing this string to Word when it is finally built, eg

tmp:= '';
with qExample do
 begin
  close;
  params[0].asinteger:= n;
  open;
  while not eof do
   begin
    tmp:= tmp + fieldbyname ('name').asstring + #13;
    next
  end
end;

wrdSel.typetext (tmp);

Optimisation should not be limited solely to outputting text. Looking over one of my programs, I discovered that I had output text to a table, and then set the shading for each cell in a column separately. It is, of course, better to shade a table column all in one go, and then set any cells which need to have differring shading. Bearing in mind what I wrote previously about accessing Word collections in Delphi, the code becomes

wrdTable:= WrdDoc.Tables.Add (wrdapp.selection.Range, row + 4, 12, 1, 2);
wrdTable.columns.item(1).select;
wrdSel.range.shading.texture:= 100;
// 'unshade' certain cells
wrdTable.cell (1, 1).range.shading.texture:= 0;
wrdTable.cell (2, 1).range.shading.texture:= 0;

And now onto Excel.... The major activity when automating Excel is passing values to the various cells; other activities such as formatting the worksheet exist, but are much less important. Remembering the prime directive in automation, "call the server as infrequently as possible", it seems as if there is little to be done to automate Excel. Well, not quite.

The first, most simple and most effective optimisation is to hide Excel until all the data has been transferred. Whilst it is cool to watch the data being transferred, the screen updating is also slow. So the standard code to launch Excel should be

XLApp:= CreateOleObject('Excel.Application');

XLApp.Visible:= false;

XLApp.Workbooks.Add;

Sheet:= XLApp.Workbooks[1].WorkSheets[1];

and at the end of the code should be the line

XLApp.Visible:= true;

How can the Word optimisation of concatenating the data before exporting it be translated to Excel? The answer is to declare an array in Delphi, populate this array in Delphi, and then transfer it in one go to Excel. I don't remember where I picked up this technique so unfortunately I can't credit anyone. [Edit: here is the link]

Here is the complete code from a demonstration program which I wrote for myself; the only component on the form is a 5X5 stringgrid renamed sg. The program populates this stringgrid and then performs the magic. The important stage is to chose a range in the worksheet which is the same size and dimensions as the array to be passed; this is the 'range:= ' line.

procedure TForm1.Button1Click(Sender: TObject);
var
 xls, sheet, Range, arrData: Variant;
 i, j: integer;

begin
 {create variant array where we'll copy our data}
 arrData:= VarArrayCreate([1, sg.RowCount, 1, sg.ColCount], varVariant);

 {fill array}
 for i:= 1 to sg.RowCount do
  for j:= 1 to sg.ColCount do
   arrData[i, j] := sg.Cells[j-1, i-1];

 {initialize an instance of Excel}
 xls:= CreateOLEObject('Excel.Application');
 xls.visible:= false;
 Sheet:= XLApp.Workbooks[1].WorkSheets[1];

 Range:= sheet.Range[sheet.Cells[1, 1], sheet.Cells[sg.rowcount, sg.colcount]];
 Range.Value:= arrData; {copy data from allocated variant array}
 xls.Visible:= True;
 range:= unassigned;
 sheet:= unassigned;
 xls:= unassigned;
 close
end;

Happy optimising!