XLSReadWriteII 4 - .xlsx very slow ?

[sling]

Hi
I'm using version 4.00.29a of XLSReadWriteII4 for Delphi 7. I'm creating an Excel 2007 report dynamically.
Older versions of excel have a row limit of about 65536 rows. My reports would typically have more rows than this - hence the reason for using Excel 2007 and the .xlsx format.

When I create a .xlsx file with say 75 000 rows - and I call XLSReadWriteII4.write it takes very very long to create the file.
Sample code :

procedure TfrmXLS.Button2Click(Sender: TObject);
var
XLS: TXLSReadWriteII4;
i : integer;
begin
XLS := TXLSReadWriteII4.Create(nil);
XLS.Version := xvExcel2007;
try
for i := 0 to 75000 do
begin
XLS.Sheet[0].AsString[0,i] := inttostr(i) + ':' + 'Column1';
XLS.Sheet[0].AsString[1,i] := inttostr(i) + ':' + 'Column2';
XLS.Sheet[0].AsString[2,i] := inttostr(i) + ':' + 'Column3';
XLS.Sheet[0].AsString[3,i] := inttostr(i) + ':' + 'Column4';
end;
XLS.Filename := 'c:\Test.xlsx';
XLS.Write;
finally
XLS.Free;
end;
end;

Am I using the component incorrectly ? Is there any way I can increase the speed at which the xls.write command completes ?
The above example will probably take more than 15 minutes to create the file.

It only seems to take this long when I use XLS.Version := xvExcel2007. If I create a normal .xls file it creates the file much much quicker - but I'm limited to 65000 rows.

Please advise.
Thank you !

[larsa]

Hello

When I test your sample, it takes about 14 seconds to execute. There is however a problem with Delphi 7, as it has extremely poor string handling. As Excel 2007 files are XML file, there are a lot of string operations. The only solution is to upgrade to a later Delphi version.

[sling]

Hi Larsa

Thank you for your response. It will be an enormous task to change over to a newer version of delphi at this stage of our project.
I need to get a solution for my problem and it must be in Delphi 7.

I tried to see the amount of records currently added to the XLSReadWriteII4 component before executing the write command.
Is this possible ? I tried the following code :

XLS := TXLSReadWriteII4.Create(nil);
XLS.Version := xvExcel2007;
try
for i := 0 to 75000 do
begin
XLS.Sheet[0].AsString[0,i] := inttostr(i) + ':' + 'Column1';
XLS.Sheet[0].AsString[1,i] := inttostr(i) + ':' + 'Column2';
XLS.Sheet[0].AsString[2,i] := inttostr(i) + ':' + 'Column3';
XLS.Sheet[0].AsString[3,i] := inttostr(i) + ':' + 'Column4';
end;
showmessage(inttostr(XLS.Records.Count)); // This returns a 0 ? I expected 75000
XLS.Filename := 'c:\Test2.xlsx';
XLS.Write;
finally
XLS.Free;
end;

Would the xls.records.count show me the amount of rows I added before I execute the write command ?
Or how does this work ?

Thank you.

[larsa]

Hello

The Records property is only for internal use, and can not be used as you try. The number of records written is about the same as the number of cells.