Excel VSTO + C# .NET + Create Pivot Table

Question

I'm trying to create a pivot table through C# .Net for an Excel VSTO but keeping getting an exception thrown that states:

Exception from HRESULT: 0x800A03EC

The sheet I am trying to create the pivot table is called oSheet and is the same as the sheet that has the source data

I create a range for the pivot table source:

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

A range for where the pivot table will be:

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

then try to create the table with this:

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

The error is occurring in the table wizard line.

Any ideas on what is causing the error or a better way to create a pivot table through C# .Net?

Full Code

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

Have you tried to use PivotTables.Add instead of using wizard? — PetLahev

Hambone Hambone · Accepted Answer · 2016-10-15T01:47:23

I just answered a similar question the other day...

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

In a nutshell, the way to create a pivot table in C# is:

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

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

And then assign your row / column and data fields:

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

I plagiarized heavily from my prior answer.

-- EDIT 10/17/2016 --

Per your updated code, this should now work to create the pivot table:

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

Excel VSTO + C# .NET + Create Pivot Table

1 Answers