Forum > FPSpreadsheet

SQL queries in FPSpreadSheet

(1/5) > >>

maurobio:
Dear ALL,

FPSpreadSheet (https://wiki.lazarus.freepascal.org/FPSpreadsheet) is a really great Lazarus library for reading, writing, and handling spreadsheets in many formats. I offers a lot of functionality, but I could not find any obvious way of filtering rows in a FPSpreadSheet, preferentially using SQL queries. Is there any (not necessarily simple) way of achieving that?

Thanks in advance!

With best wishes,

wp:
This feature is not planned. You probably can inherit from the fpspreadsheet SearchEngine (unit fpssearch) to introduce an SQL parser. Maybe some methods must be declared as virtual or moved to the public section. Just tell me what you need - I am open to such conservative changes, but I will definitely not add an SQL engine to fpspreadsheet, the library is too large already now.

Or you export the worksheet(s) to an SQL database (by using the fpspreadsheet export components) when you can apply SQL immediately.

maurobio:
Dear @wp,

Thanks for your reply. In fact, I did not have in mind the inclusion of an SQL engine to the already superb FPSpreadSheet library. I just need a way of filtering records (rows in an spreadsheet) using relatively simple SQL queries with the most basic select's (no joins, etc.). But I would prefer to be able to do that in memory instead of exporting the spreadsheet data to disk, reading the exported data, and performing the queries.

I think that something like that should do:


* read the spreadsheet from disk and display it into a TsWorkSheet grid;
* copy the spreadsheet data to an in-memory dataset (using TMemDataset or TBufDataset);
* perform the SQL query on the in-memory dataset;
* load the query results in the TsWorkSheet grid;
Any examples along the above lines would be helpful.

With best wishes,

maurobio:
@wp,

In fact, ideally what should be really great would be a TDataSet descendent which would accept a spreadsheet, just like other data access components as TCSVDataset, TDbf, etc. This could then be associated to a datasource and to a DBGrid for display.

Just food for thought.

With best wishes,

wp:

--- Quote from: maurobio on July 29, 2021, 04:42:19 pm ---In fact, ideally what should be really great would be a TDataSet descendent which would accept a spreadsheet, just like other data access components as TCSVDataset, TDbf, etc. This could then be associated to a datasource and to a DBGrid for display.

--- End quote ---
Interesting idea. You mean something like a TWorksheetDataset? On the other hand, I see the basic problem that a TDataset-descendant needs strictly column-oriented data types ("fields"), but in a spreadsheet cells can contain any arbitrary data types at any place.


--- Quote from: maurobio on July 29, 2021, 04:17:49 pm ---I think that something like that should do:


* read the spreadsheet from disk and display it into a TsWorkSheet grid;
* copy the spreadsheet data to an in-memory dataset (using TMemDataset or TBufDataset);
* perform the SQL query on the in-memory dataset;
* load the query results in the TsWorkSheet grid;
--- End quote ---
There is package ZMSQL on ccr and available also via Online-Package-Manager, which implements a descendant of TBufDataset supporting SQL. If your spreadsheet fulfills the requirements of a dataset (i.e. constant data type through-out a column) you could write the worksheet to this ZMBufDataset (in memory), perform the SQL and read the result back into a worksheet. My experience with ZMSQL, however, is very limited, and in fact it did not work for me at all in my first tests some time ago - but this does not mean anything. The author, Tatamata, is an occasional user of this forum and you certainly can contact him if you have problems.

Navigation

[0] Message Index

[#] Next page

Go to full version