Chapter 4 Report Templates

About Reports

The report generated by XLReportGen is a Microsoft Excel workbook that contains one or more worksheets. The layouts, formats and styles of report are defined by a report template, and the data of report are got from databases such as Oracle, DB2.

About Report Templates

To make a report using XLReportGen, you should create a report template first. This report template is a Microsoft Excel workbook that defines the layouts, formats and styles of report. In the Microsoft Excel report template, you can input static contents such as titles, descriptions, comments, a cover, a company logo, format the static content, and define the format of cells you will fill data into.

XLReportGen will generate the report based on the report template file. All static contents, layouts, formats and styles defined in the report template file will be brought to the final report file.

Excel Basic Concepts

If you have known these concepts of Microsoft Excel, please skip this section. For more detail information about Microsoft Excel, refer to Microsoft Excel Help.

Workbooks and Worksheets

A Microsoft Excel workbook is a file that contains one or more worksheets, which you can use to organize various kinds of related information. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from more than one worksheet. When you create a chart, you can place the chart on the same worksheet as its related data or on a separate chart sheet.

Worksheet is the primary document that you use in Microsoft Excel to store and work with data. It also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.

Ranges

A range represents a cell, a row, a column, or a selection of cells containing one or more contiguous blocks of cells. The cells in a range can be adjacent or nonadjacent.

Formulas

Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). A formula can contain any or all of the following: functions, references, operators, and constants. You can perform calculations using formulas.

Names

A name is a word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy to understand names, such as Products to refer to hard to understand ranges, such as Sales!C20:C30.

Headers and Footers

Headers and footers are areas in the top and bottom margins of a worksheet. You can add a header and footer on each worksheet. You can insert a page number, date and time, graphic, file name in a header and footer, and change the font in header and footer text. You can have only one custom header and one custom footer on each worksheet. If you create a new custom header or footer, it replaces any other custom header or footer on the worksheet.

Page Breaks

Microsoft Excel will break pages automatically. You can manually insert horizontal or vertical page breaks.

Drawings, Pictures and Diagrams

You can add graphics to your worksheets and charts to make them more visually appealing, to create eye-catching reports, or to add emphasis. For example, you can display a logo on your worksheet, create a flowchart, and use graphics in chart data markers. You can make your worksheet interactive by using graphic objects as hyperlinks or by assigning buttons to macros.

Charts

Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. To create a chart, you must first enter the data for the chart on the worksheet. Then select that data and create a chart. A chart is linked to the worksheet data it's created from and is updated automatically when you change the worksheet data.

Formatting

You can use these formatting features of Microsoft Excel to effectively display your data.

Table Reports

About Table Reports

A table is made up of rows and columns of cells that you can fill with text and graphics. Tables are often used to make reports, and organize and present information.

XLReportGen supports two types of table reports: fixed table report, variable table report.

Fixed table report: The number of rows and columns in the table is fixed. When XLReportGen executes a SQL statement, directly puts the result data into the cells in the table.

Variable table report: The number of rows or columns in the table is unfixed, and it is variable as the number of result records. When XLReportGen executes a SQL statement, it repeats the table rows or columns for each record or group, and then puts data into the cells of table.

Creating a Worksheet for a Fixed Table Report

For a fixed table report, you need to create a worksheet in the report template file according to the report. The format of worksheet is the same as the format in the report, but the cells that will be filled data into are blank. When XLReportGen executes a SQL statement, the data from data source will be filled into these cells.

Excel Report - Fixed Table

Creating a Worksheet for a Variable Table Report

For a variable table report, you also need to create a worksheet in the report template file according to the report. But you just need to define one or two blank ranges for one or two records in the worksheet. A range represents a single cell or a range of cells. For example, "A3:D3". XLReportGen will insert some ranges according to the number of records returned from data source, and fill data into the ranges.

Excel Report - Variable Table

The format of the last row/column border can be different from the others. For example, the outside borders used double lines, and the inside borders used single lines. To do this, you should define two blank ranges.

The ranges can be nested. The inside range is used for the detail data, and the external range is used for the group. XLReportGen will repeat the inside range for each record, and repeat the group range for each group.

Formatting Cells for Pictures

To enhance the visual impact of your report, you can insert pictures into your report. XLReportGen supports many popular graphics file formats: bitmap, JPG, GIF, PNG, TIFF and so on. For the graphics file formats XLReportGen supports, refer to Microsoft Excel Help.

You should store the path and name of the graphics files in the database, and identify the image fileds in the report function. XLReportGen will read the graphics files, and insert them into the cells in the report file.

To specify the positioning option and size, you should write a formatting expression into the cell in the report template file. XLReportGen will get the text of cell, and insert a picture into the cell according to the instruction in the format expression. The format expression for pictures as follows:

[placement] [size]

The placement specifies the positioning option, and can be one of the following values. The default value is MNS.

Values Description
MAS Move and size with cells.
MNS Move but don't size with cells.
NMS Don't move or size with cells.

The size specifies the size of a picture. Possible values are STRETCH, Wnnn or / and Hnnn. "STRETCH" means that the picture is resized to fit within the cell. "W100" means that the width of picture is set to 100 points. "H50" means that the height of picture is set to 50 points. The default means the original size. If you just specify the width or height of picture, not both, XLReportGen will retain the original proportions of picture when XLReportGen resize it.

Example

w84

Remarks

On the supposition that the original picture is size 144 x 168 points. XLReportGen will insert a picture, set the positioning option to Move but don't size with cells, set the height of picture to 72 points, and the width to 84 points.

Charts

About Charts

Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. You can use Microsoft Excel to add sophisticated, colorful charts in your reports. For example, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales. You can create a chart on its own sheet or as an embedded object on a worksheet.

Creating a Blank Chart

To create a chart in the report using XLReportGen, you need to add a chart in the report template file. The chart will be brought into the report file with the same chart type, display option, number format, titles, data labels and legends.

To add a chart in the template file:

1. Open the report template file using Microsoft Excel.

2. Enter the sample data for the chart on the worksheet.

3. Select that data and use the Chart Wizard to step through the process of choosing the chart type and the various chart options, or use the Chart toolbar to create a basic chart that you can format later.

4. Customize the chart. For example, change the chart type, colors, lines, fills, number formats, titles, data labels and legends in charts.

5. After you have finished the customization, delete data from the chart. You should keep a blank chart in the report template file.

For more detail information, refer to Microsoft Excel Help.