Using excel formula in Pivot table CalculatedField in C# code

1
votes

I need to generate a pivot table from an excel file using C# code. In excel, some calculated fields are currently used to generate a pivot table and I would need to convert those formulas from excel to C# code. Excel formula:

=IF(CountA<>0;CountA/CountB;"0")

This formula is used in a calculated field used in Pivot table. Here CountA and CountB columns exist in excel.

I tried the following code but it is throwing an interop exception:

oPivotTable.AddDataField(oPivotTable.CalculatedFields()
    .Add("RateA", "= IF('CountA' != 0;'CountA' / 'CountB';0)", true),
  " RateA", Excel.XlConsolidationFunction.xlSum);

Although some simple formulas are working in calculatedFields.

1
c#excelpivot-tableexcel-interop

1 Answers

2
votes

Try the below code:

pivotTable.CalculatedFields().Add("RateA",
                                  "= IF('CountA' != 0,'CountA' / 'CountB',0)", true);

pivotTable.PivotFields("RateA").Orientation = MSExcel.XlPivotFieldOrientation.xlDataField;

pivotTable.PivotFields("Sum of " + drFormula["CustomList"].ToString()).Caption = " RateA";