Recent

Author Topic: TsWorkbook.ReadFromFile  (Read 5798 times)

straetch

  • Jr. Member
  • **
  • Posts: 75
TsWorkbook.ReadFromFile
« on: June 17, 2017, 12:47:02 pm »
If I do subsequent ReadFromFile on the same workbook, then the worksheets accumulate.
E.g. if a workbook contains 3 worksheets, then subsequent executions of ReadFromFile of spreadsheets with each 3 worksheets result in 3, 6, 9 etc. worksheets in the workbook.
I want that a second ReadFromFile overwrites the earlier 3 worksheets with the 3 from the last read.
The method contains an optional parameter TsStreamParams. I cannot find the explanation. Is there a parameter value that overwites the worksheets?
I tried to precede the ReadFromFile with the method RemoveAllWorksheets. That makes no difference. The help file says "This procedure conflicts with the WorkbookLink mechanism which requires at least 1 worksheet per workbook!", so I guess this is disabled.

wp

  • Hero Member
  • *****
  • Posts: 11831
Re: TsWorkbook.ReadFromFile
« Reply #1 on: June 17, 2017, 01:25:15 pm »
This should be the normal behavior. Please show your code or, better, create and upload a little demo which shows this behavior (only pas, lfm, lpi and lpr files, packed into a shared zip).

The TsStreamParams were introduced for clipboard interaction (and, maybe, will be removed when I work with fpspreadsheet again because they are no longer needed).

Do not call Workbook.RemoveAllWorksheets, it has lots of side-effects on visual controls. Only the recent trunk version works to some degree with it.

straetch

  • Jr. Member
  • **
  • Posts: 75
Re: TsWorkbook.ReadFromFile
« Reply #2 on: June 17, 2017, 04:18:45 pm »
Demo project in attachment.
Contains three cases:
1. The demo case where worksheets accumulate. Open any spreadsheet.
2. Workaround 1: The number of worksheets is counted before the ReadFromFile and removed afterwards.
3. Workaround 2: Each time the workbook is made Free and re-Created again. I hate it.
An optional parameter in ReadFromFile would be better (similar to the overwrite option when a workbook is written to file).

wp

  • Hero Member
  • *****
  • Posts: 11831
Re: TsWorkbook.ReadFromFile
« Reply #3 on: June 17, 2017, 05:25:48 pm »
Thank you, I understand now.

The problem is that you want to use a global workbook instance. This has lots of disadvantages, in particular with visual controls. When you call WorkbookSource.LoadFromWorkbook(WB) then the workbook source takes over ownership of the workbook (which probably was a bad idea - but that's the way things are, at least now ...). This means that the source at first destroys its current workbook and then assigns the new workbook (WB) to its internal workbook. But if the both workbooks are the same the workbook needed by the second step is destroyed in the first step ... Crash!

But you do not seem to use the visual components, only the base classes. Then you can call RemoveAllWorksheets without any problems - that's why it is a public method:

Code: Pascal  [Select][+][-]
  1. procedure TForm1.WO3ButtonClick(Sender: TObject);
  2. var
  3.   i: Integer;
  4. begin
  5.   for i := 0 to 3 do begin
  6.     WB.RemoveAllWorksheets;
  7.     WB.ReadFromFile(FN);
  8.     showmessage(inttostr(WB.GetWorksheetCount));
  9.   end;
  10. end;

Unfortunately the method ReleaseAllWorksheets in the release versions did not clear the worksheet list (although the individual worksheets were destroyed). That's why your this method did not work in your tests. This is fixed in the new release version 1.8.4 which I just uploaded to sourceforge (https://sourceforge.net/projects/lazarus-ccr/files/FPSpreadsheet/fpspreadsheet-1.8.4.zip/download - it is available through OnlinePackagaManager as well), or in the trunk version.
« Last Edit: June 18, 2017, 01:05:06 am by wp »

straetch

  • Jr. Member
  • **
  • Posts: 75
Re: TsWorkbook.ReadFromFile
« Reply #4 on: June 18, 2017, 03:18:50 pm »
Works great.
Thank you.

verasan

  • New Member
  • *
  • Posts: 11
Re: TsWorkbook.ReadFromFile
« Reply #5 on: June 14, 2018, 10:57:17 pm »
Hello!
I am traying to use workbook.readfromfile but it is too slow.....is any one can help me to speed it up?

wp

  • Hero Member
  • *****
  • Posts: 11831
Re: TsWorkbook.ReadFromFile
« Reply #6 on: June 14, 2018, 11:31:29 pm »
What are you doing? Show your code. How big is the file? FpSpreadsheet is not well-suited for super-large files.

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: TsWorkbook.ReadFromFile
« Reply #7 on: June 19, 2018, 05:45:30 pm »
Hello!
I am traying to use workbook.readfromfile but it is too slow.....is any one can help me to speed it up?

Excel file attached. On my pc, workbook.readfromfile takes about 6.5 sec to read 1000 row x 255 column of data (with simple formula).  This speed is ok for my purpose but I dont know about yours.

For reference purpose,  MS office take about 2 sec to open this file. However, it failed to load at times (due to insufficient resources/memory) particularly when I have 4 other apps opened  (while fpspread still can open the file at 6.5 sec)

wp

  • Hero Member
  • *****
  • Posts: 11831
Re: TsWorkbook.ReadFromFile
« Reply #8 on: June 19, 2018, 07:00:26 pm »
The fpspreadsheet speed test takes about 9-10 sec for 10.000 x 100 xlsx cells on my pc which is about 4x of your case. Considering that you also have formulas in your workbook I think that your result and mine are roughly the same.

I don't know how Excel works internally but I guess that they don't read the workbook completely, they only read what is absolutely needed. Also, their memory usage is much better than that of fpspreadsheet.

But to be honest I am not interested in a speed/memory race against Excel - a one-person activity can never compete with the million-$ effort of the large companies. Maybe it is more interesting to compare with LibreOffice Calc - it takes roughly 10-12 sec to open the ods file of the speedtest - fpspreadsheet does it in 7-8 sec, a bit faster maybe but I would not count on every digit of these numbers.

 

TinyPortal © 2005-2018