DEV Community

Allen Yang
Allen Yang

Posted on

Create Excel Pivot Tables Using C#

#programming #csharp #excel #pivottable

In data analysis and business reporting scenarios, pivot tables are a powerful tool for data summarization. They can quickly classify, summarize, and analyze large amounts of data, helping users understand data patterns from different dimensions.

For .NET developers, there are scenarios where Excel reports with pivot tables need to be automatically generated through code. For example:

  • Automatically generating sales analysis reports
  • Batch processing business data summaries
  • Dynamically creating financial analysis reports

This article will explain how to create and configure pivot tables in Excel worksheets using C#, including key steps such as data source setup, field layout, and styling.

Environment Preparation

First, you need to add the Spire.XLS for .NET library to your project. This can be installed via the NuGet package manager:

Install-Package Spire.XLS
Enter fullscreen mode Exit fullscreen mode

Core Implementation

Creating a Workbook and Basic Data

Before creating a pivot table, you need to prepare the source data. The following code demonstrates how to create a worksheet containing product sales data:

using Spire.Xls;

// Create a workbook instance
Workbook workbook = new Workbook();

// Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

// Set column headers
sheet.Range["A1"].Value = "Product";
sheet.Range["B1"].Value = "Month";
sheet.Range["C1"].Value = "Count";

// Fill product data
sheet.Range["A2"].Value = "Doc";
sheet.Range["A3"].Value = "Doc";
sheet.Range["A4"].Value = "Xls";
sheet.Range["A5"].Value = "Doc";
sheet.Range["A6"].Value = "Xls";
sheet.Range["A7"].Value = "Xls";

// Fill month data
sheet.Range["B2"].Value = "January";
sheet.Range["B3"].Value = "February";
sheet.Range["B4"].Value = "January";
sheet.Range["B5"].Value = "January";
sheet.Range["B6"].Value = "February";
sheet.Range["B7"].Value = "February";

// Fill quantity data
sheet.Range["C2"].Value = "10";
sheet.Range["C3"].Value = "15";
sheet.Range["C4"].Value = "9";
sheet.Range["C5"].Value = "7";
sheet.Range["C6"].Value = "8";
sheet.Range["C7"].Value = "10";
Enter fullscreen mode Exit fullscreen mode

This code creates a simple sales data table containing three fields: product name, month, and sales quantity. In practical applications, this data typically comes from databases or other data sources.

Creating a Pivot Table Cache

A pivot table works based on a data cache (PivotCache). The cache stores a copy of the source data to improve performance:

// Define the data source range
CellRange dataRange = sheet.Range["A1:C7"];

// Create a pivot table cache
PivotCache cache = workbook.PivotCaches.Add(dataRange);
Enter fullscreen mode Exit fullscreen mode

The PivotCache object is responsible for managing the pivot table's source data. By passing the data range to the cache, this data can be referenced in subsequent pivot table operations.

Adding a Pivot Table to the Worksheet

After creating the cache, you can add a pivot table to the worksheet:

// Add a pivot table at cell E10 on the worksheet
PivotTable pt = sheet.PivotTables.Add("Pivot Table", sheet.Range["E10"], cache);
Enter fullscreen mode Exit fullscreen mode

Three parameters are specified here:

  • "Pivot Table": The name of the pivot table
  • sheet.Range["E10"]: The starting position of the top-left corner of the pivot table
  • cache: The data cache created earlier

Configuring Row Fields

Row fields determine how data is grouped in the vertical direction. Multiple fields can be added to the row area to achieve multi-level grouping:

// Add the Product field to the row area
PivotField pf = pt.PivotFields["Product"] as PivotField;
pf.Axis = AxisTypes.Row;

// Add the Month field to the row area
PivotField pf2 = pt.PivotFields["Month"] as PivotField;
pf2.Axis = AxisTypes.Row;
Enter fullscreen mode Exit fullscreen mode

By setting the Axis property to AxisTypes.Row, fields can be placed in the row area. This way, data is first grouped by product, then within each product, further grouped by month.

Configuring Data Fields

Data fields are used to summarize numerical values. Common summary methods include sum, count, average, etc.:

// Add the Count field to the data area, using sum method
pt.DataFields.Add(pt.PivotFields["Count"], "SUM of Count", SubtotalTypes.Sum);
Enter fullscreen mode Exit fullscreen mode

The meaning of this line of code is:

  • pt.PivotFields["Count"]: Select the field to be summarized
  • "SUM of Count": The display name of the data field
  • SubtotalTypes.Sum: The summary type is sum

In addition to Sum, you can also use various summary types such as Count, Average, Max, Min.

Setting Styles and Auto-Fit

To improve readability, you can apply built-in styles to the pivot table and auto-fit column widths:

// Apply built-in style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;

// Calculate pivot table data
pt.CalculateData();

// Auto-fit column width to fit content
sheet.AutoFitColumn(5);
sheet.AutoFitColumn(6);
Enter fullscreen mode Exit fullscreen mode

The CalculateData() method ensures the pivot table completes calculations before saving. The AutoFitColumn() method automatically adjusts column widths based on content, making the output more aesthetically pleasing.

Saving the File

Finally, save the generated workbook to a file:

String result = "CreatePivotTable_output.xlsx";

// Save in Excel 2010 format
workbook.SaveToFile(result, ExcelVersion.Version2010);

// Release resources
workbook.Dispose();
Enter fullscreen mode Exit fullscreen mode

Complete Example

The following is the complete code example:

using System;
using Spire.Xls;

class Program
{
   static void Main()
    {
        // Create a workbook
        Workbook workbook = new Workbook();

        // Get the first worksheet
        Worksheet sheet = workbook.Worksheets[0];

        // Set column headers
        sheet.Range["A1"].Value = "Product";
        sheet.Range["B1"].Value = "Month";
        sheet.Range["C1"].Value = "Count";

        // Fill sample data
        sheet.Range["A2"].Value = "Doc";
        sheet.Range["A3"].Value = "Doc";
        sheet.Range["A4"].Value = "Xls";
        sheet.Range["A5"].Value = "Doc";
        sheet.Range["A6"].Value = "Xls";
        sheet.Range["A7"].Value = "Xls";

        sheet.Range["B2"].Value = "January";
        sheet.Range["B3"].Value = "February";
        sheet.Range["B4"].Value = "January";
        sheet.Range["B5"].Value = "January";
        sheet.Range["B6"].Value = "February";
        sheet.Range["B7"].Value = "February";

        sheet.Range["C2"].Value = "10";
        sheet.Range["C3"].Value = "15";
        sheet.Range["C4"].Value = "9";
        sheet.Range["C5"].Value = "7";
        sheet.Range["C6"].Value = "8";
        sheet.Range["C7"].Value = "10";

        // Create pivot table cache
        CellRange dataRange = sheet.Range["A1:C7"];
        PivotCache cache = workbook.PivotCaches.Add(dataRange);

        // Add pivot table
        PivotTable pt = sheet.PivotTables.Add("Pivot Table", sheet.Range["E10"], cache);

        // Configure row fields
        PivotField pf = pt.PivotFields["Product"] as PivotField;
        pf.Axis = AxisTypes.Row;

        PivotField pf2 = pt.PivotFields["Month"] as PivotField;
        pf2.Axis = AxisTypes.Row;

        // Configure data field
        pt.DataFields.Add(pt.PivotFields["Count"], "SUM of Count", SubtotalTypes.Sum);

        // Set style
        pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;

        // Calculate data and adjust column width
        pt.CalculateData();
        sheet.AutoFitColumn(5);
        sheet.AutoFitColumn(6);

        // Save file
        workbook.SaveToFile("CreatePivotTable_output.xlsx", ExcelVersion.Version2010);
        workbook.Dispose();

        Console.WriteLine("Pivot table created successfully!");
    }
}
Enter fullscreen mode Exit fullscreen mode

Preview of Generated Result

Python creates Excel pivot table

Practical Tips

Changing Field Layout

In addition to row fields and data fields, fields can also be added to the column area or filter area:

// Add field to column area
PivotField columnField = pt.PivotFields["Month"] as PivotField;
columnField.Axis = AxisTypes.Column;

// Add field to filter area
PivotField filterField = pt.PivotFields["Product"] as PivotField;
filterField.Axis = AxisTypes.Page;
Enter fullscreen mode Exit fullscreen mode

Using Different Summary Methods

Depending on analysis requirements, different summary types can be selected:

// Count
pt.DataFields.Add(pt.PivotFields["Count"], "Count", SubtotalTypes.Count);

// Average
pt.DataFields.Add(pt.PivotFields["Count"], "Average", SubtotalTypes.Average);

// Maximum
pt.DataFields.Add(pt.PivotFields["Count"], "Max", SubtotalTypes.Max);
Enter fullscreen mode Exit fullscreen mode

Customizing Pivot Table Options

Pivot table display options can be further configured:

// Show classic pivot table layout
pt.ShowClassicPivotTableLayout = true;

// Disable field list
pt.FieldPrintOptions.ShowFieldList = false;
Enter fullscreen mode Exit fullscreen mode

Conclusion

This article introduced how to create pivot tables in Excel using C#. By properly configuring data sources, field layouts, and style options, professional data analysis reports can be quickly generated.

After mastering these techniques, you can:

  • Extend to read real business data from databases
  • Add more fields and more complex grouping logic
  • Apply conditional formatting to enhance visualization effects
  • Batch generate multiple pivot tables for analysis

Pivot tables are one of the core features of Excel data analysis. Integrating them into automated workflows can greatly improve report generation efficiency.

Top comments (0)