Create Excel Pivot Tables in C# and VB.NET

Following sample shows how to create Pivot Table from a range of cells.

GemBox.Spreadsheet currently does not provide functionality to calculate the actual data of the Pivot Table.

It provides the functionality to arrange and customize Pivot Table appearance. Microsoft Excel application will refresh the Pivot Table when the output file is opened.

Pivot Tables are loaded from and saved to XLSX file format only.

Screenshot

Pivot Tables Screenshot

See the full code below, use RUN EXAMPLE to execute.


1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = new ExcelFile();
17
18        ExcelWorksheet ws1 = ef.Worksheets.Add("SourceSheet");
19
20        // Specify sheet formatting.
21        ws1.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
22        ws1.Columns[0].SetWidth(3, LengthUnit.Centimeter);
23        ws1.Columns[1].SetWidth(3, LengthUnit.Centimeter);
24        ws1.Columns[2].SetWidth(3, LengthUnit.Centimeter);
25        ws1.Columns[3].SetWidth(3, LengthUnit.Centimeter);
26        ws1.Columns[3].Style.NumberFormat = "[$$-409]#,##0.00";
27
28        var cells = ws1.Cells;
29
30        // Specify header row.
31        cells[0, 0].Value = "Departments";
32        cells[0, 1].Value = "Names";
33        cells[0, 2].Value = "Years of Service";
34        cells[0, 3].Value = "Salaries";
35
36        // Insert random data to sheet.
37        var random = new Random();
38        var departments = new string[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" };
39        var names = new string[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" };
40        var years = new string[] { "1-10", "11-20", "21-30", "over 30" };
41        for (int i = 0; i < 100; ++i)
42        {
43            cells[i + 1, 0].Value = departments[random.Next(departments.Length)];
44            cells[i + 1, 1].Value = names[random.Next(names.Length)] + ' ' + (i + 1).ToString();
45            cells[i + 1, 2].Value = years[random.Next(years.Length)];
46            cells[i + 1, 3].SetValue(random.Next(10, 101) * 100);
47        }
48
49        // Create pivot cache from cell range "SourceSheet!A1:D100".
50        PivotCache cache = ef.PivotCaches.AddWorksheetSource("SourceSheet!A1:D100");
51
52        // Create new sheet for pivot table.
53        ExcelWorksheet ws2 = ef.Worksheets.Add("PivotSheet");
54
55        // Create pivot table "Company Profile" using the specified pivot cache and add it to the worksheet at the cell location 'A1'.
56        PivotTable table = ws2.PivotTables.Add(cache, "Company Profile", "A1");
57
58        // Aggregate 'Names' values into count value and show it as a percentage of row.
59        PivotField field = table.DataFields.Add("Names");
60        field.Function = PivotFieldCalculationType.Count;
61        field.ShowDataAs = PivotFieldDisplayFormat.PercentageOfRow;
62        field.Name = "% of Empl.";
63
64        // Aggregate 'Salaries' values into average value.
65        field = table.DataFields.Add("Salaries");
66        field.Function = PivotFieldCalculationType.Average;
67        field.Name = "Avg. Salary";
68        field.NumberFormat = "[$$-409]#,##0.00";
69
70        // Group rows into 'Departments'.
71        table.RowFields.Add("Departments");
72
73        // Group columns first into 'Years of Service' and then into 'Values' (count 'Names' and average 'Salaries').
74        table.ColumnFields.Add("Years of Service");
75        table.ColumnFields.Add(table.DataPivotField);
76
77        // Specify the string to be displayed in row and column header.
78        table.RowHeaderCaption = "Departments";
79        table.ColumnHeaderCaption = "Years of Service";
80
81        // Do not show grand totals for rows.
82        table.RowGrandTotals = false;
83
84        // Set pivot table style.
85        table.BuiltInStyle = BuiltInPivotStyleName.PivotStyleMedium7;
86
87        ef.Save("Pivot Tables.xlsx");
88    }
89}
1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = New ExcelFile
16
17        Dim ws = ef.Worksheets.Add("SourceSheet")
18
19        ' Specify sheet formatting.
20        ws.Rows(0).Style.Font.Weight = ExcelFont.BoldWeight
21        ws.Columns(0).SetWidth(3, LengthUnit.Centimeter)
22        ws.Columns(1).SetWidth(3, LengthUnit.Centimeter)
23        ws.Columns(2).SetWidth(3, LengthUnit.Centimeter)
24        ws.Columns(3).SetWidth(3, LengthUnit.Centimeter)
25        ws.Columns(3).Style.NumberFormat = "[$$-409]#,##0.00"
26
27        Dim cells = ws.Cells
28
29        ' Specify header row.
30        cells(0, 0).Value = "Departments"
31        cells(0, 1).Value = "Names"
32        cells(0, 2).Value = "Years of Service"
33        cells(0, 3).Value = "Salaries"
34
35        ' Insert random data to sheet.
36        Dim random = New Random()
37        Dim departments = New String() {"Legal", "Marketing", "Finance", "Planning", "Purchasing"}
38        Dim names = New String() {"John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat"}
39        Dim years = New String() {"1-10", "11-20", "21-30", "over 30"}
40        For i As Integer = 0 To 100
41            cells(i + 1, 0).Value = departments(random.Next(departments.Length))
42            cells(i + 1, 1).Value = names(random.Next(names.Length)) + " "c + (i + 1).ToString()
43            cells(i + 1, 2).Value = years(random.Next(years.Length))
44            cells(i + 1, 3).SetValue(random.Next(10, 101) * 100)
45        Next
46
47        ' Create pivot cache from cell range "SourceSheet!A1:D100".
48        Dim cache As PivotCache = ef.PivotCaches.AddWorksheetSource("SourceSheet!A1:D100")
49
50        ' Create new sheet for pivot table.
51        Dim ws2 = ef.Worksheets.Add("PivotSheet")
52
53        ' Create pivot table "Company Profile" using the specified pivot cache and add it to the worksheet at the cell location 'A1'.
54        Dim table As PivotTable = ws2.PivotTables.Add(cache, "Company Profile", "A1")
55
56        ' Aggregate 'Names' values into count value and show it as a percentage of row.
57        Dim field As PivotField = table.DataFields.Add("Names")
58        field.Function = PivotFieldCalculationType.Count
59        field.ShowDataAs = PivotFieldDisplayFormat.PercentageOfRow
60        field.Name = "% of Empl."
61
62        ' Aggregate 'Salaries' values into average value.
63        field = table.DataFields.Add("Salaries")
64        field.Function = PivotFieldCalculationType.Average
65        field.Name = "Avg. Salary"
66        field.NumberFormat = "[$$-409]#,##0.00"
67
68        ' Group rows into 'Departments'.
69        table.RowFields.Add("Departments")
70
71        ' Group columns first into 'Years of Service' and then into 'Values' (count 'Names' and average 'Salaries').
72        table.ColumnFields.Add("Years of Service")
73        table.ColumnFields.Add(table.DataPivotField)
74
75        ' Specify the string to be displayed in row and column header.
76        table.RowHeaderCaption = "Departments"
77        table.ColumnHeaderCaption = "Years of Service"
78
79        ' Do not show grand totals for rows.
80        table.RowGrandTotals = False
81
82        ' Set pivot table style.
83        table.BuiltInStyle = BuiltInPivotStyleName.PivotStyleMedium7
84
85        ef.Save("Pivot Tables.xlsx")
86
87    End Sub
88
89End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.