| Chapter 4 Report Templates |
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.
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.
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.
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.
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 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.
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 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.
Microsoft Excel will break pages automatically. You can manually insert horizontal or vertical page breaks.
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 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.
You can use these formatting features of Microsoft Excel to effectively display your data.
To make text stand out, you can format all of the text in a cell or selected characters. You can set the font, color, and alignment of text.
The data in a column is often very narrow while the label for the column is much wider. Instead of creating unnecessarily wide columns or abbreviated labels, you can rotate text and apply borders that are rotated to the same degree as the text.
To distinguish between different types of information in a worksheet, you can apply borders to cells, shade cells with a background color, or shade cells with a color pattern.
You can use number formats to change the appearance of numbers, including dates and times, without changing the number behind the appearance. The number format does not affect the actual cell value that Microsoft Excel uses to perform calculations.
The conditional format is a format, such as cell shading or font color, that Microsoft Excel automatically applies to cells if a specified condition is true.
The style is a combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.
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.
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.
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.
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.
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 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.
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.