C# Excel - 数据透视表 (多项筛选)

• 下载示例 - 8.25 KB

引言

本文描述了一个快速简单的示例，展示了如何从 .NET 与 Excel 进行通信。

使用代码

我已在附带的示例项目中包含完整的源代码。该示例说明了如何

• 添加对 Excel 应用程序的引用
• 添加 Excel 工作簿/工作表
• 将数据插入到 Excel 工作表
• 向 Excel 工作表添加数据透视表
• 为数据透视表添加筛选器，允许选择多个项目
• 添加图形图表以显示来自数据透视表的数据

此代码示例旨在帮助在 C#/ Excel 互操作方面遇到困难的程序员 *（尤其是与数据透视表的多项目筛选相关的方面）*。

示例

向 Excel 工作表添加数据透视表

//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;

启用数据透视表的多项目筛选

//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;

添加图表对象

//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;