Ljzsoft XLREPORTGGEN User Manual

XLReportGen

User Manual

Version 3.8

2009-05

Copyright© 2003-2009 LJZsoft Corporation

All rights reserved

Contents

CHAPTER 1 INTRODUCTION...........................................................................................................1

1.1 OVERVIEW......................................................................................................................................1

1.2 FEATURES.......................................................................................................................................1

CHAPTER 2 INSTALLATION AND STARTUP...............................................................................5

2.1 SOFTWARE REQUIREMENTS............................................................................................................5

2.2 INSTALLING XLREPORTGEN ..........................................................................................................5

2.3 UNINSTALLING XLREPORTGEN .....................................................................................................5

2.4 COMMAND LINE .............................................................................................................................6

CHAPTER 3 QUICK START...............................................................................................................8

3.1 LEARNING HOW TO USE XLREPORTGEN.........................................................................................8

3.2 SAMPLE DATABASE ........................................................................................................................8

3.3 STEPS OF REPORTING......................................................................................................................9

3.4 MY FIRST REPORT ........................................................................................................................10

3.4.1 Creating a report template...................................................................................................10

3.4.2 Creating an XRF file............................................................................................................11

3.4.3 Configuring the report.........................................................................................................11

3.4.4 Inputting a function..............................................................................................................11

3.4.5 Understanding the function..................................................................................................12

3.4.6 Running an XRF file.............................................................................................................12

3.4.7 Opening a report..................................................................................................................13

3.4.8 Modifying the report template..............................................................................................13

3.4.9 Modifying the function.........................................................................................................13

3.4.10 Generating the report again...............................................................................................14

3.5 SAMPLES.......................................................................................................................................15

CHAPTER 4 REPORT TEMPLATES ..............................................................................................17

- I -

4.1 ABOUT REPORTS ..........................................................................................................................17

4.2 ABOUT REPORT TEMPLATES.........................................................................................................17

4.3 EXCEL BASIC CONCEPTS ..............................................................................................................17

4.3.1 Workbooks and Worksheets.................................................................................................17

4.3.2 Formulas..............................................................................................................................18

4.3.3 Names...................................................................................................................................18

4.3.4 Headers and Footers............................................................................................................18

4.3.5 Page Breaks.........................................................................................................................19

4.3.6 Drawings, Pictures and Diagrams.......................................................................................19

4.3.7 Charts...................................................................................................................................19

4.3.8 Formatting...........................................................................................................................19

4.4 TABLE REPORTS ...........................................................................................................................20

4.4.1 About Table Reports.............................................................................................................20

4.4.2 Creating a Worksheet for a Fixed Table Report..................................................................21

4.4.3 Creating a Worksheet for a Variable Table Report.............................................................21

4.4.4 Formatting Cells for Pictures..............................................................................................22

4.5 CHARTS ........................................................................................................................................24

4.5.1 About Charts........................................................................................................................24

4.5.2 Creating a Blank Chart........................................................................................................24

CHAPTER 5 REPORTING WITH XLREPORTGEN.....................................................................25

5.1 CREATING AND OPENING XRF FILES ...........................................................................................25

5.1.1 About XRF files....................................................................................................................25

5.1.2 Create a new XRF file..........................................................................................................25

5.1.3 Open an XRF file..................................................................................................................25

5.1.4 Save an XRF file...................................................................................................................25

5.2 CONFIGURING FILES.....................................................................................................................26

5.2.1 About files.............................................................................................................. ..............26

5.2.2 Configuring file information................................................................................................26

- II -

5.2.3 Converting files....................................................................................................................27

5.3 CONFIGURING DATA SOURCES.....................................................................................................30

5.3.1 About data source................................................................................................................30

5.3.2 Adding, modifying and deleting a data source.....................................................................30

5.4 CONFIGURING PARAMETERS.........................................................................................................31

5.4.1 About parameters.................................................................................................................31

5.4.2 Adding, modifying and deleting a parameter.......................................................................32

5.5 INPUTTING FUNCTIONS.................................................................................................................33

5.6 RUNNING XRF FILES....................................................................................................................33

5.6.1 Windows mode.....................................................................................................................34

5.6.2 Command line mode.............................................................................................................34

5.7 USING EXCEL FORMULAS.............................................................................................................34

5.8 SORTING, GROUPING AND TOTALING...........................................................................................37

5.8.1 Sorting data..........................................................................................................................37

5.8.2 Totaling................................................................................................................................38

5.8.3 Grouping data and subreports.............................................................................................39

5.8.4 Subtotaling...........................................................................................................................39

5.9 CHARTING ....................................................................................................................................41

5.10 PICTURES....................................................................................................................................43

5.10.1 Inserting pictures into a report template............................................................................43

5.10.2 Inserting pictures into a report..........................................................................................43

5.11 USING PARAMETERS...................................................................................................................44

5.12 PROGRAMMING...........................................................................................................................47

5.12.1 Using add-ins, macros.......................................................................................................47

5.12.2 Making XRF files programmatically..................................................................................48

CHAPTER 6 FUNCTION REFERENCE..........................................................................................49

6.1 REPORT FUNCTION.......................................................................................................................49

6.1.1 Report Function...................................................................................................................49

- III -

6.1.2 Fixed Table Report Function...............................................................................................49

6.1.3 Non-group Variable Table Report Function........................................................................53

6.1.4 Group Variable Table Report Function...............................................................................57

6.2 NAME FUNCTION..........................................................................................................................62

6.3 EXECSQL FUNCTION ...................................................................................................................64

CHAPTER 7 MENUS, TOOLBAR AND SHORTCUT KEYS........................................................66

7.1 FILE MENU ...................................................................................................................................66

7.2 EDIT MENU...................................................................................................................................66

7.3 REPORT MENU..............................................................................................................................66

7.4 TOOLS MENU................................................................................................................................67

7.5 HELP MENU..................................................................................................................................67

7.6 TOOLBAR......................................................................................................................................67

7.7 SHORTCUT KEYS ..........................................................................................................................68

CHAPTER 8 HINTS AND TIPS.........................................................................................................70

CHAPTER 9 XRF FILE REFERENCE.............................................................................................74

9.1 XRF FILE FORMAT.......................................................................................................................74

9.2 [DATA SOURCE] SECTION.............................................................................................................75

9.3 [FILE] SECTION ...........................................................................................................................77

9.4 [PARAMETER] SECTION............................................................................................................79

CHAPTER 10 LICENSE AND SUPPORT........................................................................................80

10.1 LICENSE......................................................................................................................................80

10.2 TECHNICAL SUPPORT..................................................................................................................81

- IV -

Chapter 1 Introduction

1.1 Overview

XLReportGen is a report generator for Microsoft Excel that outputs reports in
Microsoft Excel spreadsheet format. If you know how to use Microsoft Excel
and write SQL statements, you can use XLReportGen to create all kinds of
reports as you need.
To create a report,
XLReportGen need to read a
report template file and an XRF

Report

Template

XLReportGen

Report

file. The report template file is a

SQL

Microsoft Excel workbook that
defines the layouts and formats

Database

of a report. The XRF file
contains SQL statements and some information, and tells XLReportGen how
to get data from database and how to put data into a report. First XLReportGen
creates a blank report using the report template file, and then executes SQL
statements in the XRF file to get data from database, and fills data into the
report to generate the desired report in Microsoft Excel spreadsheet format.

1.2 Features

XLReportGen includes the following features:
 Using Microsoft Excel as your reporting tool
Just use Microsoft Excel as your reporting tool. You design reports like layouts,
formats and styles directly using Microsoft Excel. And you will get reports in
Microsoft Excel spreadsheet format as a result. Microsoft Excel is powerful,

- 1 -

flexible and familiar. You do not need to buy and learn extra reporting tools.
 Making report template directly using Microsoft Excel
The main advantage of using XLReportGen is based on the fact that all
formatting is done directly in Microsoft Excel. You can take full advantage of
Microsoft Excel including cell formatting, formulas, filtering and sorting,
drawing and pictures, charts, multiple sheets, page setup, headers and footers,
preview and printing, VBA, macros, and more.
 Accessing to databases using SQL
XLReportGen executes SQL statements to extract data from database.
Supports all type SQL: DML, DDL and DCL. Multiple SQL statements can be
executed in one report building process. You can perform queries on
databases, insert data into databases, and create database objects like tables.
The power of SQL can be harnessed for maximum efficiency in reporting.
 Creating reports without programming experience
You know how to use Microsoft Excel and how to write SQL, it is enough. It
does not require programming to create reports.
 Connection to all databases using ODBC
XLReportGen connects to databases using ODBC. Access to all databases
which support ODBC such as Oracle, DB2, Sybase, Informix, Microsoft SQL
Server, Teradata, MySQL, Microsoft Access, dBase.
 Supporting multi-databases in one report
XLReportGen supports multi-databases in one report. You can get data from
some different databases such as Oracle, DB2 and Microsoft SQL Server, and
put these data into one report.
 Generating reports with parameters
XLReportGen gives you an opportunity to create reports with parameters. You
may use parameters in SQL statements. You will be asked to input the values
of parameters while creating reports.

- 2 -

 Supporting Windows mode and command line mode
XLReportGen supports command line mode. So it is possible to call
XLReportGen from other program. For developers, you can integrate
XLReportGen into your application.
 Creating complex reports
You can create complex reports. The complexity might come from report
formatting as well as report content.
 Creating reports with charts
XLReportGen enables you to include sophisticated, colorful charts in your
reports. You can use charts any time you want to improve the usefulness of a
report.
 Creating reports with pictures
XLReportGen can insert pictures from the graphics files, position and size the
pictures according to your instruction.
 Many reports in one Microsoft Excel workbook
One Microsoft Excel workbook may contain many reports. You can generate a
book of reports in one generating process.
 Conversion of file formats
XLReportGen is a converter too. You can convert Microsoft Excel workbook to
and from other formats, such as HTML, XML, CSV, text, DBF, DIF, and Lotus
1-2-3. You also can convert data from database to other file format.
 Generating reports automatically
The process of report generation can be fully automated, periodically or on
events. XLReportGen can be scheduled with Windows Scheduled Tasks or
other tools.
 One time configuration
With on time configuration, you can repeatedly generate reports especially
periodic reports such as daily, weekly, monthly and annual reports.

- 3 -

 Flexible deployment
XLReportGen can be run on your desktop or server.

- 4 -

Chapter 2 Installation and Startup

2.1 Software Requirements

Microsoft Windows 95, Windows 98, Windows NT, Windows 2000, Windows
XP, Windows 2003, Windows Vista or later.
Microsoft Office 97/98, Office 2000, Office XP, Office 2003 or later.

2.2 Installing XLReportGen

Run the installation program, and follow the instructions to complete
XLReportGen installation.
If you don’t have Microsoft Office installed, please install it first.
If your environment is Windows 95/98 and Office 97, and you don’t have VB6.0
run-time files installed, please install it. For Windows 2000, Windows XP,
Windows 2003 and Office 2000 or later, you do not need to install VB6.0
run-time files because they are included in OS and Office. To install VB6.0
run-time files, just run vbrun60sp5.exe, and follow the instructions.
If you don’t have ODBC Driver installed for the database you want to access,
please install it.
If your OS is Windows 95/98 and you don’t have Microsoft Data Access
Components 2.0 (MDAC_TYP) or later installed, please install it. For Windows
2000, Windows XP and Windows 2003, you do not need to install MDAC_TYP
because it is preinstalled in OS. To install MDAC_TYP, just run mdac_typ.exe,
and follow the instructions.

2.3 Uninstalling XLReportGen

1. Quit XLReportGen.

- 5 -

2. Double-click the Add/Remove Programs icon in the Windows Control
Panel.

3. Do one of the following:
 For Windows 2000, Windows XP and Windows 2003 Edition:
Click XLReportGen in the Currently installed programs box, and then click the
Change/Remove button.
 For Windows 98 and Windows NT 4.0:
Click XLReportGen on the Install/Uninstall tab, and then click the
Add/Remove button.

4. Follow the instructions on the screen to complete uninstalling the program.

2.4 Command Line

XLReportGen can be run in Windows mode or command line mode. The
syntax of command is:

excelreport <xrf file name> [-C] [-D] [-I interval] [-U1 user1] [-P1 pwd1] …
[-U10 user10] [-P10 pwd10] [pa1 pa2 … pa10]

xrf file name Specifying an XRF (.xrf) file that tells XLReportGen how to get

data from data sources and how to put data into a report.

-C Run XLReportGen in command line mode.

-D Display the generated report with Microsoft Excel.

-I interval Log the processing records message. If interval is greater than
1, it is the interval of records. If interval is less than 1, it is the
percent of interval.

-U1 user1 …

Specify the user names. user1 is the user name of the first

-U10 user10

-P1 pwd1 ... Specify the passwords. pwd1 is the password of the first data

data source. user2 is the user name of the second data
source……

- 6 -

-P10 pwd10 source. Pwd2 is the password of the second data source……

pa1 … pa10 The values of the parameters defined in the XRF file. You can

use parameters in SQL statements. XLReportGen will replace
the names of the parameters in a SQL statement with the
actual values before it executes the SQL statement. You can
use no more than 10 parameters in one report.

For example, you have defined two parameters in your XRF file. The first
parameter is the sales date, and the second is the category of the product. You
can run XLReportGen in command line mode as follows:
excelreport c:\excelreport\myreport.xrf -c 1996-05-01 “Dairy Products”

- 7 -

Chapter 3 Quick Start

3.1 Learning how to use XLReportGen

You can teach yourself how to use XLReportGen by choosing from the
methods available in this section:
 You can study the sample reports and sample database included with

XLReportGen.

 You can use the detailed descriptions and instructions in the “My First

Report”.

3.2 Sample Database

XLReportGen comes with Sample.mdb, a sample database you can use when
learning the program. Sample.mdb is a Microsoft Access database. Virtually all
of the examples in this manual are based on Sample.mdb data.
The sample reports access the sample database through the ODBC data
source name “Report Sample”. When you install XLReportGen, you can
choose to add the ODBC data source name. And you also can add the ODBC
data source name manually.
To create the System DSN “Report Sample”, do as follows:

1. Click the Windows Start button, choose Settings, and then click Control

Panel.

2. On computers running Microsoft Windows 2000 or later, double-click

Administrative Tools, and then double-click Data Sources (ODBC). The
ODBC Data Source Administrator dialog box appears. On computers
running previous versions of Microsoft Windows, double-click 32-bit ODBC or
ODBC.

3. Select the System DSN tab, and then press Add button.

- 8 -

4. Choose Microsoft Access Driver (*.mdb), then press Finish button.

5. In the ODBC Microsoft Access Setup dialog box, type Report Sample in

the Data Source Name box.

6. Press the Select button, and browse to select Sample.mdb.

7. Press OK button to close the ODBC Microsoft Access Setup dialog box.

8. Press OK button to close the ODBC Data Source Administrator dialog

box.

3.3 Steps of Reporting

To create a report with XLReportGen, you should do as follows:

1. Prepare works

Before you create a report, you should determine the layout of the report, and
know where and how to get the data.
You must know how to access the databases you are reporting from. So you
need the data source name, user name and password. If you don’t have data
sources added, please add data sources first. Run ODBC Administrator, you
can add a new data source. For detailed information about configuring ODBC,
refer to ODBC Administrator Help.

2. Make a report template file

Create a report template file using Microsoft Excel. For detailed information
about report template, refer to “Report Templates” in this document.

3. Create an XRF file

Create an XRF file with an .xrf extension using XLReportGen. There are tow
steps to create an XRF file.
(1) Configure the report
Define the names of data sources, the name of the report template file, the
name of the report file and the name of the log file. If you want to use
parameters in SQL statements, define these parameters.

- 9 -

(2) Write functions
Write functions and SQL statements that specify how to get data from data
sources and how to put data into the report.
For detailed information, refer to “Reporting with XLReportGen” in this
document.

4. Run the XRF file

Run the XRF file to generate a report file. For detailed information about
running report, refer to “Running an XRF File” in this document.

3.4 My First Report

The following tutorial has been designed to guide you to create your first report.
In this tutorial, you will get an introduction to the program as you create a
Customer List report. The Customer List is one of the most basic business
reports and typically has information such as Customer Name, City, Country,
and Contact Name.

3.4.1 Creating a report template

1. Run Microsoft Excel, a new workbook will open.

2. Select the cell A1, type “Customer Name”. In the same way, you input “City”,

“Country” and “Contact Name” into the cells B1, C1 and D1.

3. Format the text of A1, B1, C1 and D1 as you like, including font, font size,

font colour, bold, background, alignment and border.

4. You can change the width of these columns. The report template you have

made is as follows:

5. Click Save on the File menu, chose a directory such as “C:\Report”, type

- 10 -

custlist.xls in the File name box and press Save button.

6. Click Close on the File menu.

3.4.2 Creating an XRF file

1. Run XLReportGen.

2. Click New on the File menu.

3. Click Save on the File menu, chose the directory to which you have saved

the report template, type custlist.xrf in the File name box and press Save
button.

3.4.3 Configuring the report

1. On the Report menu, click Configuration. The Configuration dialog box

appears.

2. Click the File tab.

In the Template File box, type custlist.xls; In the Report File box, type
Report\custlist.xls; In the Log File box, type Log\custlist.log.

3. Click the Data Source tab.

Press New button, the New Data Source dialog box appears. In the Name
box, type Report Sample, press OK button.

4. On the Configuration dialog box, press OK button.

3.4.4 Inputting a function

In the editor windows, input a function as follows:

@F1=Report(sheet=1 cell=A2)
SELECT CompanyName
,CityName
,CountryName
,ContactName

- 11 -

FROM Customers, Cities, Countries
WHERE Customers.CityCode = Cities.CityCode
AND Customers.CountryCode = Cities.CountryCode
AND Customers.CountryCode = Countries.CountryCode
ORDER BY CompanyName,CityName,CountryName

;
You can test the SQL statement in a query tool such as Microsoft Access or
Microsoft Query.

3.4.5 Understanding the function

Before going any further, let us understand this function.

1. The Report function will execute the SQL statement, get data from data
source, and put data into the report.

2. The sheet argument identifies a worksheet, and the value 1 is the index
number of the worksheet. So it is the first worksheet.

3. The cell argument specifies the cells that the first record will be filled into.
The value is A2. So XLReportGen will fetch the first record, put the value of
CompanyName field into A2, the value of CityName field into B2, the value of
CountryName field into C2, and the value of ContactName field into D2. An
then it fetch the next record, put them into A3,B3,C3 and D3……

3.4.6 Running an XRF file

1. On the Report menu, click Run, the Run Report dialog box appears.

2. Press Start button to run the XRF file.

3. XLReportGen will generate a report.

4. After the status is Done, click Close button.

- 12 -

3.4.7 Opening a report

1. On the File menu, click Open Report File to open the report you have
generated.
You can view and check the report.

2. On the File menu, click Open Log File to open the log file that recorded the
log information in the report generating..
You can check the log.

3. Close the report file and the log file.

3.4.8 Modifying the report template

1. On the File menu, click Open Template File to open the report template.

2. Change the width of columns. It is very useful to copy some sample data
from the report file into the report template for formatting.

3. Insert a new row on the top, type Customer List as the report title.

4. Add borders for the range “A2:D4”. The external border can be different from
the internal border. The report template you have made is as follows:

4. Save and close the template file.

3.4.9 Modifying the function

In the editor windows, modify the function as follows:

@F1=Report(sheet=1 cell=A3 reserve=2)

SELECT CompanyName

,CityName

- 13 -

,CountryName

,ContactName

FROM Customers, Cities, Countries

WHERE Customers.CityCode = Cities.CityCode

AND Customers.CountryCode = Cities.CountryCode

AND Customers.CountryCode = Countries.CountryCode

ORDER BY CompanyName,CityName,CountryName

;

1. Change the value of cell argument because you insert a row.

2. The reserve argument specifies the number of records for that you reserve
some rows. You have reserve two blank rows in the report template so that the
format of the last row/column border may be different from the others.

3.4.10 Generating the report again

1. Save the XRF file.

2. Run the XRF file to generate the report.

3. Open the report, view and check the report.
The report should now look similar to the following:

Now you have created a report.

- 14 -

3.5 Samples

After XLReportGen is installed, some sample reports are installed too. Use
these reports to learn XLReportGen. The sample reports can be changed to
adapt to your own needs.
The sample reports include a sample database, some report template files
(.xls) and XRF files (.xrf). They are located in the Application Data\LJZsoft
under All Users or your profile folder. XLReportGen was tested with Microsoft
Office 2007. Please download the sample reports for Microsoft Office 2007
from our website.
Directory Description
{commonappdata}\LJZsoft\Common\Sa
mpleDatabase
{commonappdata}\LJZsoft\XLReportGe
n\Samples

{commonappdata}\LJZsoft\XLReportGe
n\Samples\Report
{commonappdata}\LJZsoft\XLReportGe
n\Samples\Log

{commonappdata} is the path to the Application Data folder under All Users. If
you install XLReportGen without administrative privileges, {commonappdata}
is the path to the Application Data folder under the current user. The

Contains the sample database
“Sample.mdb”.
Contains the report template
files (.xls) and the XRF files
(.xrf).
Contains the report files (.xls)
generated by XLReportGen.
Contains the log files created by
XLReportGen during generating
report files.

Application Data folder is usually at:
Windows 95/98: C:\windows\All Users\Application Data\
Windows NT: C:\WinNT\Profiles\All Users\Application Data\
Windows 2000/XP: C:\Documents and Settings\All Users\Application Data\

- 15 -

Windows Vista: C:\ProgramData\

- 16 -

Chapter 4 Report Templates

4.1 About Reports

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

4.2 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 the 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 the cells you
will fill data.
XLReportGen will generate the report based on the report template file. All
static contents and the layouts, formats and styles defined in the report
template file will be brought to the final report file.

4.3 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.

4.3.1 Workbooks and Worksheets

A Microsoft Excel workbook is a file that contains one or more worksheets,

- 17 -

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.

4.3.2 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.

4.3.3 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.

4.3.4 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.

- 18 -

4.3.5 Page Breaks

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

4.3.6 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.

4.3.7 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.

4.3.8 Formatting

You can use these formatting features of Microsoft Excel to effectively display
your data.
 Format text and individual characters
To make text stand out, you can format all of the text in a cell or selected
characters. You can set font, color, alignment of the text.
 Rotate text and borders
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

- 19 -

labels, you can rotate text and apply borders that are rotated to the same
degree as the text.
 Add borders, colors, and patterns
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.
 Number formats
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.
 Conditional formatting
The conditional format is a format, such as cell shading or font color, that Excel
automatically applies to cells if a specified condition is true.
 Style
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.

4.4 Table Reports

4.4.1 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

- 20 -

XLReportGen executes a SQL statement, directly puts the result data into 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 cells of the table.

4.4.2 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 the worksheet is the same as the
format in the report, but cells that should be filled data into are blank. When
XLReportGen executes a SQL statement, the data values from data source
will be filled into these cells.

BA

BA

1

1
2

2
3

3

The fixed table defined
in the report template file

The fixed table filled data
by rows in the re port file

BA

BA

3.4141

3.4141

5.2202

5.2202

2.783

2.783

4.4.3 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 reserve some
rows/columns in the worksheet for one or two records. XLReportGen will add
some rows/columns according to the number of the records returned from data
source.

- 21 -