Unbound data transfer

Home Products Downloads Registered users Support Prices Order Primary Subscription

<< Previous

Table of contents

Next >>

Working with XL Report you can build your reports using data from different sources and not necessarily datasets. G2 is based on an imported Excel Type Library. It means that you have all the power of Excel's published interfaces. The Excel Type Library will help you to build reports based on sources other than the TDataSet. You can use it in event handlers such as AfterBuild or AfterDataTransfer. But XL Report gives you much more simple mechanisms as well.

Report parameters

Where? QDemo.dpr form: frmUData1; unit: fUData1; template: tUData1.xls

Similarly to creating parameterized queries through the use of property, you can create parameterized reports through the use of the Params property of TxlReport. This property is a collection whose items expose only two properties: Name of a string type and Value of a Variant type. Having any number of items added to the collection, you are allowed to use a field formula on the XLRParams_ParamName pattern anywhere in your report. Here XLRParams is a constant part of the formula and ParamName is to be changed according to your needs.

We have added the StartDate parameter of a Data type to the XL Report component and written the XLRParams_StarDate formula in one of the template cells. We initialize the parameter with a value of the DateTimePicker component previously added to the form before calling the Report method:

xlReport.ParamByName['StartDate'].Value := edStartDate.Date;
xlReport.Report;

Consider the Params to be just another NoRange-dataset whose Alias is always "XLRParams" or as unbound NoRange-dataset.

Unbound data in ranges

Where? QDemo.dpr form: frmUData2; unit: fUData2; template: tUData2.xls

In order to transfer a lot of unbound data you use another XL Report feature - an unbound Range-dataset.

We have created a new form and added a StringGrid with 4 columns. Let us assume our data of four different types are kept in this grid. The first column will contain strings, the second - integer values, the third - real, and the fourth - DateTime values. The item was added to xlReport.DataSources whose Dataset property was left empty, Alias was named as "Grid", and the "UnboundRange" string was put in the Range property. A template workbook was created with the four-column UnboundRange; the field formulas were: Grid_StrValue, Grid_IntValue, Grid_FloatValue, Grid_DateValue. Here we have assumed that those are the field names (StrValue, IntValue, and so on). Look, the process is the same as in case of regular Range-dataset - the only difference is emptiness of the Dataset property.

When this property is not empty, XL Report has all the metadata needed to create a report. If it is empty, it is you who must supply XL Report with data and their structure: the number of fields, their names, and their types. There are three events that will help you to provide this.

property OnGetDataSourceInfo: TxlGetDataSourceInfo;
property OnGetFieldInfo: TxlGetFieldInfo;
property OnGetRecord: TxlGetRecord;

type

TxlGetDataSourceInfo = procedure (DataSource: TxlDataSource;

var FieldCount: integer) of object;

TxlGetFieldInfo = procedure (DataSource: TxlDataSource;

const FieldIndex: integer;
var FieldName: string;
var FieldType: TxlDataType) of object;

TxlGetRecord = procedure (DataSource: TxlDataSource; const RecNo: integer;

var Values: OLEVariant; var EOF: boolean) of object;

At first, XL Report triggers the OnGetDataSourceInfo event, allowing you to define the number of fields. In our example it was:

procedure TfrmUData2.xlReport1DataSources0GetDataSourceInfo(

DataSource: TxlDataSource; var FieldCount: Integer);

Begin

FieldCount := strGrid.ColCount;

end;

Then you supply XL Report with field names and types:

procedure TfrmUData2.xlReport1DataSources0GetFieldInfo(DataSource: TxlDataSource;

const FieldIndex: Integer; var FieldName: String; var FieldType: TxlDataType);

begin

FieldName := strGrid.Cells[FieldIndex, 0];
case FieldIndex of

0: FieldType := xdString;
1: FieldType := xdInteger;
2: FieldType := xdFloat;
3: FieldType := xdDateTime;

end;

This event handler is called FieldCount times. FieldIndex starts from 0. XL Report follows Excel, so the FieldType accepts only following values:

type

TxlDataType =

(xdNotSupported, xdInteger, xdBoolean, xdFloat, xdDateTime, xdString);

At last, you supply XL Report with your data in OnGetRecord event handler.

procedure TfrmUData2.xlReport1DataSources0GetRecord(DataSource: TxlDataSource;

const RecNo: Integer; var Values: OleVariant; var EOF: Boolean);

begin

EOF := RecNo > (strGrid.RowCount - 1); // exclude fixed row
if not EOF then begin

Values[0] := strGrid.Cells[0, RecNo];
Values[1] := StrToInt(strGrid.Cells[1, RecNo]);
Values[2] := StrToFloat(strGrid.Cells[2, RecNo]);
Values[3] := StrToDate(strGrid.Cells[3, RecNo]);

end;

RecNo contains the record number of the current record and stars from 1. If your data comes to the end, you set EOF to true. Values is a 1-D variant array with the number of items equal to the number of fields, each item initialized with UnAssigned.

There is one restriction to the use of unbound data: You cannot use them in master-detail and multiple-sheet reports.

<< Previous

Table of contents

Next >>