🚀 go-pugleaf

This is a multi-part message in MIME format.
--------------040307050001090107080707
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hi,

I am automating Excel2000 using Delphi7 on Windows XP and the supplied
Server-Components. Attached is a simple test-program I wrote.

When I run this program specifying a moderately complex Excel-Worbook
(a 300K file). My output is something like:

  >$ ./XLSBench.exe d:\\Projekte\\XLSServer\\bin\\Data\\NemLocKeyTest.xls
  >                      Starting
  >T+     94ms (   94ms): CoInitialize()
  >T+     94ms (    0ms): TExcelApplication.Create()
  >T+    640ms (  546ms): FExcel.Connect()
  >T+  66703ms (66063ms): FExcel.Workbooks.Open()
  >T+  66703ms (    0ms): TExcelWorkbook.Create()
  >T+  66719ms (   16ms): WB.ConnectTo()
  >T+  66859ms (  140ms): WB.Sheets[1] as _Worksheet
  >T+  66859ms (    0ms): TExcelWorkSheet.Create()
  >T+  66875ms (   16ms): WS.ConnectTo()
  >T+  66875ms (    0ms): WS.EnableCalculation:=False
  >T+ 177750ms (110875ms): WS.EnableCalculation:=True
  >T+ 177765ms (   15ms): WS.Disconnect()
  >T+ 177765ms (    0ms): WS.Free()
  >T+ 180515ms ( 2750ms): WB.Close()
  >T+ 180515ms (    0ms): WB.Free()
  >T+ 180547ms (   32ms): FExcel.Quit()
  >T+ 180656ms (  109ms): FExcel.Free()
  >T+ 180703ms (   47ms): CoUninitialize()
  >T+ 180703ms (    0ms): Done
  >
  >Press Return to quit.

As you can see opening and recalculating the workbook is eccessively
slow. Opening Excel and the file by standard doubleclick takes me about
6s, here it's a minute. Recalculating in an interactive Excel-session is
instantaneous, here it takes almost two minutes.

If I open Excel empty before running the test my program attaches to
that instance (which I do not want it to, BTW) and I can watch the file
open and it opens quickly. Recalculating is still slow. I can watch the
progress crawling along in the Excel statusbar.

If while waiting I switch the focus to Excel recalculation takes off and
is finished at normal speed which is an eyeblink. This above all makes
me believe the slowdown is deliberate and hopefully avoidable.

Why the difference? Is my program discriminated against? :-)

What can I do to achieve the same performance in my testprogram that I
get from Excel when running it as desktop application?

Ciao, MM
-- 
Marian Aldenh�vel, Rosenhain 23, 53123 Bonn.
Fon +49 228 624013, Fax +49 228 624031.
http://www.marian-aldenhoevel.de
"FOUR MORE BEERS!"

--------------040307050001090107080707
Content-Type: text/plain;
 name="XLSBench.dpr"
Content-Transfer-Encoding: 8bit
Content-Disposition: inline;
 filename="XLSBench.dpr"

program XLSBench;
{$APPTYPE CONSOLE}

{ Benchmark f�r die Excel-Steuerung aus XLSServer. Bastelprojekt ohne
  dauerhaften Wert. }

uses
  Windows,
  SysUtils,
  Variants,
  ActiveX,
  ComObj,
  Excel2000;

var StartTicks,LastTicks:DWord;

procedure Tick(Msg:string);
begin
  writeln(Format('T+ %5dms (%5dms): %s',[GetTickCount-StartTicks,GetTickCount-LastTicks,Msg]));
  LastTicks:=GetTickCount;
end;

procedure Usage;
begin
  writeln('Erster und einziger Parameter ist eine XLS-Datei.');
  halt(255);
end;

var FExcel:TExcelApplication;
    Workbookname:string;
    _WB:_Workbook;
    WB:TExcelWorkbook;
    _WS:_WorkSheet;
    WS:TExcelWorkSheet;
begin
  try
    if ParamCount=0 then Usage;

    StartTicks:=GetTickCount;
    LastTicks:=GetTickCount;
    writeln('                      Starting');

    CoInitialize(NIL);
    Tick('CoInitialize()');

    FExcel:=TExcelApplication.Create(NIL);
    Tick('TExcelApplication.Create()');

    FExcel.Connect;
    Tick('FExcel.Connect()');

    WorkbookName:=ParamStr(1);

    _WB:=FExcel.Workbooks.Open(WorkbookName,
                               EmptyParam,EmptyParam,EmptyParam,EmptyParam,
                               EmptyParam,EmptyParam,EmptyParam,EmptyParam,
                               EmptyParam,EmptyParam,EmptyParam,EmptyParam,
                               LOCALE_USER_DEFAULT);
    Tick('FExcel.Workbooks.Open()');

    WB:=TExcelWorkbook.Create(NIL);
    Tick('TExcelWorkbook.Create()');

    WB.ConnectTo(_WB);
    Tick('WB.ConnectTo()');

    _WS:=(WB.Sheets[1] as _Worksheet);
    Tick('WB.Sheets[1] as _Worksheet');

    WS:=TExcelWorkSheet.Create(NIL);
    Tick('TExcelWorkSheet.Create()');

    WS.ConnectTo(_WS);
    Tick('WS.ConnectTo()');

    (*
    WS.EnableCalculation:=False;
    Tick('WS.EnableCalculation:=False');

    WS.EnableCalculation:=True;
    Tick('WS.EnableCalculation:=True');
    *)

    // Oder:

    WS.Range['F16','F16'].Value:=2000;
    Tick('WS.Range[''F16'',''F16''].Value:=2000');

    WS.Disconnect;
    Tick('WS.Disconnect()');

    WS.Free;
    Tick('WS.Free()');

    WB.Close;
    Tick('WB.Close()');

    WB.Free;
    Tick('WB.Free()');

    FExcel.Quit;
    Tick('FExcel.Quit()');

    FExcel.Free;
    Tick('FExcel.Free()');

    CoUninitialize;
    Tick('CoUninitialize()');

    Tick('Done');
  finally
    writeln;
    writeln('Press Return to quit.');
    readln;
  end;  
end.


--------------040307050001090107080707--