I have encountered an issue when creating a pivot table in excel using c# where the created pivot table displays additional empty columns (which are still part of the pivot table) once created and added to the worksheet.
All pivot table fields' orientation is set to Excel.XlPivotFieldOrientation.xlRowField. If I manually drag one of the fields into the Values section of the pivot table in Excel, then the table seems to display correctly. If I remove it, the additional blank columns are displayed once more.
Below is the function to create the pivot table:
public void CreatePivotTable(Excel.Workbook workbook, Excel.Worksheet worksheet, Excel.Range sourceRange)
{
object useDefault = Type.Missing;
Excel.PivotTable pivotTable;
Excel.Range pivotData;
Excel.Range pivotDestination;
Excel.PivotField parentNameField;
Excel.PivotField parameterNameField;
Excel.PivotField parameterTypeField;
Excel.PivotField parameterValueField;
string pivotTableName = "ParametersPivot";
pivotData = sourceRange;
pivotDestination = worksheet.Range["A15", "A15"];
Excel.PivotCache pivotCache = workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, sourceRange, Type.Missing);
Excel.PivotTables pivotTables = worksheet.PivotTables(Type.Missing);
pivotTable = pivotTables.Add(pivotCache, pivotDestination, pivotTableName, Type.Missing, Type.Missing);
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
pivotTable.HasAutoFormat = true;
pivotTable.
parentNameField = (Excel.PivotField)pivotTable.PivotFields(1);
parentNameField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
parameterNameField = (Excel.PivotField)pivotTable.PivotFields(2);
parameterNameField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
parameterTypeField = (Excel.PivotField)pivotTable.PivotFields(3);
parameterTypeField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
parameterValueField = (Excel.PivotField)pivotTable.PivotFields(4);
parameterValueField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
//Format the Pivot Table.
pivotTable.Format(Excel.XlPivotFormatType.xlPTNone);
pivotTable.InGridDropZones = false;
pivotTable.SmallGrid = false;
pivotTable.ShowTableStyleRowStripes = false;
pivotTable.TableStyle2 = "PivotStyleMedium8";
//// Page Field
//parentNameField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
//parentNameField.Position = 1;
//parentNameField.CurrentPage = "(All)";
}
Any field dragged to pivot values section
Resulting pivot table after dragging field to values section
