Data source queries

xlsgen > overview > Data source queries

Data source queries is a mechanism in xlsgen that populates a spreadsheet by running SQL statements over databases. It's a very powerful mechanism. It supports any database (Access, SQL Server, Oracle, Excel files, ...), any database transport model (ODBC, OLEDB, native driver), and is a generic data binding mechanism that can be customized (formatting styles).

Data source queries are made of the following :

data source : a data source is usually defined by a connection string to a database and it relates to data results with a SQL statement run over a database.
data results : that's a join table resulting from the SQL statement run over a database. It has one or more columns.
data columns : each data column is typed, has a name, and the corresponding formatting style can be customized. By default, the formatting style is inferred, by that it means that a date/time is automatically formatted as a date/time.

Data sources

The underlying mechanism uses Microsoft's ADO. Since ADO abstracts away all databases available out there through a common pipe driver protocol, it is possible to access a databse (read-only) by simply specifying the appropriate connection string.

The connection string is a set of key-value pairs defining the database file name, or database DSN (indirection through the ODBC data sources available through the Windows control panel), or a driver name. Optionally the user name and password. Examples are :

'Microsoft SQL Server ODBC Driver example
cnstr = "driver={SQL Server};server=myserver;" & _
      "database=pubs;uid=sa;pwd="

'Microsoft Access ODBC Driver example
cnstr = "Driver={Microsoft Access Driver (*.mdb)};" & _
     "Dbq=c:\program files\devstudio\vb\biblio.mdb;" & _
     "Uid=Admin; Pwd="

'Microsoft ODBC Driver for Oracle example
cnstr = "Driver={Microsoft ODBC Driver for Oracle};" & _
     "ConnectString=OracleServer.world; Uid=demo; Pwd=demo"

The database is accessed in read-only mode using a SQL statement. An example is :

'The database has a table called Table1 with 5 columns
select * from Table1

Data results

Data results represents the columns themselves. You can obtain the number of columns, the number of rows, and details for each column.

The actual data in Data results is a memory representation that is not directly accessed through xlsgen. The data results is expected to be inserted (copy/pasted) onto a spreadsheet as a table at a specified location (top-left corner).

Each column has an internal data type, and it's used to infer which formatting style to apply. xlsgen automatically encodes strings, numbers, booleans and date/time.

Each column may be customized with a provided formatting style. In fact, each column may have a custom formatting style for the header (column name), and for the data itself.

In the example below, a table is created in a spreadsheet, by accessing a Microsoft Access database (db1.mdb). The table has 5 columns : ID (number), firstname (string), lastname (string), age (number) and lastupdated (date). The 4th column gets a custom formatting style.

VB code


' this sample code queries data from a Microsoft Access database (db1.mdb)
' the 4th column of the data results gets a custom formatting style

Dim wbk As IXlsWorkbook 
Set wbk = engine.New( "sample.xls" )

Dim wksht As IXlsWorksheet 
Set wksht = wbk.AddWorksheet("Sheet1")

Dim table As IXlsTable 
Set table = wksht.get_NewTable(3,2)

table.DataSource.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\input\db1.mdb;uid=;pwd=;"

table.DataSource.SQLStatement = "select * from Table1"

Dim nbrows 
nbrows = table.DataResults.RowCount
Dim nbcolumns 
nbcolumns = table.DataResults.Columns.Count

Dim styleBackground As IXlsStyle 
Set styleBackground = wksht.NewStyle
styleBackground.Pattern.BackgroundColor = 0xFF0000
table.DataResults.Columns.Item(4).DataStyle = styleBackground

table.DataResults.Insert

wbk.Close

Java code


// this sample code queries data from a Microsoft Access database
// the 4th column of the data results gets a custom formatting style

XlsWorkbook wbk = engine.New( "sample.xls" );

XlsWorksheet wksht = wbk.AddWorksheet("Sheet1");

XlsTable table = wksht.getNewTable(3,2);

table.getDataSource().putConnectionString("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\input\\db1.mdb;uid=;pwd=;");

table.getDataSource().putSQLStatement("select * from Table1");

int nbrows = table.getDataResults().getRowCount();
int nbcolumns = table.getDataResults().getColumns().getCount();

XlsStyle styleBackground = wksht.NewStyle();
styleBackground.getPattern().putBackgroundColor(0xFF0000);
table.getDataResults().getColumns().getItem(4).putDataStyle(styleBackground);

table.getDataResults().Insert();

wbk.Close();

C# code


// this sample code queries data from a Microsoft Access database
// the 4th column of the data results gets a custom formatting style

IXlsWorkbook wbk = engine.New( "sample.xls" );

IXlsWorksheet wksht = wbk.AddWorksheet("Sheet1");

IXlsTable table = wksht.get_NewTable(3,2);

table.DataSource.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\input\\db1.mdb;uid=;pwd=;";

table.DataSource.SQLStatement = "select * from Table1";

int nbrows = table.DataResults.RowCount;
int nbcolumns = table.DataResults.Columns.Count;

IXlsStyle styleBackground = wksht.NewStyle();
styleBackground.Pattern.BackgroundColor = 0xFF0000;
table.DataResults.Columns.get_Item(4).DataStyle = styleBackground;

table.DataResults.Insert();

wbk.Close();

C/C++ code


// this sample code queries data from a Microsoft Access database
// the 4th column of the data results gets a custom formatting style

xlsgen::IXlsWorkbookPtr wbk = engine->New( L"sample.xls" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet(L"Sheet1");

xlsgen::IXlsTablePtr table = wksht->NewTable[3][2];

table->DataSource->ConnectionString = L"Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\input\\db1.mdb;uid=;pwd=;";

table->DataSource->SQLStatement = L"select * from Table1";

int nbrows = table->DataResults->RowCount;
int nbcolumns = table->DataResults->Columns->Count;

xlsgen::IXlsStylePtr styleBackground = wksht->NewStyle();
styleBackground->Pattern->BackgroundColor = 0xFF0000;
table->DataResults->Columns->Item[4]->DataStyle = styleBackground;

table->DataResults->Insert();

wbk->Close();