如何使用c＃在Excel中添加数据透视表？

How would I create a pivot chart based on the data of a table already created in a worksheet?

如何根据工作表中已创建的表的数据创建数据透视表？

2 个解决方案

#1

Try something like this:

尝试这样的事情：

            Microsoft.Office.Interop.Excel.PivotTable pivotCache = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches().
                Create(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, hoursTable2, XlPivotTableVersionList.xlPivotTableVersion12).
                CreatePivotTable("PivotTable!R1C1", "PivotTable1", Type.Missing, XlPivotTableVersionList.xlPivotTableVersion12);
            Microsoft.Office.Interop.Excel.Shape myChart = lPivotWorksheet.Shapes.AddChart();
            myChart.Chart.SetSourceData(pivotCache.TableRange1, Type.Missing);

            Globals.ThisAddIn.Application.ActiveWorkbook.ShowPivotChartActiveFields = true;

            Microsoft.Office.Interop.Excel.PivotField rowField = (Microsoft.Office.Interop.Excel.PivotField)pivotCache.PivotFields("FieldTitle1");
            rowField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;

            pivotCache.AddDataField(pivotCache.PivotFields("FieldTitle2"), "Sum of Difference", Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum);

            Microsoft.Office.Interop.Excel.PivotField pageField = (Microsoft.Office.Interop.Excel.PivotField)pivotCache.PivotFields("FieldTitle3");
            pageField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField;

            myChart.ScaleHeight(2, Microsoft.Office.Core.MsoTriState.msoFalse, Type.Missing);
            myChart.ScaleWidth(2, Microsoft.Office.Core.MsoTriState.msoFalse, Type.Missing);
            myChart.Left = 200;
            myChart.Top = 120;
            myChart.Chart.ChartTitle.Caption = "Chart Title";

#2

Berbies answer put me on the right track. I believe my code will be easier to read. The pivot table was created using documentation at this link. I'm using Excel 2007.

Berbies回答让我走上正轨。我相信我的代码会更容易阅读。数据透视表是使用此链接中的文档创建的。我正在使用Excel 2007。

        // assign the new pivot table
        PivotTable pivotTable = (PivotTable)pivotSheet.PivotTables("pivotTableName");

        // Create a pivot chart using a pivot table as its source
        Shape chartShape = pivotSheet.Shapes.AddChart();
        chartShape.Chart.SetSourceData(pivotTable.TableRange1, Type.Missing);
        CurrentWorkbook.ShowPivotChartActiveFields = true;
        chartShape.Chart.ChartType = XlChartType.xlAreaStacked;

#1