FlexCelReport
FlexCelReport is a component for making reports in Excel from a Delphi application
using a template. Uses Excel as the Report Designer, allowing you to use all of its
power, from conditional formatting to pivot tables, including graphs, formulas, multiple
sheets, multiple master-detail relationships and whatever you can do from Excel.
Reports can be generated by OLE automation or completely native, without need for
any dll or having Excel installed.
In native mode templates can be linked into the exe, allowing you to create single exe
applications.
This is a new version of TExcelReport component, renamed to get a sillier and more
unique name.
INDEX
FlexCelReport....................................................................................................................1
INDEX...............................................................................................................................2
Introduction ...................................................................................................................2
How to use.....................................................................................................................4
1- Creating the Excel Template.................................................................................4
Now, It’s time to add the ranges................................................................................6
2- Creating the Delphi Datamodule...........................................................................8
Properties.......................................................................................................................9
property Adapter: TExcelAdapter; ............................................................................9
property Template: TFileName .................................................................................9
property DataModule: TComponent .........................................................................9
property FileName: TFileName ................................................................................9
property AutoClose: Boolean ....................................................................................9
property CalcRecordCount: TCalcRecordCount default cr_Count;........................10
property PagesDataSet: TDataSet ...........................................................................10
property PagesDataField: string; .............................................................................11
property Values: TFlxPropList;...............................................................................11
Events ..........................................................................................................................11
property OnRecordCount: TRecordCountEvent .....................................................11
property OnBeforeGenerateWorkbook: TOnGenerateEvent ..................................12
property OnAfterGenerateWorkbook: TOnGenerateEvent ....................................12
property OnBeforeGeneratePage: TOnGenerateEvent ...........................................13
property OnAfterGeneratePage: TOnGenerateEvent..............................................13
property OnGetFileName: TOnGetFileNameEvent................................................13
property OnGetOutStream: TOnGetOutStream ......................................................14
property OnGetCellValue: TOnGetCellValue ........................................................14
Methods .......................................................................................................................14
procedure Run .........................................................................................................14
procedure SaveToStream.........................................................................................14
procedure Cancel .....................................................................................................14
Tips and Troubleshooting............................................................................................15
Introduction
This component allows you to generate reports in Excel with data read from memory or
from a database. It’s been designed to give you all the power of an Excel spreadsheet,
and I've tried hard to not just go with the 'most usual case', and give you the options
when you need them.
At the moment I wrote the first version (quite a long ago…), I could find no other
component that made the same, even when now I think you can find some alternatives. I
didn’t want to use QuickReports, because people normally didn’t want just to print the
reports, but to modify them, and mail them to their bosses. So I needed an Excel sheet,
and the report builders were never good at exporting their data.
The mechanics are simple. You create a ‘Template’ in Excel where you define the
layout, and put special codes where the cell should be filled with data. Then you create a
Datamodule in Delphi and put the datasets that are going to access the database, along
with some TFlexCelReport and one TOleAdapter or TXlsAdapter components. Then
you configure it, and from somewhere in your app call it’s “run” method. And that’s it.
If you later want to change the template, you can do it without recompiling the
application.
I’ve included a little Demo/tutorial app, with templates and the final result that should
help you understand the way it works. Many times the best way to learn something is
not reading the boring documentation, but just doing it...
A last thing. I did want to keep the interface as simple as possible, so I tried not to
duplicate anything that Delphi or Excel can make easily. For example, I give no ways to
sort the report, because sorting it is as easy as sorting the dataset. (And if you use SQL
like me, you have infinite ways to combine, ascending, descending, etc). The idea is: do
as much as you can in the Excel or standard Delphi side. So you get code that depends
very little of this component in particular (normally, just one line of code: Report.run),
making your life much easier if you want to replace it by another.
How to use
1- Creating the Excel Template
To generate reports, the first thing you need is a template. Create a document like this:
(you can see it complete in the demo file Invoices.xls)
Here you put:
1) All the titles, images and formatting of the report. You can include graphs, filters,
conditional format, images or anything you can think of. You can also fill as
many sheets as you want, all of them will be filled with the data you request.
2) The fields to be filled from the database. Here you have to put a text on the form
##<dataset>##<field>
For example if you are going to create a dataset called “Cust” with a field
“Country” you will write ##Cust##Country in the cell where you want the value.
Note that the database can be a “Real” one or a TFlxMemDB holding memory
data.
3) You can define some extra variables in the datamodule that are not tied to the
database, by writing
#.<Variable>
. For example, if you had defined a published variant property called
“Current_Date” you could include its value by writing #.Current_Date in a cell
4) You can also include variant arrays here, just write
#.<Variable>#.<index1>#.<index2>#. …… #.<indexN>
For example, to write the value of the published variant property Price[1,3] into a
cell, you could write something like “#.Price#.1#.3
Note that you can use the replaced field values inside a formula. For example, if you
have in the cells:
A1: ##Client##FirstName
A2: ##Client##LastName
A3: (formula) =A1 &” “ & A2
(value) ##Client##FirstName ##Client##LastName
After the report is run, you will get:
A1: John
A2: Smith
A3: (formula) =A1 &” “ & A2
(Value) John Smith
But you can’t replace more than one value in the same cell. For example, if you have in
A1: ##Client##FirstName ##Client##LastName, this will not be replaced correctly. To
solve this case, you need to create a new calculated field in the dataset that contains the
two others concatenated, and use this field in the template.
Note: from v2.1, you can also use the Values property instead of defining a published
prop.
Another thing to take in count, date and time fields. They will be passed as a number to
Excel (there is no variant to represent a date/time). So you must format the cell
including the date (for example ##Client##SaleDate) with a date format. Note that
when programming the component I’ve tried to pass Field.value to this cells (it passes a
string), and it works without needing to format, but has problems with international
representations.
Besides cells, you can also stream Images (only in native mode) or comments to the file.
For comments, just write the usual ##Dataset##Field in the comment.
For images, drop a Blank image, select it, and in the names combo, change its name to
##DataSet##Field##ImageType
where ImageType is JPEG or PNG. Verify that the image format in the database is one
of them, or convert them as needed. For more info, see the demo app.
