Ljzsoft XLREPORTCOM User Manual (2024)

XLReportCom

User Manual

Version 1.4

2009-07

Copyright© 2007-2009 LJZsoft Corporation

All rights reserved

- IV -

Chapter 1 Introduction

1.1 Overview

XLReportCom is a solution that generates reports using Microsoft Excel. Using

Microsoft Excel and XLReportCom, you can create all kinds of reports quickly and easily. XLReportCom includes an ActiveX DLL and an executable file that can be used to develop your applications. It will significantly accelerate your application development.

XLReportCom is a template-based solution. To create a report, you need to create a report template file first. The report template file is a Microsoft Excel workbook that defines the layouts and formats of a report. XLReportCom retrieves data from data source and fills data into Excel workbooks.

1.2 Features

XLReportCom 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, 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 XLReportCom 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.

- 1 -

Accessing to databases using SQL

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

Using ADO to access and manipulate data sources

Using ADO, XLReportCom can access and manipulate a wide variety of data sources such as Oracle, DB2, Sybase, Informix, Microsoft SQL Server,

Teradata, MySQL, Microsoft Access, dBase.

Integrating Microsoft Excel into your application

XLReportCom includes an ActiveX DLL for building application. Developers can save time and meet their users needs by integrating the report processing power of XLReportCom into their applications.

Command line program

XLReportCom includes a command line program ExcelReport.exe. You can use the program to create reports too. It does not require programming. It is enough if you know how to use Microsoft Excel and how to write SQL.

Various reporting capabilities

XLReportCom provides various reporting capabilities including sorting data, grouping data, subreports, totaling and summarizing data, formatting, charting and pictures. It is easy to create simple reports, and, you can create complex reports.

- 2 -

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 XLReportCom

Run the installation program, and follow the instructions to complete XLReportCom 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 XLReportCom

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

-3 -

Panel.

2. Do one of the following:

For Windows 2000, Windows XP and Windows 2003 Edition:

Click XLReportCom in the Currently installed programs box, and then click the Change/Remove button.

For Windows 98 and Windows NT 4.0:

Click XLReportCom on the Install/Uninstall tab, and then click the

Add/Remove button.

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

2.4 XLReportCom.dll

XLReportCom.dll is an ActiveX DLL that provides XLReport object. You can write a program to work with the object. Before you can use the XLReport object, you must create a reference to the object. And you should create references to Microsoft Excel Object Library too.

To create a reference to the XLReport object 1. Do one of the following:

For Visual Basic 6.0

From the Project menu, choose References.

For Microsoft Excel Visual Basic For Application

From the Tools menu, choose References.

2.In the References dialog box, select XLReportCom.

3.You can use the Browse button to search for XLReportCom.dll.

4.In the References dialog box, Select Microsoft Excel Object Library to create their references.

5.Declare an object variable of the object's class.

Dim xlrpt As XLReport

6.Assign an object reference to the variable by using the New keyword in a

-4 -

Set statement.

Set xlrpt = New XLReport

2.5 ExcelReport.exe

ExcelReport.exe is an executable program that developed using

XLReportCom.dll. It likes XLReportGen command line and can read an XRF file to create an Excel report. The syntax of command is:

excelreport <xrf file name> [-D] [-U1 user1] [-P1 pwd1] … [-U10 user10]

[-P10 pwd10] [pa1 pa2 … pa10]

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 ExcelReport.exe as follows:

excelreport c:\excelreport\myreport.xrf 1996-05-01 “Dairy Products”

- 5 -

2.6 Run-Time Files

You can distribute royalty-free the run-time files of XLReportCom with your applications. The run-time files are files your application must have in order to work correctly after installation. The following are the run-time files you need to distribute:

To register a DLL file, use regsvr32.exe. For example,

regsvr32.exe /s “C:\Program Files\LJZsoft\XLReportCom\XLReportCom.dll”

- 6 -

Chapter 3 Quick Start

3.1 Learning how to use XLReportCom

You can teach yourself how to use XLReportCom by choosing from the methods available in this section:

You can study the samples included with XLReportCom.

You can use the detailed descriptions and instructions in this document.

3.2 Sample Database

XLReportCom 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 XLReportCom, 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.

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

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

-7 -

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 Samples

After XLReportCom is installed, some samples are installed too. Use these samples to learn XLReportCom.

The samples include a sample database, VB sample programs, VBA sample programs and sample reports. They are located in the Application Data\LJZsoft under All Users or your profile folder. XLReportCom was tested with Microsoft

Office 2007. Please download the sample reports for Microsoft Office 2007 from our website.

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

Application Data folder is usually at:

Windows 95/98: C:\windows\All Users\Application Data\

- 8 -

Windows NT: C:\WinNT\Profiles\All Users\Application Data\

Windows 2000/XP: C:\Documents and Settings\All Users\Application Data\ Windows Vista: C:\ProgramData\

3.4 Creating a Report Programmatically

1. Create a template

In Microsoft Excel, create a report template file named “custlist.xls”. Static values and any Excel features included in the template will be included in the generated report. The template file you have created as follows:

2. Write the code in your application. Set con = New ADODB.Connection Set rec = New ADODB.Recordset

con.ConnectionString = "Data Source=Report Sample" con.Open

strSQL = "SELECT CompanyName, CityName, 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"

rec.Open strSQL, con

xlrpt.VarTableReport Recordset:=rec, Worksheet:=xlWorksheet, CellList:="A3", Reserve:=2

rec.Close

- 9 -

3.5 Creating a Report with ExcelReport.exe

1. Create a template

In Microsoft Excel, create a report template file named “custlist.xls”. Static values and any Excel features included in the template will be included in the generated report. The template file you have created as follows:

2. Create an XRF file

Create an XRF file named “custlist.xrf” using XLReportGen or a text editor. The following is the content of the XRF file.

ExcelReport Version 2.0

[Data Source]

Name1=Report Sample

[File]

ReportTemplateFileName=custlist.xls

ReportFileName=Report\custlist.xls

LogFileName=Log\custlist.log

[SQL]

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

,CityName

,CountryName

- 10 -

,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

3. Run ExcelReport.exe excelreport c:\report\custlist.xrf

- 11 -

Chapter 4 Report Templates

4.1 About Reports

The report generated by XLReportCom 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 XLReportCom, 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.

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

- 12 -

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

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

- 13 -

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.

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.

- 14 -

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

- 15 -

information.

XLReportCom 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

XLReportCom 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 XLReportCom 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 the cells that should be filled data into are blank. When XLReportCom executes a SQL statement, the data values from data source will be filled into these cells.

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

- 16 -

template file according to the report. But you just need to reserve some rows/columns in the worksheet for one or two records. XLReportCom will add some rows/columns according to the number of the records returned from data source.

4.4.4 Formatting a Cell for Pictures

To enhance the visual impact of your report, you can insert pictures into your report. XLReportCom supports many popular graphics file formats: bitmap,

JPG, GIF, PNG, TIFF and so on. For the graphics file formats XLReportCom supports, refer to Microsoft Excel Help.

You should store the path and name of the graphics files in the database, and identify the image fields in the report function. XLReportCom 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. XLReportCom will get the text of the 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.

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 the picture is set to 100 points. “H50” means that the height of the picture is set to 50 points. The default means the original size. If you just specify the width or height of the picture, not both,

XLReportCom will retain the original proportions of the picture when XLReportCom resize it.

Example

w84

- 18 -

Remarks

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

points.

4.5 Charts

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

4.5.2 Creating a Blank Chart

To create a chart in the report using XLReportCom, 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.

- 19 -

5. After you have finished the customization, delete data from the chart. You should keep a blank chart in the report template file. You will put data into the worksheet using XLReportCom.

For more detail information, refer to Microsoft Excel Help.

- 20 -

Chapter 5 API Reference

5.1 Objects

5.1.1 XLReport Object

Represents the XLReportCom. XLReport is the main class for report generation using XLReportCom.

Using the XLReport Object

The following example creates an XLReport object in another application and then generates a report using an XRF file.

Dim xlrpt As XLReport

Set xlrpt = New XLReport

xlrpt.ExcelReport xlApp, “customer_list.xrf"

5.2 Methods

5.2.1 FixTableReport Method

Generates a fixed table report based on a template. In a fixed table report, the number of rows and columns is fixed. XLReportCom gets data from a recordset object, and directly fills data into the cells of a worksheet.

Syntax

object.FixTableReport(Recordset, Worksheet, CellList, Range, Copy,

FillOrder, ImageList, PageBreak)

- 21 -

object Required. The object is the XLReport object.

Recordset Required. An object variable that represents the

ADODB.Recordset object to provides data. Before calling this method, please keep the current record position to the first record.

Worksheet Required. An object variable that represents the Excel.Worksheet object to be filled data.

CellList Required. A string that represents the list of cells separated by the

“,” character. For example, “A2,B2,B3,D2,D3”. The cells in the CellList should correspond to the data source fields in the recordset. The value of the first field is put into the first cell, and the value of the second field is put into the second cell ……

Range Optional. A string that indicates the range in the worksheet to be used for the records. XLReportCom will skip or repeat the range for each record.

You can reference a range of cells like “2:4” or “B2:D5”. The default range is the area that includes all cells for the records.

Copy Optional. An integer that indicates whether XLReportCom will copy the range for each record. If the value is zero, XLReportCom will skip the rows/columns of the range for each record. Otherwise it will copy the original range to the range where data will be filled for each record.

FillOrder Optional. An integer that indicates the order in which XLReportCom fills data. If the value is zero, XLReportCom will fill data by rows. 1 means to fill data by columns. Default is 0.

ImageList Optional. A string that indicates which data source fields are the picture files. The ImageList is the list of data source fields separated by the “,” character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the

- 22 -