XEROF

xlsgen 4.0.0.94 : Pivot charts

Build 4.0.0.94 of xlsgen adds support for pivot charts.

Pivot charts simply associate an arbitrary chart to an existing pivot table so that the rows and columns that make the pivot table are used as data source for the chart. All the common chart formatting remains available.

A PivotTableDataSource property is available in the chart object interface in order to make the association to an existing pivot table.

C/C++ code


// create a pivot table
xlsgen::IXlsWorksheetPtr wkshtNew = workbook->AddWorksheet( L"SheetNew" );

xlsgen::IXlsPivotTablePtr pt = wkshtNew->NewPivotTable();
pt->DataSource->Range = L"Data!A1:E6";

xlsgen::IXlsPivotTableFieldPtr pf_v0 = pt->Data->AddByName(L"Group Name");

xlsgen::IXlsPivotTableFieldPtr pf_v1 = pt->Data->AddByName(L"Value1");
xlsgen::IXlsPivotTableFieldPtr pf_v2 = pt->Data->AddByName(L"Value2");
xlsgen::IXlsPivotTableFieldPtr pf_v3 = pt->Data->AddByName(L"Value3");

xlsgen::IXlsPivotTableFieldPtr pf_d1 = pt->Rows->AddDataFields(); // in rows

pt->InsertAt(5,2);

// create a chart
xlsgen::IXlsWorksheetPtr wkshtChart = workbook->AddWorksheet( L"Chart" );

xlsgen::IXlsChartPtr chart = wkshtChart->NewChart(xlsgen::charttype_bar2D, 
					     2, //row1
					     2, //col1
					     17, //row2
					     9 //col2
					     );

// attach the chart to the pivot table
chart->PivotTableDataSource = pt;

In Excel, pivot charts can be tweaked by slicing and dicing rows and columns, as they reflect the pivot table slicing and dicing. Of course, the same can be done programmatically with xlsgen when the pivot table is being created.