How to add PivotTables and Slicers to MS Excel programmatically
As I’ve mentioned in How to add sparklines and charts to MS Excel programmatically Excel enables us to provide our users with interactive and powerful ways to visualize their data. Pivot tables add another dimension to this by summarizing thousands of records of data in one page and let you analyse trends in your data without the need for formulas.
In today’s post I’ll demonstrate how you can programmatically add a pivot table and slicers to an Excel spread sheet. I’ll be using Add-in Express 2010 for Office and .net.
As usual we’ll start by creating a new Visual Studio COM Add-in project.
Finish the New Microsoft Office COM Add-in wizard by selecting Visual C# as the programming language and since Slicers were only introduced in Excel 2010, select Microsoft Office 2010 as the minimum supported Office version.
Pivots and slicers are only available in Excel so we’ll choose Microsoft Excel as our supported application.
Make sure you have the AddinModule designer open. If it is not open, you can open it by double-clicking on the AddinModule.cs file in the Solution Explorer. Add a new ADXRibbonTab component to the designer by clicking on its button on the designer toolbar.
Add a ribbon group and two ribbon buttons to the ribbon tab. The design should resemble the following image:
The “Insert Data from Database” button will read data from the sales data table and populate the active sheet in the open Excel workbook with the data. Add an event handler for the button by selecting it and double-clicking in its OnClick event in the properties window. Add the following code to the event handler:
private void insertDataRibbonButton_OnClick(object sender, IRibbonControl control, bool pressed) { Excel.Worksheet activeSheet = null; Excel.Range range = null; Excel.Range headerRange = null; Excel.Font headerFont = null; try { activeSheet = ExcelApp.ActiveSheet as Excel.Worksheet; string[] header = new string[] { "Month", "Product", "SalesPerson", "Units Sold" }; headerRange = activeSheet.get_Range("A1", "D1"); headerRange.set_Value(null, header); headerFont = headerRange.Font; headerFont.Bold = true; DataTable dtSalesData = RetrieveSalesData(); int rowCount = 2; foreach (DataRow row in dtSalesData.Rows) { range = activeSheet.get_Range("A" + rowCount, "D" + rowCount); range.set_Value(null, row.ItemArray); Marshal.ReleaseComObject(range); range = null; rowCount++; } } finally { if (activeSheet != null) Marshal.ReleaseComObject(activeSheet); if (range != null) Marshal.ReleaseComObject(range); if (headerRange != null) Marshal.ReleaseComObject(headerRange); if (headerFont != null) Marshal.ReleaseComObject(headerFont); } }
The RetrieveSalesData method connects to a Microsoft Access database and returns an ADO data table. The code for it will look like this:
private DataTable RetrieveSalesData() { DataSet dsData = new DataSet(); using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Data\NorthwindReports.accdb;Persist Security Info=False;")) { OleDbDataAdapter daData = new OleDbDataAdapter("Select * From SalesData", conn); daData.Fill(dsData); } return dsData.Tables[0]; }
I’ve included the database in the demo project for your convenience. Now that we have the data for our pivot table in the worksheet, let’s add the code to insert the pivot table and slicers. First, add a new event handler for the “Create Pivot with Slicers” button in a similar manner as you’ve added the event handler for the other button.
Add the following code to the event handler, I have to warn you it’s a lot : )
private void createPivotRibbonButton_OnClick(object sender, IRibbonControl control, bool pressed) { Excel.Workbook activeWorkBook = null; Excel.Sheets sheets = null; Excel.Worksheet pivotWorkSheet = null; Excel.PivotCaches pivotCaches = null; Excel.PivotCache pivotCache = null; Excel.PivotTable pivotTable = null; Excel.PivotFields pivotFields = null; Excel.PivotField monthPivotField = null; Excel.PivotField productPivotField = null; Excel.PivotField salesPersonPivotField = null; Excel.PivotField unitsSoldPivotField = null; Excel.PivotField unitsSoldSumPivotField = null; Excel.SlicerCaches slicerCaches = null; Excel.SlicerCache monthSlicerCache = null; Excel.Slicers monthSlicers = null; Excel.Slicer monthSlicer = null; Excel.SlicerCache productSlicerCache = null; Excel.Slicers productSlicers = null; Excel.Slicer productSlicer = null; Excel.SlicerCache salesPersonSlicerCache = null; Excel.Slicers salesPersonSlicers = null; Excel.Slicer salesPersonSlicer = null; try { activeWorkBook = ExcelApp.ActiveWorkbook; sheets = ExcelApp.Sheets; pivotWorkSheet = (Excel.Worksheet)sheets.Add(); // Create the Pivot Table pivotCaches = activeWorkBook.PivotCaches(); pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, "Sheet1!$A$1:$D$31"); pivotTable = pivotCache.CreatePivotTable("Sheet4!R3C1"); // Set the Pivot Fields pivotFields = (Excel.PivotFields)pivotTable.PivotFields(); // Month Pivot Field monthPivotField = (Excel.PivotField)pivotFields.Item("Month"); monthPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; monthPivotField.Position = 1; // Product Pivot Field productPivotField = (Excel.PivotField)pivotFields.Item("Product"); productPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; // Sales Person Pivot Field salesPersonPivotField = (Excel.PivotField)pivotFields.Item("SalesPerson"); salesPersonPivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField; // Units Sold Pivot Field unitsSoldPivotField = (Excel.PivotField)pivotFields.Item("Units Sold"); // Sum of Units Sold Field unitsSoldSumPivotField = pivotTable.AddDataField(unitsSoldPivotField, "# Units Sold", Excel.XlConsolidationFunction.xlSum); slicerCaches = activeWorkBook.SlicerCaches; // Month Slicer monthSlicerCache = slicerCaches.Add(pivotTable, "Month", "Month"); monthSlicers = monthSlicerCache.Slicers; monthSlicer = monthSlicers.Add(ExcelApp.ActiveSheet, Type.Missing, "Month", "Month", 160, 10, 144, 200); // Product Slicer productSlicerCache = slicerCaches.Add(pivotTable, "Product", "Product"); productSlicers = productSlicerCache.Slicers; productSlicer = productSlicers.Add(ExcelApp.ActiveSheet, Type.Missing, "Product", "Product", 160, 164, 144, 200); // Sales Person Slicer salesPersonSlicerCache = slicerCaches.Add(pivotTable, "SalesPerson", "SalesPerson"); salesPersonSlicers = salesPersonSlicerCache.Slicers; salesPersonSlicer = salesPersonSlicers.Add(ExcelApp.ActiveSheet, Type.Missing, "SalesPerson", "SalesPerson", 160, 318, 144, 200); } finally { if (salesPersonSlicer != null) Marshal.ReleaseComObject(salesPersonSlicer); if (salesPersonSlicers != null) Marshal.ReleaseComObject(salesPersonSlicers); if (salesPersonSlicerCache != null) Marshal.ReleaseComObject(salesPersonSlicerCache); if (productSlicer != null) Marshal.ReleaseComObject(productSlicer); if (productSlicers != null) Marshal.ReleaseComObject(productSlicers); if (productSlicerCache != null) Marshal.ReleaseComObject(productSlicerCache); if (monthSlicer != null) Marshal.ReleaseComObject(monthSlicer); if (monthSlicers != null) Marshal.ReleaseComObject(monthSlicers); if (monthSlicerCache != null) Marshal.ReleaseComObject(monthSlicerCache); if (slicerCaches != null) Marshal.ReleaseComObject(slicerCaches); if (unitsSoldSumPivotField != null) Marshal.ReleaseComObject(unitsSoldSumPivotField); if (unitsSoldPivotField != null) Marshal.ReleaseComObject(unitsSoldPivotField); if (salesPersonPivotField != null) Marshal.ReleaseComObject(salesPersonPivotField); if (productPivotField != null) Marshal.ReleaseComObject(productPivotField); if (monthPivotField != null) Marshal.ReleaseComObject(monthPivotField); if (pivotFields != null) Marshal.ReleaseComObject(pivotFields); if (pivotTable != null) Marshal.ReleaseComObject(pivotTable); if (pivotCache != null) Marshal.ReleaseComObject(pivotCache); if (pivotCaches != null) Marshal.ReleaseComObject(pivotCaches); if (pivotWorkSheet != null) Marshal.ReleaseComObject(pivotWorkSheet); if (sheets != null) Marshal.ReleaseComObject(sheets); if (activeWorkBook != null) Marshal.ReleaseComObject(activeWorkBook); } }
As you can see it takes quite a lot of code to achieve the result, but to be able to give your users an interactive reporting UI such as is illustrated in the image below, it is worth the effort.
Thank you for reading. Until next time, keep coding!
Available downloads:
This sample add-on was developed using Add-in Express for Office and .net:
Sample Excel add-on in C#
7 Comments
Hi,
Although it’s possible to create Excel reports on the fly we rarely use it. Creating native Excel templates which we populate with data is much more smoother. OK, any business logic should be separated from the templates so it can be stored in an add-in.
Kind regards,
Dennis
Hi Dennis,
I agree with you. It is much easier and an added benefit is it gives your users the ability to customize their excel reports.
Thanks for your comment!
The PivotGrid WinForms Control is designed for developers to build for Windows Forms with similar functionality to the PivotTables in MS Excel.
When you created pivot table you have used
pivotTable = pivotCache.CreatePivotTable(“Sheet4!R3C1”);
What do you mean by “R3C1”. It doesnt sound like a range? I am getting below exception when I trying to do something similar. Any hint would help.
“The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.”
Hello Shubham,
This is the R1C1 reference style, see e.g. https://tduhameau.wordpress.com/2012/09/27/the-beauty-of-the-r1c1-reference-style/. As to the error message, try googling. See e.g. https://www.youtube.com/watch?v=edOnJVrNyMs.
By following this tutorial, I got a problem.
When I show data from datatable_1, which has 5 rows by using below code, it works perfectly for the first time but if I filter the data using on screen filter and now datatable_1 has 2 rows. They shows 2 rows and also three rows from previous datatable_1. How do I clear previous rendered record from excel sheet.
DataTable dtSalesData = RetrieveSalesData();
int rowCount = 2;
foreach (DataRow row in dtSalesData.Rows)
{
range = activeSheet.get_Range(“A” + rowCount, “D” + rowCount);
range.set_Value(null, row.ItemArray);
Marshal.ReleaseComObject(range);
range = null;
rowCount++;
}
Regards
Hello Shah,
If you’ve modified the project, please send it to me. Also, please send me a video demonstrating the issue; you can use Jing; it’s free. You can find the support email address in {Add-in Express installation folder}\readme.txt. Please make sure your email contains a link to this page.