Very slow Hyperlink creation (v5.10.26)

[d3nton]

Hello!

Writing a large number (180.000 rows) of hyperlinks into an XLSX file took me more than 6 minutes.
I profiled the XLS ReadWrite component and it seems that there are two methods which are responsible for this behaviour:
1.
function TOPCItem.MakeId: AxUCString;
var
n: integer;
begin
n := FChilds.Count + 1;
Result := 'rId' + IntToStr(n);
while getchildindex(fchilds, result) >= 0 do begin
Inc(n);
Result := 'rId' + IntToStr(n);
end;
end;
The 'while' loop seems not to be necessary since the code in the loop seems never to be used and
this loop is very time consuming.

2.
procedure TXLSWorksheet.SetAsHyperlink(ACol, ARow: integer; const Value: AxUCString);
var
HLink: TXLSHyperlink;
begin
HLink := FHyperlinks.Find(ACol,ARow);
if HLink = Nil then
HLink := FHyperlinks.Add;
if Copy(Lowercase(Value),1,3) = 'www' then
// Hyperlinks in Excel 2007 must start with http://
HLink.Address := 'http://' + Value
else
HLink.Address := Value;
HLink.Col1 := ACol;
HLink.Col2 := ACol;
HLink.Row1 := ARow;
HLink.Row2 := ARow;
end;
The first line in this procedure seems not to be necessary if you write the hyperlinks to a new file and increase the rows
and columns in a loop since HLink always returns nil for me in this case (the hyperlinks.findoperation extremley slows down the
file creation)

Another issue: writing more than ~65000 rows as hyperlinks seems to create corrupted xlsx files.

When I removed both code parts from the methods, the hyperlink creation was really fast.
Could you please take a look at the code for the hyperlink creation and check if there are some possible optimizations.
Thank you.

Best regards.

[larsa]

Hello

Don't use AsHyperlink to add several 100000 of hyperlinks.

Instead create one hyperlink where the area is the hyperlink text:

Code: Select all

var
  R: integer;
  HLink: TXLSHyperlink;
begin

// You must also set the color/underline of the text if you want it to look like a standard hyperlink.
  for R := 1 to 19 do
    FXSS.XLS[0].AsString[0,R] := 'www.google.com';

  HLink := FXSS.XLS[0].Hyperlinks.Add;
  HLink.Col1 := 0;
  HLink.Row1 := 0;
  HLink.Col2 := 0;
  HLink.Row2 := 19;
  HLink.Address := 'http://www.google.com';

[d3nton]

Hello Lars.
Thanks for the quick response.
I tried your suggested solution and one of the problems seems to be gone.The creation of the hyperlinks
is faster (setashyperlink is not used anymore) but unfortunately TOPCItem.MakeId still slows down the writing process
(using Excelfile.Write. Please see my last comment. The while getchildindex(fchilds, result) extremely slows down the export).

Sample Code (writing the file needs more than 5 minutes. The file is also corrupted.):
var
ExcelFile5 : TXLSReadWriteII5;
i: Integer;
lHyperlink: TXLSHyperlink;
begin
ExcelFile5 := TXLSReadWriteII5.Create(nil);
ExcelFile5.Filename := 'D:\output.xlsx';
ExcelFile5.Sheets[0].Name := 'test';

for I := 0 to 80000 do begin
ExcelFile5.Sheets[0].AsString[0, i] := 'C:\Windows\';
lHyperlink := ExcelFile5.Sheets[0].Hyperlinks.add;
lHyperlink.Col1 := 0;
lHyperlink.Row1 := i;
lHyperlink.Col2 := 0;
lHyperlink.Row2 := i;
lHyperlink.Address := 'C:\Windows\';
end;
ExcelFile5.Write;
End.

I also noticed that reading an excel file which contains hyperlinks takes a long time:
This function seems to be the reason:

function TOPCItem.Add(ARelationship: TCT_Relationship): TOPCItem;
begin
if FChilds.IndexOf(ARelationship.Id) >= 0 then
raise Exception.Create('Duplicate id in OPC');
...

without the lookup for the 'Relationship.id' the reading is way faster.
Is this code really necessary or are there any optimization which can be done.

Thank you.

[larsa]

Hello

Reading and writing hyperlinks is optimized in update 5.10.28
Please note that Excel has a limit of max 65530 hyperlinks.

[d3nton]

Thank you very much for the quick fix.

>Please note that Excel has a limit of max 65530 hyperlinks.
Thanks for the hint. I did not know that before.

[d3nton]

...an additional question:
Is there a way (function,method, property or s.th.else) to return the current number of hyperlinks of an excel worksheet?
This would be very helpful since it makes it easier to decide whether additional hyperlinks could be added to an existing sheet or not.

best regards
d3nton

[larsa]

Hello

Yes:

Code: Select all

function CountHyperlinks: integer;
var
  i: integer;
begin
  Result := 0;
  for i := 0 to XLS.Count - 1 do
    Inc(Result,XLS[i].Hyperlinks.Count);
end;

[d3nton]

Thank you !