Build 4.5.0.83 of xlsgen adds support for database connections in table objects.

Before this build, you could program and run SQL requests but the result would not become an actual Excel table object fetched with the database data. It would just be rows fetched with database data. Likewise if you created an actual Excel table object in xlsgen, you couldn't program and run an SQL request from it.

This is put together in this build. You can now 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,

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

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

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

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

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

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;

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;

table->DataResults->Insert();

workbook->Close();