Click here to Skip to main content
Click here to Skip to main content

C# Excel - Pivot Table (Filter on Multiple Items)

, 18 Jul 2010
Rate this:
Please Sign up or sign in to vote.
Add a filter to the Pivot Table where multiple items can be selected.

PivotTableMultiFilter_4.png

Introduction

This article describes a quick and easy example of communicating with Excel from .NET.

Using the code

I have included the complete source code in the attached example project. The sample illustrates how to:

  • Add reference to the Excel application
  • Add Excel Workbook/Sheets
  • Insert data to an Excel Worksheet
  • Add a Pivot Table to an Excel Worksheet
  • Add a filter to the Pivot Table where multiple items can be selected
  • Add a graphic chart to display the data from the Pivot Table

This code example is aimed at programmer having difficulty with C#/ Excel interop (especially related to the multiple item filter from the Pivot Table).

Examples

Adding a pivot table to an Excel Worksheet:

//get a reference to the pivot chache
Excel.PivotCaches pch = m_objBook.PivotCaches();

//Activate the data sheet before adding the range to the pivot table
_m_objSheet_Data.Activate();

//add a pivot table reference to the sheet 
pch.Add(Excel.XlPivotTableSourceType.xlDatabase, "'" 
+ _m_objSheet_Data.Name + "'!A1:'" + _m_objSheet_Data.Name 
+ "'!J5").CreatePivotTable(_m_objSheet_Report_1.Cells[1, 1],
 "PivTbl_1", Type.Missing, Type.Missing);
Excel.PivotTable pvt = _m_objSheet_Report_1.PivotTables("PivTbl_1") 
                       as Excel.PivotTable;

////Column
//Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("Target Language"));
//fld.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
//fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
//fld.NumberFormat = "@";

//Data
Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("Context Matches"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
fld.NumberFormat = "#,##0_);[Red](#,##0)";

//Data
fld = ((Excel.PivotField)pvt.PivotFields("Words (100)"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
fld.NumberFormat = "#,##0_);[Red](#,##0)";

//define how the xlDataField values are aligned
pvt.DataPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

Enabling the multiple items filter on the Pivot Table:

//initialize the filter ensuring that all items are checked/selected
fld_projectName.CurrentPage = "(All)";

//Apply the 'Multiple Items' filter on the pivot table.
//This should resemble the same functionality as manullay 
//checking the checkbox for 'Select Multiple Items'
//and then selecting multiple items for the filter.

Excel.PivotFields _PivotFields = 
   (Excel.PivotFields)pvt.get_PageFields(Missing.Value);

foreach (Excel.PivotField _PivotField in _PivotFields)
{
  if (string.Compare(_PivotField.Caption, fld_projectName.Caption, true) == 0)
  {
     Excel.PivotItems _PivotItems = 
        (Excel.PivotItems)_PivotField.PivotItems(Missing.Value);
     foreach (Excel.PivotItem _PivotItem in _PivotItems)
     {
        //Note: only the filter items whose items match ("Proj_1, Proj_2") 
        //will be checked/selected
        if (ListToFilter.Contains(_PivotItem.Caption))
            _PivotItem.Visible = true;
        else
            _PivotItem.Visible = false;
     }
  }
}

//define how the xlDataField values are aligned
pvt.DataPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

Adding a chart object:

//Add the new chart object

Excel.ChartObjects _ChartObjects = (Excel.ChartObjects)
   (_m_objSheet_Report_1.ChartObjects(Missing.Value));
Excel.ChartObject _ChartObject = _ChartObjects.Add(170, 0, 400, 300);
Excel.Chart _Chart = _ChartObject.Chart;

_Chart.SetSourceData(_m_objRange_Chart, Excel.XlRowCol.xlColumns);
_Chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPieExploded;
_Chart.HasTitle = true;
_Chart.ChartTitle.Text = "Italian Word Count % - Example";
_Chart.ChartTitle.Font.Size = 12;
_Chart.ApplyDataLabels(

Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowNone
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, false
, Missing.Value, true, Missing.Value, Missing.Value);

_Chart.Rotation = 0; //X
_Chart.Elevation = 60; //Y
_Chart.Perspective = 10; //Perspective
_Chart.DepthPercent = 100; //Depth
_ChartObject.Chart.HasLegend = true;

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Patrick.Hartnett
Software Developer (Senior)
Italy Italy
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberumta13-Jun-13 6:48 
GeneralMy vote of 5 PinmemberOctavian130526-Jul-10 22:06 
GeneralRe: My vote of 5 PinmemberPatrick.Hartnett27-Jul-10 5:42 
GeneralMy vote of 5 PinmemberBruno Antonini18-Jul-10 23:51 
GeneralRe: My vote of 5 PinmemberPatrick.Hartnett19-Jul-10 19:49 
GeneralSource code link is broken PinmemberTony Bermudez18-Jul-10 7:49 
GeneralRe: Source code link is broken PinmemberPatrick.Hartnett18-Jul-10 20:40 
GeneralMy vote of 1 PinmentorKunalChowdhury17-Jul-10 18:36 
GeneralRe: My vote of 1 PinmemberPatrick.Hartnett17-Jul-10 19:28 
GeneralRe: My vote of 1 PinmentorKunalChowdhury17-Jul-10 19:33 
GeneralRe: My vote of 1 Pinmemberladuran11-Nov-13 14:19 
GeneralNot an article. PinmvpDave Kreskowiak17-Jul-10 18:23 
GeneralRe: Not an article. PinmemberPatrick.Hartnett17-Jul-10 19:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 19 Jul 2010
Article Copyright 2010 by Patrick.Hartnett
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid