Programming with Slicers in Excel 2010

Microsoft Excel 2010 slicers are easy to use controls that let the user visually filter data by selecting values from a list. Connecting slicers to Microsoft PivotTable dynamic views or Microsoft PivotChart dynamic views allows the user to quickly filter data for different scenarios during analysis. This Visual How To illustrates how to use the Excel 2010 primary interop assembly to programmatically create an Excel Workbook, add a PivotTable and a PivotChart, and finally add slicers connected to both the PivotTable and PivotChart.

This Visual How To walks through the following steps to illustrate the process of programmatically adding slicers to an Excel workbook:

1. Creating a Windows console application solution in Visual Studio 2010.

2. Adding a reference to the Excel 2010 primary interop assembly.

3. Adding the sample code to the solution.

Creating a Windows Console Application in Visual Studio 2010

This Visual How To uses a Windows console application as the framework for the sample code. The console application type was selected only for its simplicity. Other application types could use the same approach presented here.

To create a Windows console application in Visual Studio 2010

1. Start Microsoft Visual Studio 2010.

2. On the File menu, point to New, and then click Project.

3. In the New Project dialog box select the Visual C# Windows type in the Project types pane.

4. Select Console Application in the Templates pane.

5. Name the project and solution Excel2010Slicers.

   Figure 1. Creating the solution

   
   

6. To create the solution, click OK.

Adding a Reference to the Excel 2010 Primary Interop Assembly

This Visual How To uses the Excel 2010 primary interop assembly to create and manipulate an Excel workbook. The Excel 2010 primary interop assembly exposes the Excel 2010 object model to managed code and is installed with Visual Studio 2010.

To add a reference to the Excel 2010 primary interop assembly

1. On the Visual Studio Project menu, click Add Reference.

2. Select version 14.0.0.0 of the Microsoft.Office.Interop. Excel component in the Add Reference dialog box and then click OK to add the reference.

   Figure 2. Adding the reference

   
   

Adding the Sample Code to the Solution

Replace the contents of the Program.cs source file with the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Office.Interop.Excel;

namespace Excel2010Slicers
{
 class Program
 {
  static void Main(string[] args)
  {
    //Declare variables that hold references to Excel objects.
    Application eApplication = null;
    Workbook eWorkbook = null;
    Worksheet sheet = null;
    PivotTable pivotTable = null;
    Range pivotData = null;
    Range pivotDestination = null;
    PivotField salesRegion = null;
    PivotField salesAmount = null;
    ChartObjects chartObjects = null;
    ChartObject pivotChart = null;
    SlicerCache salesTypeSlicer = null;
    SlicerCache salesRegionSlicer = null;
    SlicerCache salesPersonSlicer = null;

    //Declare helper variables.
    string workBookName = @"C:\temp\Excel2010Slicers.xlsx";
    string pivotTableName = @"Sales By Type";
    string workSheetName = @"Quarterly Sales";

    try
    {
      //Create an instance of Excel.
      eApplication = new Application();

      //Create a workbook and add a worksheet.
      eWorkbook = eApplication.Workbooks.Add(
          XlWBATemplate.xlWBATWorksheet);
      sheet = (Worksheet)(eWorkbook.Worksheets[1]);
      sheet.Name = workSheetName;

      //Add data to the worksheet.
      //Use the custom SetRow helper function located at the end of this 
      //  source file to add a row of values to the worksheet.
      SetRow(sheet, 1,
        "Sales Region", "Sales Person", "Sales Type", "Sales Amount");
      SetRow(sheet, 2, "West", "Joe", "Wholesale", "123");
      SetRow(sheet, 3, "West", "Joe", "Retail", "432");
      SetRow(sheet, 4, "West", "Joe", "Government", "111");
      SetRow(sheet, 5, "East", "Robert", "Wholesale", "564");
      SetRow(sheet, 6, "East", "Robert", "Retail", "234");
      SetRow(sheet, 7, "East", "Robert", "Government", "321");
      SetRow(sheet, 8, "East", "Michelle", "Wholesale", "940");
      SetRow(sheet, 9, "East", "Michelle", "Retail", "892");
      SetRow(sheet, 10, "East", "Michelle", "Government", "10");
      SetRow(sheet, 11, "West", "Erich", "Wholesale", "120");
      SetRow(sheet, 12, "West", "Erich", "Retail", "45");
      SetRow(sheet, 13, "West", "Erich", "Government", "410");
      SetRow(sheet, 14, "West", "Dafna", "Wholesale", "800");
      SetRow(sheet, 15, "West", "Dafna", "Retail", "3409");
      SetRow(sheet, 16, "West", "Dafna", "Government", "123");
      SetRow(sheet, 17, "East", "Rob", "Wholesale", "777");
      SetRow(sheet, 18, "East", "Rob", "Retail", "450");
      SetRow(sheet, 19, "East", "Rob", "Government", "900");
      sheet.Columns.AutoFit();

      //Select a range of data for the PivotTable.
      pivotData = sheet.get_Range("A1", "D19");

      //Select location of the PivotTable.
      pivotDestination = sheet.get_Range("F2");

      //Add a PivotTable to the worksheet.
      sheet.PivotTableWizard(
          XlPivotTableSourceType.xlDatabase,
          pivotData,
          pivotDestination,
          pivotTableName
          );

      //Set variables used to manipulate the PivotTable.
      pivotTable =
        (PivotTable)sheet.PivotTables(pivotTableName);
      salesRegion = ((PivotField)pivotTable.PivotFields(3));
      salesAmount = ((PivotField)pivotTable.PivotFields(4));

      //Format the PivotTable.
      pivotTable.TableStyle2 = "PivotStyleLight16";
      pivotTable.InGridDropZones = false;

      //Set Sales Region as a Row Field.
      salesRegion.Orientation =
        XlPivotFieldOrientation.xlRowField;

      //Set Sum of Sales Amount as a Value Field.
      salesAmount.Orientation =
        XlPivotFieldOrientation.xlDataField;
      salesAmount.Function = XlConsolidationFunction.xlSum;

      //Add a PivotChart to the work sheet.
      chartObjects = (ChartObjects)sheet.ChartObjects();
      pivotChart = chartObjects.Add(310, 100, 225, 175);

      //Format the PivotChart.
      pivotChart.Chart.ChartWizard(pivotData,
        XlChartType.xlColumnClustered,
        Title: "Sales",
        HasLegend: false,
        CategoryLabels: 3,
        SeriesLabels: 0);

      //Add slicers to the PivotTable.
      salesTypeSlicer =
        eWorkbook.SlicerCaches.Add(pivotTable, "Sales Type");
      salesTypeSlicer.Slicers.Add(sheet,
        Top: 10, Left: 540, Width: 100, Height: 100);
      salesRegionSlicer =
        eWorkbook.SlicerCaches.Add(pivotTable, "Sales Region");
      salesRegionSlicer.Slicers.Add(sheet,
        Top: 120, Left: 540, Width: 100, Height: 100);
      salesPersonSlicer =
        eWorkbook.SlicerCaches.Add(pivotTable, "Sales Person");
      salesPersonSlicer.Slicers.Add(sheet,
        Top: 10, Left: 645, Width: 100, Height: 200);

      //Save the workbook.
      sheet.get_Range("A1").Activate();
      eWorkbook.SaveAs(workBookName,
        AccessMode: XlSaveAsAccessMode.xlNoChange);
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
    }
    finally
    {
      //Release the references to the Excel objects.
      salesAmount = null;
      salesRegion = null;
      pivotDestination = null;
      pivotData = null;
      pivotChart = null;
      chartObjects = null;
      pivotTable = null;
      salesTypeSlicer = null;
      salesRegionSlicer = null;
      salesPersonSlicer = null;
      sheet = null;

      //Release the Workbook object.
      if (eWorkbook != null)
        eWorkbook = null;

      //Release the ApplicationClass object.
      if (eApplication != null)
      {
        eApplication.Quit();
        eApplication = null;
      }

      GC.Collect();
      GC.WaitForPendingFinalizers();
      GC.Collect();
      GC.WaitForPendingFinalizers();
    }
  }
  /// <summary>
  /// Helper method to set values for a row of cells.
  /// </summary>
  static void SetRow(Worksheet sheet, int row, params string[] values)
  {
    for (int x = 0; x < values.Length; x++)
    {
      sheet.Cells[row, x + 1] = values[x];
    }
  }
 }
}

The sample code shown here creates a workbook named Excel2010Slicers.xlsx in the C:\Temp folder. The code then adds data, a PivotTable, and a PivotChart to the worksheet. Finally a set of slicers are added to the worksheet and tied to the PivotTable and PivotChart.

The sample code used with this Visual How To shows how to use the Excel 2010 primary interop assembly to create a worksheet, add data to the worksheet, and add a PivotTable and PivotChart to the worksheet. After the PivotTable and PivotChart are added to the worksheet slicers are added and connected to the PivotTable and PivotChart.The following code example shows how to use the Excel 2010 primary interop assembly to add a PivotTable to a worksheet based on a preselected data range. The code uses the Workbook.PivotTableWizard method to create the PivotTable. The properties and method of the PivotTable interface and the PivotTable.PivotFields collection are then used to format the PivotTable interface.

//Select a range of data for the PivotTable.
pivotData = sheet.get_Range("A1", "D19");

//Select location of the PivotTable.
pivotDestination = sheet.get_Range("F2");

//Add a PivotTable to the worksheet.
sheet.PivotTableWizard(
    XlPivotTableSourceType.xlDatabase,
    pivotData,
    pivotDestination,
    pivotTableName
    );

//Set variables used to manipulate the PivotTable.
pivotTable =
  (PivotTable)sheet.PivotTables(pivotTableName);
salesRegion = ((PivotField)pivotTable.PivotFields(3));
salesAmount = ((PivotField)pivotTable.PivotFields(4));

//Format the PivotTable.
pivotTable.TableStyle2 = "PivotStyleLight16";
pivotTable.InGridDropZones = false;

//Set Sales Region as a Row Field.
salesRegion.Orientation =
  XlPivotFieldOrientation.xlRowField;

//Set Sum of Sales Amount as a Value Field.
salesAmount.Orientation =
  XlPivotFieldOrientation.xlDataField;
salesAmount.Function = XlConsolidationFunction.xlSum;

The following code fragment shows how to use the Excel 2010 primary interop assembly to add a PivotChart to a workbook by using the same data range used to create the pivot table. The code uses the ChartObjects.Add method to create the PivotChart. The ChartObject.Chart property is then used to format the PivotChart. Named arguments are used because all parameters are not being passed to the ChartWizard method.

//Add a PivotChart to the work sheet.
chartObjects = (ChartObjects)sheet.ChartObjects();
pivotChart = chartObjects.Add(310, 100, 225, 175);

//Format the PivotChart.
pivotChart.Chart.ChartWizard(pivotData,
  XlChartType.xlColumnClustered,
  Title: "Sales",
  HasLegend: false,
  CategoryLabels: 3,
  SeriesLabels: 0);

The following code fragment shows how to use the Excel 2010 primary interop assembly to add slicers connected to the PivotTable. Because the PivotTable and the PivotChart are using the same data, you do not have to create separate slicers for the PivotChart. The PivotTable and PivotChart share the same slicers.

Three slicers are added: Sales Type, Sales Region, and Sales Person. All slicers follow the same creation process. The only difference is the column used for filtering and the location on the worksheet.

The Workbook.SlicerCaches method of the workbook creates the slicers. The first parameter of the method associates the slicer with the PivotTable and the second parameter identifies the column to use for filtering. The selection items displayed in the slicer is based on the column values.

The eWorkbook.SlicerCaches.Add method adds the slicer to the worksheet and defines its position. The example uses Named arguments because all parameters are not passed to the Add method.

//Add slicers to the PivotTable.
salesTypeSlicer =
  eWorkbook.SlicerCaches.Add(pivotTable, "Sales Type");
salesTypeSlicer.Slicers.Add(sheet,
  Top: 10, Left: 540, Width: 100, Height: 100);
salesRegionSlicer =
  eWorkbook.SlicerCaches.Add(pivotTable, "Sales Region");
salesRegionSlicer.Slicers.Add(sheet,
  Top: 120, Left: 540, Width: 100, Height: 100);
salesPersonSlicer =
  eWorkbook.SlicerCaches.Add(pivotTable, "Sales Person");
salesPersonSlicer.Slicers.Add(sheet,
  Top: 10, Left: 645, Width: 100, Height: 200);

Length: 00:04:42

Grab the Code

• Overview of PivotTable and PivotChart reports

• Microsoft Excel Team Blog - Slicers

• How to: Install Office Primary Interop Assemblies

• Microsoft Visual Studio on MSDN

• Office Development in Visual Studio 2010