c# - Excel VSTO + C# .NET + 创建数据透视表

Question

我正在尝试通过 C# .Net 为 Excel VSTO 创建数据透视表，但一直抛出异常，指出:

Exception from HRESULT: 0x800A03EC

我尝试创建数据透视表的工作表称为 oSheet，与包含源数据的工作表相同

我为数据透视表源创建了一个范围:

Excel.Range pivotData = osheet.Range["A1", "B3"];

数据透视表的范围:

Excel.Range pivotDest = osheet.Range["A12", "B14"];

然后尝试用这个创建表:

osheet.PivotTableWizard(
              Excel.XlPivotTableSourceType.xlDatabase, 
              pivotData, 
              pivotDest, 
              "Table", 
              true, 
              true, 
              true, 
              true, 
              true, 
              true, 
              false, 
              false, 
              Excel.XlOrder.xlDownThenOver, 
              0, 
              false, 
              false
);

错误发生在表向导行中。

关于导致错误的原因或通过 C# .Net 创建数据透视表的更好方法有什么想法吗？

完整代码

Excel.Application oApp;
Excel.Worksheet osheet;
Excel.Workbook oBook;

oApp = new Excel.Application();
oBook = oApp.Workbooks.Add();
osheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);

osheet.Cells[1, 1] = "Name";
osheet.Cells[1, 2] = "Salary";

osheet.Cells[2, 1] = "Frank";
osheet.Cells[2, 2] = 1500000;

osheet.Cells[3, 1] = "Millford";
osheet.Cells[3, 2] = 2200;

//Now capture range of first sheet = I will need this to create pivot table
Excel.Range oRange = osheet.Range["A1", "B3"];


var pch = oBook.PivotCaches();

Excel.Range pivotData = osheet.Range["A1", "B3"];

Excel.PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, pivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["A12"], "MyPivotTable");

pvt.PivotFields("Col1").Orientation = XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Col2").Orientation = XlPivotFieldOrientation.xlColumnField;
pvt.AddDataField(pvt.PivotFields("Col3"), "Sum of Col3", XlConsolidationFunction.xlSum);

Answer 1

前几天刚回答过类似的问题...

https://stackoverflow.com/a/39968355/1278553

简而言之，在C#中创建数据透视表的方法是:

Excel.Range pivotData = osheet.Range["A1", "B3"];

Excel.PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, pivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["A12"], "MyPivotTable");

然后分配您的行/列和数据字段:

pvt.PivotFields("Col1").Orientation = XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Col2").Orientation = XlPivotFieldOrientation.xlColumnField;
pvt.AddDataField(pvt.PivotFields("Col3"), "Sum of Col3", XlConsolidationFunction.xlSum);

我严重抄袭了我之前的回答。

-- 编辑 2016 年 10 月 17 日 --

根据您更新的代码，现在应该可以创建数据透视表:

var pch = oBook.PivotCaches();

Excel.Range pivotData = osheet.Range["A1", "B3"];

Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, pivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["A12"], "MyPivotTable");

pvt.PivotFields("Name").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.AddDataField(pvt.PivotFields("Salary"), "Total Salary",
    Excel.XlConsolidationFunction.xlSum);