Lazarus

Programming => Packages and Libraries => Lazarus Extra Components => Topic started by: PatBayford on May 25, 2018, 03:04:07 am

Title: Error from FPSpreadsheet
Post by: PatBayford on May 25, 2018, 03:04:07 am
I am attempting to make use of FPSpreadsheet, however, I keep running into a problem displaying data - basic test
1 new project
2 add a tsWorkBookSource, set SourceFile name - gives error due to sfUser being the default format setting!
3 add a tsSpreadsheetInspector - displays details for the specified source file.
4 add a tsWorksheetGrid - yields a Range Check Error (see attached screenshot)
Any ideas on what the problem really is?
Title: Re: Error from FPSpreadsheet
Post by: wp on May 25, 2018, 09:50:50 am
You seem to use an older version of fpspreadsheet which still allowed viewing of spreadsheet files at designtime. But this requires linking in the readers and writers of all spreadsheet file formats supported (i.e. unit fpsAllformats being added to the uses clause of one of the package files). Since there is a chance that a user may need only one specific reader/writer and since adding a unit to the uses clause cannot be undone, I removed fpsAllformats from the visual package's uses. Unfortunately this breaks designtime viewing of spreadsheet files. But I don't know a simple way of having both options.

Since you are using an older version, this does not explain the failure which you are reporting. But because the issue is related to an out-dated feature I will not seek into it.

You should remove the filename from the corresponding property of the WorkbookSource and set everything at runtime. Don't forget to add the reader/writer unit of the needed file format to the uses clauses of your form unit.

Code: Pascal  [Select]
  1. uses
  2.   ..., xlsBIFF5, ...;  // Unit for Excel 5 reader/writer -- or whatever you need, or fpsAllFormats for all formats.
  3.  
  4. procedure TForm1.Form1Create(Sender: TObject);
  5. begin
  6.   sWorkbookSource1.FileName := 'your_file.xls';
  7. end;
Title: Re: Error from FPSpreadsheet
Post by: PatBayford on May 26, 2018, 12:42:44 am
Thanks wp!
I just checked - I have version 1.9 of FPSpreadsheet installed - should I revert to one of the older versions? And, if so, which version would be best?
Just tried your solution - even with no filename in WorkbookSource, dropping a tsWorksheetGrid yields exactly the same error!
Title: Re: Error from FPSpreadsheet
Post by: wp on May 26, 2018, 01:06:05 am
Since you have v1.9 my reply was wrong, and I will have a closer look.
Title: Re: Error from FPSpreadsheet
Post by: wp on May 27, 2018, 11:51:27 pm
No problem here...

Did you specify the correct file format in the WorkbookSource? Alternatively you can select AutoDetectFormat to true. And you must have the unit with the reader/writer of the expected format in the uses clause of your project. Alternatively use fpsAllFormats to register all file formats supported.

If you cannot fix the issue this way: please give an exact step-by-step description of what you are doing so that i can reproduce the issue.

When the error that you observe happens, can you save the project? If yes, when you load the saved project does the error still occur? In this case you should pack your project files (*.pas, *.lfm, *.lpi, *.lpr) and the data file into a common zip and upload it here under "Attachments and other options" for me to have a closer look.
Title: Re: Error from FPSpreadsheet
Post by: PatBayford on May 28, 2018, 02:57:33 am
Sadly, the error forces Lazarus to close, so the project cannot be saved.
I have set AutoDetectFormat true, and added fpsAllFormats.
Title: Re: Error from FPSpreadsheet
Post by: wp on May 28, 2018, 09:45:12 am
Let me quote myself:

If you cannot fix the issue this way: please give an exact step-by-step description of what you are doing so that i can reproduce the issue.
Title: Re: Error from FPSpreadsheet
Post by: PatBayford on June 04, 2018, 01:21:30 am
Sorry, I thought I had - please see the original post.
In fact, If I start a new project, and drop a tsWorksheetGrid on it I get the same error.
Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 04, 2018, 05:48:04 am
I am attempting to make use of FPSpreadsheet, however, I keep running into a problem displaying data - basic test
1 new project
2 add a tsWorkBookSource, set SourceFile name - gives error due to sfUser being the default format setting!
3 add a tsSpreadsheetInspector - displays details for the specified source file.
4 add a tsWorksheetGrid - yields a Range Check Error (see attached screenshot)
Any ideas on what the problem really is?

Try change the fileformat property to specific format of the file which you are going to open eg sfExcelXML.
Based on my experience and observation,  fpspread crash the IDE quite frequently if you open the file at design file. Thus I would not open file at design time (until such time the component is rectified). The basic function of the component works quite well provided you only open the file at runtime.
Title: Re: Error from FPSpreadsheet
Post by: wp on June 04, 2018, 09:37:22 am
Sorry, I thought I had - please see the original post.
Of course, I followed your first post. But I cannot reproduce the bug with it, and I tried to explain why this cannot work because the package no longer has fpsallformats in its "uses". I am also referring to your later post saying that you see the bug at runtime which i cannot reproduce either. Therefore I am asking for more detailed step-by-step instructions of what you are doing.

These are the steps which I am doing
- Create a new GUI project
- Drop a TsWorkbookSource
- Set TsWorkbookSource's FileFormat to sfOOXML, and FileName to that of any xlsx file on my dist
- I notice that FileFormat jumps back to sfUser - this is certainly wrong and will have to be fixed
- Drop a TsSpreadsheetInspector
- Set its WorkbookSource to sWorkbookSource1
- Set its Align to alRight
- Drop a TsWorksheetGrid
- Set its WorkbookSource to sWorkbookSource1
- Set its Align to alClient
- fpsAllformats (or xlsxOOXML) to "uses" of the form
- Compile and run
- The grid displays the file assigned to the workbooksource. No issues, no crash.

Please test if these steps work for you too. If they do compare where you are doing something differently.
Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 04, 2018, 07:45:03 pm
My IDE always crash if a spreadsheet file is opened at designtime. It would not crash immediately after I set the file name for worksheetsource but when I am doing some other things. For example if I click the  workbooksource1 icon in the form, the program will stop responding and then crash with message (see attached pix)

Title: Re: Error from FPSpreadsheet
Post by: wp on June 04, 2018, 08:36:20 pm
How can you open a spreadsheet file at design time? This used to work, but I removed the unit fpsAllFormats from the uses clause of the fpspreadsheetctrls to avoid linking all readers/writers into your program automatically, and since that time, the IDE does no longer have access to the reading/writing code to open spreadsheets at designtime.

This must be changed. A crash is not acceptable.

Unfortunately there are two conflicting reqirements.
What is more important?

Alternatively, I could split the visual package into a runtime and designtime package. In fact, this would be the correct way to have both options. But installation of fpspreadsheet would be a bit more complicated.
Title: Re: Error from FPSpreadsheet
Post by: wp on June 04, 2018, 11:27:23 pm
ok - I split the package laz_fpspreadsheet_visual into a runtime and designtime package. The runtime package is named like before, the designtime package has an appended _dsgn in the name:
As noted the installation of fpspreadsheet is a bit more complicated
Steps (4) and (5) can be skipped if you don't need these features.

What is the advantage of designtime packages?
Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 05, 2018, 08:34:43 am
How can you open a spreadsheet file at design time? This used to work, but I removed the unit fpsAllFormats from the uses clause of the fpspreadsheetctrls to avoid linking all readers/writers into your program automatically, and since that time, the IDE does no longer have access to the reading/writing code to open spreadsheets at designtime.

This must be changed. A crash is not acceptable.

Unfortunately there are two conflicting reqirements.
  • Being able to view spreadsheet files at designtime? This means  that all readers/writers must be linked to the visual controls automatically.
  • Link only the formats which are needed by your application? This means that you must add the reader/writer unit(s) manually, and the files can be accessed only at run time.
What is more important?

Alternatively, I could split the visual package into a runtime and designtime package. In fact, this would be the correct way to have both options. But installation of fpspreadsheet would be a bit more complicated.

Ability to view the file at design time is a nice feature but not essential for me. Dont know what is the opinion of others?
Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 06, 2018, 01:52:37 pm
1. Still have the same problem if I open file at designtime :- hourglass (mouse pointer) keep appearinf AND IDE after a while.  No problem so long as I dont set the workbooksource.filename;

2. I am unable to remove the workbooksource.filename or set it to blank (after the unit file is already saved). The only way is to open the form file as text to edit it.

3. Would it be better to have add an Active property. The file would be opened if active is changed to true and closed when it is set to false?

Title: Re: Error from FPSpreadsheet
Post by: wp on June 06, 2018, 02:57:31 pm
1. Still have the same problem if I open file at designtime :- hourglass (mouse pointer) keep appearinf AND IDE after a while. 
Sorry, no idea what this could mean... "keep appearinf AND IDE"? Please write down every step you do
Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 06, 2018, 04:58:34 pm
1. Still have the same problem if I open file at designtime :- hourglass (mouse pointer) keep appearinf AND IDE after a while. 
Sorry, no idea what this could mean... "keep appearinf AND IDE"? Please write down every step you do

Sorry for the typo. it should read keep 'appearing and IDE crash ...'
This is what I did:
1. Drop worksheetGrid and workbooksource onto a new form/new poroject, Set worksheetGrid1.workbooksource to Workbooksource1.
2. Click the WorkbookSource1.Filename in object inspector, select an excel file from harddisk;
3. After the excel files is loaded into the worksheetgrid, try to move around the form (click on components  on the form) eg. click the workbooksource1, then click the worksheetgrid ... after 1 or 2 seconds, the IDE crashed. (see the screen shot in my previous post).

If I press F9 to run the apps after step 2 above, the program will be compiled and saved.  However, the IDE will crash when I close and then open the project again (after I clicked any component on the form).  I am not able to change or delete the filename property of workbooksource1. The filename can only be deleted by editing the .lfm file (in text mode).
Title: Re: Error from FPSpreadsheet
Post by: totya on June 06, 2018, 06:01:19 pm
With 6470 revision of fps, I haven't any problem with your steps. I use "old" format excel, so I need  add to uses line: fpsallformats

Possible wp master solved this problem meanwhile, but good idea if you attach the problematic excel file.
Title: Re: Error from FPSpreadsheet
Post by: wp on June 06, 2018, 06:03:40 pm
No luck. Everything working correctly here. Only when I compile and run I get the error that the reader is not found - but this is clear because I did not add the unit to "uses". After adding xlsBiff8, the program runs correctly.

I assume that you are using fpspreadsheet trunk. After updating from svn did you perform a clean rebuild of the IDE? Normally this is not necessary. But one of the recent revision had a drastic change in the fpspreadsheet packages. So, please, go to "Tools" > "Configure Build Lazarus", in the "Clean-up" box select "Clean all" and "Switch after building to automatically". Then "Build".

Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 06, 2018, 07:05:49 pm
With 6470 revision of fps, I haven't any problem with your steps. I use "old" format excel, so I need  add to uses line: fpsallformats

Possible wp master solved this problem meanwhile, but good idea if you attach the problematic excel file.
I dont think there is any problem with the excel file because I tested it with a blank new worksheet (.xlsx) which you also can create ;-)
Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 06, 2018, 07:10:38 pm
No luck. Everything working correctly here. Only when I compile and run I get the error that the reader is not found - but this is clear because I did not add the unit to "uses". After adding xlsBiff8, the program runs correctly.

I assume that you are using fpspreadsheet trunk. After updating from svn did you perform a clean rebuild of the IDE? Normally this is not necessary. But one of the recent revision had a drastic change in the fpspreadsheet packages. So, please, go to "Tools" > "Configure Build Lazarus", in the "Clean-up" box select "Clean all" and "Switch after building to automatically". Then "Build".
The compiled exe does not has any problem (requires fpsallformat to uses clause in the source). The problem only occurs in designtime and only after I set the filename in the object inspector.
Will try to clean up as per your advice.
Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 07, 2018, 06:17:08 am
Tried clean rebuild but cannot solve the problem.
Uninstalled the existing ver 1.82 and installed ver 1.84 ... Works!!!

However, I have new problem now:- I cannot select filename when I am in in the filename property field of the object inspector. Instead I have to type in the full path name of the file.  I think this has something to do with TFilenamePropertyEditor as I noticed that the same problem applies to the Filename property of other component such as TSaveDialog & TOpenDialog.  How can I solve this problem?
Title: Re: Error from FPSpreadsheet
Post by: wp on June 07, 2018, 11:18:52 am
No luck, I checked Lazarus versions back to 1.0, and all work correctly. Maybe you had somehow messed up your 1.8.2 installation.

As for the FileName property editor I wonder why you had it with version 1.8.2, and I even wonder why I have myself it in my standard Laz trunk with fpc 3.0.4 because the TFileName property editor for all properties named "FileName" is installed only in combination with fpc trunk. It could be that some third-party package has installed their own property editor for us.

Anyway, I now added the TFileName property editor to the designtime package of visual components. Therefore, the '...' button should be back for the TsWorkbookSource even for non-fpc-truink installations.
Title: Re: Error from FPSpreadsheet
Post by: kjteng on June 07, 2018, 02:47:58 pm
I dont know how my v1.82 is messed up on my notebook AND desktop. May be due to installation/uninstallation of other third party components.

You are right about the TFileNamePropertyEditor as I have found the statement
RegisterPropertyEditor(TypeInfo(TFilename), TComponent, 'FileName',
                                     TFileNamePropertyEditor);
in one of my .pas file which I have uninstalled accidentally.

Thank again for your help and for your effort in developing the fpSpread component. Cheers.
 
Title: Re: Error from FPSpreadsheet
Post by: PatBayford on June 10, 2018, 06:35:57 am
I would also find display a design time useful, but not essential. I will try re-installing the split components - thanks