Recent

Author Topic: Filtering rows in FPSpreadsheet  (Read 1446 times)

maurobio

  • Sr. Member
  • ****
  • Posts: 304
  • Ecology is everything.
    • GitHub
Filtering rows in FPSpreadsheet
« on: August 25, 2021, 04:33:21 pm »
Dear ALL,

I have a spreadsheet with data on plant specimens collected in some plots. The spreadsheet is read using the great FPSpreadsheet component (https://wiki.freepascal.org/FPSpreadsheet). I want to be able to filter the rows of the spreadsheet, by executing SQL-like queries on the data (eg., for displaying only the plants belonging to a certain family, or all specimens from a given family collected at a certain locality, etc.) and displaying the filtered results in the same (or another) spreadsheet.

One way I devised (but have not tested!) of achieving that would involve exporting the spreadsheet data to a csv file and then using the ZMSQL (https://wiki.freepascal.org/ZMSQL) component for performing the query on the data and display the results. But this seems very convoluted. Is there a way of doing this using only FPSpreadsheet?

I attach a sample project, including the original data spreadsheet in Excel97 format. This project includes a gross filter dialog (which is incomplete and buggy, but it should suffice to convey the general idea) to build SQL-like queries. See also the attached screenshot.

Thanks in advance for any assistance you can provide!

With best wishes,
« Last Edit: August 25, 2021, 04:55:44 pm by maurobio »
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 9025
Re: Filtering rows in FPSpreadsheet
« Reply #1 on: August 26, 2021, 10:21:04 am »
Why don't you do like I did in the sample code I presented in the related thread (https://forum.lazarus.freepascal.org/index.php/topic,55981.0.html)? Iterate through all rows, find out which row matches your filter criterion, and then copy the cells from the matching rows to a new empty worksheet.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 304
  • Ecology is everything.
    • GitHub
Re: Filtering rows in FPSpreadsheet
« Reply #2 on: August 26, 2021, 01:54:17 pm »
Dear @wp,

Quote
Why don't you do like I did in the sample code I presented in the related thread

But I did! I loved it! (Haven't you seen my reply in that thread?), but it did not occurred to me that it could be extended to the more general case of filtering rows according to the criteria provided by the user. I am still quite new to FPSpreadsheet, and learning its functionalities step by step.

BTW, if I may make a suggestion: wouldn't it be possible (and useful) to have a property 'Filter' added to a worksheet, which would take a user-provided SQL-like query for filtering the displayed rows of a spreadsheet?

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

 

TinyPortal © 2005-2018