[SOLVED] Format 32/64bit problem and anomalous behavior with events

[Phoenix]

Hello,
I found abnormal behavior regarding number formatting. If I compile for 64bit I get the desired result while if I compile for 32bit the formatting is changed by adding unwanted zeros (but only to certain values: at the moment the one in the example).

I attach the test source and related images (watch zip attachments)

This is a secondary thing..
If I use the "save" button and try to open the file obtained with LibreOffice (6.1.4.2 (x64)) the field that should be saved as text (columns C and D) is formatted as a number (however the cell is displayed correctly).
I found this because of a cell with WordWrap enabled that instead of showing the largest cell displayed the base height with only the last part of the text inside (last line).
Unfortunately I could not reproduce this problem. But I remember having modified the cell with LibreOffice in text and then the height corresponded to all the lines of text. Maybe there is something if cell type number + WordWrap?

Configuration:
Windows 10 64bit
Lazarus 2.0.4 FPC 3.0.4 x86_64-win64-win32/win64
fpspreadsheet updated (last modified 2 days ago)

Thanks for any help

[wp]

I found abnormal behavior regarding number formatting. If I compile for 64bit I get the desired result while if I compile for 32bit the formatting is changed by adding unwanted zeros (but only to certain values: at the moment the one in the example).

The problem with floating point values is that values, e.g. -96.9, even if they  look "nice" to us, cannot be represented exactly like that in the binary system with the number of bytes provided (8 for double).

For the general number format, fpspreadsheet rounds to 15 digits before stripping the trailing zeros. Don't ask me why this is sufficient for 64bit code, but not for 32bit. Looking at LibreOffice Calc I found that the value of pi is displayed in a very wide cell with 14 digits, Excel even allows only for 9 digits. Therefore, I reduced the 15 digits to 14, and this seems to fix the issue. Please test the new revision.

If I use the "save" button and try to open the file obtained with LibreOffice (6.1.4.2 (x64)) the field that should be saved as text (columns C and D) is formatted as a number (however the cell is displayed correctly).

I don't understand: When a cell contains text it cannot be formatted as number (of course, you can apply the format, but it will be ignored as long as the cell contains text; it will be used only when the cell is changed to contain a number).

Maybe there is something if cell type number + WordWrap?

If a cell contains a number and is formatted with wordwrap, the wordwrap is ignored.
When you have text with wordwrap and want to display the entire text in a higher cell you should set the RowHeightType of the row to rhtAuto:

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

1. function TForm1.AddNewRow: Integer;
2. begin
3.  with Grid.Worksheet do
4.  begin
5.   with AddRow(GetLastRowIndex+1)^ do begin
6.    RowHeightType := rhtAuto;
7.    Result := Row;
8.   end;
9.   FormatRow(Grid.RowCount-1);
10.  end;
11. end;

P.S.
I see in your code that you do not use the fpspreadsheet packages but add the path to the source to the unit path of your project. I know that this is common practice in Delphi, but in Lazarus you should add the packages to the requirements of the projects - then the IDE takes care of finding the files needed. This also works when I, the maintainer of fpspreadsheet, once decide to move source files to other directories. And sometimes very complex compilation issues can occur when a project is allowed to compile the sources of required packages.

[Phoenix]

I don't understand: When a cell contains text it cannot be formatted as number (of course, you can apply the format, but it will be ignored as long as the cell contains text; it will be used only when the cell is changed to contain a number).

In fact I was also surprised when I saw this (I installed the latest LibreOffice 6.3.2 version to try again):  (look at the attached image)
In theory should it be of the "text" type even if I load it with LibreOffice?

If a cell contains a number and is formatted with wordwrap, the wordwrap is ignored.
When you have text with wordwrap and want to display the entire text in a higher cell you should set the RowHeightType of the row to rhtAuto

If I can reproduce the problem I will try this too.

The problem with floating point values is that values, e.g. -96.9, even if they  look "nice" to us, cannot be represented exactly like that in the binary system with the number of bytes provided (8 for double).

For the general number format, fpspreadsheet rounds to 15 digits before stripping the trailing zeros. Don't ask me why this is sufficient for 64bit code, but not for 32bit. Looking at LibreOffice Calc I found that the value of pi is displayed in a very wide cell with 14 digits, Excel even allows only for 9 digits. Therefore, I reduced the 15 digits to 14, and this seems to fix the issue. Please test the new revision.

Yes this solves the problem thanks for the solution and the explanation !! 

[wp]

I don't understand: When a cell contains text it cannot be formatted as number (of course, you can apply the format, but it will be ignored as long as the cell contains text; it will be used only when the cell is changed to contain a number).

In fact I was also surprised when I saw this (I installed the latest LibreOffice 6.3.2 version to try again):  (look at the attached image)
In theory should it be of the "text" type even if I load it with LibreOffice?

Pure text such as "This is a test" does not require special formatting. It is always displayed like text not matter which number or date/time format is assigned to the cell. A number, however, can also be formatted as text. Suppose you have a table with various sections which you want to label as "1.1", "1.2" etc. in the first column and some captions in the second column. When you enter "1.1" Excel and LibreOffice may interpret and display this as a date (Jan 1). To avoid this annoying automatism you can format it as text by entering "'1.1" (note the leading hyphen) This works in fpspreadsheet, too, BTW. There are certainly more examples like this.

A cell in fpspreadsheet - and I am sure that Excel and Calc do it the same way - has several cell attributes irrespective of the cell content. So, the number format is there even for an empty (of course, existing) cell. By default the number format is nfGeneral which writes as many decimal places as needed. When the cell does not contain a numerical value the number format is ignored, in the same way as the word-wrap flag is ignored by an empty cell.

[Phoenix]

Ok, thanks for the further explanation !! 

[Phoenix]

I was able to find what triggered the problem that I could not reproduce. I must admit that it was difficult to identify. The first time I saw the problem, it allowed me to update my gui so I didn't notice.
Given the effect, I think it's a bug. Or an improper use of the event ?? 

the cause is the assignment of "Worksheet.OnChangeCell"

Effect found:
- if "load" (I left the document in the zip that shows the problem visible on the last line) modifying the text the cell does not update the height
- if I press "create" nothing is displayed (if you try to save the document it will be ruined)

always reproducible (even after restarting Windows to "clean" the ram)

Perhaps the event creates some kind of problem (synchronization) with the GUI? 

Attached are the images of the "effects" and the source

[Phoenix]

There Is anyone experiencing the same problem (with test 2)? 

[wp]

Sorry I had forgotten about is message...

You should not attach your own message handlers to worksheet or workbook events. They are needed for communication of the basic components with the visual controls. This is the reason why your "Create" button seems to be not working - because the grid is not notificed of the worksheet being changed.

Not 100% sure about the "last line issue". Fact is that the row format of the last row is different.

Code: XML  [Select][+][-]

1.  <table:table-row table:style-name="ro1">
2.  <table:table-row table:style-name="ro2">
3.  <table:table-row table:style-name="ro3">
4.  <table:table-row table:style-name="ro2">
5.  <table:table-row table:style-name="ro3">
6.  <table:table-row table:style-name="ro1">

Showing only the <table:table-row> tags here for simplicity you can see that the last row is formatted with style "ro1", like the first row. And these are the styles:

Code: XML  [Select][+][-]

1.  <style:style style:name="ro1" style:family="table-row">
2.    <style:table-row-properties style:row-height="5.292mm" style:use-optimal-row-height="true" fo:break-before="auto" />
3.  </style:style>
4.  <style:style style:name="ro2" style:family="table-row">
5.    <style:table-row-properties style:row-height="9.313mm" style:use-optimal-row-height="true" fo:break-before="auto" />
6.  </style:style>
7.  <style:style style:name="ro3" style:family="table-row">
8.    <style:table-row-properties style:row-height="17.357mm" style:use-optimal-row-height="true" fo:break-before="auto" />
9.  </style:style>

"ro1" has a row height of 5.3 mm, while the others have 9.3 and 17.4 mm. So, the issue must be in your code somewhere. My problem is that these rows are marked as "use-optimal-row-height" which IIRC means that the row height should be calculated automatically, actually what Libreoffice Calc does. Therefore, I guess there is a small bug in the ods reader.

[Phoenix]

Sorry I had forgotten about is message...

No need to apologize, this is not a problem. Thank you for your time to help on the forum

Not 100% sure about the "last line issue".

So, the issue must be in your code somewhere.

I think that in the end it is related to the same problem. Because even the document in the zip was created with the test application (or better with both).
Just perform these actions to create it:

(using test 1)
create
add
save
(using test 2)
load
save

Therefore, I guess there is a small bug in the ods reader.

in fact when I saw that LibreOffice displayed the height that I assumed was "correct" I was convinced that there was a problem in the library. 

You should not attach your own message handlers to worksheet or workbook events. They are needed for communication of the basic components with the visual controls. This is the reason why your "Create" button seems to be not working - because the grid is not notificed of the worksheet being changed.

Ok 

So to summarize, for events that concern the GUI, the events of the graphical control itself must be used.
Example, instead of Grid.Worksheet.OnChangeCell should be used Grid.OnEditingDone. Or Grid.OnValidateEntry if you need more information.
Are there any other "special" conditions for using events to consider?

[wp]

My problem is that these rows are marked as "use-optimal-row-height" which IIRC means that the row height should be calculated automatically, actually what Libreoffice Calc does. Therefore, I guess there is a small bug in the ods reader.

No. I remember the issue now: Since fpspreadsheet per se does not know how many cells are contained in a worksheet and how complex the cell content is calculation of row heights can be a time-consuming task leading to noticable delays. Therefore I took the conservative path and used the row height given in the file even if the row height is marked to be auto-calculated. If you don't have "too many" rows you could make the grid call UpdateRowHeights(-1, true) to trigger row height re-calculation. Don't ask me what "too many" exactly means, probably more some several thousands; and it also depends on the way how complex the cell formats are. The first parameter (-1) means: begin with first grid row below the headers, the second parameter (true) means: Enforce row height calculation.

The question left is: How did that "ro1" style make it into the file for the last row? I cannot reproduce this with your "test for problems2" demo after removing the assigned OnChangeCell handler.

So to summarize, for events that concern the GUI, the events of the graphical control itself must be used.
Example, instead of Grid.Worksheet.OnChangeCell should be used Grid.OnEditingDone. Or Grid.OnValidateEntry if you need more information.

Correct. I added a note to the wiki documentation (https://wiki.lazarus.freepascal.org/FPSpreadsheet#Events).

Are there any other "special" conditions for using events to consider?

I don't know what people are doing -- there's a good chance that there is quite a lot of "don't do this"...

[Phoenix]

I don't know what people are doing -- there's a good chance that there is quite a lot of "don't do this"...

Yes you are right 

I added a note to the wiki documentation

thanks, perfect it will also be useful for other people

The question left is: How did that "ro1" style make it into the file for the last row? I cannot reproduce this with your "test for problems2" demo after removing the assigned OnChangeCell handler.

I don't know if I understood..
Removing OnChangeCell from test 2 I also no longer encounter the problem (I only explained where the attached file came from). Note: in the test there are two assignments to this event.

thanks for the info on UpdateRowHeights 

PS. I go back to "solve" the post since both problems were solved. Many thanks!!