[Solved]How to get search result that match 2 columns' value
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
Jonvy · 2018-10-14 14:00

Hi,
I want to get search result that match 2 columns' value.
Example, the column B's text should be 10041109 and column D's text
should be Qt.
But I can not get correct search result by my following code:
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  procedure
TForm1.BtnReadClick(Sender: TObject);var  MyWorkbook:TsWorkbook; 
MyWorksheet:TsWorksheet;  cell:PCell;  MyDir: string;  InputFilename:
string;    MySearchParams1,MySearchParams2: TsSearchParams; 
MyRow,MyCol: Cardinal;begin  // Open the input file  MyDir :=
ExtractFilePath(ParamStr(0));  InputFileName := MyDir +
'PartData.xls';   MyWorkbook:=TsWorkbook.Create; 
MyWorkbook.Options:=MyWorkbook.Options+[boReadFormulas]; 
MyWorkbook.ReadFromFile(InputFileName,sfExcel8); 
MyWorksheet:=MyWorkbook.GetWorksheetByIndex(0);  //Search 
 MySearchParams1.SearchText:=trim(EdtPart.Text); 
 MySearchParams1.Within:=swWorksheet; 
 MySearchParams1.Options:=[soEntireDocument]+[soCompareEntireCell];  
 MySearchParams2.SearchText:=trim(EdtCtmCode.Text); 
 MySearchParams2.Within:=swWorksheet; 
 MySearchParams2.Options:=[soAlongRows]+[soCompareEntireCell];      
 // Create search engine and execute search    with
TsSearchEngine.Create(MyWorkbook) do    begin      if
FindFirst(MySearchParams1,MyWorksheet,MyRow,MyCol) and
FindFirst(MySearchParams2,MyWorksheet,MyRow,MyCol) then       begin  
    sBarInfo.SimpleText:='First '+MySearchparams2.SearchText+' found
in cell '+GetCellString(MyRow, MyCol);         
cell:=MyWorksheet.GetCell(MyRow, MyCol-1);         
edtCustomer.Text:=MyWorksheet.ReadAsText(cell);          
cell:=MyWorksheet.GetCell(MyRow, MyCol+2);         
EdtCstPN.Text:=MyWorksheet.ReadAsText(cell);          
cell:=MyWorksheet.GetCell(MyRow, MyCol+1);         
edtName.Text:=MyWorksheet.ReadAsText(cell);          
cell:=MyWorksheet.GetCell(MyRow, MyCol+4);         
edtQty.Text:=MyWorksheet.ReadAsText(cell);      end;      Free;   
end;     MyWorkbook.Free;end;         
When column B's text is 10041109 and column D's text is Qt, I should
get search result Row 448, but actually result I get is Row 442.

I know the second search for column D's text not with the result for
first search, but I don't know how to do it?

Is it a way to get search within the range of first search result?
Or tell me how to solve my search problem?
I attach the excel file here.

Thanks!

----------------------------------------------------------------------
wp · 2018-10-17 16:37

The current search engine is not built for multiple search criteria,
and I don't see an easy way to extend it without breaking code.

But searching is not a difficult task. Why don't you do it without the
search engine by just iterating through all rows and checking the
corresponding cells? Here's some teaser:

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
Project2; {$mode objfpc}{$H+} uses  fpspreadsheet, fpstypes, fpsutils,
xlsbiff8; const  SEARCH_TEXT_B = '10041109';  SEARCH_TEXT_D = 'Qt'; 
SEARCH_COL_B = 1;  SEARCH_COL_D = 3; var  b: TsWorkbook;  sh:
TsWorksheet;  txtB, txtD: String;  r: Cardinal; begin  b :=
TsWorkbook.Create;  try    b.ReadFromFile('PartData.xls');    sh :=
b.GetFirstWorksheet;    for r := 0 to sh.GetLastOccupiedRowIndex do
begin      txtB := sh.ReadAsText(r, SEARCH_COL_B);      txtD :=
sh.ReadAsText(r, SEARCH_COL_D);      if (txtB = SEARCH_TEXT_B) and
(txtD = SEARCH_TEXT_D) then        WriteLn('Search texts found in row
with index ', r)    end;  finally    b.Free;  end;   ReadLn;end.

----------------------------------------------------------------------
Jonvy · 2018-10-21 02:09

Thanks wp!
Follow your way,I get the results I need!

Jonvy