Does the Flexcel.Report feature provide an easy way to create a report directly from an Excel Worksheet? I already have a program which generates an Excel worksheet from various other sources: it is not 'products', but assume it has columns headed Product, Date, Time, Data1, Data2, Value, and a single headings row. I would now like to create a very simple Flexcel report, looking much like the example in the Encryption and Subtotals demo. with a total row showing the Value column for each product and a grand total. I can pre-sort the worksheet on time/date/product if necessary.
I assume I could manage to save the worksheet in an XLSX file and then set it up as an Excel data source, and fairly easily hack the example code to make this work. But ideally I would like to do it in a single operation, to first create the intermediate worksheet as now, and then save the XLSX file containing the report. I have the feeling that the Report feature seems smart enough to do this, but I do not yet see how.
What would be the best approach to achieve this? Don't assume experience with Named Ranges, but I am learning fast from the report examples!
While it should be possible to use an Excel file as a source to a report, it wouldn't really be easy, and I wonder if in your case it wouldn't make more sense to just reformat the existing sheet, since you already have the data?
There are some methods that you could use for this:
This method allows you to group your data by a field, and add subtotals in every column. It works similar to then Ribbon->Data->Subtotal command in Excel.
To remove columns you don't want. You might also hide the columns you don't want.
But of course there is much more than you could do, like for example adding conditional formatting, etc. You can use APIMate to find out how to do most of that stuff.
I mention this all because I think it might be simpler to use that functionality than to create a report here, since you have already created a report.
If you need to do stuff that is not possible directly with the API, then well, you could load indeed the data into a TList<TProduct> where TProduct would be a class having Product, Data, Time... or whatever fields you want for the report. You can then directly use that TList<TProduct> as a datasource for FlexCel. (see http://www.tmssoftware.biz/flexcel/doc/vcl/samples/delphi/reports/reports-from-lists/index.html )
About how to fill that TList<TProduct> from the Excel sheet, well, it depends in how you have the original excel file. If you have it as a TStream or a physical file, you can just call xls.Open to open the file or stream in FlexCel, and then load all the rows by using code similar to the one here:
If you have a different way to load the data directly into a TList<TProduct> by changing the program that generates the original Excel file, then it might be other way too.