Make Custom Calculations Programmatically Using Excel-Like Pivot Tables in C# .NET & Java
Tags: webdev, dotnet, tutorial, csharp
Published: 2022-09-23 20:41:16

---
canonical_url: https://www.grapecity.com/blogs/make-custom-calculations-progammatically-using-excel-like-pivot-tables-c-sharp-net-java
description: Make Custom Calculations Programmatically Using CalculatedItem in Excel-Like Pivot Tables in C# .NET & Java. See more from GrapeCity Documents today.
---
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](https://www.grapecity.com/documents-api-excel/docs/online/PivotTableSettings.html) to a Pivot table using GcExcel for [C# .NET](https://grapecity.com/documents-api-excel) and [Java](https://grapecity.com/documents-api-excel-java), following these steps:

1\. [Load the Workbook](#load)

2\. [Create a Pivot Table](#create)

3\. [Add Calculated Item to the Pivot](#add)

4\. [Hide the Duplicate Name Items](#hide)

5\. [Save the Workbook](#save)

## 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:

![Pivot Table](https://files.grapecity.com/gc-website-media/ajip5fq5/01-pivottable.png?width=500&height=226.95652173913044)

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.

![Pivot Table](https://files.grapecity.com/gc-website-media/ehgavb5y/02-pivottable.png?width=500&height=268.59504132231405)

Let’s see how this is done using the above use-case and a few simple steps with GcExcel in [C# .NET](https://www.grapecity.com/documents-api-excel/docs/online/overview.html) and [Java](https://www.grapecity.com/documents-api-excel-java/docs/online/overview.html).

### <a id="load"></a>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");
```

### <a id="create"></a>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:

![Pivot Table](https://files.grapecity.com/gc-website-media/34xfooz3/03-pivottable.png?width=419&height=293)

### <a id="add"></a>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:

![Pivot Table](https://files.grapecity.com/gc-website-media/fb0n5sow/04-pivottable.png?width=449&height=316)

### <a id="hide"></a>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:

![Pivot Table](https://files.grapecity.com/gc-website-media/bvgdzmes/05-pivottable.png?width=446&height=257)

### <a id="save"></a>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");
```

![Pivot Table](https://files.grapecity.com/gc-website-media/ia5gh0fi/06-pivottable.png?width=500&height=337.41830065359477)

_Download the complete sample for_ [_.NET_](https://cdn.mescius.io/umb/media/i1lkvjop/calculateditem_net.zip) _and_ [_Java_](https://cdn.mescius.io/umb/media/p45d43rm/calculateditem_java.zip) _and see the steps in action!_

## 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](https://www.grapecity.com/documents-api-excel/demos/calculateditem) | [Java](https://www.grapecity.com/documents-api-excel-java/demos/calculateditem) demos for Calculated Items.

Check out the [.NET](https://www.grapecity.com/documents-api-excel/demos/calculatedfield) | [Java](https://www.grapecity.com/documents-api-excel-java/demos/calculatedfield) demos for Calculated Fields.