Make Custom Calculations Programmatically Using Excel-Like Pivot Tables in C# .NET & Java

Excel is one of the most utilized applications for Data Analysis today. Equipped with powerful features such as the Pivot Table, it can easily process vast amounts of data and visualize it in amazing ways. A Pivot Table can extract important data from an extensive dataset and rearrange it to bring crucial and valuable insights in a crisp, easy, and manageable way.

In Pivot Table, data analysts often want to make custom calculations. More commonly, these calculations are performed on the fields. For example, combine the fields “Quantity” and “UnitPrice” to obtain the “Sales Amount”. However, certain spreadsheets need calculations performed directly on the field values (or items stored within a field). For example, compared to other devices like tablets or desktops, the sales percent share of mobile devices, focuses on the sales of core products while combining all other products as “Other Products” and many more.

Excel provides the Calculated Item with Pivot Table to meet this business requirement. In this blog, we’ll see how we can programmatically add this Calculated item to a Pivot table using GcExcel for C# .NET and Java, following these steps:

1. Load the Workbook

2. Create a Pivot Table

3. Add Calculated Item to the Pivot

4. Hide the Duplicate Name Items

5. Save the Workbook

Use-Case

As the Purchase Manager of your company, you have been asked to analyze the order statuses and compare the completed orders to the orders lost mid-way for each product. Based on the outcomes, you want to make decisions on whether to continue with a particular product or not.

The data is available to you in a simple tabular format as shown below:

You want to prepare a Pivot table from this data, keeping Completed and Processing Order items under analysis while moving other items under a separate item — Lost.

Let’s see how this is done using the above use-case and a few simple steps with GcExcel in C# .NET and Java.

Step 1 — Load the Workbook

First, load the Excel file with data into the application using GcExcel with the following code:

C#/Java

Workbook workbook = new Workbook();
workbook.open("SalesData.xlsx");

Step 2 — Create a Pivot Table

Next, add a Pivot Table for the available data. The data is available from the “Sales data” worksheet in the Excel file. Add the Pivot Table to a new sheet using the Add method of the IWorksheet interface as shown in the code below:

C#

//Add sheet for Pivot Table
IWorksheet pivotSheet = workbook.Worksheets.Add();
pivotSheet.Name = "Sales Analysis";
// Add pivot table.
IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:G71"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add(pivotCache, pivotSheet.Range["A1"]);
pivotTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField;
pivotTable.PivotFields["Status"].Orientation = PivotFieldOrientation.RowField;
pivotTable.PivotFields["Category"].Orientation = PivotFieldOrientation.ColumnField;
pivotTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;
pivotTable.DataFields["Sum of Amount"].NumberFormat = "$#,##0_);($#,##0)";

Java

//Add sheet for Pivot Table
IWorksheet pivotSheet = workbook.getWorksheets().add();
pivotSheet.setName("Sales Analysis");
// Add pivot table.
IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:G71"));
IPivotTable pivotTable = pivotSheet.getPivotTables().add(pivotCache, pivotSheet.getRange("A1"));
pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
pivotTable.getPivotFields().get("Status").setOrientation(PivotFieldOrientation.RowField);
pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
pivotTable.getDataFields().get("Sum of Amount").setNumberFormat("$#,##0_);($#,##0)");

The Pivot Table at this step will appear as follows:

Step 3 — Add Calculated Item to the Pivot

When the Pivot Table is ready, the next step is adding the Calculated Item. To add a calculated item to a Pivot Table using GcExcel, use the ICalculatedItems interface to add a collection of calculated items to the PivotFields. Add a name and expression to it as shown below:

C#

//Create a calculated item on status field
ICalculatedItems statusCalcItems_lost = pivotTable.PivotFields["Status"].CalculatedItems();
   
//Add name and expression to the calculated item
statusCalcItems_lost.Add("Lost", "=Failed+Returned+Pending");

Java

//Create a calculated item on status field
ICalculatedItems statusCalcItems_lost = pivotTable.getPivotFields().get("Status").getCalculatedItems();
//Add name and expression to the calculated item
statusCalcItems_lost.add("Lost", "=Failed+Returned+Pending");

The Name appears as a value in the Pivot table, and the Expression performs the desired calculation. Here, the expression aggregates the sales total for Failed, Returned, and Pending orders and will be displayed by the item named “Lost”.

The Pivot Table at this point will appear as shown below:

Step 4 — Hide the Duplicate Name Items

Once the calculated item is added, you will want to hide the items duplicated by the calculated item and avoid double-counting. Here, we want to hide the “Failed”, “Returned”, and “Pending” items. To do so, get the PivotItems from a Pivot field and set the Visible property for required items to false, as shown in the code snippet below:

C#

// hide the duplicate normal item combined in the calculated item
IPivotItems status = pivotTable.PivotFields["Status"].PivotItems;
status["Failed"].Visible = false;
status["Returned"].Visible = false;
status["Pending"].Visible = false;

Java

// hide the duplicate normal item combined in the calculated item
IPivotItems status = pivotTable.getPivotFields().get("Status").getPivotItems();
status.get("Failed").setVisible(false);
status.get("Returned").setVisible(false);
status.get("Pending").setVisible(false);

The Pivot Table, with calculated items, at this step will appear as shown below:

Step 5 — Save the Workbook

Finally, apply styling and formatting to the cells, adjust column width, etc. and save the workbook. The final report is shown below:

C#/Java

workbook.Save("CalculatedItem.xlsx");

Conclusion

With the Calculated item, you can do almost any kind of calculation on a field item and make your analysis result look more assembled and reasonable. You may do calculations on field items, such as

• calculate the discounted price for a particular product item
• calculate the percentage share of an item compared to others
• reference a field item with an alternate name, and much more

Try out our .NET | Java demos for Calculated Items.

Check out the .NET | Java demos for Calculated Fields.

Originally published at https://www.developer.mescius.com on September 2, 2022.