Create a Pivot table from a dynamic table in Excel

Aspose.Cells Product Family
1

Hello,

We are using Aspose.Cells to create a report in Excel.

I would like some help creating a Pivot table from Dynamic data. I explain the case: We have an export in Excel from our application that creates a Sheet with data. That data can be different from one export to another based on the criteria selected on the screen. That is, if we select a value in a combo, the list of fields (columns) could be different from other options selected.

Then, on a second sheet , we have a pivot table that sum up values for those columns from the previous sheet.

Can anyone figure out how can I solve this issue?

Thank in advance.

2

@juanc

To create a Pivot Table from dynamic data in Excel using Aspose.Cells, you can follow these steps:

Step 1: Import the Aspose.Cells Library

Make sure you have the Aspose.Cells library added to your project. You can download it from the Aspose releases.

Step 2: Load the Workbook

First, load the workbook that contains your dynamic data. You can do this with the following code:

Workbook workbook = new Workbook("path_to_your_excel_file.xlsx");

Step 3: Create a Pivot Table

After loading the workbook, you can create a Pivot Table. You need to specify the source data range and the location where you want to place the Pivot Table. Here’s an example:

Worksheet dataSheet = workbook.getWorksheets().get(0); // Assuming your data is in the first sheet
PivotTableCollection pivotTables = workbook.getWorksheets().get(1).getPivotTables(); // Assuming you want to create it in the second sheet
int index = pivotTables.add("=Data!A1:C30", "E3", "PivotTable1"); // Adjust the range and location as needed
PivotTable pivotTable = pivotTables.get(index);

Step 4: Configure the Pivot Table

You can configure the Pivot Table by adding fields to the row, column, and data areas. Here’s how you can do it:

pivotTable.addFieldToArea(PivotFieldType.ROW, 0); // Add the first field to the row area
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1); // Add the second field to the column area
pivotTable.addFieldToArea(PivotFieldType.DATA, 2); // Add the third field to the data area

Step 5: Refresh the Pivot Table

If your source data changes, you can refresh the Pivot Table to reflect the updates:

pivotTable.refreshData();

Step 6: Save the Workbook

Finally, save the workbook to persist your changes:

workbook.save("path_to_save_your_file.xlsx");

Conclusion

By following these steps, you can create a dynamic Pivot Table in Excel using Aspose.Cells. This allows you to summarize and analyze data effectively, even when the structure of your data changes from one export to another.

For more detailed examples and additional features, you can refer to the Aspose documentation on creating dynamic Pivot Tables here and here.

If you have any further questions or need assistance, feel free to ask!

3

@juanc,

In case, you still could not accomplish your task using Aspose.Cells APIs, please provide your expected Excel file (please zip it first prior attaching) containing your desired pivot table. We will check and then help you implement the task via Aspose.Cells APIs.