Creating Pivot Table with "Values" Column

Aspose.Cells Product Family
1

Can anyone assist on a small problem I have please.

I’ve created a PivotTable but want to replicate the “[Sigma] Values” column that is automatically generated in Excel (in the Column Labels).

I don’t want to list Columns, just the generic “Data” that encapsulates individual dragged fields into the data area.

Is there any way of doing this?

2

Hi,


I think you may use RowGrand or ColumnGrand boolean attributes of PivotTable accordingly for your need:
e.g
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

If you have different requirement, give us your simple sample template file with details (you may give description/text or screen shots for your needs), so that we can understand your requirement and reply you accordingly.

Thank you.
3

Amjad,

Thanks for your help. Unfortunately this produces an error in Excel (shows the red bar at the top saying there’s a problem).

The code I have is:

Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(0)
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report1
pivotTable.RowGrand = True
pivotTable.ColumnGrand = True

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 0)

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Product”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Detail 1”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Detail 2”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Detail 3”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Detail 4”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Brand”)

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear1”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear2”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear3”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear4”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear5”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “SumOfYear6”)


Which as you can see, I’m adding rows and the data values but don’t want the columns.


4

Hi,


Please paste your complete code here with the attachment of input and output Excel files here. Also, give us a sample file with your desired pivot table in it. We will check it soon.

Thank you.
5

All,

I ran into the same issue -- my "{Sigma} Values" data was auto-placed in the ROW instead of where I wanted it in the COLUMN. I have a hack (Java) below that moves the PivotField over to the COLUMN, but maybe the experts have a better way?

- Kieron

int i = ws.getPivotTables().add(data, "A1", PIVOT_TABLE_NAME);

PivotTable p = ws.getPivotTables().get(i);

p.setRowGrand(true);
p.setColumnGrand(true);

p.addFieldToArea(PivotFieldType.ROW, 0); //Year
p.addFieldToArea(PivotFieldType.ROW, 1); //Month
p.addFieldToArea(PivotFieldType.DATA, 2); //No. Findings
p.addFieldToArea(PivotFieldType.DATA, 3); //No. Meetings

//this is a hack to move the auto-generated "{Sigma} Values" PivotField to the correct location
if(p.getFields(PivotFieldType.ROW).size() > 0) {
PivotFields pfs = p.getFields(PivotFieldType.ROW);
for (int x = 0; x < pfs.size(); x++) {
PivotField pf = pfs.get(x);
//System.out.println("PF, {name:"+pf.getName()+", displayName:"+pf.getDisplayName()+", field:"+pf.getBaseField()+", function:"+pf.getFunction()+"}");

//I only added "Year" and "Month" above. "Data" is the name of the auto-generated PivotFieldType.ROW
if (pf.getName().equals("Data")) {
//System.out.println("pf moved");
p.addFieldToArea(PivotFieldType.COLUMN, pf);
}

}
}

6

Kieron,

Thanks for the help there, glad to hear I explained myself correctly and there’s a somewhat-solution behind it.

Will look at converting that hack to VB and see if it does the trick, but likewise if the Aspose experts have a better/proper/official way of achieving this it would be nice to now.

Thanks again Kieron, much appreciated.

7

Kieron

Modified my code to be a VB equivalent of your Java hack and it works perfectly!

Thank you SO much for your assistance.

Best regards,

Jason

8

Glad to help -- and to know that at least this hack is cross-platform-able.

- Kieron

9

HUGE HELP! THANK YOU.


I can confirm this worked in C# as well:

PivotTableCollection pivotTables = sheet2.PivotTables;
PivotTable p = pivotTables[0];
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

pivotTable.AddFieldToArea(PivotFieldType.Row, “SOME FIELD”); // BU
pivotTable.AddFieldToArea(PivotFieldType.Row, “SOME FIELD2”); // LE_NM
pivotTable.AddFieldToArea(PivotFieldType.Data, “SOME FIEL3”);
pivotTable.AddFieldToArea(PivotFieldType.Data, “SOME FIELD4”);

//this is a hack to move the auto-generated “{Sigma} Values” PivotField to the correct location
if (p.Fields(PivotFieldType.Row).Count > 0) {
PivotFieldCollection pfc = p.Fields(PivotFieldType.Row);
for (int x = 0; x < pfc.Count; x++) {
PivotField pf = pfc[x];
if (pf.Name.Equals(“Data”)) {
p.AddFieldToArea(PivotFieldType.Column, pf);
}
}
}

10

Hi,

You can use this sample code:


pivotTable.ColumnFields.Add(pivotTable.DataField);//move datafied to columns


OR

PivotField data = pivotTable.DataField;
pivotTable.AddFieldToArea(PivotFieldType.Column, data);


11

So the property DataField stores the "{Sigma} Values" com.aspose.cells.PivotField Object. Niiice. I'll try that out. Certainly a lot more efficient to access a property than to run a "for" loop.

Could "DataField" be added to the list of public properties in the Java Doc? Although I guess it might be confusing when "DataField" is alongside the "getDataFields()" function... hmmm... *shrug*

Oh well. Good to know. Thank you for the insight!

- Kieron

12

Hi,


We provide the similar APIs for Java version as well, e.g There are two property setters/getters for PivotTable class in the JAVA Docs:

getDataField:
public PivotField getDataField()
Gets a PivotField object that represents all the data fields in a PivotTable. Read-only.It would be init only when there are two or more data fields in the DataPiovtFiels. It only use to add DataPivotField to the PivotTable row/column area . Default is in row area.

getDataFields:
public PivotFieldCollection getDataFields()
Gets a PivotField object that represents all the data fields in a PivotTable. Read-only.It would be init only when there are two or more data fields in the DataPiovtFiels. It only use to add DataPivotField to the PivotTable row/column area . Default is in row area.