[SOLVED] FPSpreadsheet, unwanted blank line

[Phoenix]

Good morning,
I've noticed a small issue with the library. An unwanted row is added to the sheet. To see it, just call "load," and even removing it and then saving doesn't change the situation. I was wondering what I'm doing wrong. I've already added a workaround, but I think there must be a better one (see the "menu load" code).

I tested with the latest trunk version (downloaded yesterday) of the library but it doesn't change the behavior. Win 10 Laz 3.6 FPC 3.2.2 64bit

[wp]

Low-level peeking into the data file shows that there IS an empty last line. This is because it has a different row style due to a slightly different row height.

Deleting this line in LibreOffice crashes FPSpreadsheet when trying to read this modified file ("Operation at index 5 exceeds the range of defined grid rows (5)"). This happens because I left the cursor in the empty row 5 after deletion, but your code modifies the AutoExpand property of the grid by removing the aeDefault, aeNavigation, and aeData options. Removal of the latter two prevents expansion of the worksheet grid by navigating and by putting data into the not-yet defined grid cells. But putting the active cell into this range just fires this. To avoid this while keeping the restricted display of only the used cells, you must make sure that when the workbook is saved with the cursor in the occupied part of the grid (maybe I should re-think the idea to raise an exception in such a case rather than moving the active cell to some innocent place, e.g. cell A1). Or, of course, leave the AutoExpand property alone - but then you must accept the grid extending to "infinity".

[EDIT]
Committed a more tolerant version of the worksheet grid in which this situation does not cause a crash any more. It is possible, though, that there are still other occasions when the AutoExpand functions can raise an exception.

[Phoenix]

Hi,

Low-level peeking into the data file shows that there IS an empty last line. This is because it has a different row style due to a slightly different row height.

Thanks for the very quick reply. This enlightened me; here's how the problem appears!

Foreword: Initially, the program didn't have the row problem, but over time, it appeared in some tables, and I didn't understand how it happened.
I'm attaching "Test 2" to make it appear from a "healthy" file.
- Load 2
- Add a row
(In the attached test, this is actually enough, while in the original program I had to enter a text in the "E" field that was long enough to have to start a new line)
- Delete the row
- Save
The next time you load the file, the unwanted row will appear.

[Phoenix]

Here's the change to prevent the line from appearing with each addition (as in the original program). But as mentioned, when the text in the "E" field spans multiple lines, the same problem will recur.

Code: Pascal  [Select][+][-]

1. procedure TForm1.Load2Click(Sender: TObject);
2. begin
3.  grid.Workbook.ReadFromFile('test2.ods',sfOpenDocument);
4.  
5.  grid.Workbook.SetDefaultFont(grid.Font.Name,12);
6.  grid.UpdateRowHeights(-1, True);
7. end;
8.  

[wp]

There were two bugs:

• TWorksheet.DeleteRowOrCol did not remove the row record if the the to-be-deleted row had one. Row records store the row heights, for example. Therefore, the index of the last row did not decrease when the last row was deleted, and the extent of the worksheet remained unchanged.
• TWorksheetGrid.DeleteRow did not adjust the RowCount when a row was deleted. Normally, this is not required because the worksheet is supposed to extend to the max row limit. But in your case of preventing the grid to grow its extent by means of the AutoExtend property for navigation and dat, the RowCount must be changed.

As a consequence the handler for deletion of the last row can be simplified to

Code: Pascal  [Select][+][-]

1. procedure TForm1.DeleteLast1Click(Sender: TObject);
2. var
3.   r: Integer;
4. begin
5.   r:= grid.RowCount;
6.   if r <> 0 then
7.     grid.DeleteRow( r-1 );
8. end;

The issues are is fixed in the current development repository of FPSpreadsheet at Lazarus-CCR. To get the new version either use svn or download the snapshot from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/.

[Phoenix]

Thank you very much for solving the problem, have a good day too  .

[Phoenix]

just one thing, trying again with the control enabled, there is a memory leak

[wp]

Took me some time until I understood: I had used FRows.Delete(row) to delete the specified row, but it turned out that this only deletes the pointer to the row record from the row list and does not free the row record itself. The correct call is Worksheet.RemoveRow(rowindex) which does the full job.

Fixed in ccr, r9982.

[Phoenix]

Thanks again for all your work, I confirm that it works as expected  .