Author Topic: fpsSearch  (Read 495 times)


  • Jr. Member
  • **
  • Posts: 69
« on: August 02, 2020, 09:23:57 am »
I want to do a search in more than one column with something like (derived from wiki):
Code: Pascal  [Select][+][-]
  1.       with TsSearchEngine.Create(MyWorkbook) do begin
  2.         SearchParams.Options:= [];
  3.         SearchParams.Within := swColumn;
  4.         SearchParams.SearchText := 'xyz';
  5.         for  j := 0 to 1 do begin   // simplified for this example
  6.             MyWorksheet.FindCell(1,j); // set active cell for search, starting after the spreadsheet heading
  7.             if FindFirst(SearchParams,MyWorksheet,ZoekRij,ZoekKolom) then begin
  8.               x := 0;    // do something
  9.               while FindNext(SearchParams,MyWorksheet,ZoekRij,ZoekKolom) do
  10.                 x := 0;
  11.             end;  // if
  12.         end; // for
  13.         Free;
  14.     end;   // with

When the program is at FindCell for the second iteration I get an exception SIGSEGV in file fpspreadsheet.pas at line 2353.
Outside of the FindFirst/FindNext context FindCell works fine.
So, a FindCell (or a ReadAsText, etc) at this place is not permitted. This could be useful in a program.
I wonder why there is no FindClose method that could nicely clean up the search buffer before another search. If there is only one search (as in the wiki example) the cleanup is done with freeing the searchengine.

I could create/free the searchengine with each search as a workaround.

A detail: the unit is fpsSearch but the uses section in the wiki example writes fpsearch.
« Last Edit: August 02, 2020, 09:33:10 am by straetch »


  • Hero Member
  • *****
  • Posts: 7524
Re: fpsSearch
« Reply #1 on: August 02, 2020, 11:41:22 am »
Please provide a full compilable sample project (.pas, .lfm, .lpr, .lpi and spreadsheet files only, packed to a single zip). But I suspect that the issue is FindCell which does not set the "acitve" cell - FindCell only returns the pointer to the cell record at the given coordinates, it has nothing to do with "searching" in the sense of your question. FindCell returns nil when a cell record does not exist for this particular cell; this is the reason why worksheet.FindCell(ARow, AColor).ReadAsText does not work.

Using SelectCell(ARow, ACol), however, sets the internal row/col variables for the "active" cell which are used by "searching".

FindClose would have no effect because there is no "search buffer". When you want to re-use the same SearchEngine for another search with different parameters just initialize the SearchParams records with new values.

Thanks for reporting the typo in the wiki - these things happen... I also added a sentence on how to define the active worksheet and active cell if needed by the search.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...


  • Jr. Member
  • **
  • Posts: 69
Re: fpsSearch
« Reply #2 on: August 02, 2020, 03:47:51 pm »
Attached a quick sample with dummy spreadsheet.
I changed FindCell by SelectCell. That makes the cell active, but it does not solve the SIGSEGV issue which now happens with the SelectCell method call at the second iteration.
If you run the sample with only one column selected, it does work.
If two columns are selected, the exception occurs with the second iteration, ie. when SelectCell is executed for the second column (file fpspreadsheet.pas at line 2353).
The parameters passed to SelectCell are correct, i.e. not out of range.


  • Hero Member
  • *****
  • Posts: 7524
Re: fpsSearch
« Reply #3 on: August 02, 2020, 05:01:23 pm »
I see. The problem is that the worksheet parameters in the FindFirst and FindNext are the worksheets in which the search text has been found. This is not necessarily the worksheet WITHIN which you perform the search. In particular, when the search has not been successful the worksheet parameter becomes nil, and the SelectCell call in the next loop cycle applies to a nil worksheet.

Simple solution: use a temporary local variable for the search result worksheet:
Code: Pascal  [Select][+][-]
  1. procedure TMainForm.SearchButtonClick(Sender: TObject);
  2. var
  3.   j: integer;
  4.   Aant: integer;
  5.   foundWS: TsWorksheet;
  6. begin
  7.   Aant := 0;
  8.   ResultEdit.Text := '';
  9.   SearchParams.SearchText := SearchTermEdit.Text;
  10.   SearchParams.Options := [];
  11.   SearchParams.Within := swColumn;
  12.   with TsSearchEngine.Create(WB) do begin
  13.     for  j := 0 to 3 do begin
  14.       if SelectColumnsCheckGroup.Checked[j] then begin  // als de kolom geselecteerd is
  15.         WS.SelectCell(0,j); // set active cell
  16.         if FindFirst(SearchParams,foundWS,FoundRow,FoundCol) then begin
  17.           Aant := Aant + 1;
  18.           while FindNext(SearchParams,foundWS,FoundRow,FoundCol) do
  19.             Aant := Aant + 1;
  20.         end;  // if
  21.       end;  // if
  22.     end; // for
  23.     Free;  // van TsSearchEngine
  24.     ResultEdit.Text := inttostr(Aant);
  25.   end;  // with
  27. end;
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...


  • Jr. Member
  • **
  • Posts: 69
Re: fpsSearch
« Reply #4 on: August 03, 2020, 08:43:05 am »
Thanks for the solution. I think I understand.
The three parameters (foundWS,FoundRow,FoundCol) in FindFirst/FindNext are the items that will be overwritten by the function as these are the result of the search.
So if WS (the loaded worksheet) is the parameter of the function, it becomes nil when the search is unsuccessful (at the end of the while loop). Meaning WS is no longer the first worksheet in the workbook. Also if the entire workbook is searched, WS may change as it will point to the last worksheet where the search item was found.
Therefore the need for local variables for the worksheet, row and column. Correct?

In this respect, the example in the wiki is dangerous.
MyWorksheet is part of MyWorkbook and it is also used as the second parameter in FindFirst/FindNext.
In this simple example it will work. But users (like me) are inspired to use the example in their program and will experience this problem.
I am pretty sure that eg. a  MyWorksheet.FindCell  statement after the try/finally will fail.
I suggest to adapt the example in the wiki with addition of the temporary local variable and a strong warning in the wiki text and in the comments of the example.

Many thanks for your assistance.


  • Hero Member
  • *****
  • Posts: 7524
Re: fpsSearch
« Reply #5 on: August 03, 2020, 10:52:15 am »

the example in the wiki is dangerous.
The example itself is safe because the variable MyWorksheet is not used for anything else than holding the search result. This is different from your code which needs a worsheet also to define the active worksheet. But I see the point that it may seduce the user to think like you did. Therefore I renamed the variable to "foundWorksheet" to indicate that it should not be confused with the active worksheet. I also corrected the "demo_search" project in the "examples" folder of the fpspreadsheet installation.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...


TinyPortal © 2005-2018