Workbook/Worksheet operation speedup (Begin/End Update?)

[totya]

Hi!

I want execute many (~1000) CopyCell in xls file (see sample code below), it is success, but it's slow, because I think the ExcelGrid drawing always happen. How can I speed up this process? I can't see begin/endupdate procedure in TsWorksheetGrid. But I see the TsWorksheetGrid.Workbook.DisableNotifications procedure, with it the operation is faster.

For example code:

Code: Pascal  [Select][+][-]

1. if Assigned(SrcCell) then
2.   begin
3.     DestCell:= ExcelGrid.Worksheet.GetCell(ActualExcelRow, RepeatColumnNumber);
4.     ExcelGrid.Worksheet.CopyCell(SrcCell, DestCell);
5.   end;
6.  

Thanks!

[wp]

But I see the TsWorksheetGrid.Workbook.DisableNotifications procedure, with it the operation is faster.

Sorry - thiere is no DisableNotification... Where is it?

As for the Begin/EndUpdate - yes the WorksheetGrid has them and it is already called a lot in order to suppress unnecessary redraws when changing many grid properties.

But as you notice it causes only a partial speedup because most of the speed-loss originates in the notifications of the worksheet via workbooksource to all attached visual spreadsheet controls. There is a Enable/DisableControls which is supposed to bypass the notification process, but as I see now it was lost by one bug fix some time abo - I try to re-activate it when I have some time.

You get best speed if you work with the bare TsWorkbook and TsWorksheet in native mode, not linked to any visual controls.

[totya]

Sorry - thiere is no DisableNotification... Where is it?

Hi!

If I press "." key after the "Grid.Workbook" this show the available procedures under Lazarus Well, I use time measure component, under lazarus this is the EpikTimer. If I use "ExcelGrid.Workbook.DisableNotifications;" line, the process is much faster, for example (about 800 copy cell):
without it: 1,54 sec
with it: 0,47sec

This is very much difference!

You get best speed if you work with the bare TsWorkbook and TsWorksheet in native mode, not linked to any visual controls.

Certainly, thank you. The old trick to connection disable and enable, for example, if I use this line

Code: Pascal  [Select][+][-]

1. Grid.WorkbookSource:=nil;

the speed is about 1,5sec again, similar of ExcelGrid.Workbook.DisableNotifications.

Thanks for the answer!

Edit.:
Without Grid, the process time is:  0,132sec

[wp]

Sorry - I was seeking in the workbookSource for the DisableNotifications.

If I use "ExcelGrid.Workbook.DisableNotifications;" line, the process is much faster, for example (about 800 copy cell):
without it: 1,54 sec
with it: 0,47sec  (...)
without Grid, the process time is:  0,132sec

Nice, but you will probably have to pay back the difference between the latter two values when the gridless-worksheet is attached to the grid, due to row height calculation which still happens in case of rows without a row record. The new revision uses the default row height now in case of such rows with unknown height. Since UpdateRowHeights now is public it can be called with AEnforceCalcRowHeight=true to enforce iterating through all cells of all rows to correctly consider font sizes and height-relevant formatting.

[totya]

Hi!

Thank you for your job!

With

Code: Pascal  [Select][+][-]

1. ExcelGrid.UpdateRowHeights(-1, true);

the few distorted cell heights now are okay. But I lost 0.5 sec But certainly the first is the error-free sheet appear.

But I get distorted (bad row height) cell with simple zoomdemo too, at the moment I can't create sample from private xls, but I know what's wrong.

This is sample in Excel:
http://p.coldline.hu/2016/10/12/2357287-20161012-61FQB8.png

In zoom demo:
http://p.coldline.hu/2016/10/12/2357288-20161012-liSL1Q.png

Yes, in zoomdemo the "B1" cell isn't distorted, but this is cause distortion problem with my real job excel file, I get distorted cell with these type of error (the cell height is smaller than needed).

The differents as you see, the excel can wrap text with "-", but your component can't do this, and the cell width is bigger than needed now, but my private xls, few cell heights samller than needed, because as I remember this componenet read the original row heigths, and if the word-wrap is not compatible between excel and FPSpreadsheet, this cause distrotion problem (cell height is smaller than needed).

Original:
(1977-2055)
Excel can wrap to: "(1977-" and "2055)"
FPSpreadsheet doesn't.

Thanks!

[wp]

Files written by Excel do have row records - the row height should be displayed correctly in the grid (not exactly because the exact calculation method is not documented)

Files written by FPSpreadsheet with stand-alone worksheets (i.e. no grid involved) do not write row records because the non-visual workbook does not know about text size exactly. When such a file is loaded into the WorksheetGrid all rows should have the default row height. If this is not correct - because you maybe have varying font sizes - then your code should call Grid.UpdatedateRowheights(-1, true) to enforce row height calculation (which of course will take some time because every cell has to be checked for many formatting cases)

The zoomdemo was very slow in case of a large input file because inherited Begin/EndUpdate was not called. Fixed now.

[totya]

Hi!

I'm sorry for my bad english!

Luckily, I understand you, but you don't undersdtand me This is my fault, I know.

Once again, the untouched zoomdemo display my job file incorrectly (few cell only), like as fpsgrid_no_install too, this isn't zoomdemo problem. The row heights incorrect for few cell in my job file, because few row height same as what I see in excel, but the excel use different wordwrap, what I showed my previous message. Again: Excel can break a word, if word contain the "-". See sample pictures in my previous message.

...and yes, I succesfully created the sampe

Open attached file in excel. You will see similar of this:

http://p.coldline.hu/2016/10/12/2357702-20161012-FOot80.png

... then open attached file from FPSpreadsheet, for example fpsgrid_no_install or zoomdemo. You can see similar of this:

http://p.coldline.hu/2016/10/12/2357703-20161012-D6LQrN.png

I think the problem is the different wordwrap, if the word contain "-" sign, see: "1990-2020". I hope you understand me now! 

[wp]

Your English is fine, no problem - I just forgot writing about this issue...

Thanks for the file. You are right. Excel is doing a different wordwrap resulting in a 2-line-cell, while fpspreadsheet has a 3-line cell. And because fpspreadsheet does not calculate row heights any more it uses the 2-line row height stored in the Excel file, instead of the 3-line row height needed.

Unfortunately, there's not much I can do against this. Since column width and row height calculations are not well-documented and may always be off by a few pixels there is always a chance that Excel and fpspreadsheet will use different line breaks even if I'd allow a hyphen as a wrappable character in fpspreadsheet like Excel does (which I am very hesitant to do because it may open another set of issues due to localization differences).

The only thing you can do is to call the grid's UpdateRowHeights after loading a file. In the fpctrls_demo there is now a new menu command "Format" / "Auto row heights" for this purpose.

As for the zooming speed mentioned in the previous post, I'd like to add that I had loaded one of the 10.000-lines x100-columns files created by the fpsspeedtest. Zooming was really extremely slow (a few seconds per zoom step). But now that the Begin/EndUpdate of the grid is fixed, zoom reacts instantly.

[totya]

Hi!

Thanks for the answer!

Well, never be the perfect visualization for the excel file, because for example the Excel file visualization depend on printer driver, and excel version too...

[chenyuchih]

Hi,

Recently I started to try the fpSpreadsheet and encountered the speed problem, too. Finally I find the bottleneck of my application and figured out a solution for it. Although this topic is over 2 years old, I think it's still worthy to reply here for those who have the same issue.

As what wp explained, the visual component, WorksheetGrid, is notified of refreshment by the Workbook when the content changed. Sometimes my application may change a lot of cells in one procedure, and it causes lots of notification, too. That's why the application slows down so much, right? But actually, I just want to see the result of procedure, the visual changes/refreshment/update during operation is not needed.

I tried to use WorksheetGrid.Workbook.DisableNotifications at the beginning of the procedure and put WorksheetGrid.Workbook.EnableNotifications before procedure end, but I found that the WorksheetGrid hanged and didn't update anymore.

After many tests, I realized that the WorksheetGrid hanged because there was no furthur refresh notification after EnableNotifications. So, I modified my code and put the WorksheetGrid.Workbook.EnableNotifications before the last cell change. It works like a charm! Everything runs as good as what I want. The attachment is a small sample to show how much improvement it goes. I wish this tip could help somebody in similar situation.

BTW, is it possible to send the notification once in WorksheetGrid.Workbook.EnableNotifications? Thanks!

Best Regards,
ChenYuChih

[wp]

Is it possible to send the notification once in WorksheetGrid.Workbook.EnableNotifications?

Looking at the code again (after a while) I see that DisableNotifications/EnableNotifications refer to the OnChanged event which is emitted by the worksheet whenever cell content, cell format etc. changes. This event is distributed by the WorkbookSource among all visual spreadsheet controls which react on this event: the grid for example displays the new cell content or selects the newly focused cell etc. In total this consumes a lot of time, and this is why Disable/EnableNotfications were introduced: between Disable and EnableNotifications the OnChanged events simply are not generated.

Since it is not possible to recreate the last OnChanged event in the EnableNotification method you must always place the EnableNotification BEFORE the last change of a cell.

[chenyuchih]

I see. It seems that my trick is the most workable usage so far. Thanks wp!