FPSpreadsheet - modifing cells causes advance to next row down

[wp]

The current trunk version of fpspreadsheet now has primitive reading support for html files. So far, only the text data ofare retrieved, formats and styles are ignored. A global record HTMLparams (defined in fpsHTML) contains several controlling variables. HTMLParams.TableIndex, for example, is the (zero-based) index of the table in the file which is imported, or use -1 to catch all tables into individual worksheets. Nested tables (tables within tables) are not properly read.

Usage is the same as with the other file format, just specify the new format sfHTML. You can read from file, stream or stringlist.

[dodgebros]

Awesome wp!  I'll try it out later today!

TD

[wp]

There is a new demo, htmlread_http, which reads a html table directly from the internet, similar to what you are doing.

[dodgebros]

Where would I find that demo? 
TD

[wp]

I hope you are using the trunk version of fpspreadsheet (svn), otherwise you won't see the recent changes. The demo is in your fpspreadsheet folder, subfolder "examples/read_write/htmldemo", project "htmlread_http".

[dodgebros]

Thanks wp. 

I have a new issue !  I run my app to modify the spreadsheet then I close my app.  I do not have MS Excel 2010, which is what my client has, so I am using the latest version of the MS Excel Viewer software from Microsoft plus Libre Office Calc to examine the spreadsheet afterwards.

If I try to open the newly modified spreadsheet with MS Excel Viewer first it flashes up the file for a second then the spreadsheet goes away (see attached screen capture).  I had a friend try using their full Excel 2010 to open the file and it worked fine there.

If I first use Calc to open the file and make a simple change, like a column width change, and then save it in back to xlsx format, which it was already, then MS Excel Viewer will open the file ok.  BTW, Libre Calc always works opening it.

This is probably some issue with the MS Excel Viewer app but I thought I might mention it anyway.

TD

[wp]

(see attached screen capture)

There is no attachment...

You should also provide the file which is causing the problem (if you don't want to make it public send me a PM). I assume it is written by fpspreadsheet. Which file format: xlsx, xls (biff8) or earlier? Or does it happen with any file written by fps such as the simple test files in the "examples/read_write" folders? Which Excel viewer are you using? There could be several versions... And which Windows? I'll have to get me the Excel viewer and install it in a virtual machine.

[wp]

Ok. In that case, wouldn't it be better for TsWorksheetGrid to create a dummy WorkbookSource? As it is now, it creates a dummy Worksheet (I think) and leaves Workbooksource blank/nil. But because that hasn't the correct notification-methods it should go through a dummy WorkbookSource object (which in turn creates a dummy Workbook and Worksheet). (Or am I not seeing this correctly?)

In r4272, the TsWorksheetGrid does work with an internal WorkbookSource now if used in a standalong-mode. The fpsgrid demos show that the grid is automatically expanded if a cell outside the pre-defined range is created by code.

[rvk]

Yes, It works...
So unexpected users can drop a TsWorksheetGrid and it will all work with autoexpanding when accessing a cell beyond the current sheet.

I did find something strange. Normally you would do sWorksheetGrid1.SaveToSpreadsheetFile (because you wouldn't have knowledge of the "dummy" WorkbookSource which is connected but for the hell of it I tried sWorksheetGrid1.WorkbookSource.SaveToSpreadsheetFile(). It gave me an error on this line:

Code: [Select]

procedure TsWorkbookSource.SaveToSpreadsheetFile(AFileName: String;
  AOverwriteExisting: Boolean = true);
begin
  if FWorkbook <> nil then begin // <-------- ERROR
Which I found strange. If there is a dummy TsWorkbookSource shouldn't the FWorkbook be nil (or a dupicate of sWorksheetGrid1.Workbook)?

Code: [Select]

Project project1 raised exception class 'External: SIGSEGV'.

In file 'fpspreadsheetctrls.pas' at line 1025:
if FWorkbook <> nil then begin

Or is the dummy TsWorkbookSource (FInternalWorkbookSource) you created not a full-fledged dummy TsWorkbookSource?
In the end it doesn't really matter because you wouldn't access the dummy WorkbookSource but I did find it strange.

Testcode:

Code: [Select]

procedure TForm1.Button1Click(Sender: TObject);
begin
  // this works
  sWorksheetGrid1.Worksheet.WriteText(99, 0, 'aaa');
  sWorksheetGrid1.Worksheet.WriteFontStyle(99, 0, [fssBold]);
  ShowMessage('99 worked');

  // now works too
  sWorksheetGrid1.Worksheet.WriteText(120, 0, 'aaa');
  sWorksheetGrid1.Worksheet.WriteFontStyle(120, 0, [fssBold]);
  ShowMessage('120 worked');

  // this works as expected
  sWorksheetGrid1.SaveToSpreadsheetFile('c:\temp\test.xlsx');

  // this gives an error
  sWorksheetGrid1.WorkbookSource.SaveToSpreadsheetFile('c:\temp\test.xlsx');

end;


[wp]

Good point.

Code: [Select]

sWorksheetGrid1.WorkbookSource.SaveToSpreadsheetFile('c:\temp\test.xlsx'); If there is no external workbooksource then the corresponding grid property *IS* nil (at least in r4272). So the problem is back to you because you are deferencing a nil pointer. No idea why the exception is raised in an implementation line of TsWorkbookSource, but not in the generating line above.

The next code works without the exception, of course:

Code: [Select]

if sWorksheetGrid.WorkbookSource <> nil then
  sWorksheetGrid1.WorkbookSource.SaveToSpreadsheetFile(...);

Anyway, I do not except the user to check WorkbookSource for nil. Therefore I modified in r4273 the declaration of this property such that it never can become nil:

Code: [Select]

  TsCustomWorksheetGrid = class ...
  private
    function GetWorkbookSource: TsWorkBookSource;
  public
    property WorkbookSource: TsWorkbookSource read GetWorkbookSource write SetWorkbookSource;
  end;

function TsCustomWorksheetGrid.GetWorkbookSource: TsWorkbookSource;
begin
  if FWorkbookSource <> nil then
    Result := FWorkbookSource else
    Result := FInternalWorkbookSource;  // is created in the constructor
end;As a side-effect, the property value of the grid's WorkbookSource is no longer empty in the object inspector, but contains the value "internal". Similar to the Source of TChartSeries in the TAChart package.

[rvk]

Aaaah, yes now I see.

You have a FWorkbookSource AND a FInternalWorkbookSource. I thought at first you would create the FWorkbookSource as dummy but you used a separate variable for that.

Now I understand the error (because FWorkbookSource is still nil).

I thought you would have implemented a GetWorkbookSource for WorkbookSource-property too which would get the correct internal or external one.

Edit:
Ahah... I see you did just that in r4273

[dodgebros]

You guys were so much help to me regarding this project I feel it necessary to apologize for dropping off the forum for a while;  I had to do the hardware/networking thing to pay the bills. 

As it turns out, after all of that work, the client did not need it anymore as he changed the scope of his business during the two week period I was working on it.  Now, his business doesn't have to deal with spreadsheets.

I sincerely appreciate the great help you guys provided, maybe the next project will involve spreadsheets and I'll be ready for it !!!

TD