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#