Recent

Author Topic: fpSpread - Workbook.ReadFromFile method  (Read 6197 times)

kjteng

  • Sr. Member
  • ****
  • Posts: 258
fpSpread - Workbook.ReadFromFile method
« on: June 19, 2018, 11:56:10 am »
Can I use Workbook.ReadFromFile(filename)  to read worksheet file or should I use wbSource1.FileName := filename.

I have problem with readfromfile method - the worksheetgrid would display some extra sheets (see attached screen shots) when I use this method to open file.

Please advise. Thanks.





wp

  • Hero Member
  • *****
  • Posts: 11830
Re: fpSpread - Workbook.ReadFromFile method
« Reply #1 on: June 19, 2018, 12:14:49 pm »
I don't know - they should be the same. Can you post a file for which you see this difference?

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpSpread - Workbook.ReadFromFile method
« Reply #2 on: June 19, 2018, 12:41:33 pm »
I don't know - they should be the same. Can you post a file for which you see this difference?
Attached

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: fpSpread - Workbook.ReadFromFile method
« Reply #3 on: June 19, 2018, 01:10:11 pm »
I see. There is a subtle difference: "workbook.ReadFromFile", if called repetively, always adds the new sheets to the already existing ones. Setting the filename, on the other hand, reads the file to an empty worksheet.

So, no problem to use "workbook.ReadfromFile", just make sure that "workbook" is new or has been emptied before, e.g.

Case 1: NEW WORKBOOK

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   workbook: TsWorkbook;
  4. begin
  5.   workbook := TsWorkbook.Create;
  6.   workbook.ReadFromFile (....);
  7.   WorkbookSource.LoadFromWorkbook(workbook);  // use this workbook in workbooksource
  8.   // or: WorksheetGrid.LoadFromWorkbook(workbook);
  9. end;
Don't .Free the workbook in this code because it is used by the WorksheetGrid. Because of this unusual requirement - normally you must destroy what you create - I recommend the WorksheetGrid or WorkbookSource methods.

Case 2: EMPTIED WORKBOOK
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.   WorkbookSource.Workbook.RemoveAllWorksheets;
  4.   WorkbookSource.Workbook.ReadFromFile(...);
  5. end;


kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpSpread - Workbook.ReadFromFile method
« Reply #4 on: June 19, 2018, 02:11:18 pm »
Method 2 is what I have adopted (in my much ealier demo uploaded here). However it has a minor problem: If I tried to open an invalid file (eg. non existence file) , an exception would be raised and the wsgrid would be frozen and display some strange rectangular block.  However, it would be back to normal if I open a valid excel file again.

Method 1 seems to be a good choice to me. However, I would like to ask whether there would be any memory leak if I keep open file with this method (becuase a workbook object is created each time button1 is clicked and the workbook is not freed.  Should I call WorkbookSource.workbook.Free before a new one is created? 


Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   workbook: TsWorkbook;
  4. begin
  5.   workbook := TsWorkbook.Create;
  6.   workbook.ReadFromFile (....);
  7.   WorkbookSource.LoadFromWorkbook(workbook);  // use this workbook in workbooksource
  8.   // or: WorksheetGrid.LoadFromWorkbook(workbook);
  9. end;

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.   WorkbookSource.Workbook.RemoveAllWorksheets;
  4.   WorkbookSource.Workbook.ReadFromFile(...);
  5. end;
« Last Edit: June 19, 2018, 03:45:09 pm by kjteng »

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpSpread - Workbook.ReadFromFile method
« Reply #5 on: June 19, 2018, 05:01:25 pm »
Have a closer look of the outcome from Readfromfile method :-

Run my demo program (simpletest.lpi, uploaded few hours ago)

1.Click read button and select the file book3.xlsx (attached below).
Outcome looks ok;

2. Read the same file again.
Outcome:
3 additional BLANK sheets ( 'one1', 'two1', 'three1) are inserted into the wsGrid;

3. Goto ms office (don't quit simpletest program). (i) add one sheet ('four') into the book3.xlsx; (ii) key in some numbers in each and every sheet; and  (iii) then save the file.

4. Go back to simpletest program and read book3.xlsx again. 
Outcome: 
4 additional BLANK sheets ('one2', 'two2', 'three2', 'four') are inserted into the wsGrid. AND  the Content of sheet 'one',' two', 'three' does not change !

5. click read Book4.xlsx (attached below, which contains 4 sheets 'one',' two', 'three', 'four').
Outcome: 
4 additional BLANK sheets are inserted ('one3', 'two3', 'three3', 'four1').
Again, the content of sheet 'one',' two', 'three' does not change !

My question: Are these feature (i.e. insert blank sheets and do not load the data from the sheet with same name)  useful ?

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: fpSpread - Workbook.ReadFromFile method
« Reply #6 on: June 19, 2018, 06:18:34 pm »
The problem is that when you read the same file a second time the workbook must rename the sheets at the second reading in order to have unique sheet names. But the sheets are referenced within the file by their original name, and the reader puts the data of the second reading into the sheets read initially because they have the correct name, i.e. the new sheets will remain empty.

There are two ways to overcome this issue: avoid renaming a sheet when its name already exists. In the end, this inhibits reading the same file several times.

Or rewrite the reading process such that the newly read worksheets are not merged to the workbook directly , but are kept inside a temporary workbook. When reading is finished the sheets are renamed (--> formulas must be updated etc) and moved into the original book (*). But to be honest, I am not very motivated to do this because I consider this a very rare usage of fpspreadsheet...

---------------------
(*) NOTE ADDED: But this approach has another problem: the workbook contains the format list. If the file is read into a temporary workbook then the format indexes used by the cells would have to change when the main and temporary workbooks are merged.
« Last Edit: June 20, 2018, 06:59:39 am by wp »

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: fpSpread - Workbook.ReadFromFile method
« Reply #7 on: June 19, 2018, 07:24:25 pm »
Method 2 is what I have adopted (in my much ealier demo uploaded here). However it has a minor problem: If I tried to open an invalid file (eg. non existence file) , an exception would be raised and the wsgrid would be frozen and display some strange rectangular block.
Cannot confirm.

I would like to ask whether there would be any memory leak if I keep open file with this method (becuase a workbook object is created each time button1 is clicked and the workbook is not freed.
No you misunderstood. The workbook that you created is moved into the WorkbookSource, and the WorkbookSource will destroy it. But please turn on HeapTrc and check yourself (maybe your test conditions are different from mine).

Why don't you use "WorkbookSource.LoadFromSpreadsheetFile"? It does what you call method 1.

[EDIT]
Changed the typo in the line above: "Workbook.LoadFromSpreadsheetFile" --> "WorkbookSource.LoadfromSpreadsheetFile"
« Last Edit: June 20, 2018, 07:30:38 am by wp »

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpSpread - Workbook.ReadFromFile method
« Reply #8 on: June 20, 2018, 12:06:37 am »
The problem is that when you read the same file a second time the workbook must rename the sheets at the second reading in order to have unique sheet names. But the sheets are referenced within the file by their original name, and the reader puts the data of the second reading into the sheets read initially because they have the correct name, i.e. the new sheets will remain empty.

There are two ways to overcome this issue: avoid renaming a sheet when its name already exists. In the end, this inhibits reading the same file several times.

Or rewrite the reading process such that the newly read worksheets are not merged to the workbook directly , but are kept inside a temporary workbook. When reading is finished the sheets are renamed (--> formulas must be updated etc) and moved into the original book. But to be honest, I am not very motivated to do this because I consider this a very rare usage of fpspreadsheet...

Is it possible to make readfromfile method work like wbSource1.FileName := filename ...so that there wont be extra worksheet inserted (since the new sheets inserted are of not much purposes) ?

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpSpread - Workbook.ReadFromFile method
« Reply #9 on: June 20, 2018, 12:16:23 am »
Method 2 is what I have adopted (in my much ealier demo uploaded here). However it has a minor problem: If I tried to open an invalid file (eg. non existence file) , an exception would be raised and the wsgrid would be frozen and display some strange rectangular block.
Cannot confirm.
I think I know the reason for that now. It is because there is no worksheet in the workbook now (failed to read  file, thus the wsGrid is not responsive and certain area is gray out).  Just add the following line after reading the file will solve the problem:
Code: Pascal  [Select][+][-]
  1.         if Workbook.GetWorksheetCount = 0 then
  2.           Workbook.AddWorksheet('Sheet1');  

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: fpSpread - Workbook.ReadFromFile method
« Reply #10 on: June 20, 2018, 01:20:25 am »
Is it possible to make readfromfile method work like wbSource1.FileName := filename ...so that there wont be extra worksheet inserted (since the new sheets inserted are of not much purposes) ?
Do your really want to load the same file several times into the same worksheet? I don't see any use of this.

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpSpread - Workbook.ReadFromFile method
« Reply #11 on: June 20, 2018, 03:47:06 am »
Do your really want to load the same file several times into the same worksheet? I don't see any use of this.

Not really. However, the main issue here is that most of our worksheets have the default sheetname (sheet1, sheet2, sheet3... ) So readfromfile method would not read the file properly without using the work around code per your suggestion. Most of the programmer would think readfromfile and setting Workbook.Filename are the same  (which is not true here).

Is it possible to change the code so that readfromfile method will remove all the worksheets first before loading the new file?

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpSpread - Workbook.ReadFromFile method
« Reply #12 on: June 20, 2018, 03:58:50 am »

No you misunderstood. The workbook that you created is moved into the WorkbookSource, and the WorkbookSource will destroy it. But please turn on HeapTrc and check yourself (maybe your test conditions are different from mine).

Why don't you use "Workbook.LoadFromSpreadsheetFile"? It does what you call method 1.

Tested on my pc. You are right: No memory leak.
I have also browsed through the source and found that before a new workbook is loaded, the existing workbook would be  freed (see extract below). So it is safe to use  method 1 (Case 1: NEW WORKBOOK ... in your previous post).

Code: Pascal  [Select][+][-]
  1. procedure TsWorkbookSource.InternalCreateNewWorkbook(AWorkbook: TsWorkbook = nil);
  2. begin
  3.   FreeAndNil(FWorkbook);
  4.   FWorksheet := nil;
  5.   ...
  6. end;
  7.        
       

NB. InternalCreateNewWorkbook is called in InternalLoadFromWorkbook (which is in turn called in LoadFromWorkbook)                   
« Last Edit: June 20, 2018, 04:14:46 am by kjteng »

 

TinyPortal © 2005-2018