The parameter is incorrect excel pivot c#

The parameter is incorrect excel pivot c#

If you are encountering this error during code compilation while trying to create dynamic pivot table it means you are not passing all the recommended parameters. In this article I will provide you correct code to create a pivot table.

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Code example:

excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
excel.Worksheet oWorksheet = oWorkbook.ActiveSheet;

//Create pivot caches collection
excel.PivotCaches oCaches = oWorkbook.PivotCaches();

//create or add new pivot cache
excel.PivotCache oCache= oCaches.Add(SourceType: excel.XlPivotTableSourceType.xlDatabase, SourceData: oWorkbook.Range["A1:G20"]);

//get pivot tables collection from worksheet
excel.PivotTables oPivotTables = oWorksheet.PivotTables();

//Create or Add new pivot table using cache
oPivotTables.Add(oCache, oWorksheet.Range["J2"], "myTable");

//add Pivot field
excel.PivotTable oPivotTable = oWorksheet.PivotTables("myTable");
excel.PivotField oPivotField_one = oPivotTable.PivotFields("Column-1");
oPivotField_one.Orientation = excel.XlPivotFieldOrientation.xlRowField;
oPivotField_one.Position = 1;

//Add data field
excel.PivotField oPivotField_two = oPivotTable.PivotFields("Column-2");
oPivotTable.AddDataField(oPivotField_two, "Sum of Column-2", excel.XlConsolidationFunction.xlSum);

Leave a Reply

Your email address will not be published.