Let's go

XL Report components allow you to create a variety of reports using one or more TDataSet instances, or its descendants. XL Report is based on the TxlReport component. This class contains the properties and methods required to access an Excel workbook and to transfer data to it. To get a report you must create a special Excel workbook that will serve as a template and will contain appropriate formatting as well as description of data transfer, placement and processing.. XL Report will find the template, create a new Excel workbook based on this template, fill report cells with the data of the datasets specified by you, and then open the Excel window. XL Report's properties and methods allow you to create reports simply and easily.

Quick Start

Where? QDemo.dpr form: frmQuickStart; unit: fQuickStart; template: tQuickStart.xls

For this example, we have included the frmQuickStart form in the QDemo.dpr project. We have also inserted two instances of TTable (tblCustomers and tblOrders) and two instances of TDataSource (dsrCustomers and dsrOrders) onto the form and bound them to Customer.db and Orders.db tables. Then we linked these tables (one?to?many relation) by setting the appropriate values to the MasterSource and MasterFields properties of tblOrders, indexing on the CustNo field. To view the data we have added an instance of TDBNavigator and several TDBEdit edit fields for the Customer.db table as well as an instance of TDBNavigator and the TDBGrid for the Orders.db table. To build the report and to edit its template, two command buttons (TSpeedButton) with Report and Template captions (Caption property) were placed on the form and both of the TTables were made active.

XL Report adds two nonvisual components, TxlReport and TxlReportG2 onto the Delphi component palette. We built all examples on TxlReport because TxlReportG2 is left for compatibility only and isn't recommended for use in your new projects. So an instance of the TxlReport component has been added to the form, which is named xlReport.

As stated earlier, to create a report you must first create a report template. To create the template for this example, double-click on xlReport or right-click on it and choose Edit template. Because Microsoft Excel is a component editor for TxlReport it will start with an empty workbook opened.

Note
The correct setting of the XLSTemplate property ensures the right workbook opening. If it is empty then XL Report launches Excel and opens a new empty workbook. If you omit the path to the template then at design-time XL Report will try to open the template in the same folder where .dpr file resides. At run-time XL Report will look for the template in the application start-up folder. You are also allowed to use a relative path name from the current directory, e.g. "..\Templates" or ".\Templates".

The first thing you will notice is that there are a lot of #NAME? error messages (if there isn't enough place for this text Excel replaces it with # signs). This is normal. Pick any such cell and look at the Formula bar. Almost all the formulas include the pattern =Alias_FieldName - we call such a pattern a field formula (the underscore is required) - where Alias is equal to tblCustomers or tblOrders and FieldName - one of field names of the dataset. Since Excel cannot find these cells in the workbook immediately it displays #NAME?.

You can apply any formatting to any workbook cells, insert pictures, and modify any of the parameters of the workbook itself. In this example, we have turned off the zero values display and hidden the gridlines (Tools|Options). XL Report will preserve all changes to the template.

To produce a report of all orders for the current customer we created a specially formatted set of cells - B11:L15. Looking at the design of the report you can see that certain cells have been merged and some columns have been hidden. The hidden cells D15 and E15 contain the formulas- =tblOrders_ShipToAddr1 and =tblOrders_ShipToAddr2 respectively. The values of these cells are used in the formula of cell G15 - =""&D15&" "&E15 - to create the full shipping address. As a reminder - it is permissible to use all of the standard Excel formulas as well as functions, references, etc. This report includes a Special tax that is less than the "standard" Tax Rate by the value in cell L4. The formula for this Special tax rate - =I13-L$4 - can be seen in cell J14 (Note the use of $ character in referencing the L4 cell - this is an example of an absolute reference. See Excel Help for details).

We froze the top horizontal pane just under the report head (Window|Freeze Pane). Then we added colors to the report. To distinguish the orders honored by Visa cards, we applied conditional formatting to the G13 cell that contains the payment method - =$G13="Visa". To see this, select this cell and choose Format|Conditional formatting. So, for Visa orders, the field value will be displayed in a different color. The same trick was used for the Special tax cell. As the result, all negative values in this cell are shown in red.

As the final step in our work on the template, the A13:L16 range was named OrdersRange. You can find it in the Name box at the left of the Formula bar (to create the name we selected the cells and printed the name in the Name box). Select this name from the Name box and you can see this range selected on the worksheet. Note the empty column at range's left and the empty row below. These cells are used in XL Report to describe additional actions that used to manipulate the data. They have been left empty in this example.

Why A13:L16?
An Active XL Report range consists of three regions: the first one describes the output format for a single record of a dataset bound to the range, the second one is an additional row just below the first region (option row), and the third is an additional column at the left of the first region (option column). While processing the dataset, Active XL Report multiplies the first region as many times as needed to output the dataset. This description implies that header row(s) containing column labels must not be included in the range. Nevertheless, the header row must be placed above some of Active XL Report ranges. See the "List range" chapter below.

The workbook has been saved in the project folder. You can choose any other folder. We named the workbook tQuickStart.xls. You can choose any other name. Then we closed Excel and returned to our Delphi form.

There must be a link between the TxlReport component and the report template. To create the link we set XLSTemplate property of TxlReport to the path and file name of the template. If you set it to the file name only, the program will attempt to locate the template according to the following rule - at design time, the template must reside in the project folder and at run time it must reside in the app start folder.

To finish the design of the report the TxlReport component must be bound to datasets by using the DataSources property of the TxlReport. This property is a collection, each item of which represents a dataset. Two items have been added to the collection and their DataSet properties set respectively to tblCustomers and tblOrders. For the last one the Range property has also been changed by setting it to OrdersRange - the name of the range for the orders of the current customer. It is worth of mentioning that the Alias property takes the value of the Name property of a given Dataset automatically.

If you follow this process yourself, then clicking on Report in context menu of TxlReport will produce the report. To create a report at run-time add a line of code to the Click event of the Report button:

To allow your users edit the template at run-time we simply add the following line to the Click event of the Template button:

Test the app by building reports for several customers.

Note the report workbook name - it was created by adding serial numbers to the name of a report template.

Note
You can use the Options property to change the default behavior of XL Report. If Excel has been launched at the time you run the report building procedures, XL Report will use this instance by default. If you include roNewInstance in Options, XL Report will use the new Excel instance instead. If you exclude roOptimizeLaunc, you make XL Report release all Excel interfaces immediately after the report building. Here "To release the interfaces" does not mean "to unload the Excel process". The user must close Excel but roOptimizeLaunch affects the Excel process unloading. There is a feature in XL Report that forces the Excel interface that is releasing to ignore roOptimizeLaunch. In this case use the TxlReport.ReleaseExcelApplication class method that resides in the xlReport unit.

Creating the component dynamically

Where? QDemo.dpr form: frmQuickStart2; unit: fQuickStart2; template: tQuickStart.xls

To demonstrate the use of dynamically created TxlReport instances we took the previous form and deleted the TxlReport. Then we renamed the Template button in Report2 and rewrote Click events of both buttons. Report2 button as follows:

The two first lines of the code create an instance of TxlReport and bind it to the template. Then the new item is added to the DataSources collection and bound to tblCustomers dataset. Another item representing tblOrders is processed the same way. Data output of this item is aimed at the OrdersRange. Finally, the Report method builds a report.

This is the standard method of dynamic component creation. To shorten the code even more the XL Report team has developed several useful methods. Look at the code of the second button:

It's simple, isn't it?

Time to look back - quick start

To summarize the topics covered in this section:

• XL Report adds two non-visual components to the Delphi Component palette - TxlReport and TxlReportG2. The last one is left for compatibility only and not recommended for use in new projects.
• Creating a report means mostly "to create a template in MS Excel".
• Template editing and report building options are available at design time in the component's context menu.
• Special field formulas define data transfer.
• To transfer one record (namely the current one) of a dataset in a report, you have to add an item in the DataSources collection of TxlReport and bind it to the dataset.
• To transfer all records of a dataset in a report, you have to add an item in the DataSources collection of TxlReport and bind it simultaneously to the dataset and to the named range of the special format in the report template.
• XL Report preserves all the template cell formats in a report as well as the parameters of the worksheets and the workbook.
• To build a report, you have to place the TxlReport component onto a form, set the properties needed, and call the Report method.
• To edit a template at run time, you have to place the TxlReport component onto the Delphi form, set the properties needed, and call the Edit method.