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 database (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 / Administrative tools / ODBC data sources), 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 = "Provider=MSDASQL;" & _
     "Driver={Microsoft Access Driver (*.mdb)};" & _
     "Dbq=c:\program files\devstudio\vb\biblio.mdb;" & _
     "Uid=Admin; Pwd="

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

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

'Microsoft ODBC Driver for MySQL example
cnstr = "driver={MySQL ODBC 5.3 Unicode Driver};server=127.0.0.1;" & _
      "database=sakila;uid=root;pwd=;option=3"

A note on 64-bit systems

If you intend to use the MSDASQL data provider and read ODBC sources such as Access databases on a 64-bit system, make sure to either download the 64-bit version of Excel, or the 64-bit version of the Access database engine redistribuable, available here (on Microsoft site)

Also, if you plan to use the MSOLEDBSQL data provider to target SQL server, make sure to understand this is a botched one, and that Microsoft went back and forth with it. It may as well not work as planned, in which case the suggestion is to use the "old" SQLOLEDB data provider, or even plain old MSDASQL accompanied by the SQL Server driver.

Then, once the connection is established, 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

The data source can be configured to adapt your needs. The following properties are exposed :

CommandTimeout : lets you set the time before any command (SQL statement) is regarded timed out. Default is 30 seconds. 0 means indefinite wait.
ConnectionTimeout : lets you set the time before the database connection times out. Default is 15 seconds. 0 means indefinite wait.
RecordCursor : lets you set a cursor for navigating records and finding how many records are available. Default is : cursor is server based. Some database engines (such as MySQL) do not have a server cursor, so you have to set it as a client so a client application can benefit the capabilities.

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 object model. 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 = "Provider=MSDASQL;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("Provider=MSDASQL;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 = "Provider=MSDASQL;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"Provider=MSDASQL;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();

In case there is a discrepancy between the data column type (such as VARCHAR(80)) from the database and the resulting column type in the spreadsheet, a dump file may be of help and may help technical support to troubleshoot the issue. Simply pass the filename where to dump the mapping of data types in the following property DatatypesToDumpToFile, exposed by the data source object.

Customizing Data results

xlsgen provides a number of post-data fetching mechanisms for making sure the data is presented the way intended, including :

hiding table columns
table styles
...more to come

An exemple of hiding table columns

The simple mechanism for hiding columns allows to customize if any column in the data results will be inserted or not in the actual spreadsheet. By default all columns coming from the data fetch are inserted. Hiding one of the columns is as simple as :

C/C++ code

// hide the second column (i.e. first name)
table->DataResults->Columns->Item[2]->Hidden = TRUE;

Tables, Pivot tables and data connections

Using data sources with tables

It is possible to create an actual Excel table object, program and run an SQL request that will bound the table rows to the underlying database. And even better, xlsgen creates the database connection in the Excel file so you can reuse it elsewhere.

Here is how it works,

VB code


Dim workbook As IXlsWorkbook
Set workbook = engine.New( "output.xlsx" )

Dim worksheet As IXlsWorksheet
Set worksheet = workbook.AddWorksheet("Sheet1")

' create a new table at row 3, column B
Dim table As IXlsTable
Set table = worksheet.NewTable(3, 2)

' set up a data connection against an Access database (db1.mdb)
table.DataSource.CommandTimeout = 40
table.DataSource.ConnectionTimeout = 120
table.DataSource.ConnectionString = "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:\datasource\db1.mdb;uid=;pwd=;"


' define the SQL request
table.DataSource.SQLStatement = "select * from Table1"

' query the results
Dim nbrows
nbrows = table.DataResults.RowCount
Dim nbcolumns
nbcolumns = table.DataResults.Columns.Count

Dim column1 As String
column1 = table.DataResults.Columns.Item(1).Name
Dim column2 As String
column2 = table.DataResults.Columns.Item(2).Name
Dim column3 As String
column3 = table.DataResults.Columns.Item(3).Name
Dim column4 As String
column4 = table.DataResults.Columns.Item(4).Name
Dim column5 As String
column5 = table.DataResults.Columns.Item(5).Name

Dim definedName As String
definedName = table.DataResults.Name

' our 4-th column is a date, so format it according to our needs
Dim styleDate As IXlsStyle
Set styleDate = worksheet.NewStyle
styleDate.Format = "dd/mm/yyyy"

Dim styleBackground As IXlsStyle
Set styleBackground = worksheet.NewStyle
styleBackground.Pattern.BackgroundColor = &hFF0000

table.DataResults.Columns.Item(4).DataStyle = styleBackground

' insert the table in the worksheet
table.DataResults.Insert

workbook.Close

Java code


XlsWorkbook workbook = engine.New( "output.xlsx" );

XlsWorksheet worksheet = workbook.AddWorksheet("Sheet1");

// create a new table at row 3, column B
XlsTable table = worksheet.NewTable(3, 2);

// set up a data connection against an Access database (db1.mdb)
table.getDataSource().putCommandTimeout(40);
table.getDataSource().putConnectionTimeout(120);
table.getDataSource().putConnectionString("Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:\\datasource\\db1.mdb;uid=;pwd=;");

// define the SQL request
table.getDataSource().putSQLStatement("select * from Table1");

// query the results
int nbrows = table.getDataResults().getRowCount();
int nbcolumns = table.getDataResults().getColumns().getCount();

String column1 = table.getDataResults().getColumns().getItem(1).getName();
String column2 = table.getDataResults().getColumns().getItem(2).getName();
String column3 = table.getDataResults().getColumns().getItem(3).getName();
String column4 = table.getDataResults().getColumns().getItem(4).getName();
String column5 = table.getDataResults().getColumns().getItem(5).getName();

String definedName = table.getDataResults().getName();

// our 4-th column is a date, so format it according to our needs
XlsStyle styleDate = worksheet.NewStyle();
styleDate.putFormat("dd/mm/yyyy");

XlsStyle styleBackground = worksheet.NewStyle();
styleBackground.getPattern().putBackgroundColor(0xFF0000);

table.getDataResults().getColumns().getItem(4).putDataStyle(styleBackground);

// insert the table in the worksheet
table.getDataResults().Insert();

workbook.Close();

C# code


IXlsWorkbook workbook = engine.New( "output.xlsx" );

IXlsWorksheet worksheet = workbook.AddWorksheet("Sheet1");

// create a new table at row 3, column B
IXlsTable table = worksheet.NewTable(3, 2);

// set up a data connection against an Access database (db1.mdb)
table.DataSource.CommandTimeout = 40;
table.DataSource.ConnectionTimeout = 120;
table.DataSource.ConnectionString = "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:\\datasource\\db1.mdb;uid=;pwd=;";

// define the SQL request
table.DataSource.SQLStatement = "select * from Table1";

// query the results
int nbrows = table.DataResults.RowCount;
int nbcolumns = table.DataResults.Columns.Count;

String column1 = table.DataResults.Columns.get_Item(1).Name;
String column2 = table.DataResults.Columns.get_Item(2).Name;
String column3 = table.DataResults.Columns.get_Item(3).Name;
String column4 = table.DataResults.Columns.get_Item(4).Name;
String column5 = table.DataResults.Columns.get_Item(5).Name;

String definedName = table.DataResults.Name;

// our 4-th column is a date, so format it according to our needs
IXlsStyle styleDate = worksheet.NewStyle();
styleDate.Format = "dd/mm/yyyy";

IXlsStyle styleBackground = worksheet.NewStyle();
styleBackground.Pattern.BackgroundColor = 0xFF0000;

table.DataResults.Columns.get_Item(4).DataStyle = styleBackground;

// insert the table in the worksheet
table.DataResults.Insert();

workbook.Close();

C/C++ code


xlsgen::IXlsWorkbookPtr workbook = engine->New( L"output.xlsx" );

xlsgen::IXlsWorksheetPtr worksheet = workbook->AddWorksheet(L"Sheet1");

// create a new table at row 3, column B
xlsgen::IXlsTablePtr table = worksheet->NewTable[3][2];

// set up a data connection against an Access database (db1.mdb)
table->DataSource->CommandTimeout = 40;
table->DataSource->ConnectionTimeout = 120;
#ifndef _WIN64
  table->DataSource->ConnectionString = L"Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\datasource\\db1.mdb;uid=;pwd=;";
#else
  table->DataSource->ConnectionString = L"Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:\\datasource\\db1.mdb;uid=;pwd=;";
#endif

// define the SQL request
table->DataSource->SQLStatement = L"select * from Table1";

// query the results
int nbrows = table->DataResults->RowCount;
int nbcolumns = table->DataResults->Columns->Count;

_bstr_t column1 = table->DataResults->Columns->Item[1]->Name;
_bstr_t column2 = table->DataResults->Columns->Item[2]->Name;
_bstr_t column3 = table->DataResults->Columns->Item[3]->Name;
_bstr_t column4 = table->DataResults->Columns->Item[4]->Name;
_bstr_t column5 = table->DataResults->Columns->Item[5]->Name;

_bstr_t definedName = table->DataResults->Name;

// our 4-th column is a date, so format it according to our needs
xlsgen::IXlsStylePtr styleDate = worksheet->NewStyle();
styleDate->Format = L"dd/mm/yyyy";

xlsgen::IXlsStylePtr styleBackground = worksheet->NewStyle();
styleBackground->Pattern->BackgroundColor = 0xFF0000;

table->DataResults->Columns->Item[4]->DataStyle = styleBackground;

// insert the table in the worksheet
table->DataResults->Insert();

workbook->Close();

Using data sources with pivot tables

VB code


Dim workbook As IXlsWorkbook 
Set workbook = engine.New( "pivotTableSQL.xlsx" )

Dim worksheet As IXlsWorksheet
Set worksheet = workbook.AddWorksheet("Sheet1")

Dim pt As IXlsPivotTable
Set pt = worksheet.NewPivotTable

' create the SQL data connection (Access MDB database)
pt.DataSource.DataConnection.CommandTimeout = 40
pt.DataSource.DataConnection.ConnectionTimeout = 120
pt.DataSource.DataConnection.ConnectionString = "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\\input\\db1.mdb;uid=;pwd=;"

pt.DataSource.DataConnection.SQLStatement = "select * from Table1"

' fetch the data
pt.DataSource.DataConnection.Run

' create the pivot table itself
pt.Rows.AddByName("firstname")
pt.Rows.AddByName("ID")

pt.InsertAt(11,2)

workbook.Close

Java code


XlsWorkbook workbook = engine.New( "pivotTableSQL.xlsx" );

XlsWorksheet worksheet = workbook.AddWorksheet("Sheet1");

XlsPivotTable pt = worksheet.NewPivotTable();

// create the SQL data connection (Access MDB database)
pt.getDataSource().getDataConnection().putCommandTimeout(40);
pt.getDataSource().getDataConnection().putConnectionTimeout(120);
pt.getDataSource().getDataConnection().putConnectionString("Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\\input\\db1.mdb;uid=;pwd=;");

pt.getDataSource().getDataConnection().putSQLStatement("select * from Table1");

// fetch the data
pt.getDataSource().getDataConnection().Run();

// create the pivot table itself
pt.getRows().AddByName("firstname");
pt.getRows().AddByName("ID");

pt.InsertAt(11,2);

workbook.Close();

C# code


IXlsWorkbook workbook = engine.New( "pivotTableSQL.xlsx" );

IXlsWorksheet worksheet = workbook.AddWorksheet("Sheet1");

IXlsPivotTable pt = worksheet.NewPivotTable();

// create the SQL data connection (Access MDB database)
pt.DataSource.DataConnection.CommandTimeout = 40;
pt.DataSource.DataConnection.ConnectionTimeout = 120;
pt.DataSource.DataConnection.ConnectionString = "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\\input\\db1.mdb;uid=;pwd=;";

pt.DataSource.DataConnection.SQLStatement = "select * from Table1";

// fetch the data
pt.DataSource.DataConnection.Run();

// create the pivot table itself
pt.Rows.AddByName("firstname");
pt.Rows.AddByName("ID");

pt.InsertAt(11,2);

workbook.Close();

C/C++ code


xlsgen::IXlsWorkbookPtr workbook = engine->New( L"pivotTableSQL.xlsx" );

xlsgen::IXlsWorksheetPtr worksheet = workbook->AddWorksheet(L"Sheet1");

xlsgen::IXlsPivotTablePtr pt = worksheet->NewPivotTable();

// create the SQL data connection (Access MDB database)
pt->DataSource->DataConnection->CommandTimeout = 40;
pt->DataSource->DataConnection->ConnectionTimeout = 120;
pt->DataSource->DataConnection->ConnectionString = L"Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\\input\\db1.mdb;uid=;pwd=;";

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

// fetch the data
pt->DataSource->DataConnection->Run();

// create the pivot table itself
pt->Rows->AddByName(L"firstname");
pt->Rows->AddByName(L"ID");

pt->InsertAt(11,2);

workbook->Close();

The data connection attached to a pivot table needs not be created or recreated everytime the Excel file is loaded using xlsgen. It can be run over and over, like this :

VB code


' open an existing file with a pivot table
Dim workbook As IXlsWorkbook
Set workbook = engine.Open( "pivottable_database_connection.xlsb",
                                             "pivottable_database_connectionRefresh.xlsb")

Dim worksheet As IXlsWorksheet
Set worksheet = workbook.WorksheetByIndex(1)

Dim pt As IXlsPivotTable
Set pt = worksheet.PivotTables.Item(1)
		
' run the existing data connection
pt.DataSource.DataConnection.Run

workbook.Close

Java code


// open an existing file with a pivot table
XlsWorkbook workbook = engine.Open( "pivottable_database_connection.xlsb",
                                                            "pivottable_database_connectionRefresh.xlsb");

XlsWorksheet worksheet = workbook.getWorksheetByIndex(1);

XlsPivotTable pt = worksheet.getPivotTables().getItem(1);
		
// run the existing data connection
pt.getDataSource().getDataConnection().Run();

workbook.Close();

C# code


// open an existing file with a pivot table
IXlsWorkbook workbook = engine.Open( "pivottable_database_connection.xlsb",
                                                            "pivottable_database_connectionRefresh.xlsb");

IXlsWorksheet worksheet = workbook.WorksheetByIndex(1);

IXlsPivotTable pt = worksheet.PivotTables.get_Item(1);
		
// run the existing data connection
pt.DataSource.DataConnection.Run();

workbook.Close();

C/C++ code


// open an existing file with a pivot table
xlsgen::IXlsWorkbookPtr workbook = engine->Open( L"pivottable_database_connection.xlsb",
                                                                               L"pivottable_database_connectionRefresh.xlsb");

xlsgen::IXlsWorksheetPtr worksheet = workbook->WorksheetByIndex[1];

xlsgen::IXlsPivotTablePtr pt = worksheet->PivotTables->Item[1];
		
// run the existing data connection
pt->DataSource->DataConnection->Run();

workbook->Close();