Build 4.9.0.19 of xlsgen adds direct support for data connections such as SQL to pivot tables in XLSX, XLSB and XLS files.

Until now, a pivot table created in xlsgen could be attached only to a worksheet-type data source. Making it possible to attach a pivot table to a SQL data connection without any clutter (such as a temporary table object) makes it both useful and clean for applications.

Here is how it works (below is C++ code) :

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

xlsgen::IXlsWorksheetPtr worksheet = workbook->AddWorksheet("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();