Recent

Author Topic: Error from FPSpreadsheet  (Read 7124 times)

PatBayford

  • Full Member
  • ***
  • Posts: 125
Error from FPSpreadsheet
« 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?
Lazarus 1.8.0 FPC 3.0.2 SVN 56594 Windows 10 64bit (i386-win32-win32/win64)

wp

  • Hero Member
  • *****
  • Posts: 6447
Re: Error from FPSpreadsheet
« Reply #1 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;
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

PatBayford

  • Full Member
  • ***
  • Posts: 125
Re: Error from FPSpreadsheet
« Reply #2 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!
« Last Edit: May 26, 2018, 12:58:31 am by PatBayford »
Lazarus 1.8.0 FPC 3.0.2 SVN 56594 Windows 10 64bit (i386-win32-win32/win64)

wp

  • Hero Member
  • *****
  • Posts: 6447
Re: Error from FPSpreadsheet
« Reply #3 on: May 26, 2018, 01:06:05 am »
Since you have v1.9 my reply was wrong, and I will have a closer look.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 6447
Re: Error from FPSpreadsheet
« Reply #4 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

PatBayford

  • Full Member
  • ***
  • Posts: 125
Re: Error from FPSpreadsheet
« Reply #5 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.
Lazarus 1.8.0 FPC 3.0.2 SVN 56594 Windows 10 64bit (i386-win32-win32/win64)

wp

  • Hero Member
  • *****
  • Posts: 6447
Re: Error from FPSpreadsheet
« Reply #6 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

PatBayford

  • Full Member
  • ***
  • Posts: 125
Re: Error from FPSpreadsheet
« Reply #7 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.
Lazarus 1.8.0 FPC 3.0.2 SVN 56594 Windows 10 64bit (i386-win32-win32/win64)

kjteng

  • Full Member
  • ***
  • Posts: 181
Re: Error from FPSpreadsheet
« Reply #8 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.

wp

  • Hero Member
  • *****
  • Posts: 6447
Re: Error from FPSpreadsheet
« Reply #9 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 181
Re: Error from FPSpreadsheet
« Reply #10 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)


wp

  • Hero Member
  • *****
  • Posts: 6447
Re: Error from FPSpreadsheet
« Reply #11 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.
  • 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 6447
Re: Error from FPSpreadsheet
« Reply #12 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:
  • laz_fpspreadsheet_visual.lpk --> runtime package
  • laz_fpspreadsheet_visual_dsgn.lpk --> designtime package
As noted the installation of fpspreadsheet is a bit more complicated
  • Load package laz_fpspreadsheet.lpk > "Compile"
  • Load package laz_fpspreadsheet_visual.lpk >  "Compile"
  • Load package laz_fpspreadsheet_visual_dsgn.lpk  > "Compile". Then "Use" > "Install"
  • If you need access to protection of xls files: Load package laz_fpspreadsheet_crypto.lpk > "Compile"
  • If you want to use the export component: Load package laz_fpspreadsheetexport_visual.lpk > "Compile". Then "Use" > "Install"
Steps (4) and (5) can be skipped if you don't need these features.

What is the advantage of designtime packages?
  • It prevents linking designtime code into the binary. The component palette images, for example, are no longer contained in the exe --> smaller
  • It allows to view spreadsheet files in the worksheet grid at designtime since the reader/writer units are registered with the designtime package:
    • Add a TsWorkbookSource to the form
    • Enter name of the spreadsheet file to be viewed in property "Filename"
    • Add a TsWorksheetGrid to the form
    • Set its property "WorkbookSource" to the TsWorkbookSource added. --> the file is displayed in the grid
    • BUT: The reader is known only at designtime. Before you can successfully RUN the program you still must add the reader unit (or fpsAllFormats for all file formats) to the uses clause of the program. The names of the reader/writer units are listed at the end of the wiki chapter http://wiki.lazarus.freepascal.org/FPSpreadsheet#Workbook
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 181
Re: Error from FPSpreadsheet
« Reply #13 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?

kjteng

  • Full Member
  • ***
  • Posts: 181
Re: Error from FPSpreadsheet
« Reply #14 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?