Working With Pivot Tables in Excel Files using C++

01 Working With Pivot Tables in Excel Files using CPP

Working With Pivot Tables in Excel Files using C++

Create-Pivot-Table.cpp

// Source directory path.
StringPtr srcDir = new String("SourceDirectory\\");

// Output directory path.
StringPtr outDir = new String("OutputDirectory\\");

// Create an instance of the IWorkbook class
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook();

// Access the first worksheet
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

// Add source data for pivot table
intrusive_ptr<String> str = new String("Fruit");
worksheet->GetICells()->GetObjectByIndex(new String("A1"))->PutValue(str);
str = new String("Quantity");
worksheet->GetICells()->GetObjectByIndex(new String("B1"))->PutValue(str);
str = new String("Price");
worksheet->GetICells()->GetObjectByIndex(new String("C1"))->PutValue(str);
str = new String("Apple");
worksheet->GetICells()->GetObjectByIndex(new String("A2"))->PutValue(str);
str = new String("Orange");
worksheet->GetICells()->GetObjectByIndex(new String("A3"))->PutValue(str);
str = new String("Mango");
worksheet->GetICells()->GetObjectByIndex(new String("A4"))->PutValue(str);
worksheet->GetICells()->GetObjectByIndex(new String("B2"))->PutValue(3);
worksheet->GetICells()->GetObjectByIndex(new String("B3"))->PutValue(4);
worksheet->GetICells()->GetObjectByIndex(new String("B4"))->PutValue(4);
worksheet->GetICells()->GetObjectByIndex(new String("C2"))->PutValue(2);
worksheet->GetICells()->GetObjectByIndex(new String("C3"))->PutValue(1);
worksheet->GetICells()->GetObjectByIndex(new String("C4"))->PutValue(4);

// Add pivot table
int idx = worksheet->GetIPivotTables()->Add(new String("A1:C4"), new String("E5"), new String("MyPivotTable"));

// Access created pivot table
intrusive_ptr<IPivotTable> pivotTable = worksheet->GetIPivotTables()->GetObjectByIndex(idx);

// Manipulate pivot table rows, columns and data fields
pivotTable->AddFieldToArea(PivotFieldType_Row, pivotTable->GetIBaseFields()->GetObjectByIndex(0));
pivotTable->AddFieldToArea(PivotFieldType_Data, pivotTable->GetIBaseFields()->GetObjectByIndex(1));
pivotTable->AddFieldToArea(PivotFieldType_Data, pivotTable->GetIBaseFields()->GetObjectByIndex(2));
pivotTable->AddFieldToArea(PivotFieldType_Column, pivotTable->GetIDataField());

// Set the pivot table style
pivotTable->SetPivotTableStyleType(PivotTableStyleType_PivotTableStyleMedium9);

// Save the output excel file
workbook->Save(outDir->StringAppend(new String("outputCreatePivotTable.xlsx")));

Hide-Rows-In-Pivot-Table.cpp

// Source directory path.
StringPtr srcDir = new String("SourceDirectory\\");

// Output directory path.
StringPtr outDir = new String("OutputDirectory\\");

// Path of the input excel file
StringPtr samplePivotTable = srcDir->StringAppend(new String("SamplePivotTable.xlsx"));

// Path of the output excel file
StringPtr outputHiddenRowPivotTable = outDir->StringAppend(new String("outputHiddenRowPivotTable.xlsx"));

// Load the sample excel file
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(samplePivotTable);

// Access the first worksheet
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

// Access the pivot table
intrusive_ptr<IPivotTable> pivotTable = worksheet->GetIPivotTables()->GetObjectByIndex(0);

// Get pivot table body range
intrusive_ptr<ICellArea> dataBodyRange = pivotTable->GetIDataBodyRange();

// Pivot table starting row
int currentRow = 5;

// Pivot table ending row
int rowsUsed = dataBodyRange->GetendRow();

// Iterate through the rows, compare the cell value and hide the rows.
for (int i = currentRow; i < rowsUsed; i++) {
	intrusive_ptr<ICell> cell = worksheet->GetICells()->GetICell(i, 4);
	if (strcmp(cell->GetStringValue()->charValue(), "Orange") == 0) {
		worksheet->GetICells()->HideRow(i);
	}
}

// Refresh and calculate the data in the pivot table.
pivotTable->RefreshData();
pivotTable->CalculateData();

// Save the output excel file
workbook->Save(outputHiddenRowPivotTable);

Manipulate-Pivot-Table-Data.cpp

// Source directory path.
StringPtr srcDir = new String("SourceDirectory\\");

// Output directory path.
StringPtr outDir = new String("OutputDirectory\\");

// Path of the input excel file
StringPtr samplePivotTable = srcDir->StringAppend(new String("SamplePivotTable.xlsx"));

// Path of the output excel file
StringPtr outputManipulatePivotTable = outDir->StringAppend(new String("outputManipulatePivotTable.xlsx"));

// Load the sample excel file
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(samplePivotTable);

// Access the first worksheet
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

// Change value of cell A2 which is inside the source data of pivot table
intrusive_ptr<String> str = new String("Orange");
worksheet->GetICells()->GetObjectByIndex(new String("A2"))->PutValue(str);

// Access pivot table, refresh and calculate it
intrusive_ptr<IPivotTable> pivotTable = worksheet->GetIPivotTables()->GetObjectByIndex(0);
pivotTable->RefreshData();
pivotTable->CalculateData();

// Save the output excel file
workbook->Save(outputManipulatePivotTable);

Sort-Pivot-Table.cpp

// Source directory path.
StringPtr srcDir = new String("SourceDirectory\\");

// Output directory path.
StringPtr outDir = new String("OutputDirectory\\");

// Path of the input excel file
StringPtr samplePivotTable = srcDir->StringAppend(new String("SamplePivotTable.xlsx"));

// Path of the output excel file
StringPtr outputSortedPivotTable = outDir->StringAppend(new String("outputSortedPivotTable.xlsx"));

// Load the sample excel file
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(samplePivotTable);

// Access the first worksheet
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

// Access the pivot table
intrusive_ptr<IPivotTable> pivotTable = worksheet->GetIPivotTables()->GetObjectByIndex(0);

// Set pivot table sorting
pivotTable->AddFieldToArea(PivotFieldType_Row, 0);
intrusive_ptr<IPivotField> pivotField = pivotTable->GetIRowFields()->GetObjectByIndex(0);
pivotField->SetAutoSort(true);
pivotField->SetAscendSort(false);

// Refresh and calculate the data in the pivot table.
pivotTable->RefreshData();
pivotTable->CalculateData();

// Save the output excel file
workbook->Save(outputSortedPivotTable);