How to add pivottable in excel 2013 using a datatable in C#

I’ve a datatable and I want to add a pivottable in excel sheet using that datatable. I’m getting a pivottable but getting only one column in it but there are 2 columns in datatable and I want both columns of the datatable in the pivottable

Here is my code

public static void CreatePivotTable(ref Workbook wb,ref Worksheet ws, int firstRow, int lastRow)
{
        Excel.PivotCaches pivotCaches = null;
        Excel.PivotCache pivotCache = null;
        Excel.PivotTable pivotTable = null;
        Excel.PivotFields pivotFields = null;
        Excel.PivotField monthPivotField = null;
        Excel.PivotField sumPivotField = null;
        pivotCaches = wb.PivotCaches();
        pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase,
            "Team pyramid!$A$3:$B$5");
        Range rangeDes = ws.Range["H3"];
        pivotTable = pivotCache.CreatePivotTable(TableDestination: rangeDes);

        pivotFields = (Excel.PivotFields)pivotTable.PivotFields();

        monthPivotField = (Excel.PivotField)pivotFields.Item(0);
        sumPivotField = (Excel.PivotField)pivotFields.Item(1);

        monthPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
        sumPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

        monthPivotField.Position = 1;
}

This is what I’m getting

enter image description here

This is what I want

enter image description here


Source: .net

Leave a Reply