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.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.
I think that something like that should do: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.
- 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;
Yes, something like a TWorksheetDataset would be really great! [...] If you are considering this idea, please count me in as a committed beta-tester!
I will give it a try, although it seems not to be exactly (at least in its present state) what I need.
What I really would love to see implemented in FPSpreadsheet would be a filtering facility identical to that of LibreOffice (see attached screenshot), in which spreadsheet rows meeting the (multiple) filtering criteria would be displayed, while those rows not meeting such criteria would be hidden (that is, the filtering operation would be performed in the current spreadsheet, without the need of 'copying' filtered rows to another worksheet, etc.).I will definitely not add forms to the basic fpspreadsheet library. Imagine a user wants to change the arrangements of the controls on the form. Or another user cannot use the LCL controls because his entire application is based on some commercial control library and this form would break a consistent look and feel of the application.
I surely want to give your new component a try, but I do not see how I can help - and how the new component can help me - at this time, since you stated that filtering is not yet implement (and filtering is what I mostly need).It was working, just forgot to refresh the dataset after activating the filter.