Recent

Author Topic: [Solved]How to get search result that match 2 columns' value  (Read 1922 times)

Jonvy

  • New member
  • *
  • Posts: 28
[Solved]How to get search result that match 2 columns' value
« on: October 14, 2018, 04:00:14 pm »
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]
  1.  
  2. procedure TForm1.BtnReadClick(Sender: TObject);
  3. var
  4.   MyWorkbook:TsWorkbook;
  5.   MyWorksheet:TsWorksheet;
  6.   cell:PCell;
  7.   MyDir: string;
  8.   InputFilename: string;  
  9.   MySearchParams1,MySearchParams2: TsSearchParams;
  10.   MyRow,MyCol: Cardinal;
  11. begin
  12.   // Open the input file
  13.   MyDir := ExtractFilePath(ParamStr(0));
  14.   InputFileName := MyDir + 'PartData.xls';
  15.  
  16.   MyWorkbook:=TsWorkbook.Create;
  17.   MyWorkbook.Options:=MyWorkbook.Options+[boReadFormulas];
  18.   MyWorkbook.ReadFromFile(InputFileName,sfExcel8);
  19.   MyWorksheet:=MyWorkbook.GetWorksheetByIndex(0);
  20.   //Search
  21.    MySearchParams1.SearchText:=trim(EdtPart.Text);
  22.    MySearchParams1.Within:=swWorksheet;
  23.    MySearchParams1.Options:=[soEntireDocument]+[soCompareEntireCell];
  24.  
  25.    MySearchParams2.SearchText:=trim(EdtCtmCode.Text);
  26.    MySearchParams2.Within:=swWorksheet;
  27.    MySearchParams2.Options:=[soAlongRows]+[soCompareEntireCell];
  28.  
  29.        // Create search engine and execute search
  30.     with TsSearchEngine.Create(MyWorkbook) do
  31.     begin
  32.       if FindFirst(MySearchParams1,MyWorksheet,MyRow,MyCol) and FindFirst(MySearchParams2,MyWorksheet,MyRow,MyCol) then
  33.  
  34.       begin
  35.         sBarInfo.SimpleText:='First '+MySearchparams2.SearchText+' found in cell '+GetCellString(MyRow, MyCol);
  36.           cell:=MyWorksheet.GetCell(MyRow, MyCol-1);
  37.           edtCustomer.Text:=MyWorksheet.ReadAsText(cell);
  38.  
  39.           cell:=MyWorksheet.GetCell(MyRow, MyCol+2);
  40.           EdtCstPN.Text:=MyWorksheet.ReadAsText(cell);
  41.  
  42.           cell:=MyWorksheet.GetCell(MyRow, MyCol+1);
  43.           edtName.Text:=MyWorksheet.ReadAsText(cell);
  44.  
  45.           cell:=MyWorksheet.GetCell(MyRow, MyCol+4);
  46.           edtQty.Text:=MyWorksheet.ReadAsText(cell);
  47.       end;
  48.       Free;
  49.     end;
  50.  
  51.  
  52.  
  53.   MyWorkbook.Free;
  54. end;        
  55.  

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!
« Last Edit: October 21, 2018, 04:11:17 am by Jonvy »

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: How to get search result that match 2 columns' value
« Reply #1 on: October 17, 2018, 06:37:08 pm »
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]
  1. program Project2;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. uses
  6.   fpspreadsheet, fpstypes, fpsutils, xlsbiff8;
  7.  
  8. const
  9.   SEARCH_TEXT_B = '10041109';
  10.   SEARCH_TEXT_D = 'Qt';
  11.   SEARCH_COL_B = 1;
  12.   SEARCH_COL_D = 3;
  13.  
  14. var
  15.   b: TsWorkbook;
  16.   sh: TsWorksheet;
  17.   txtB, txtD: String;
  18.   r: Cardinal;
  19.  
  20. begin
  21.   b := TsWorkbook.Create;
  22.   try
  23.     b.ReadFromFile('PartData.xls');
  24.     sh := b.GetFirstWorksheet;
  25.     for r := 0 to sh.GetLastOccupiedRowIndex do begin
  26.       txtB := sh.ReadAsText(r, SEARCH_COL_B);
  27.       txtD := sh.ReadAsText(r, SEARCH_COL_D);
  28.       if (txtB = SEARCH_TEXT_B) and (txtD = SEARCH_TEXT_D) then
  29.         WriteLn('Search texts found in row with index ', r)
  30.     end;
  31.   finally
  32.     b.Free;
  33.   end;
  34.  
  35.   ReadLn;
  36. end.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Jonvy

  • New member
  • *
  • Posts: 28
Re: How to get search result that match 2 columns' value
« Reply #2 on: October 21, 2018, 04:09:04 am »
Thanks wp!
Follow your way,I get the results I need!

Jonvy