Pivot table in Excel Add-in code example

Pivot Table

A pivot table is a table which shows calculative summary of data. This summary can include aggregate functions like sums, averages, count etc. which makes pivot more readable and attractive. In this article we will be using VSTO (Visual Studio Tools for Office) in C# and VB Style coding.

Create PivotTable

Step 1: Select Insert tab and click Pivot Table

Step 2: Select source data in Table Range selector under Create Pivot Table dialog:

Step 3: Selection destination where you want to populate you pivot table under Location range selector as shown above and say OK.

Step 4: Drag fields from upper list to desired areas like Filters, Columns, Rows or Values and observe the Pivot behavior:

Filters

Filters will further summarize your record set in pivot by evaluating conditions provided. It is similar to normal filter in excel where you can do greater than, less than or equal operations as shown below:

Column formatting

Select respective field and click on Field Settings… as shown below:

Click Number format button and select appropriate number formatting from the format cells dialog as shown below and say OK:

VBA code example

Public Sub CreatePivotTableExample()
    'Destination range
    Dim oRange As Range
    Set oRange = ActiveSheet.Range("C2")
    
    'Workbook object
    Dim oWorkbook As Workbook
    Set oWorkbook = ThisWorkbook
    
    Const PIVOT_NAME As String = "myTableTest"
    'Create Cache
    Dim oPivotCache As PivotCache
    Set oPivotCache = oWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "A1:B19", Version:=6)
    'Populate table
    oPivotCache.CreatePivotTable TableDestination:=oRange, TableName:=PIVOT_NAME, DefaultVersion:=6
    
    'Set fields
    With ActiveSheet.PivotTables(PIVOT_NAME).PivotFields("Product")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(PIVOT_NAME).PivotFields("Count")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    'Change column format
    ActiveSheet.PivotTables(PIVOT_NAME).PivotFields("Count").Name = "0.00"
    
    'CleanUp
    If Not oRange Is Nothing Then
        Set oRange = Nothing
    End If
    If Not oPivotCache Is Nothing Then
        Set oPivotCache = Nothing
    End If
    If Not oWorkbook Is Nothing Then
        Set oWorkbook = Nothing
    End If
End Sub

C# code example

private void btnPivotTable_Click(object sender, RibbonControlEventArgs e)
{
	excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
	excel.Worksheet oWorksheet = oWorkbook.ActiveSheet;
	try
	{
		//Capture user response
		var userRange = Globals.ThisAddIn.Application.InputBox("Select range to insert Pivot Table:",
			"VBAOVERALL Range Selector", string.Empty, System.Reflection.Missing.Value,
			System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, 8);
		
		//Cast user range
		if (userRange != null)
		{
			//build range from user input
			excel.Range oRange = (excel.Range)userRange;
		
			//Create pivot caches collection
			excel.PivotCaches oCaches = oWorkbook.PivotCaches();
			
			//create or add new pivot cache
			excel.PivotCache oCache= oCaches.Add(SourceType: excel.XlPivotTableSourceType.xlDatabase, SourceData: oRange);
			
			//get pivot tables collection from worksheet
			excel.PivotTables oPivotTables = oWorksheet.PivotTables();
			
			//Create or Add new pivot table using cache
			oPivotTables.Add(oCache, oWorksheet.Range["J2"], "myTable");
			
			//add Pivot field
			excel.PivotTable oPivotTable = oWorksheet.PivotTables("myTable");
			excel.PivotField oPivotField_one = oPivotTable.PivotFields("Column-1");
			oPivotField_one.Orientation = excel.XlPivotFieldOrientation.xlRowField;
			oPivotField_one.Position = 1;

			//Add data field
			excel.PivotField oPivotField_two = oPivotTable.PivotFields("Column-2");
			oPivotTable.AddDataField(oPivotField_two, "Sum of Column-2", excel.XlConsolidationFunction.xlSum);
		}
	}
	catch (Exception ex)
	{

		MessageBox.Show(ex.Message);
	}
}

Output

A range selector will appear prompting user to make range selection on top the Pivot would build as shown below:

On click of OK the pivot will be formed as shown below:

Next : Insert picture from external file in Excel c#

Share on Facebook

Tweet