get last non empty row /col
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
kjteng · 2018-05-03 08:03

I want to get the row index of last row which contains data (in a
worksheet) by using  GetLastOccupiedRowIndex. It works so far until
when the user move the cursor below the last row or delete some rows. 
eg. say I have a worksheet contain 10 rows of data,
getLastOccupiedRowIndex return 9, which is correct. However, if I move
the cursor to row 15, getLastOccupiedRowIndex would return row index
of 14 despite that rows 11 to 15 contain no data.  Is this the
intended behaviour of the method?

To solve my problem, I have added the  following methods to the
TsWorksheet class. 

function TsWorksheet.GetLastOccupiedCol: Cardinal;
var  cell: PCell;
begin
  Result := 0;
  for cell in FCells do
    if ((cell^.UTF8StringValue > '') or (cell^.FormulaValue > '')) and
(cell^.Col > Result) then
      Result := cell^.Col;
end;

function TsWorksheet.GetLastOccupiedRow: Cardinal;
var  cell: PCell;
begin
  Result := 0;
  for cell in FCells do
    if ((cell^.UTF8StringValue > '') or (cell^.FormulaValue > '')) 
and (cell^.Row > Result) then
      Result := cell^.Row;
end;

I would like to seek your view that whether the test 
(cell^.UTF8StringValue > '') alone is sufficient to ensure that a cell
is not empty. Thank you.

----------------------------------------------------------------------
wp · 2018-05-03 08:25

I cannot reproduce. Please post a demo project and precise
instructions what to do in order to show the error.

----------------------------------------------------------------------
kjteng · 2018-05-03 12:11

It is part of a large file. Please allow me some time to remove the
irrelevant part beforr i post the demo source code.

----------------------------------------------------------------------
wp · 2018-05-03 13:37

Quote from: kjteng on May 03, 2018, 02:11:13 pmremove the irrelevant
part beforr i post the demo source code.
Thank you - this is much easier for me.

BTW: what is your definition of "empty"? Apart from having an empty
string content there can also be cells with ContentType cctEmpty.
These empty cells are usually needed for particular formatting, mostly
framing.

----------------------------------------------------------------------
kjteng · 2018-05-04 02:22

Thank you - this is much easier for me.
BTW: what is your definition of "empty"? Apart from having an empty
string content there can also be cells with ContentType cctEmpty.
These empty cells are usually needed for particular formatting, mostly
framing.
[/quote]


In the process of cleaning up the code I discovered the following:
GetLastOccupiedRowIndex only return different number when I have
ReadAsText / ReadFormulaAsString in the onSelectcell handler. 
 Everything will be ok if I remove the call for ReadAs... method.
Attached is the source code.

BTW:  For my purpose, 'Empty' means no text or number value in the
cell. Blank cell with formatting data will be ignored as we only want
to do some processing on the text/number only.

----------------------------------------------------------------------
wp · 2018-05-04 07:54

Fixed in fpspreadsheet trunk r6377. The problem was that
Worksheet.ReadAsText/ReadAsUTF8Text called Worksheet.GetCell instead
of FindCell - GetCell creates a new cell if a cell does not yet exist
at the specified row/col indexes; FindCell simply returns nil in this
case. Therefore, with every click into an unoccupied part of the
worksheetgrid you extended the worksheet cells by one.
GetLastOccupiedCol/RowIndex itself is correct.

I did not see an issue with ReadFormulaAsString. Could you please
investigate whether the problem still exists.

----------------------------------------------------------------------
kjteng · 2018-05-04 09:14

Thank you for the fix.
My earlier problem was also partly due to the call for
ReadFormulaAsString(GetCell(aRow-1, aCol-1). Problem solved after
changing getcell to Findcell.

BTW, where can I download the trunk r6277 ? or should I manually
change the file in my harddisk?

----------------------------------------------------------------------
wp · 2018-05-04 09:46

You get the trunk version usually by svn from
svn://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet, or
you must download the Lazarus-CCR snapshot from
https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tarball and extract
only the folder components/fpspreadsheet from the zip file.

Alternatively you can modify your current version easily:
Open fpspreadsheet.pasNavigate to the implementation of
TsWorksheet.ReadAsText and replace its code byCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TsWorksheet.ReadAsText(ARow, ACol: Cardinal): string;var  cell:
PCell;begin  cell := FindCell(ARow, ACol);  if cell <> nil then Result
:= ReadAsText(cell) else Result := '';end;Navigate to the
implementation of the TsWorksheet.ReadAsUTF8Text and replace its code
byCode: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TsWorksheet.ReadAsUTF8Text(ARow, ACol: Cardinal): string;begin  Result
:= ReadAsText(ARow, ACol);end;

----------------------------------------------------------------------
kjteng · 2018-05-04 10:24

Thank you very much.