XLReportGen

How to export data from database to Microsoft Excel

XLReportGen is a powerful and flexible reporting tool that gets data from database and outputs reports in Microsoft ® Excel ® spreadsheeet format. XLReportGen accesses database using SQL.

Connecting to databases through ODBC

Open Database Connectivity (ODBC) is a standard protocol for accessing relational databases based around SQL. Because of accessing data through ODBC, XLReportGen can access a wide range of data sources. XLReportGen works with all kinds of data, from simple text files to advanced client-server SQL databases.

The databases and files that XLReportGen supports includes Oracle, Sybase, Informix, IBM DB2, Teradata, Microsoft SQL Server, MySQL, Microsoft Access, Microsoft Visual FoxPro, dBase, Paradox, Microsoft Excel and text file.

XLReportGen supports more than one data sources in one report. You can get data from some different databases such as Oracle, IBM DB2 and Microsoft SQL Server, and put them into one report.

Accessing databases using SQL

Structured Query Language (SQL) is the industry standard language for communicating with Relational Database Management Systems. To extract data from database into reports, you need to write SQL. XLReportGen can execute SQL statements, and supports all features of SQL.

SELECT, INSERT, UPDATE, DELETE, INSERT SELECT
Functions
Subqueries
Joins
EXECUTE Stored Procedure
CREATE, DROP, ALTER
Temporary Tables
GRANT, REVOKE

Executing multiple SQL statements

In one report building process, XLReportGen can execute multiple SQL statements. To create one complex report, you can create a temporary table, perform one or more queries on database, insert data into the temporary table, and then get data from the temporary table into your report.

Example

This example is for Microsoft SQL Server, and tested on XLReportGen.

/*********************************************************
Compare with Last Month by Countries
*********************************************************/
/* Create temporary table tmp_country_sales */
CREATE TABLE #tmp_country_sales (
CountryCode INTEGER,
Quantity INTEGER,
Amount NUMERIC(19,4)
);

/* Get the sales amount by countries in the current month */
INSERT INTO #tmp_country_sales (CountryCode, Quantity, Amount)
SELECT o.ShipCountryCode
    , SUM(d.Quantity)
    , Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
    ,OrderDetails d
WHERE o.OrderID = d.OrderID
AND YEAR(o.OrderDate) = YEAR('1996-04-01')
AND MONTH(o.OrderDate) = MONTH('1996-04-01')
GROUP BY o.ShipCountryCode
;

/* Show the sales amount by countries in the current month */
SELECT c.CountryName
    , ISNULL(t.Quantity,0)
    , ISNULL(t.Amount,0)
FROM Countries c LEFT JOIN #tmp_country_sales t
ON c.CountryCode = t.CountryCode
ORDER BY c.CountryName
;

/* Delete from table tmp_country_sales */
DELETE FROM #tmp_country_sales;

/* Get the sales amount by countries in the last month */
INSERT INTO #tmp_country_sales (CountryCode, Quantity, Amount)
SELECT o.ShipCountryCode
    , SUM(d.Quantity)
    , Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
    ,OrderDetails d
WHERE o.OrderID = d.OrderID
AND o.OrderDate >= DateAdd(m,-1,'1996-04-01')
AND o.OrderDate < '1996-04-01'
GROUP BY o.ShipCountryCode
;

/* Show the sales amount by countries in the last month */
SELECT ISNULL(t.Quantity,0)
    , ISNULL(t.Amount,0)
FROM Countries c LEFT JOIN #tmp_country_sales t
ON c.CountryCode = t.CountryCode
ORDER BY c.CountryName
;