Recent

Author Topic: get last non empty row /col  (Read 4305 times)

kjteng

  • Sr. Member
  • ****
  • Posts: 259
get last non empty row /col
« on: May 03, 2018, 10:03:10 am »
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.
« Last Edit: May 03, 2018, 11:13:01 am by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: get last non empty row /col
« Reply #1 on: May 03, 2018, 10:25:37 am »
I cannot reproduce. Please post a demo project and precise instructions what to do in order to show the error.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: get last non empty row /col
« Reply #2 on: May 03, 2018, 02:11:13 pm »
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

  • Hero Member
  • *****
  • Posts: 11858
Re: get last non empty row /col
« Reply #3 on: May 03, 2018, 03:37:20 pm »
remove 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

  • Sr. Member
  • ****
  • Posts: 259
Re: get last non empty row /col
« Reply #4 on: May 04, 2018, 04:22:53 am »
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.
« Last Edit: May 04, 2018, 06:17:40 am by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: get last non empty row /col
« Reply #5 on: May 04, 2018, 09:54:28 am »
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

  • Sr. Member
  • ****
  • Posts: 259
Re: get last non empty row /col
« Reply #6 on: May 04, 2018, 11:14:43 am »
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

  • Hero Member
  • *****
  • Posts: 11858
Re: get last non empty row /col
« Reply #7 on: May 04, 2018, 11:46:39 am »
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.pas
  • Navigate to the implementation of TsWorksheet.ReadAsText and replace its code by
Code: Pascal  [Select][+][-]
  1. function TsWorksheet.ReadAsText(ARow, ACol: Cardinal): string;
  2. var
  3.   cell: PCell;
  4. begin
  5.   cell := FindCell(ARow, ACol);
  6.   if cell <> nil then Result := ReadAsText(cell) else Result := '';
  7. end;
  • Navigate to the implementation of the TsWorksheet.ReadAsUTF8Text and replace its code by
Code: Pascal  [Select][+][-]
  1. function TsWorksheet.ReadAsUTF8Text(ARow, ACol: Cardinal): string;
  2. begin
  3.   Result := ReadAsText(ARow, ACol);
  4. end;

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: get last non empty row /col
« Reply #8 on: May 04, 2018, 12:24:20 pm »
Thank you very much.

 

TinyPortal © 2005-2018