Create Pivot Table In Excel

  • Demo
  • C# source
  • VB.Net source
This demo shows you how to create a privot table in excel.
No Matter How Big or Small Your Project is,
Any technical question related to our product, contact us at
Any question related to the purchase of product, contact us at
If you don't find the demo you want, please contact for the free customized demo just for you.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Spire.Xls;
namespace DemoOnlineCode
    class CreatePivotTableInExcel
        public void demoCreatePivotTableInExcel(string filePath,string resultFilePath)
            Workbook workbook = new Workbook();
            workbook = CreatePivotTable(filePath);

        public Workbook  CreatePivotTable(string filePath)
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Data Source";
            Worksheet sheet2 = workbook.CreateEmptySheet();
            sheet2.Name = "Pivot Table";

            CellRange dataRange = sheet.Range["A1:G17"];
            PivotCache cache = workbook.PivotCaches.Add(dataRange);
            PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

            var r1 = pt.PivotFields["Vendor No"];
            r1.Axis = AxisTypes.Row;
            pt.Options.RowHeaderCaption = "Vendor No";

            var r2 = pt.PivotFields["Name"];
            r2.Axis = AxisTypes.Row;

            pt.DataFields.Add(pt.PivotFields["Area"], "Average of Area", SubtotalTypes.Average);
            pt.DataFields.Add(pt.PivotFields["Sales"], "SUM of Sales", SubtotalTypes.Sum);
            pt.DataFields.Add(pt.PivotFields["OnHand"], "Max of OnHand", SubtotalTypes.Max);
            pt.DataFields.Add(pt.PivotFields["OnOrder"], "Min of OnOrder", SubtotalTypes.Min);
            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
            sheet = workbook.Worksheets[1];
            return workbook;

Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Data
Imports Spire.XLS
Namespace DemoOnlineCode
    Class CreatePivotTableInExcel
        Public Sub demoCreatePivotTableInExcel(filePath As String, resultFilePath As String)
            Dim workbook As New Workbook()
            workbook = CreatePivotTable(filePath)
        End Sub

        Public Function CreatePivotTable(filePath As String) As Workbook
            Dim workbook As New Workbook()
            workbook.LoadFromFile(filePath, ExcelVersion.Version2007)
            Dim sheet As Worksheet = workbook.Worksheets(0)
            sheet.Name = "Data Source"
            Dim sheet2 As Worksheet = workbook.CreateEmptySheet()
            sheet2.Name = "Pivot Table"

            Dim dataRange As CellRange = sheet.Range("A1:G17")
            Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)
            Dim pt As PivotTable = sheet2.PivotTables.Add("Pivot Table", sheet.Range("A1"), cache)

            Dim r1 = pt.PivotFields("Vendor No")
            r1.Axis = AxisTypes.Row
            pt.Options.RowHeaderCaption = "Vendor No"

            Dim r2 = pt.PivotFields("Name")
            r2.Axis = AxisTypes.Row

            pt.DataFields.Add(pt.PivotFields("Area"), "Average of Area", SubtotalTypes.Average)
            pt.DataFields.Add(pt.PivotFields("Sales"), "SUM of Sales", SubtotalTypes.Sum)
            pt.DataFields.Add(pt.PivotFields("OnHand"), "Max of OnHand", SubtotalTypes.Max)
            pt.DataFields.Add(pt.PivotFields("OnOrder"), "Min of OnOrder", SubtotalTypes.Min)
            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12
            sheet = workbook.Worksheets(1)
            Return workbook

        End Function
    End Class
End Namespace