Create Calculated Field in Excel Pivotable C#

Question

4.00/5 (1 vote)

See more:

Can anyone help me convert this excel macro to c#. I need to add
the calculated field to my pivot table programmaticlly.

VB

ActiveSheet.PivotTables("MFRData").CalculatedFields.Add "calc", _
       "=order_qty *  +order_qty * 10%", True
   ActiveSheet.PivotTables("MFRData").PivotFields("calc").Orientation = _
       xlDataField

Posted 15-Jan-12 8:01am

Member 7969139

Updated 15-Jan-12 8:28am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuan-Yu Lin · Answer 1 · 2012-06-24T17:13:00

Solution 1

C#

Excel.PivotTable pt = xlSheet.PivotTables("MFRData");
Excel.PivotField ptField
Excel.CalculatedFields cfField = pt.CalculatedFields();
ptField = cfField .Add("calc", "='order_qty' * +'order_qty' * 10%", true);
pt.AddDataField(ptField , " calc", Excel.XlConsolidationFunction.xlSum);

Posted 24-Jun-12 17:13pm

Kuan-Yu Lin

Comments

gianpy67 4-Aug-15 10:08am

Hi, this row
ptField = cfField .Add("calc", "='order_qty' * +'order_qty' * 10%", true);
is error why ??

gianpy67 4-Aug-15 11:00am

When the line command is
pt.AddDataField(ptField , " calc", Excel.XlConsolidationFunction.xlSum);
error, why ??

SG2107 19-Aug-15 8:03am

Is this code working for anyone?
I am getting following error in line :
pt.AddDataField(ptField , " calc", Excel.XlConsolidationFunction.xlSum);

Error: {"The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"} System.Exception {System.Runtime.InteropServices.COMException}

Can anyone help, I need to find Percentage of 2 pivot columns

gvijay94 21-Sep-18 7:54am

Instead of this line
pt.AddDataField(ptField , " calc", Excel.XlConsolidationFunction.xlSum);

please add below 2 lines like this
ExcelLib.PivotField CalculatedPivotField = (ExcelLib.PivotField)pivotTable.PivotFields("calc");
CalculatedPivotField.Orientation = ExcelLib.XlPivotFieldOrientation.xlDataField;