How do i re-write the code using EPPlus Library.

Question

The following piece of code creates a Pivot Table in Excel sheet using Interop. Same needs to be done using EPPlus.

 

Object oMissing = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Range oSourceData = _objSheet.get_Range(_objSheet.Cells[1, 1], _objSheet.Cells[lastRow, lastColumn]);

Microsoft.Office.Interop.Excel.PivotTable ptTable = _objSheet.PivotTableWizard(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, oSourceData, oMissing,"BD_Pivot", false, false, true, false, oMissing, oMissing, false, false, Microsoft.Office.Interop.Excel.XlOrder.xlDownThenOver, 5, oMissing, oMissing);

ptTable.ManualUpdate = true;

ptTable.HasAutoFormat = false;

ptTable.DisplayContextTooltips = false;

PivotField oPivotField0;

oPivotField0 = (Microsoft.Office.Interop.Excel.PivotField)ptTable.PivotFields("CN");

oPivotField0.Orientation = XlPivotFieldOrientation.xlColumnField;

for (int index = 1; index < 13; index++)

{

oPivotField0.set_Subtotals(index, false);

}

PivotField oPivotField6 = (Microsoft.Office.Interop.Excel.PivotField)ptTable.PivotFields("BP_N");

oPivotField6.Orientation = XlPivotFieldOrientation.xlRowField;

for (int index = 1; index < 13; index++)

{

oPivotField6.set_Subtotals(index, false);

}

PivotField oPivotField8 = (Microsoft.Office.Interop.Excel.PivotField)ptTable.PivotFields("P");

oPivotField8.Orientation = XlPivotFieldOrientation.xlDataField;

ptTable.ManualUpdate = false;

 

 

I want to reWrite the Same using EPPlus.

 

This is what I Tried

 

ExcelRange rg = _objSheet.Cells[firstRow, firstColumn, lastRow, lastColumn];

string tableName = dtSample.TableName;

//Ading a table to a Range

ExcelTable tblData = _objSheet.Tables.Add(rg, tableName);

ExcelRange dataCells = _objSheet.Cells[tblData.Address.Address];

_objSheet = Data.outputExcel.Workbook.Worksheets.Add("PivotInput1");

ExcelRange pvtLocation = _objSheet.Cells["B4"];

string pvtName = "BD_Pivot";

ExcelPivotTable pivotTable = _objSheet.PivotTables.Add(pvtLocation, dataCells, pvtName);

ExcelPivotTableField oPivotField0 = pivotTable.ColumnFields.Add(pivotTable.Fields["CONTROL_NUMBER"]);

oPivotField0.SubtotalTop = false;

oPivotField0.SubTotalFunctions = eSubTotalFunctions.None;