<<1>> Employee:ByName  // Quick start

  Quick start

For creating simple reports you don't have to define the Bands property.
EXLReport defines the position of the bands in the template automatically
in the way described below.

The beginning of the data band (Master Data) is considered if the fields
of the data source, set in the DataSet property, are found in the template
row. The band continues until the band of the report summary.

The position of the report summary band (Summary) is considered if
the summary fields are found (the fields, containing EXLReport function; see
example 'User fields'). All not empty rows found until the end of
the template are included into the band. That's why if a row in the end
of the template contains formatting, but does not contain data, you should
enter some data - e.g. space - to one of the row cells, if you want this
row to be included into the report.

The report title band (Title) starts from the first template row and
continues until the data band.

All the bands, created automatically, have the same width, determined by
the last not empty column of the template.

If property DataSet is empty, the result report consist of only one band -
Title, which occupies the whole template (see example 'Label report').

<<2>> Employee:ByName  // Label report

  Label report

If you don't set the DataSet property of the component, then on automatic
band creating only one band is created, which occupies all the template,
and the report represents a copy of the template, in which fields,
relating to some data set, are replaced by the corresponding field values
in the current data set position (see the example of using fields in 'User
fields').

<<3>> Employee  // Summary report

  Summary report

For receiving summary reports you don't have to define the bands either.
If you still create bands manually, you don't have to create a data band
(Master Data), otherwise you have to set an empty value for its Range
property.

<<4>> Employee:ByName  // User fields

  User fields

In the EXLReport templates you can use user report fields as well as data
set fields. For calculating values of the user fields you should write
handler for the OnGetFieldValue event, or use the Dictionary collection.

The field in the template is indicated by the character, defined in
the FieldSign property. The field name may contain letters of the English
alphabet, full stop character, underline character, numbers, and round
brackets. To use other characters in the field name, you should enclose
the field name in quotation marks or apostrophes. For example,
  #Name1  - permissible,
  #Form.Table.Field  - permissible,
  #"User field" - permissible,
  #Form. Table. Field  - not permissible.
On the analogy of the apostrophe character in Delphi, if you want to use
in the template a FieldSign property character, which would not indicate
the EXLReport field, you should enter two such characters one after another.

If you enter an Excel formula, which uses EXLReport fields as arguments, to
the template cell, and an Excel error 'Error in formula' occurs, then you
should insert a field sign before the equality symbol ('=') - this symbol
will be deleted on formatting the report.

The fields from the data set must have the following format:
 <Form component name>.<Dataset component name>.<Table field name>.
If EXLReport component and the data set component are placed on the same
form, then you don't have to provide the form component name. For
the fields of the data set, defined in the DataSet property, the data set
component name is also not necessary.

In the summary bands (Detail Footer, Group Footer  Summary) five
aggregate functions can be executed over the EXLReport fields: SUM, AVG,
MIN, MAX, and COUNT. These functions correspond to the corresponding Excel
functions. The functions should be set in the following format:
 #<EXLReport function>(<EXLReport field>),
 e.g. #SUM(Salary).

No spaces are permitted between the bracket and the field name. Do not set
a field sign before the field name in brackets. Expressions as function
arguments are not permitted.

If property Options contains element qxoTotalsAsFormula, the result of
calculating the function is the corresponding Excel formula (if the detail
bands contain the corresponding field also), otherwise it is a number.

The sequence of searching the field value:
1. Dictionary collection (useful for setting constant values of the fields).
2. Data sets. The searching is held with consideration of the form and
data set component names. If they are not specified, the field is searched
in the DataSet component first, then in the detail dataset.
3. OnGetFieldValue event. If the field value is not found, the exception
is raised.

<<5>> Employee:ByName  // Formatting cells in program

  Formatting cells in program

Use OnFormatCell event for formatting the sheet cells of the report.
The following parameters are passed to the event handler: the current
band, row and column numbers of the current template and report cells,
the cell value, and the interface ExcelWorksheet, using which you can
change the cell format.

It is necessary to consider, that in contrast to the events
OnGetFieldValue and OnSetCellValue, in the moment of the OnFormatCell
rise the position of the current record in Master and Detail data sets is
not defined. That means, you can't use the field values of these data sets
in the event handler.

Note. Formatting in the OnFormatCell event handler works slower that
the Excel conditional formatting.

<<6>> Employee:ByName  // Conditional formatting cells

  Conditional formatting cells

To speed up the report building it is recommended, if possible, to use
the Excel conditional formatting (menu item 'Format | Conditional
formatting...') instead of the OnFormatCell event handler.

<<7>> Clients.dbf  // Row height

  Row height

The default row height in the report is defined in the following way: if
the template row height is equal to the row height, acquired after
executing 'Format | Row | AutoFit', then for all the corresponding report
rows autofit is also executed, otherwise the report row height is defined
by the template row height. Thus, if you need the height of the report
row, containing wrapped cells, to be defined automatically, you should
execute autofit for the corresponding template rows.

The current example shows how to make the cell text not to be cut, and
make the minimum row height 21 points. For cells A3:D3 of the template
'Wrap text' is on, and the vertical alignment by center is selected; for
row 3 the autofit is executed. For correcting the row height
the OnFormatCell event handler is used.

<<8>> Clients.dbf  // Changing cell data

  Changing cell data

The EXLReport component has the OnSetCellValue event, which allows you
to change the content of the report cells. This event is used, when it is
necessary to execute some action on a quantity of cells, e.g. to change
the cell character case to upper case, as shown in this example.

<<9>> Employee:ByName  // Totals

  Totals

To receive totals in the EXLReport reports use EXLReport functions SUM, AVG,
MIN, MAX, and COUNT (see example 'User fields').

If these functions do not allow achieving the required result, you can
create your own user field, and give the necessary Excel function to it
in the OnGetFieldValue event handler. For the same purpose you can use
events OnSetCellValue and OnFormatCell.

If you use localized Excel version (not English), and the names of
the Excel functions differ from their English analogues, then you should
use the localized function variants in the OnGetFieldValue and
OnSetCellValue event handlers.

To be independent from the Excel version, you can use the OnFormatCell
event handler. In this handler give the necessary Excel function in
its English variant to the property Range[].Formula of the Report
parameter. Using this possibility is shown in the current example.

In EXLReport there are the following limitations, which influence receiving
totals if the totals are represented as Excel formulas (property Options
contains element qxoTotalsAsFormula).
1. Maximum string length in the Excel cell - 1023 characters.
2. Maximum quantity of areas for calculating functions - 900.

That's why if one of these requirements is not met (it is possible, if
the content of the report is large, and the data band breaks or consists
of several rows), the formula of the report total is substituted with
a number.

<<10>> Employee:ByName  // Totals in data band

  Totals in data band

Sometimes it is necessary to perform calculations with the report totals
in the detail row. In this case you can either calculate the total value
programmatically first, and then give it to the user field; or create
usual total EXLReport field in the Summary band, then in the OnFormatCell
event handler for the cell, containing the created summary, give the Excel
formula - reference to the created summary - to the FormulaLocal property
of some Title band cell, and in the detail row calculations use this Title
band cell. The second variant is shown in the current example.

<<11>> Customer:ByCompany Orders:CustNo  // Master - detail

  Master - detail

For creating master - detail reports you should define the position of
the bands manually.

The MasterData band is displayed in any case, and
the DetailHeader/DetailFooter - only if there are records in the detail
data set.

<<12>> Events  // Groupping

  Groupping

For creating reports with groups you should also define the position of
the bands manually.

As a grouping criterion you can set either a field of the DataSet property
component (the FieldName property of the GroupHeader band), or write
handle for the OnCalcExpression event of the GroupHeader band. The current
example shows the second variant.

The GroupFooter band is linked to the GroupHeader band with the FooterBand
property of the GroupHeader.

<<13>> // User data

  User data

If rows are taken to the report from the source different from
the TDataSet component, you can use events OnDataXXX for building
the report.

<<14>> Employee:ByName  // Change sheet properties

  Change sheet properties

To change the parameters of the finished report sheet, execute the Excel
macro, etc. you can use the OnAfterBuild and OnFormatCell event parameter
Report of the ExcelWorksheet type.

This example shows how to change the name of the Excel worksheet and
execute the Excel macro.

<<15>> Employee:ByName  // Multi-sheet

  Multi-sheet report

To create a multi-sheet report, you should use as many ExcelReport 
components, as there are sheets in the report.

For one of these components, let's call it the main, it is necessary 
to define the values of the Template and TemplSheet properties. Then 
using the SubReport property of the main component you should link it 
to the other ExcelReport component. In the same way set the SubReport 
property of the second ExcelReport component to the third one, etc.

There is no need to define the Template property for components other 
than the main, but the TemplSheet property is to be defined for all 
the components.

All other component properties are set just as for the usual single-sheet 
reports.

To view the report you should call the Show method of the ExcelReport 
component.
