Creating Dynamic Pivot Tables and Charts with C#

Creating Excel reports programmatically becomes challenging when advanced features like Pivot Tables and Pivot Charts are involved. While basic spreadsheets are easy to generate, automating dynamic data analysis and visualization requires a deeper understanding of Excel’s structure and the right libraries. This tutorial shows how to create Excel Pivot Charts using C#, enabling you to transform raw data into clear, actionable visualizations through automation.

You’ll be guided through the complete workflow—from preparing source data and building a Pivot Table to generating a Pivot Chart and saving the final workbook. By the end, you’ll know how to define fields, configure chart types, and integrate automated Excel reporting into your C# applications efficiently.

Understanding Pivot Tables and Charts Programmatically

At the core of dynamic data analysis in Excel lies the Pivot Table. It's a powerful tool that summarizes, analyzes, explores, and presents summary data from a larger dataset. A Pivot Chart, in turn, is a graphical representation of the data within a Pivot Table. This fundamental relationship is crucial: you cannot have a Pivot Chart without an underlying Pivot Table. The chart directly reflects the structure and aggregations defined by its associated Pivot Table, meaning any changes to the Pivot Table automatically update the Pivot Chart.

The benefits of programmatically creating Pivot Charts are substantial:

• Automation: Eliminate manual data manipulation and chart creation, saving time and reducing human error.
• Consistency: Ensure standardized reports and visualizations across different datasets or time periods.
• Integration: Seamlessly embed advanced reporting features into your existing C# applications.
• Scalability: Generate complex reports for large datasets efficiently.
• Dynamic Reporting: Create templates that adapt to varying data inputs, producing up-to-date analyses whenever needed.

For our programmatic solution, we will be using Spire.XLS for .NET. This library provides a robust set of functionalities for interacting with Excel files, allowing developers to create, read, edit, and convert Excel documents with C#. Its object model closely mirrors Excel's structure, making it intuitive for tasks like creating Pivot Tables and Charts.

Setting Up Your C# Project and Data Source

Before we dive into creating Pivot Charts, we need to set up our development environment and prepare some sample data.

Project Setup

1. Create a New C# Project: Open Visual Studio and create a new Console App (.NET Core) or Console App (.NET Framework) project. Name it appropriately, for instance, ExcelPivotChartGenerator.
2. Install Spire.XLS for .NET: Right-click on your project in the Solution Explorer, select "Manage NuGet Packages...", go to the "Browse" tab, search for Spire.XLS, and install the Spire.XLS package.

Preparing Your Data Source

For this tutorial, we'll use a simple DataTable to simulate sales data. This data will then be loaded into an Excel worksheet, which will serve as the source for our Pivot Table.

using System.Data;
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace ExcelPivotChartGenerator
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new workbook
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Sales Data";

            // Prepare sample data in a DataTable
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("Region", typeof(string));
            dataTable.Columns.Add("Product", typeof(string));
            dataTable.Columns.Add("Salesperson", typeof(string));
            dataTable.Columns.Add("Units Sold", typeof(int));
            dataTable.Columns.Add("Revenue", typeof(double));
            dataTable.Columns.Add("Date", typeof(DateTime));

            // Add sample data
            dataTable.Rows.Add("East", "Laptop", "Alice", 10, 12000.00, new DateTime(2023, 1, 15));
            dataTable.Rows.Add("West", "Mouse", "Bob", 50, 750.00, new DateTime(2023, 1, 20));
            dataTable.Rows.Add("East", "Keyboard", "Alice", 25, 1250.00, new DateTime(2023, 2, 10));
            dataTable.Rows.Add("North", "Monitor", "Charlie", 5, 1500.00, new DateTime(2023, 2, 25));
            dataTable.Rows.Add("West", "Laptop", "Bob", 8, 9600.00, new DateTime(2023, 3, 5));
            dataTable.Rows.Add("South", "Mouse", "David", 30, 450.00, new DateTime(2023, 3, 12));
            dataTable.Rows.Add("North", "Keyboard", "Charlie", 15, 750.00, new DateTime(2023, 4, 1));
            dataTable.Rows.Add("East", "Monitor", "Alice", 3, 900.00, new DateTime(2023, 4, 8));
            dataTable.Rows.Add("West", "Mouse", "Bob", 40, 600.00, new DateTime(2023, 4, 15));
            dataTable.Rows.Add("South", "Laptop", "David", 12, 14400.00, new DateTime(2023, 5, 2));
            dataTable.Rows.Add("East", "Laptop", "Alice", 7, 8400.00, new DateTime(2023, 5, 10));
            dataTable.Rows.Add("North", "Monitor", "Charlie", 6, 1800.00, new DateTime(2023, 5, 20));

            // Insert data into the worksheet
            sheet.InsertDataTable(dataTable, true, 1, 1);

            // Auto-fit columns for better readability
            sheet.AllocatedRange.AutoFitColumns();

            // The rest of the code for pivot table and chart will go here
            // ...
        }
    }
}

In this code, we create a DataTable with various sales metrics and then use sheet.InsertDataTable() to populate the first worksheet of our Excel workbook. The true parameter indicates that the first row of the DataTable contains headers.

Creating the Pivot Table in C

Now that our data is in the Excel worksheet, we can proceed to create the Pivot Table. This involves defining the data source, specifying row and column fields, and setting the value fields for aggregation.

            // ... (previous code)

            // Add a new worksheet for the Pivot Table and Chart
            Worksheet pivotSheet = workbook.Worksheets.Add("Sales Analysis");

            // Get the data range for the Pivot Table (A1:F13 from "Sales Data" sheet)
            CellRange dataRange = sheet.Range["A1:F" + (dataTable.Rows.Count + 1)];

            // Create a Pivot Cache
            PivotCache cache = workbook.PivotCaches.Add(dataRange);

            // Create a Pivot Table
            // The first parameter is the name of the Pivot Table
            // The second parameter is the destination cell for the top-left corner of the Pivot Table
            // The third parameter is the Pivot Cache created above
            PivotTable pivotTable = pivotSheet.PivotTables.Add("SalesPivotTable", pivotSheet.Range["A1"], cache);

            // Add fields to the Pivot Table
            // Add 'Region' to Row Fields
            pivotTable.PivotFields["Region"].Axis = AxisTypes.Row;

            // Add 'Product' to Column Fields
            pivotTable.PivotFields["Product"].Axis = AxisTypes.Column;

            // Add 'Salesperson' to Report Filter Fields
            PivotReportFilter reportFilter = new PivotReportFilter("Salesperson", true);
            pivotTable.ReportFilters.Add(reportFilter);
            PivotReportFilter reportFilter1 = new PivotReportFilter("Date", true);

            // Add 'Revenue' to Data Fields (values) and set aggregation to Sum
            //IPivotField revenueField =
            pivotTable.DataFields.Add(pivotTable.PivotFields["Revenue"], "Sum of Revenue", SubtotalTypes.Sum);
            //revenueField.Axis = AxisTypes.Data;
            //revenueField.Subtotals = SubtotalTypes.Sum; // Default is Sum for numeric data
            //revenueField.NumberFormat = "$#,##0.00"; // Format as currency

            // Add 'Units Sold' to Data Fields (values) and set aggregation to Sum
            //IPivotField unitsSoldField =
            pivotTable.DataFields.Add(pivotTable.PivotFields["Units Sold"], "Sum of Units Sold", SubtotalTypes.Sum);
            //unitsSoldField.Axis = AxisTypes.Data;
            //unitsSoldField.Subtotals = SubtotalTypes.Sum;
            //unitsSoldField.NumberFormat = "#,##0";

            // Set Pivot Table style
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium10;

            // Refresh the Pivot Table to apply changes
            pivotTable.CalculateData();

            // Auto-fit columns for the pivot sheet
            pivotSheet.AllocatedRange.AutoFitColumns();

            // ... (rest of the code for chart)

In this section:

• We create a new worksheet, Sales Analysis, to house our Pivot Table and Chart.
• CellRange dataRange defines the source data from our Sales Data sheet.
• A PivotCache is created, which is an internal Excel component that stores the source data for the Pivot Table, improving performance.
• The PivotTable object is instantiated, specifying its name, target location (pivotSheet.Range["A1"]), and the PivotCache.
• We then define the structure of the Pivot Table by assigning fields to Row (Region), Column (Product) (Report Filter - Salesperson), and Data (Revenue, Units Sold) axes.
• For data fields, we explicitly set the Subtotal type (e.g., Sum) and apply number formatting.
• Finally, pivotTable.BuiltInStyle applies a professional look, and pivotTable.CalculateData() refreshes the table to reflect all settings.

Generating the Pivot Chart from the Pivot Table

With the Pivot Table successfully created, the next step is to generate a Pivot Chart that visualizes its data. Remember, the chart is intrinsically linked to the table.

            // ... (previous code for pivot table)

            // Add a chart to the pivot sheet
            // We specify the chart type directly when creating it.
            Chart chart = pivotSheet.Charts.Add();
            chart.ChartType = ExcelChartType.ColumnClustered; // A common and versatile chart type

            // Link the chart to the Pivot Table
            chart.PivotTable = pivotTable;

            // Set the position and size of the chart on the worksheet
            chart.LeftColumn = 1;
            chart.TopRow = 12;
            chart.RightColumn = 8;
            chart.BottomRow = 35;

            // Customize chart elements
            chart.ChartTitle = "Sales Performance by Region and Product";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;

            // Set axis titles
            chart.PrimaryCategoryAxis.Title = "Region / Product";
            chart.PrimaryValueAxis.Title = "Amount";
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;

            // Position the legend
            chart.Legend.Position = LegendPositionType.Right;

            // Hide the field buttons on the chart for a cleaner look
            //chart.DisplayEntireFieldButtons = false;

            // Save the workbook to a file
            string filePath = "PivotChartReport.xlsx";
            workbook.SaveToFile(filePath, ExcelVersion.Version2016);
            workbook.Dispose();

            Console.WriteLine($"Excel file with Pivot Table and Chart created successfully: {filePath}");
            Console.ReadKey();
        }
    }
}

Here's a breakdown of the chart creation process:

• pivotSheet.Charts.Add() creates a new Chart object on our Sales Analysis worksheet.
• chart.ChartType = ExcelChartType.ColumnClustered; sets the visual representation. Spire.XLS supports a wide array of chart types, allowing you to pick the best visualization for your data.
• Crucially, chart.PivotTable = pivotTable; establishes the link between the chart and the previously defined Pivot Table. This ensures the chart dynamically updates with any changes to the Pivot Table.
• The chart's LeftColumn, TopRow, RightColumn, and BottomRow properties define its placement and size on the worksheet.
• We then proceed with various customizations: chart.ChartTitle sets the main title; chart.PrimaryCategoryAxis.Title and chart.PrimaryValueAxis.Title label the axes; chart.Legend.Position adjusts the legend's location; chart.ShowPivotChartFieldButtons = false; cleans up the chart interface by hiding interactive field buttons.
• Finally, workbook.SaveToFile() saves the entire workbook, containing both the raw data, the Pivot Table, and the Pivot Chart, to an Excel file.

Preview of the Pivot Chart

Below is a preview of the Pivot Chart generated by the above code:

Advanced Customizations and Best Practices

While the above covers the fundamentals, here are some considerations for more advanced scenarios and best practices:

• Dynamic Source Data: If your raw data changes frequently, ensure your dataRange is defined dynamically (e.g., by finding the last row/column) or by using a named range in Excel that expands automatically.
• Refreshing Pivot Tables: Use pivotTable.CalculateData() to refresh the table during creation, for existing workbooks, or for dynamic updates.
• Multiple Data Fields: When you have multiple data fields (like 'Revenue' and 'Units Sold' in our example), Excel automatically creates a "Values" field. The Pivot Chart will then display a series for each of these value fields.
• Chart Types: Experiment with different ExcelChartType options (e.g., Line, BarStacked, Area) to find the most effective way to present your specific data.
• Error Handling: In a production environment, always include robust error handling (e.g., try-catch blocks) around file operations and library calls.
• Performance: For extremely large datasets, consider optimizing your data loading process and the complexity of your Pivot Table to ensure efficient generation.

Conclusion

This tutorial walked through the complete process of creating Excel Pivot Charts programmatically using C#—from preparing source data and building a Pivot Table to generating a Pivot Chart directly linked to it. With Spire.XLS for .NET, these steps can be fully automated, allowing you to produce clear, interactive Excel reports directly from your applications.

Programmatic Pivot Tables and Charts significantly enhance reporting efficiency and consistency. By extending this approach with different chart types, calculated fields, or dynamic data sources, you can build flexible reporting solutions that scale with your data and deliver deeper insights to end users.