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.

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

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.

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

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

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

Windows Vista: C:\ProgramData\

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

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