TsWorksheetGrid - distorted cells after scroll [SOLVED]

[totya]

Hi!

This is not the "new bug" which appear on 5218 svn! (Result is similar).

From te begining (few days ago) I have seen this error. This is very ugly, and high priority problem, really.

Instructions:
svn 5214 (the last workable version)

examples/fpsgrid_no_install, open it with Lazarus, put one button on the form, code:

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

1. procedure TForm1.Button1Click(Sender: TObject);
2. begin
3.   Grid.Row:=40;
4. end;
5.  

Compile/run, load the attached file: test_distorted_view.xls. View is okay. But now press the new "button1" button, and u see, the view is distorted. This is happen sometimes when I scroll the Grid content too, without direct scroll with button or code.

Thanks!

ps.: attached file analysis

Sample pictures:

before scroll:
http://p.coldline.hu/2016/09/27/2340160-20160927-ZCeKlK.png
after scroll:
http://p.coldline.hu/2016/09/27/2340161-20160927-k9OTur.png


Edit1, similar problem when tab (sheet) switched:

As I said, this "bug" disturb me few days ago, this bug is the reason of this topic too:

TsWorksheetGrid.LoadFromSpreadsheetFile overloaded problem

because this "distort view problem" appear too, If I switch between tabs (worksheet) in fpsgrid_no_install. This is the real reason why I need to open only one worksheet.

Instructions:
svn 5214
First steps same as above. The result you can see the distorted view.
Then switch to the next tab. This tab is empty, but the visible cell height is wrong, much higher than needed. (cells heights are remain from the first opened sheet...) If u switch to the sheet3 and back to the sheet2, the cells height will be okay.

Sample pictures:

start:
http://p.coldline.hu/2016/09/27/2340161-20160927-k9OTur.png
switch to the next tab, the result cells height are bad:
http://p.coldline.hu/2016/09/27/2340173-20160927-wDjyaI.png
switch to the sheet3 and back to the sheet2, the cells height will be okay:
http://p.coldline.hu/2016/09/27/2340174-20160927-3866ab.png

[wp]

What do you mean as "distorted"? The attached screenshot shows what I get. But this is normal because the cells contain very long lines which are word-wrapped in your file.

[totya]

Hi!

I attached the sample pictures to the first message in this topic.

And you need must follow my instructions step by step, for example, I don't see the new button1 button in your picture... and you need scroll the to line 40, as I wrote.

Thanks

[wp]

Because worksheets can contain a huge number of rows, row heights are calculated only for the visible rows (plus some margin above and below). For some reason the calculation of the visible range of rows failed. Should be fixed now in r5221.

[totya]

Hi!

Thank you for your fantastic job!

But new problem appear, if I press the button1 (scroll to row 40), this function doesn't work, because I see only 29-31 row on the screen.
 

[wp]

Looks like the fix in r5221 is not good. Probably the entire handling of row heights is not good. On the one hand, I want to avoid calculating row heights because this means calculation of every cell height (with all the oddities such as rotated text direction, variable fonts, variable fonts per character, word wrap) and this can become terribly slow. On the other hand, if I calculate only the visible rows then jumping to other cells will be very inaccurate because the row heights in between are not known - this is your reported bug.

So, just to warn you: this may take some time...

[totya]

Hi!

I wrote this error is Reply #4, because I tried it, and I see this immediately. But this error doesn't happen very often. I think depend on window (grid) size, and the line count in the cell... Well, in my app and with the real job file, I didn't see this problem.

So, just to warn you: this may take some time...

Absolutely no problem, and thanks for the many quick bug fixes!

Have a nice day!

[totya]

Well, in my app and with the real job file, I didn't see this problem.

Today I use my app in work all day, and sometimes scroll function doesn't work correctly

As I said, :=row
doesn't jump always to the correct excel line. This is problem, because this is important function in my app, because user search in sql database, and the hit jump always to the appropriate excel line...

Thanks!

[wp]

As a workaround and temporary solution you can now activate the define CALC_ALL_ROWHEIGHTS at the top of fpspreadsheetgrid.pas. This results in calculation of all row heights, instead of only the visible ones. As a consequence, jumps to rows are more accurate (I don't understand at the moment why sometimes the destination row is not fully visible). There is a speed penalty in case of large worksheets, though. And for this reason, I will keep the define off in the "official" version.

[totya]

As a workaround and temporary solution you can now activate the define CALC_ALL_ROWHEIGHTS at the top of fpspreadsheetgrid.pas. This results in calculation of all row heights, instead of only the visible ones. As a consequence, jumps to rows are more accurate (I don't understand at the moment why sometimes the destination row is not fully visible). There is a speed penalty in case of large worksheets, though. And for this reason, I will keep the define off in the "official" version.

Hi!

Big thanks to you, this is the exactly, what I wanted!

[totya]

Hi!

Thanks again for you, with CALC_ALL_ROWHEIGHTS, Row:= xy command jump to the right excel line. My excel files isn't too big (about 1000 lines max), but the machines in work are old... (about P4), but the draw speed is okay, fully usable (and I uses (idle)timer to delay for jump).

But if you will develop your excellent component, that will work without CALC_ALL_ROWHEIGHTS, I can test for you.

[wp]

I am currently working at a version in which the CALC_ALL_ROWHEIGHTS can be on all the time. It uses the row heights stored in the data files; this will work fine for Exel xls and xlsx as well as OpenDocument ods because they store very reliable row height data.

In addition, the grid will have a new property AutoCalcRowHeight; if true, row height calculation will occur after loading the file - this is needed because some file formats do not store any rowheights. If false, it will be assumed that the row heights in the file are correct; if there are no row heights, the default row height will be used.

In contrast to user-defined rowheights (where the user had dragged the row headers in the grid), these automatic row heights are stored in fpspreadsheet as negative numbers. Thus those rows with a negative height will be recalculated if the user changes the font size, word-wrap, text direction etc. Rows with positive heights will not be recalculated because their height is set by the user.

I want to perform some more tests before publishing the new version.

[totya]

Hi!

Thanks for your excellent  job, I tested the new 5235 revision, but I don't see any differences between AutoCalcRowHeights ON and OFF state, the result are both good.

But I will test in my work from Monday, I put a checkbox in my application to change this property.

Only one question: can I change this property value any time? After I change this property value, need repaint, invalidate or any command?

[wp]

In most cases you won't see any difference. AutoCalcRowHeights is respected only during loading and, if true, triggers a recalculation of all row heights which are not user-defined. At the moment it is considered also during zooming, but I plan to remove the calculation here because the row heights should already be known as soon as the user has the chance to zoom.

In case of large worksheets with many columns and rows and compilicated formatting in many cells, recalulation of row heights may cause a noticable delay - this is the "only" reason why it was introduced.

If AutoCalcRowHeights is off during loading the row heights will be taken from the file, i.e. what the generating application had stored. Unfortunately, not every file format stores row heights as precisely as xls, xlsx or ods, and even fpspreadsheet does not store row heights if the file as generated without a gui because the basic fpspreadsheet package does not use the LCL, intentionally, and cannot determine text size accurately. These files will be rendered with the default row height only. Therefore, it may be a good idea to turn AutoCalcRowHeight on.

Regarding the default state of this new property, you may notice that I am constantly oscillating between both cases, on or off...

[totya]

(...) If AutoCalcRowHeights is off during loading the row heights will be taken from the file, i.e. what the generating application had stored. Unfortunately, not every file format stores row heights as precisely as xls, xlsx or ods, and even fpspreadsheet does not store row heights if the file as generated without a gui because the basic fpspreadsheet package does not use the LCL, intentionally, and cannot determine text size accurately. These files will be rendered with the default row height only. Therefore, it may be a good idea to turn AutoCalcRowHeight on (...)

Hi!

Thanks for the detailed answer!

If I understand correctly, few format (or fpspreadsheet without gui) do not store the row heights informations in the file, so the AutoCalcRowHeight ON is needed in this case.

Then my question is... under file loading, if row height informations are missing in file, isn't a good idea, if AutoCalcRowHeight turned on automatically? But you wrote "precisely" I don't know what does it mean really in this case.