FPSpreadsheet: How to get the last filled row cell of a column?
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
Jvan · 2021-01-31 16:21

For instance, if I need the last filled cell of column D, how to get
its index?
if, I need the last filled cell of row 8, how to do the same?

----------------------------------------------------------------------
wp · 2021-01-31 17:15

Since the cells are stored by rows in a sorted tree there is a method
to quickly iterate through a row, and this very quickly finds the
first and last cell in each row: cell :=
Worksheet.Cells.GetFirstCellOfRow(rowIndex) and .GetLastCellOfRow.

As of the column, however, I am afraid you must run through the column
yourself. Use the sheet methods GetFirstRowIndex and GetLastRowIndex
the find the row indices of the first and last cell of a given column,
and then iterate through this column.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
Project1; uses  fpspreadsheet, fpstypes, fpsutils, fpsClasses; var  b:
TsWorkbook;  sh: TsWorksheet;  cell: PCell; function
GetFirstCellOfCol(ASheet: TsWorksheet; ACol: Cardinal): PCell;var  r,
r1, r2: Cardinal;begin  r1 := ASheet.GetFirstRowIndex;  r2 :=
ASheet.GetLastRowIndex;  for r := r1 to r2 do  begin    Result :=
ASheet.FindCell(r, ACol);    if Result <> nil then      exit;  end; 
Result := nil;end; function GetLastCellOfCol(ASheet: TsWorksheet;
ACol: Cardinal): PCell;var  r, r1, r2: Cardinal;begin  r1 :=
ASheet.GetFirstRowIndex;  r2 := ASheet.GetLastRowIndex;  for r := r2
downto r1 do  begin    Result := ASheet.FindCell(r, ACol);    if
Result <> nil then      exit;  end;  Result := nil;end;  begin  b :=
TsWorkbook.Create;  try    sh := b.AddWorksheet('Sheet 1');   
sh.WriteText(2, 1, 'abc');    sh.WriteText(2, 10, 'def');   
sh.Writetext(2, 5, 'ghi');     sh.WriteText(5, 1, 'ABC');   
sh.WriteText(4, 1, 'DEF');    sh.writeText(11, 1, 'GHI');     for cell
in sh.Cells do      WriteLn('Row: ', cell^.Row, ' Col: ', cell^.Col, '
Value: ', sh.ReadAsText(cell));     WriteLn;     cell :=
sh.Cells.GetFirstCellOfRow(2);    Write('First col in row 2: ');    if
cell <> nil then Writeln(cell^.Col) else WriteLn('none');     cell :=
sh.Cells.GetLastCellOfRow(2);    Write('Last col in row 2: ');    if
cell <> nil then Writeln(cell^.Col) else WriteLn('none');     cell :=
sh.Cells.GetFirstCellOfRow(3);    Write('First col in row 3: ');    if
cell <> nil then Writeln(cell^.Col) else WriteLn('none');    
WriteLn;     cell := GetFirstCellOfCol(sh, 1);    Write('First row in
col 1: ');    if cell <> nil then Writeln(cell^.Row) else
WriteLn('none');     cell := GetLastCellOfCol(sh, 1);    Write('Last
row in col 1: ');    if cell <> nil then Writeln(cell^.Row) else
WriteLn('none');     cell := GetFirstCellOfCol(sh, 2);    Write('First
row in col 2: ');    if cell <> nil then Writeln(cell^.Row) else
WriteLn('none');   finally    b.Free;  end;   ReadLn;end.