Ljzsoft EXCELREPORTGEN, XLREPORTGEN User Manual

XLReportGen

Looking for a powerful and flexible reporting tool that’s easy to use, cost
effective and extract data from any database? Microsoft Office is your choice.
With the help of XLReportGen, Microsoft Excel is a powerful and flexible
reporting tool.
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.

Benefit

n Use Microsoft Excel as your reporting tool
You have installed Microsoft Office in your computer, and you almost use
Microsoft Office every day. You are familiar with Microsoft Excel, and already
know how to use it. Use Microsoft Excel as reporting tool, it is very convenient.
n Easy to use
You already know how to use Microsoft Excel. It is easy. Get data using SQL. It
is simple. You can create reports without programming experience. Easy to
learn, use and maintain.
n Powerful and flexible
XLReportGen is powerful and flexible because Microsoft Excel and SQL are
powerful and flexible. You can create extremely complex reports.
n Saves money
You do not need to buy extra costly reporting tools. No training is necessary
because everyone uses Microsoft Office.
n Saves time
Reduce development time and training time.

- 1 -

n Improves productivity
More productive when reports are in familiar formats like Microsoft Excel
spreadsheet.
n For various users
For IT users, you know Microsoft Excel and SQL, it is enough. No
programming is required. For business users, you can create report templates
using Microsoft Excel. XLReportGen enables you to design reports like
defining the layouts, styles, formats of reports. For developers, XLReportGen
can be called by your program. It enables you to integrate Microsoft Excel into
your application.

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

Report

need to read a report template
file and an XRF file. The report

Template

XLReportGen

Report

template file is a Microsoft Excel

SQL

workbook that defines the
layouts and formats of a report.

Database

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.

- 2 -

Feature

n 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,
flexible and familiar. You do not need to buy and learn extra reporting tools.
n 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.
n 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.
n 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.
n 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.
n Supporting multi-databases in one report
XLReportGen supports multi-databases in one report. You can get data from

- 3 -

some different databases such as Oracle, DB2 and Microsoft SQL Server, and
put these data into one report.
n 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.
n 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.
n Creating complex reports
You can create complex reports. The complexity might come from report
formatting as well as report content.
n Creating reports with pictures
XLReportGen can insert pictures from the graphics files, position and size the
pictures according to your instruction.
n 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.
n 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.
n 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.
n One time configuration
With on time configuration, you can repeatedly generate reports especially

- 4 -

periodic reports such as daily, weekly, monthly and annual reports.
n Flexible deployment
XLReportGen can be run on your desktop or server.

Reporting steps

XLReportGen is simple and easy to use. It does not require programming to
create reports. It is enough if you know how to use Microsoft Excel and write
SQL statements. Developing a report with XLReportGen requires only three
steps:
Step 1. Create a report template file using Microsoft Excel, define the layouts,
styles, formats of reports.
For example, to create a Customer List report, you make a report template as
follows.

Step 2. Create an XRF file with an .xrf extension using XLReportGen. The
XRF file tell XLReportGen how to extract data from database, and where and
how to put data.
For example, you create an XRF file, and write a report function and SQL as
follows.

/* Customer List */
@F1=Report(sheet="Customer List" type=var cell=A6 reserve=2)
SELECT CompanyName
,CityName

- 5 -

,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
Step 3. Run XLReportGen to generate a report file in Microsoft Excel
spreadsheet.

The report Customer List is generated as follows.

- 6 -

System requirement

n Microsoft Windows 95, Windows 98, Windows NT, Windows 2000,

Windows XP, Windows 2003 or later.

n Microsoft Office 97/98, Office 2000, Office XP, Office 2003 or later.

XLReportGen Copyright © 2003 – 2006 LJZsoft Corporation. All Rights Reserved.

Contact/Support

Email: [e-mail]

[e-mail]

[e-mail]

WWW: http://www.ljzsoft.com

- 7 -