Pivot tables rearrange data to represent it in a meaningful way. They provide different sorting options and provide sums, averages, or other statistics by grouping data together. It is an essential tool for data analysis and is a fundamental part of MS Excel. You might find yourself in scenarios where you need to create and manipulate pivot tables programmatically. To that end, this article will teach you how to work with pivot tables in Excel files using C++.
- C++ API for Working with Pivot Tables in Excel Files
- Create a Pivot Table in an Excel File using C++
- Sort Pivot Table in an Excel File using C++
- Hide Rows in a Pivot Table using C++
- Manipulate Pivot Table Data using C++
C++ API for Working with Pivot Tables in Excel Files
Aspose.Cells for C++ is a native C++ library that allows you to create, read and update Excel files without requiring Microsoft Excel to be installed. The API also supports working with pivot tables in Excel files. You can either install the API through NuGet or download it directly from the Downloads section.
PM> Install-Package Aspose.Cells.CppCreate a Pivot Table in an Excel File using C++
In the following example, we will create a new Excel file, insert sample data into it and create a pivot table. The file generated in this example will be used as the source file for other examples. The following are the steps to create a pivot table in an Excel file.
- Firstly, create an instance of the IWorkbook class to represent the new Excel file.
- Access the worksheet where you want to insert the pivot table using IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Add sample data for the pivot table.
- Add pivot table using the IWorksheet->GetIPivotTables()->Add(intrusive_ptr<Aspose::Cells::Systems::String> sourceData, intrusive_ptr<Aspose::Cells::Systems::String> destCellName, intrusive_ptr<Aspose::Cells::Systems::String> tableName) method.
- To access the pivot table, use the IWorksheet->GetIPivotTables()->GetObjectByIndex(Aspose::Cells::Systems::Int32 index) method.
- Manipulate the fields and set the style of the pivot table.
- Finally, save the Excel file using the IWorkbook->Save (intrusive_ptr<Aspose::Cells::Systems::String> fileName) method.
The following sample code shows how to create a pivot table in an Excel file using C++.
Sort Pivot Table in an Excel File using C++
In the following example, we will sort the first column of the pivot table in descending order. The following are the steps to sort data in a pivot table.
- Firstly, load the sample Excel file using the IWorkbook class.
- Retrieve the worksheet containing the pivot table using IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Access the pivot table using IWorksheet->GetIPivotTables()->GetObjectByIndex(Aspose::Cells::Systems::Int32 index) method.
- Get row field and sort the pivot table using the IPivotField->SetAutoSort(bool value) and IPivotField->SetAscendSort(bool value) methods.
- Refresh the contents of the pivot table and calculate the data using the IPivotTable->RefreshData() and IPivotTable->CalculateData() methods respectively.
- Finally, save the Excel file using the IWorkbook->Save (intrusive_ptr<Aspose::Cells::Systems::String> fileName) method.
The following sample code demonstrates how to sort a pivot table in an Excel file using C++.
Hide Rows in a Pivot Table using C++
With Aspose.Cells for C++ API, you can also hide the rows in a pivot table. In the following example, we will hide the row with the “Orange” row label. The following are the steps to hide rows in a pivot table.
- Firstly, load the sample Excel file using the IWorkbook class.
- Retrieve the worksheet containing the pivot table using IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Access the pivot table using IWorksheet->GetIPivotTables()->GetObjectByIndex(Aspose::Cells::Systems::Int32 index) method.
- Get the pivot table data body range using IPivotTable->GetIDataBodyRange() method.
- Iterate through the rows of the pivot table and hide the rows meeting your criteria.
- Refresh the contents of the pivot table and calculate the data using the IPivotTable->RefreshData() and IPivotTable->CalculateData() methods respectively.
- Finally, save the Excel file using the IWorkbook->Save (intrusive_ptr<Aspose::Cells::Systems::String> fileName) method.
The following sample code shows how to hide rows in a pivot table using C++.
Manipulate Pivot Table Data using C++
You can also manipulate the data of an existing pivot table using Aspose.Cells for C++ API. In the following example, we will replace the text “Apple” in cell “A2” with “Orange” and reflect the change in the pivot table. The following are the steps to manipulate pivot table data.
- Firstly, load the sample Excel file using the IWorkbook class.
- Retrieve the worksheet containing the pivot table data using the IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Update the data of the pivot table according to your requirements.
- In order to access the pivot table, use IWorksheet->GetIPivotTables()->GetObjectByIndex(Aspose::Cells::Systems::Int32 index) method.
- Refresh the contents of the pivot table and calculate the data using the IPivotTable->RefreshData() and IPivotTable->CalculateData() methods respectively.
- Finally, save the Excel file using the IWorkbook->Save (intrusive_ptr<Aspose::Cells::Systems::String> fileName) method.
The following sample code shows how to update the data of a pivot table using C++.
Get a Free License
In order to try the API without evaluation limitations, you can request a free temporary license.
Conclusion
In this article, you have learned how to work with pivot tables in Excel files using C++. Specifically, you have learned how to create a pivot table and sort, hide and update the data in a pivot table using C++. Aspose.Cells for C++ is a vast API that provides a bunch of additional features for working with Excel files. You can explore the API in detail by visiting the official documentation. In case of any questions, please feel free to reach us on our free support forum.
