Other options

In addition to range options and column options, XL Report processes several sheet options and report options. The first ones have an influence upon a worksheet, and the second, accordingly, on a report workbook. Both are used to automate getting the optimal height and width for cells, scaling to the installed printer, hiding some worksheets and protecting them from changes.

Sheet options

Where? QDemo.dpr form: frmOpts1; unit: fOpts1; template: tOpts1.xls

You need to specify the sheet options in the A2 cell of a given worksheet. When processing every worksheet of the template, XL Report checks that cell's value. When XL Report recognizes any options, it takes some additional actions. Here they are:

• OnlyValues - replaces all formulas on a worksheet with their values.
• Hide or SheetHide - hides a worksheet in a generated report workbook.
• RowsFit - makes the range rows fit the contents. It can be done manually - select the rows needed and double-click on the boundary between any of the selected rows.
• ColsFit - makes the range columns fit the contents. It can be done manually - select the columns needed and double-click on the boundary between any of the selected columns.
• AutoScale - sets up the page option Fit to 1 page wide by (empty) tall (this can be found in File|Page Setup on the Page tab).
• AutoSafe - protects a sheet with a random password. This corresponds to Tools|Protection|Protect Sheet menu command call and Contents box checking.

Note
Using the AutoSafe option together with clearing (checking) the "Locked" checkbox in a cell's properties, you can realize a flexible approach to user's modifications of a report. For instance, if you clear the "Locked" checkbox of a given cell in a template workbook and use the AutoSafe sheet or report option, then the user still will be able to change the contents of the cell in the report workbook.

The A2 cell will be cleared only if it contains any XL Report options. So you can use this cell for your own needs. This rule also refers to the A1 cell that contains report options. Some XL Report users, knowing that report options can be specified on any sheet, create a hidden worksheet with report options.

Report options

Where? QDemo.dpr form: frmOpts2; unit: fOpts2; template: tOpts2.xls

Report options should be placed in the A1 cell of any sheet (or sheets - in this case all the options supplied will count) of a template workbook. While processing every sheet of a template, XL Report checks this cell. When XL Report recognizes any options it takes some additional actions. Here is the options list:

• OnlyValues - replaces every formula in the report workbook with its calculated value.
• ShowPivotBar - turns on the Pivot table bar. This bar is hidden by default even if the report contains a pivot table. This panel allows the user to edit the Pivot table. See Excel Help for additional information.
• AutoSafe - protects a report workbook with a random password. This corresponds to the Tools|Protection|Protect Workbook menu command with the Structure check box on. The use of AutoSafe on report levels completely protects a report workbook because the Tools|Protection|Protect sheet menu command is applied to every worksheet in the workbook.

Visualize report generation

Where? QDemo.dpr form: frmEvents1; unit: fEvents1; template: tEvents1.xls

Analytical reports can contain lots of data. So, report generation can take a lot of time. To reassure the user that the program is progressing appropriately, we recommend you use the OnProgress property of the TxlReport class declared as follows:

Report generation consists of several steps. You get the number of steps in the Max parameter. The current step is kept in the Position parameter. By using the TProgressBar component named pbReport, you can process the event this way:

Note the assignment statement pbReport.Max:=Max. The report generation algorithm consecutively parses a template. This means that at some stage the total amount of steps needed is unknown. For example, no one can know before list range option processing if there is a need to create a pivot table. That's why the Max value can change in run-time.

OnBeforeBuild and OnAfterBuild events

Where? QDemo.dpr form: frmEvents2; unit: fEvents2; template: tQuickStart.xls

XL Report can open and close the datasets engaged in report building automatically. The TxlReport instance has the Options property which roAutoOpen and roAutoClose inhabit. If they are True, then all unopened datasets will be opened before report building and, accordingly, they will be closed immediately after report building. Since parameterized queries are often used, however, you will need to initialize these parameters. We recommend you use the OnBeforeBuild event of TxlReport class or the OnBeforeDataTransfer event of DataSources collection item (TxlDataSource class):

The TxlReport and TxlDataSource classes both have the Tag property, which is usual in Delphi and often used to distinguish between components in a common event handler. If using this property the OnBeforeBuild handler looks like the following:

The form used as example in this chapter shows a list of clients from the Customer.Db table. The report goal is to show all orders for a given client. So we created the query whose CustNo parameter is initialized in this event handler. The DataSources collection item containing tblOrders dataset has rgoAutoOpen and rgoAutoClose flags that were set to True. So report building passes through the following stages. First, in the OnBeforeBuild handler, the CustNo parameter of the query is set equal to the value of the same field in the client table. Then the rgoAutoOpen flag is handled - the query is opened and the report is built. After report generation rgoAutoClose is handled - the query is closed. We recommend you use this approach to minimize the need for system resources.

TxlReport.Options - options of a report

While working on previous examples, you might run into a property that follows VCL fashion. Some of the Options' flags are complete analogs of sheet and report options of a template; others are accessible in the Delphi Object Inspector only.

Options property flag		Description
xroAddToMRU		True - every template opened at design time, will be added to the Excel list of recently used workbooks. Useful in case of a large number of templates. Default - false.
xroAutoClose		True - automatically closes datasets engaged after report generation. If false - all datasets remain open. Default - false. Attention! This flag change causes the same change in the rgoAutoClose flag for every item in the DataSources collection.
xroAutoOpen		True - automatically opens datasets engaged in report generation if they are closed. If false and a dataset is not open - an exception is raised. Default - true. Attention! This flag change causes the same change of rgoAutoOpen flag for every item in the DataSources collection.
xroAutoSave		True - report workbook is saved via the Workbook.Save method called immediately after generation. If false and roDisplayAlerts is included - Excel raises an exception. Default - false.
xroDisplayAlerts		False - suppresses Excel prompts and alert messages while a macro is running. Can be found in Excel.Application properties. Default - true.
xroHideExcel		True - lets you build a report leaving Excel processes invisible. Can work only if the Excel process is created by XL Report. To determine the path to save the report, use the roUseTemp flag and TempPath property. After being built, the report is closed immediately. Default - false.
xroNewInstance		True - XL Report app creates a separate Excel process. False - XL Report app uses an existing Excel process or launches it anew. Default - false.
xroOptimizeLaunch		To optimize the number of Excel launches. If true, Excel is unloaded only after the application is closed, thus preventing Excel from continually launching while working with XL Report. Default - true. Call the TxlReport.ReleaseExcelApplication method to unload Excel manually.
xroSaveClipboard		XL Report makes use of the Clipboard extensively. This option allows you to preserve its contents. But in some rare cases this can lead to inadequate behavior of Excel objects. It shows itself in wrong formatting of a report. For instance, working on our documentation we had a chance to see that the use of the Clipboard by Adobe PhotoShop causes the PasteSpecial method to work not in a way described in Excel documentation. If you ever come across such situations, please inform us and try to set this option to False (Default).
xroUseTemp		True - a report workbook is generated with the file name in the stencil Template-workbook-file-name & date & time & .xlrtmp. Such files are deleted while the app is closing. The folder name to keep these files is provided in the TxlReport.TempPath property. BTW, this is the only use of the property. If TempPath is empty, the temp files will be created in the application's start folder. False - the temp files will be created in the default working folder (General tab in Tools|Options dialog) and will be named according to Excel naming conventions. Default - false.

TxlDataSource.Options

DataSources collection items have their own Options property. In contrast to TxlReport.Options, this property relates to a given dataset that is bound to the collection item or to the given range into which the data is put. Here they are.

Options property flag		Description
xrgoAutoClose		True - automatically closes the dataset associated with the DataSources collection item after report generation. If false - the dataset remains open. Default - false.
xrgoAutoOpen		True - automatically opens the dataset associated with the DataSources collection item if it is closed. If false and a dataset is not open - an exception is raised. Default - true.
xrgoPreserveRowHeight		Keeps the height of rows in a report equals to the height of corresponding rows in a template. Default - True.

Add a report to an existing workbook

Where? QDemo.dpr form: frmAddF1; unit: fAddF1; template: tAddF1.xls+tExistingBook.xls

This example adds the Sheet1 worksheet to the tExistBook.xls. If the Sheet1 worksheet already exists in the target workbook the added worksheet will be renamed according to Excel naming convention. Thus Sheet1 will become Sheet1(1). This is done using the following method:

The WorkbookName parameter allows you to specify the workbook that receives the report's worksheet. The NewWorkbookName parameter allows specifying the name of the workbook, which will receive the final report. The following examples demonstrate the logics of the method:

The first example causes creation of a report and saving it in NewBook.xls, while the second one creates a report, adds it to WB1.xls, and saves the resulting report in NewBook.xls.

XL Report creates a report going through usual chain of methods and events, and adds all non-hidden worksheets of the report workbook to the workbook specified. The OnlyValues option will be applied to every worksheet added. And more, all names referencing the worksheet added will be destroyed. The report worksheets are added after the last existing worksheet.

XL Report fires an exception if the WorkbookName file doesn't exist and keeps silence if the NewWorkbookName file exists.

Several reports in one workbook

Where? QDemo.dpr form: frmAddF2; unit: fAddF2; template: tAddF2.xls.xls

XL Report contains the MergeReports class method that allows you to merge several reports in one workbook.

You should pass an array of TxlReport instances - the reports that you want to be merged - to the Reports parameter. Every process of report generation in this case is isolated from other report generation processes so you will not be bothered with existing event handlers for every report or with macros in their templates. After processing the current report, the OnlyValues option will be applied to its visible worksheets and every name (the Name object) will be deleted from them (in order to prevent probable name conflicts). All the reports will be added to the first instance of TxlReport in the Reports array. Every worksheet will be added with its own name prefixed with the prefix taken from the SheetPrefixes parameter. You should ensure that both of the arrays have equal number of elements.

Custom options - the XLOptionPack technology

XL Report supports the XLOptionPack technology that allows the developer to create option packages. This technology bases on the architecture of XL Report, which permits expanding the space of options due to specially designed additional components. These components must be descendants of the TxlOptionPackage component class that encapsulates the mechanisms required in option package creation. Every option in such a package must descend from the TxlOption class. You can find detailed information on creating custom options and option packages in "XLOptionPack Developer's Guide".

Time to look back - other options

Please remember that:

• Along with column and range options, there are sheet and workbook options.
• Sheet options must be placed in the A2 cell of a given worksheet.
• Report options must be placed in the A1 cell of any worksheet or worksheets of a workbook.
• Using these options, you can: hide some sheets, protect sheets or the workbook, and substitute formulas with their values.