I use an existing file and add a new tab.
With this code I fill this new tab. Colcount is 8 and rowcount about 500.
Problem is, that this code takes about 10 minutes. I guess, that is the problem with linked cells from existing tabs to new created tabs.
How can I improve this function?
for c := 0 to SGExport.ColCount -1 do begin
for r := 0 to SGExport.RowCount -1 do begin
Xls.SetCellFromString(r + 1, c + 1, SGExport.Cells[c, r]);
end;
end;
Hi,
Adding 8x500 cells shouldn't take near 10 minutes. One thing that can be slow here is SetCellFromString, since it tries a lot of possible things (is it a number? maybe a date? yyyy-mm-dd or dd/mm/yyyy? a time? etc). If you know which kind of formats you expect, it can go much faster. But still, 10 minutes look like too much.
Do you have a small example that you can send me (adrian@tmssoftware.com) which I can look here to try to reproduce it? Also, are the 10 minutes spent in that loop, or when you actually save the file?
It seems, that not SetCellFromString is the problem but this line
Xls.AutofitCol(1, Xls.ColCount, 1, Xls.RowCount, false, 1.2, 0, 0, 0, TAutofitMerged.None);
Hi,
Yes, sadly AutofitCol can be very slow. This is a known issue and something we would like to fix, but it is not an easy one. The problem is: AutofitCol does a real rendering of each cell, including stuff like multiple fonts in a single cell, formatting if the cell has a number, takes in account right to left, etc. So it is slow to measure each cell. But the story doesn't end there, we also use GDI+ instead of GDI in all graphics operations, and GDI+ is incredibly slow to measure strings, which is what AutofitCol basically does.
The result is that when you autofit some millions of cells, it can get slow fast.
As said, it is something in our todo list to improve its performance, but it isn't a simple one. We can't really switch away from GDI+, and other stuff like measuring each letter first and in the loop add the widths of each letter would only work with western languages. If you are measuring arabic or other complex scripts, the letters change depending on what comes before or after, so you can't just add font sizes.
Right now, the workaround is to not autofit the full sheet. Normally you can autofit some hundred of rows and you will get the same results way faster. In fact, that's what Excel does (or did, I haven't checked lately( when you autofit a column: It autofits the visible part and some thousand rows, not the full thing.
For that, you would need to do something like
Xls.AutofitCol(1, Xls.ColCount, 1, 1000, false, 1.2, 0, 0, 0, TAutofitMerged.None);
As said, it is in our list to see how to improve the performance here, but in the meantime, the workaround works normally well, and it is much much faster.