Best Solution to Create Excel Pivot Table with C#, VB.NET

Submit Blog |

Blogs Home |

An Excel pivot table is a table that can miraculously aggregate your table information and show the information in a new perspective. Pivot table enables you to move rows to columns or vice versa. Of course, the most important function of excel pivot table is to provide convenience for analyzing excel data. But the problem is that people always think that excel pivot table is difficult to create. Actually, it is not hard at all, if you do not believe it, please see the below method.

Before my method, I want to introduce an Excel component Spire.XLS to help me finish the task quickly. Spire. XLS supports C#, VB.NET, ASP.NET, ASP.NET MVC and Silverlight.

Spire.XLS Download Zip

How to create excel pivot table with C#, VB.NET

The whole procedure can be following steps:

Step1. Create a new project.

1.      Create a new project in Visual Studio.

2.      Set the Target Framework to be .NET Framework 2 or above in Properties.

3.      Add Spire.XLS Dll as reference.

Step2. Create excel pivot table

1. Load an Excel file from system.

In this step, you need to create a new Excel workbook and load an excel file. There are two worksheets needed, one is for the current worksheet, the other is for pivot table worksheet, so an empty worksheet should be created. Then, name them to be "Data Source" and "Pivot Table".

C# Code:


            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\DatatableSample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Data Source";
            Worksheet sheet2 = workbook.CreateEmptySheet();
            sheet2.Name = "Pivot Table";

VB.NET Code:


         Dim workbook As New Workbook()
         workbook.LoadFromFile("D:\michelle\e-iceblue\Spire.XLS\Demos\Data\DatatableSample.xlsx")
         Dim sheet As Worksheet = workbook.Worksheets(0)
         sheet.Name = "Data Source"
         Dim sheet2 As Worksheet = workbook.CreateEmptySheet()
         sheet2.Name = "Pivot Table"

2. Create excel pivot table

First, you should set table range according to the original data source and create a PivotCahce to save data information. Then, create a pivot table in "Pivot Table" worksheet. Finally, using sheet2.PivotTables.Add() method to assign value for excel pivot table. Three parameters passed to this method: name string, table location and pivot cache.

C# Code:

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

VB.NET Code:

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

Second, define row labels. You can get some data information as row labels to assign data.

C# Code:

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

VB.NET Code:

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

Finally, add data fields and set format. Besides the fields I add in the method, you also can add some other fields in need to calculate by using pt.DataFileds.Add() method.

C# Code:

            pt.DataFields.Add(pt.PivotFields["Sales"], "Average of Sales", SubtotalTypes.Average);
            pt.DataFields.Add(pt.PivotFields["OnHand"], "SUM of OnHand", SubtotalTypes.Sum);
            pt.DataFields.Add(pt.PivotFields["OnOrder"], "SUM of OnOrder", SubtotalTypes.Sum);
            pt.DataFields.Add(pt.PivotFields["Population"], "Averrage of Population", SubtotalTypes.Average);
            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;

VB.NET Code:

           pt.DataFields.Add(pt.PivotFields("Sales"), "Average of Sales", SubtotalTypes.Average)
           pt.DataFields.Add(pt.PivotFields("OnHand"), "SUM of OnHand", SubtotalTypes.Sum)
           pt.DataFields.Add(pt.PivotFields("OnOrder"), "SUM of OnOrder", SubtotalTypes.Sum)
           pt.DataFields.Add(pt.PivotFields("Population"), "Averrage of Population", SubtotalTypes.Average)
           pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

Step3. Save and launch the file

C# Code:

            workbook.SaveToFile("PivotTable.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("PivotTable.xlsx");

VB.NET Code:

            workbook.SaveToFile("PivotTable.xlsx", ExcelVersion.Version2010)
            System.Diagnostics.Process.Start("PivotTable.xlsx")

Preview

Data Source

Pivot Table

More About Spire.XLS

Spire.XLS is a professional Excel component which enables developers/programmers to fast generate, read, write and modify Excel document for .NET and Silverlight. It allows user to operate Excel document directly such as save to stream, save as web response, copy, lock/unlock worksheet, set up workbook properties, etc.

Code Source: http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Program-Guide.html

Blog Source: http://everlasting129.weebly.com

Found interesting? Add this to:

| More | Bookmark It

More Blogs from Lacy

More ...

About Keating Arly

Experience:	1 year(s)
Home page:	http://www.e-iceblue.com
Member since:	Tuesday, March 27, 2012
Level:	Starter
Status:	[Member]
Biography:

>> Write Response - Respond to this post and get points

More Blogs

Submit Blog | More ...