Adding Calculated Field into Pivot table in C#.NET

Aspose.Cells Product Family
1

Does anyone have simple example that will do this? I looked everywhere in this forum.

Maybe I just can't find it, sorry

thank you,

C

2

Hi,

Thanks for considering Aspose.

I am not sure about your exact need. Could you elaborate you query to exaplian it more.

And Please check for reference some topics in the section, may be it's a bit helpful to you:

Thank you.

3

hi Amjad,

I need to insert a new list using "Calculated Field" to PT Field List. (ie, I have Quantity and Price in the Field list. I need Total(new list) = quanity * price.

This is just simple calculation. Things I need will be more complicated.This has to be done because I need calculated answer from lists already in the PT Field List.

thanks,

C

4

Hi,

We will get back to you soon.

Thanks for being patient!

5

Hi,

Could you create a file in Excel to show what you want to do? It will help us know your need.

6

I made a really simple pivot table using "calculated field". If you look at the Field list, you'll see a total list but not in the Raw Data sheet. I can do this easily in Excel but not using C# which I need to do in order to automate the Report. If you need any other info, please let me know. thanks C

7

Hi,

It's same as in Excel.You should drag 3 pivot field to data area and drag 1 pivot field to column area.The "calculated field" is PivotTable.DataField. See following codes(the source file contains "Raw data " sheet):

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\source.xls");//source sheet
workbook.Worksheets.Add();
Worksheet pivotSheeet = workbook.Worksheets[1];
pivotSheeet.Name = "Pivot Table";
workbook.Worksheets.Move(0, 1);
PivotTables pTables = pivotSheeet.PivotTables;

string sourceData = "='Pivot Table'!A1:C6";
string destCellName = "A3";
int index = pTables.Add(sourceData, destCellName, "PivotTable1");
// access the new pivottable
PivotTable pTable = pTables[index];
// drag 3 fields to the data area.
pTable.AddFieldToArea(PivotFieldType.Data, 0);
pTable.AddFieldToArea(PivotFieldType.Data, 1);
pTable.AddFieldToArea(PivotFieldType.Data, 2);
//drag "Date" pivot fields to column area.
pTable.AddFieldToArea(PivotFieldType.Column, 0);
workbook.Save(@"F:\FileTemp\dest.xls", FileFormatType.Excel2003);

8

Thank you very much for trying to help me.

I'll try to be more clear. In C#, I know how to create pivot table and know how to drag pivot field to all the areas(ie, page area, column area, row area, and data area. Only question I have is how do I create a "Calculated Field" and be able to put in "Formulas" for one. So, how can I do that in codes? Simple example from my excel file would be total = price * quantity.

thanks,

C

9

Hi,

Sorry for my fault. Now I know what you want to do. But the feature is not supported. We will looking to this feature.

10

Do you think this feature could be added soon or do you think I should create my own com using Interop? Please let me know soon. thanks… C

11

Hi,

Thanks for considering Aspose.

We will get back to you soon.

Thank you.

12

Hi,

We add two method to help you add calculated field.

///


/// Adds a calclulated field to pivot field and drag it to data area.
///
/// The name of the calculated field
/// The formula of the calculated field.
public void AddCalculatedField(string name, string formula)
///
/// Adds a calclulated field to pivot field.
///
/// The name of the calculated field
/// The formula of the calculated field.
/// True,drag this field to data area immediately
public void AddCalculatedField(string name, string formula,bool dragToDataArea)

We will attach the fix soon.Thanks for your patience.

13

Hi Warren,

Thanks for your help. Do you have an estimate of when the hotfix will be ready ? Doesn't have to be exact, just trying to get an idea of the wait.

14

Hi,

Please try this fix.

We have implemented the two methods.

15

Warren, thanks for your help, we have downloaded the evaluation copy.

One thing I notice is that the Aspose.Cells.Columns.Style property is now read only ... is there any way to set a style for an entire column/row other that doing it cell by cell?

Thanks

16

Hi,

Thanks for considering Aspose.

Well, in the new versions of Aspose.Cells, you may create a Style object, specify some format settings and use StyleFlag struct to implement your desired formattings only, finally you may use Column / Row . ApplyStyle(style, styleflag) method. And moreover this method will enhance your performance too.

May the following code helps you for your need.

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Define a style object adding a new style
//to the collection list.
Style stl2 = workbook.Styles[workbook.Styles.Add()];
//Set the custom cell shading color.
stl2.ForegroundColor = Color.Yellow;
//Set the solid background pattern for fillment color.
stl2.Pattern = BackgroundType.Solid;
//Set the font.
stl2.Font.Name = "Trebuchet MS";
//Set the font color.
stl2.Font.Color = Color.Maroon;
//Set the font size.
stl2.Font.Size = 10;
//Set the style flag struct.
StyleFlag flag = new StyleFlag();
//Apply cell shading.
flag.CellShading = true;
//Apply the font.
flag.FontName = true;
//Apply the font color.
flag.FontColor = true;
//Apply the font size.
flag.FontSize = true;
//Get the first column in the worksheet.
Column col = worksheet.Cells.Columns[0];
//Apply the style to it.
col.ApplyStyle(stl2,flag);
Workbook.Save("d:\\test\\formatcol.xls");
Thank you.
17

never mind, I figured it out … using Columns.ApplyStyle instead