Recent

Author Topic: A few glitches in the new FPSpreadsheetDataset  (Read 1184 times)

maurobio

  • Sr. Member
  • ****
  • Posts: 304
  • Ecology is everything.
    • GitHub
A few glitches in the new FPSpreadsheetDataset
« on: September 04, 2021, 10:11:57 pm »
Dear ALL,

Over the last several days, I have been testing the new FSpreadsheetDataset component currently under active development by the author of the great FPSpreadSheet. (The new component is available here: https://github.com/wp-xyz/FPSpreadsheetDataset).

Overall, is works just fine, but I have noticed a few glitches to which I would like to find a solution:

1) In the FPSpreadsheetDataset component, most of the numeric data in my example spreadsheet are not correctly displayed;

2) Field names containing diacritics (as 'Estação' and 'Posição') are truncated in the FPSpreadsheetDataset component;

3) The font sizes of the DBGrid and WorksheetGrid do not appear to be the same, although they are set to the same values in the Object Inspector (this is not really a problem, I am just curious about it);

4) In the FPSpreadsheetDataset component, the SheetName property is compulsory; could it not be made optional (with the component getting the name of the first sheet by default)?
I just saw that this has been already fixed in the latest release of the FPSpreadsheetDataset component.

A sample application that demonstrate these problems is attached, including my example data set.

Thanks in advance for any assistance you can provide.

With best wishes,

PS I just attempted to install the latest version of the FPSpreadsheetDataset component, but this time had compiler errors as follows:

Code: Pascal  [Select][+][-]
  1. fpsdataset.pas(322,84) Error: Wrong number of parameters specified for call to "Create"
  2. Error: Found declaration: constructor Create(TCollection);
  3. Error: Found declaration: constructor Create(TFieldDefs;const AnsiString;TFieldType;LongInt;Boolean;LongInt);
« Last Edit: September 25, 2021, 02:46:23 am by maurobio »
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 9034
Re: A few glitches in the new FPSpreadsheetDatasheet
« Reply #1 on: September 04, 2021, 11:36:37 pm »
1) In the FPSpreadsheetDataset component, most of the numeric data in my example spreadsheet are not correctly displayed;
The spreadsheet dataset has a property AutoFieldDefs which is true by default. In this case, the dataset guesses the field types from the cell content in the worksheet columns. The FieldDefs can be set up quickly and easily this way. But the numeric field types of a database TField have many more facets than just the numeric cell content. Therefore, you should turn AutoFieldDefs off and set up the FieldDefs manually by yourself. This can also be done at designtime. However, the code is not yet very stable here, so please be prepared for incompatibilities.

In your particular case, all numeric fields are ftFloat, and you see here a display issue with all (?) TDataset descendants in Lazarus. To avoid this set the DisplayFormat of the float fields after opening:

Code: Pascal  [Select][+][-]
  1. var
  2.   f: TField;
  3. [...]
  4.   sWorksheetDataset1.Open;
  5.   for f in sWorksheetDataset1.Fields do
  6.     if f is TFloatField then
  7.       TFloatField(f).DisplayFormat := '0.###';
  8. end;

2) Field names containing diacritics (as 'Estação' and 'Posição') are truncated in the FPSpreadsheetDataset component;
Thanks, I had not yet tested this case.

3) The font sizes of the DBGrid and WorksheetGrid do not appear to be the same, although they are set to the same values in the Object Inspector (this is not really a problem, I am just curious about it);
The font used in the cells of the WorksheetGrid is completely determined by the fonts defined in the workbook. I think the WorksheetGrid.Font property is ignored.

PS I just attempted to install the latest version of the FPSpreadsheetDataset component, but this time had compiler errors as follows:
This is another change introduced by FPC 3.2. The new revision has a version check in the code. But note that FPC before 3.2 does not support native UTF8 text fields. There are no conversions involved, so the text is basically displayed correctly. But since a pre-v3.2 TStringField has the strict max text length as defined by the field's Size property, UTF8-text may be truncated when non-ASCII characters (> #127) are contained in the text and the field's Size is set up too narrow. Automatic FieldDef detection (AutoFieldDefs = true) creates text fields as ftWideString to avoid this issue to a large degree.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 304
  • Ecology is everything.
    • GitHub
Re: A few glitches in the new FPSpreadsheetDatasheet
« Reply #2 on: September 04, 2021, 11:59:37 pm »
@wp,

Hi! Thanks a lot for your reply and the recent fixes to the FPSpreadsheetDatasheet component.

In my application, I cannot turn the AutoFieldDefs property off and set up the FieldDefs manually because they are not known in advance (the 'fields' are taken to be the columns of a spreadsheet). However, the code fragment you suggested worked fine and the numbers are now correctly displayed (see attached screenshot).

All compilation errors are gone.

If seems that the only problem is still the truncation of the field (columns) names which have diacritics.

The fixed sample application is attached.

Thanks A LOT!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

maurobio

  • Sr. Member
  • ****
  • Posts: 304
  • Ecology is everything.
    • GitHub
Re: A few glitches in the new FPSpreadsheetDatasheet
« Reply #3 on: September 05, 2021, 12:04:01 am »
@wp,

Just after sending my previous reply, I saw that you have made available a new version of the component! It fixes the problem with the display of diacritics!

Everything seems to be running smoothly now. :D

Again, thank you VERY MUCH!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 9034
Re: A few glitches in the new FPSpreadsheetDatasheet
« Reply #4 on: September 05, 2021, 12:20:38 am »
In my application, I cannot turn the AutoFieldDefs property off and set up the FieldDefs manually because they are not known in advance.
There may be cases where this still is necessary because AutoFieldDefs makes wrong guesses. In such a case, open the dataset with AutoFieldDefs = true, iterate through the FieldDefs found and store a copy of them in a separate list. Then close the dataset and turn AutoFieldDefs off. Clear the FieldDefs of the dataset and copy the saved versions back into place. Finally do the modifications needed and open the dataset.

Untested, but it should work in principle. I'll prepare an example in the next days.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 304
  • Ecology is everything.
    • GitHub
Re: A few glitches in the new FPSpreadsheetDatasheet
« Reply #5 on: September 05, 2021, 12:26:39 am »
@wp,

Nice suggestion, thanks! I look forward to seeing your example of it 'in action'.

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

 

TinyPortal © 2005-2018