Pivot Table. Wrong Fields layout in data section

Aspose.Cells Product Family
1

Hello

Using aspose the result is:
Rows Data
RUSSIA Sum of Sprzedaż2013 347191
Sum of Marża 2013 155620,83
Sum of Marża 2014 153803,9867





And I want this layout

Rows Suma z Sprzedaż2013 Suma z Marża 2013 Suma z Marża 2014




RUSSIA 347191 155620,83 153803,9867








Part of my code:

Worksheet pivotArkusz = excel.Worksheets[excel.Worksheets.Add()];

pivotArkusz.Name = "arkusz2";

PivotTableCollection pivArk = pivotArkusz.PivotTables;

int index = pivArk.Add("=Arkusz1!B2:L6493", "B2", "tabelaPivot");

PivotTable pivotTabela = pivArk[index];

pivotTabela.RowGrand = false;
pivotTabela.ColumnGrand = false;
pivotTabela.IsAutoFormat = true;


pivotTabela.AddFieldToArea(PivotFieldType.Row, "Kraj");
pivotTabela.AddFieldToArea(PivotFieldType.Data, "Sprzedaż 2013");
pivotTabela.AddFieldToArea(PivotFieldType.Data, "Marża 2013");
pivotTabela.AddFieldToArea(PivotFieldType.Data, "Marża 2013");
pivotTabela.AddFieldToArea(PivotFieldType.Data, "Sprzedaż 2013");

Thanks for help
2

Hi Arkadiusz,


Thank you for contacting Aspose support.

Unfortunately, we are unable to properly evaluate your presented scenario due to the lack of your sample spreadsheet. However, we think your code is correct for adding the PivotField to the Data area. Additionally you should set the function for the PivotField as demonstrated below.

In case you still face any difficulty, please share your complete code (preferably a sample application if you are creating the spreadsheet from scratch) along with your input spreadsheet and desired result. You may create the desired results in Excel application manually as it will give us a good idea of your exact requirement.

C#

//Dragging Field to Data Area
PivotField field = pivotTable.BaseFields[“Sprzedaż 2013”];
field.Function = ConsolidationFunction.Sum;
field.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, field);
3

Hello


I send complete code. All required excel files are in Resources folder.

Thanks for help
4

Hi Arkadiusz,

Thank you for sharing your code and spreadsheets.

Please add the following piece of code after adding the fields to the Data area. Also attached is the resultant spreadsheet for your reference.

C#


if (pivotTabela.DataField != null)
{
//DataField attribute of PivotTable exists only if DataFields contain two or more PivotField
pivotTabela.AddFieldToArea(PivotFieldType.Column, pivotTabela.DataField);
}
5

Thanks for help. It started working

6

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is fixed by moving the DataField into the Column Area. Let us know if you encounter any other issue, we will be glad to look into it and help you further.