How to apply formula to pivot table data ysing C#

Aspose.Cells Product Family
1

Hi All,

I am having sheet which shows the data for employees Shift wise. I have used the pivot table to show the data employee wise..

the problem is wherever I am using Consolidation Function as SUM the data is coming correctly but when i am using Consolidation Function as Average , the values are getting different.

for this reason I want to calculate in pivot table..

So Please If any one can tell me how to calculate or apply formulas in pivot table..

I tried pivotTable.AddCalculatedField but I think it will apply to already existing data of sheet..

I want to apply formulas to datafields of pivot table ..

I hope .. understand my requirement..

Thanks,

2

Hi,

"the problem is wherever I am using Consolidation Function as SUM the
data is coming correctly but when i am using Consolidation Function as
Average , the values are getting different."

Well if it works fine for SUM function, it should work equally fine for Average Consolidation Function. e.g the following sample code should work fine:
PivotField pivotField = pivotTable.DataFields[0];
pivotField.Function = ConsolidationFunction.Average;
pivotField.NumberFormat = “0.00”;

Kindly provide me the sample code with template file(s) to reproduce the issue here, we will check your issue soon.

Thank you.

3

Hi Amjad sahi,

thanks for your Reponse..

Sorry.. i cant attach any files but i will explain you.

my problem is .. I am having data in my first sheet Like this

Name Shift Sale Cancel Cancel %

XYZ AM 8 0 100 %

XYZ PM 5 1 80 %

Now the formula Cancel % is 1-(Cancel/sale) * 100

Now in the second sheet ... i am using Pivot table to get Employee wise and taking SUM function for sale and Cancel and Average Function for Cancel %.

In the second sheet I am getting data as

Name Sale Cancel Cancel %

XYZ 13 1 90 %

Cancel % as 90 % but actually if we go by above formula it has to be 92%

So , for this reason I asked you how to calculate the formulas in pivot table.

So , I will be grateful .. if you can provide C# code and expalination for this problem.

Thanks ,

4

Hi,

Well, I am afraid, we only provide PivotTable.AddCalculatedField api to add formulas/functions, you may try if it suit your need. If it does not work, kindly create a simple Excel file (with dummy data - as you mentioned) containing your desired pivot table report and post it here, we will check if we can make it.

Thank you.


5

Hi Amjad sahi,

I have attached the Dummy File ..

If you go to pivot table sheet..

In grand totals .. Cancel % is 92.50 %

but actually If you apply formula what i mentioned in earlier posts.. it should be 83.33 %

I hope you got my point now..

Please if possible provide solution and C# for this kind of requirement.

Thanks,

6

Hi,

Here is the sample code to mimic the pivot table report result similar to your template.

Workbook workbook = new Workbook(@“e:\test\SampleBookforTSr.xlsx”);

Worksheet sourceSheeet = workbook.Worksheets[0];
Cells cells = sourceSheeet.Cells;
int i = workbook.Worksheets.Add();
Worksheet pivotSheeet = workbook.Worksheets[i];
pivotSheeet.Name = “Pvt Table”;

PivotTableCollection pTables = pivotSheeet.PivotTables;

string sourceData = “=DataSheet!A1:E5”;
string destCellName = “A3”;
int index = pTables.Add(sourceData, destCellName, “PivotTable1”);
// access the new pivottable

PivotTable pTable = pTables[index];

pTable.AddFieldToArea(PivotFieldType.Page, 0); 

pTable.AddFieldToArea(PivotFieldType.Row, 1);

// drag 3 fields to the data area.
pTable.AddFieldToArea(PivotFieldType.Data, 2);
pTable.AddFieldToArea(PivotFieldType.Data, 3);
pTable.AddFieldToArea(PivotFieldType.Data, 4);
//pTable.AddCalculatedField(“Average of Cancel %”, “1-(Cancels/Sales) * 100”, true);
pTable.AddFieldToArea(PivotFieldType.Column, pTable.DataField);
pTable.ColumnFields[0].DisplayName = “Values”; 

pTable.DataFields[0].Function = ConsolidationFunction.Sum;
pTable.DataFields[1].Function = ConsolidationFunction.Sum;
pTable.DataFields[2].Function = ConsolidationFunction.Average;
pTable.DataFields[2].NumberFormat = “0.00%”;
workbook.Save(@“e:\out.xlsx”);

But again for:

“but actually If you apply formula what i mentioned in earlier posts… it should be 83.33 %”

I asked you how could you do this in pivot table report (in MS Excel manually) that should automatically gives you “83.33%”, you may post a template file for that here. Just for your knowledge Aspose.Cells forces MS Excel to refresh or create pivot table for the source data, as Aspose.Cells does not create pivot table on its own.

Thank you.

7

Hi Amjad Sahi,

"

I asked you how could you do this in pivot table report (in MS Excel manually) that should automatically gives you "83.33%", you may post a template file for that here. Just for your knowledge Aspose.Cells forces MS Excel to refresh or create pivot table for the source data, as Aspose.Cells does not create pivot table on its own.

"

I did not get what you want to say.. can you please explain more clearly.

I tried this line of code

pTable.AddCalculatedField("Average of Cancel %", "1-(Cancels/Sales) * 100", true);

But error is coming . I mean while opening the sheet some Inegrity Error is coming..

I dont want anything to do manually..

I asked you if we want to apply this formula 1-(Cancels/Sales) * 100 in pivot table..then

how can we achieve it through C# code.

To be More Specific.. I want to apply formula to Pivot Table data..

I hope You understand what I want..

i am extremely sorry. if I cant able to Explain you properly..

Thanks

8

Hi,

I requested if you could create your desired pivot table (in MS Excel
manually) that should automatically gives you “83.33%” result against your formula in a pivot table report and save the Excel file to post us here. We will check to see if we can make it with Aspose.Cells for .NET API, Also, I am afraid, if you want to insert custom formulas into some of its data cells or format pivot table, Aspose.Cells does not support it at the moment, as the product does not customize the pivot table report (it totally depend on MS Excel to forces it to create the pivot table report when one opens the generated file into MS Excel) actually.

To further update you, we are currently working on some advanced features regarding pivot tables, here is the recent plan for you.

Before the end of:

1) August 2010: Support for reading Pivot Table from xlsx files.
2) September 2010: Support for modifying the existing Pivot Tables in the template files.


Thank you.

9

@jack123,

We supported the feature (e.g create, manipulate and refresh Pivot Tables in XLSX (MS Excel 2007 - 2019)) with enhancements in newer versions. We recommend you to kindly upgrade to and give it a try to latest version of Aspose.Cells. Also, see the document for your reference:
Create Pivot Tables and Pivot Charts