Recent

Author Topic: [SOLVED] fpspreadsheetgrid  (Read 3473 times)

docno

  • New Member
  • *
  • Posts: 16
[SOLVED] fpspreadsheetgrid
« on: November 24, 2017, 02:43:30 pm »
Hello,

I can load and view an xls file with fpspreadsheetgrid like this :

Code: Pascal  [Select][+][-]
  1.  
  2. sworkbooksource1.FileName:='myfile.xls';
  3.  

is it possible to do the same with stream ? (resource stream or filestream) ??

I have tested :
Code: Pascal  [Select][+][-]
  1. sworkbooksource1.Workbook.ReadFromStream(s, sfexcelxml ,[]);
  2.  
but with error...

Any help appreciated...
« Last Edit: November 25, 2017, 11:50:02 am by docno »

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: fpspreadsheetgrid
« Reply #1 on: November 24, 2017, 06:23:07 pm »
This is a rarely-used way of loading a workbook, and therefore this bug remained unnoticed. Should be fixed in r6076. If you don't use the trunk version of fpspreadsheet you can fix it easily in your local version: Open fpspreadsheet.pas, find procedure TsWorkbook.ReadFromStream (the second overloaded version). Immediately after "finally" add the line "FReadWriteFlag := rwfNormal;"; the entire procedure should be like this:

Code: Pascal  [Select][+][-]
  1. procedure TsWorkbook.ReadFromStream(AStream: TStream; AFormatID: TsSpreadFormatID;
  2.   APassword: String = ''; AParams: TsStreamParams = []);
  3. var
  4.   AReader: TsBasicSpreadReader;
  5.   ok: Boolean;
  6. begin
  7.   AReader := CreateSpreadReader(self, AFormatID);
  8.   try
  9.     PrepareBeforeReading;
  10.     FReadWriteFlag := rwfRead;
  11.     ok := false;
  12.     inc(FLockCount);
  13.     try
  14.       AStream.Position := 0;
  15.       AReader.ReadFromStream(AStream, APassword, AParams);
  16.       ok := true;
  17.       UpdateCaches;
  18.       if (boAutoCalc in Options) then
  19.         Recalc;
  20.       FFormatID := AFormatID;
  21.     finally
  22.       FReadWriteFlag := rwfNormal;   // <---- NEW
  23.       dec(FLockCount);
  24.       if ok and Assigned(FOnOpenWorkbook) then
  25.         FOnOpenWorkbook(self);  
  26.     end;
  27.   finally
  28.     AReader.Free;
  29.   end;
  30. end;

A comment on your code referring to format sfExcelXML. Are you sure that this is the correct format? Or do you mean then xlsx file format. sfExcelXML is a very rarely xml format introduced by Microsoft during the Office2003 times, xlsx was created later for Office2007, its fpspreadsheet identifier is sfOOXML. sfExcelXML was added to fpspreadsheet for testing its extendability to other formats; currently it is only supported for writing.

docno

  • New Member
  • *
  • Posts: 16
Re: fpspreadsheetgrid
« Reply #2 on: November 25, 2017, 09:17:29 am »
Thanks,
almost done, with that and correct fpspreadsheet.pas.

Code: Pascal  [Select][+][-]
  1. sworkbooksource1.Workbook.ReadFromStream(s, sfexcel8 ,[]);
  2.  

it works but i have a empty tab Sheet1 on first tab.
If a delete it, then i have a gap in tab and grid.


wp

  • Hero Member
  • *****
  • Posts: 11830
Re: fpspreadsheetgrid
« Reply #3 on: November 25, 2017, 10:08:17 am »
Sorry I don't understand. Please post a little demo project showing the issue.

docno

  • New Member
  • *
  • Posts: 16
Re: fpspreadsheetgrid
« Reply #4 on: November 25, 2017, 10:34:43 am »
I have an xls file with tabs.

If i load with :

Code: Pascal  [Select][+][-]
  1. sworkbooksource1.FileName:=extractfilepath(application.exename)+'myfile.xls';
  2.  

all is ok, tabs and grid.

if i add the file in resource and load with :

Code: Pascal  [Select][+][-]
  1. s:=tresourcestream.create(hinstance,'myfile',RT_RCDATA);
  2.   try
  3.   sworkbooksource1.Workbook.ReadFromStream(s, sfexcel8 ,[]);
  4.   finally
  5.     s.free;
  6.   end;                                    
  7.  

All is almost good, except, an additional tab Sheet1 is added in first position in workbooktabcontrol.
The grid is ok.

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: fpspreadsheetgrid
« Reply #5 on: November 25, 2017, 11:23:45 am »
Loading a workbook via the grid's workbook does not erase the tabcontrol. Maybe this is useful to somebody, and I will not change it.

But if you read the workbook from the stream into a local TsWorkbook and load this into the Workbooksource no more tabs are added. Like this:

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   s: TMemorystream;
  4.   wb: TsWorkbook;
  5. begin
  6.   sWorkbookTabControl1.WorkbookSource := sWorksheetGrid1.WorkbookSource;
  7.  
  8.   s := TMemorystream.Create;   // Of course, you will use a ResourceStream here...
  9.   try
  10.     s.LoadFromFile('test.xls');
  11.     wb := TsWorkbook.Create;
  12.     wb.ReadFromStream(s, sfExcel8);
  13.     sWorksheetGrid1.WorkbookSource.LoadFromWorkbook(wb);
  14.     // WARNING: Do not destroy wb here. The workbooksource has taken its ownership in the prev command...
  15.   finally
  16.     s.Free;
  17.   end;
  18. end;

docno

  • New Member
  • *
  • Posts: 16
Re: [SOLVED] fpspreadsheetgrid
« Reply #6 on: November 25, 2017, 11:51:33 am »
Nice, it works as expected.

Thanks for the time you spend for me.

 

TinyPortal © 2005-2018