Using 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.

INDEX

FlexCelReport
INDEX
Introduction
How to use
1- Creating the Excel Template
Now, It's time to add the ranges
2- Creating the Delphi Datamodule
Tips and Troubleshooting

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)
Yo can create it directly on Excel, or just create a blank sheet, assign it to the FlexCelReport, and double-click it to access a Template Editor. From there you can drag and drop the fields instead of writing them.
Now you have to write:

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.

1. 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
2. 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_Values:_TFlxPropList;\||] 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.

Now, It's time to add the ranges

You have to define one named range for each dataset you want to use, with the name _<DataSet>_ For example, if you want to populate the dataset "Items" in the range B19:E19 you should:
Go to Excel and select Insert->Name->Define from the menu.
Create a "_Items_" range for B19:E19
Now you can select it from the combo box.

Note that there are 2 "", not one. It is "<DataSet>", not "<DataSet>_"

This range will be copied once for each entry of the Dataset, with the values replaced. It is possible to create as many ranges as you want inside others, to reflect master-detail relationships. (For example, you may have the Cust range covering all used range of the sheet, and then a Orders range inside and a Items inside the Orders

When the report is run, the _Cust_ range will be copied as many times as records are in the Cust Dataset, and for each copy, the range _Orders_ will be copied as many times as records are in the Order dataset, for the corresponding value of the _Cust_ Dataset. Same way, the _Items_ range will be copied in each _Orders_ copy, as many time as entries are in the Items dataset, for the corresponding Orders and Cust entry.

Range notes

Note 1: Don't intersect ranges, or you are going to get strange results. Each range should be either completely inside or completely outside the others.
Note 2: The ranges affect the entire row that will be copied. Never put one range to the right or left of the other.
Note 3: It is possible to define some _XX_ ranges that don't correspond to any dataset. These ranges are not going to be copied, but values will be substituted
Note 4: Always define some Range that includes all the others, so the component knows where to make the replacements. FlexCelReport always will look for the biggest range to begin replacing values. I usually call this range _MAIN, but you can call it as you want, as long as it begins with "" and ends with "_"
Note 5: From v2.2, you can have multiple ranges in the way "_db1db2db3_ …_dbn_" and all of them will be propagated. See the side-by-side demo for more info.

Blank Rows

A problem that happens with Excel and that I have not been able to completely solve is related with the way you insert the cells.
Imagine you define the following Template:

And let be the _Items_ range = A2:B2
When the report is run it will insert n-1 cells between rows 2 and 3, where n is the number of records in the Items dataset. But the formula in B3 ( "=Sum(B2:B2)" ) will not change to reflect the inserted rows. You will get something like this:

As a rule of thumb, to solve this problem you should always leave a blank line below the ranges to let the formulas adapt. You should define a template like:

With the _Items_ range defined as A2:B2. Now, when the rows are inserted between rows 2 and 3, the formula now in B4 will be updated right.
You can leave the template this way (by making the row 3 very small), or if you want to get rid of the blank line, you could use the "...delete row...", as in the image.

All the rows that have in the "A" column the text "…delete row…" (without any spaces an all in lowercase) will be deleted after the report is run. An use of this is if you want to create a Pivot Table or a Graph , and you can see it in the PivotDemo.xls template.
Note: With The standard configuration, Excel XP automatically replaces the 3 dots "..." with an ellipsis char "…". Uf. But even when they look the same they are not the same!!! If you are having problems with FlexCel understanding "delete rows", check this.

2- Creating the Delphi Datamodule

Now let's go with the other part, creating the app that will generate the reports. First, you define a Datamodule (not necessary, it could be a Form too) where all the data to the reports will be. In my experience, it's better to have all the queries for the reports different from the ones for the rest of the app.
Then you have to add the datasets, one for each in the template, and with the same names. Case is not important. After that, define all the calculated fields.
Then, it's time to define the properties (for the cells including "#.<Variable>") They should be published and return a variant. Of course that variant may be a multidimensional array of variant. (for the cells including "#Variable#.index#.index …. #.index)
If you want to create a multisheet report, you can assign one dataset to the [PagesDataSet] property, and the active page will be copied (and filled) for each record of the dataset. Warning: In OLE mode don't use a large dataset to populate the sheets, or it will take forever to fill (and it will be very difficult for the user to navigate between all the sheets!) I would advice that the dataset should have no much more than 10 records. If you are using Native mode, you can make it larger, but don't abuse.
The database transactions should be Snapshot or equivalent if possible, because this will allow the data to remain the same during all the process.
Now drop a TFlexCelReport and a TXlsAdapter or a TOLEAdapter in the module. Fill in the properties, and that's all. Whenever you want to generate the report, call the "run" method of the component.

Tips and Troubleshooting

Here are some tips taken from the mistakes I make more often. Feel free to add your own

• When you are having trouble with a template, first thing you should do is right-click the template and choose "Check Template" to see if there are warnings.
• Properties referenced in the template must be of variant type and published.
• Excel XP automatically replaces "..." (3 dots) with "…" This is one character only, and even when they look similar, FlexCel won't recognize it when used in "…delete row…" or "…page break…". I recommend to turn off this "feature" in Excel. (Go to tools->Autocorrect options)
• Do not create data ranges that intersect, like _d1=a1:a5 and __d2_=a3:a7. You will be asking for trouble. See [Range notes|#_Notes]
• Be careful when creating formula that refers to inserted rows, always leave a blank row so the formula is updated. See [Blank rows|#_Blank_Lines].
• If you see 'garbage' data at the end of the report, or if not all the data is present, it may be a problem of the recordcount property. See [CalcRecordCount]
• Beware with the dates! They will be passed as a number to Excel, so the cells in the template must be formatted with a date/time format.
• For reports in general, be sure to use a 'snapshot' or similar transaction mode, or the data may be changed between the first record that goes to Excel and the last.
• When running the demo, you might end up with some files *.mb. These are temporary files from the BDE, not from the component!!! (The component doesn't create any temporary file)
• Always define a MAIN range ! See [Range notes|#_Notes]
• When you don't know which command or parameter to use, record a macro in Excel and see what it does. And don't forget you have the help on Visual Basic for Excel also.
• In native mode, when you use formulas, Excel will ask for saving a Worksheet saved with an earlier version. This really means that it will save the worksheet with the formulas recalculated. The only way to avoid this message is not to use formulas.
• You can use Spanish, French, Italian and many other languages. All FlexCel messages are saved in the files XlsMessages.pas and UFlxMessages.pas as resourcestrings. If you define "SPANISH", "FRENCH" "ITALIAN" or any of the languages listed on these units, messages will be translated. Also, if you want to translate them to your own language, you only need to look there. (if you do this, please send me a copy…I will include them in the next version)