Creating PivoTable

Aspose.Cells Product Family
1

Hi,

I am creating one workbook with two sheets, one with data and other with the pivotable.

In the pivotable, i need the information to be presented with two columns with Sum values, as the labels of those values as Colum label, bt i only can preente that data as row label.

In the file that i have attached, you can understand better what i need.

As you will seed in the file, the PIVOT1 is generated by code, but the PIVOT2 is how i need it to be presented, can you help me on this?

Can you please tell me how i need to define the PivotTable?

Thanks in advance,

Regards

2

Hi,


Thanks for sharing the template file with some details.

Could you paste your runnable sample code on how you have created the first pivot table in the file, we need to evaluate your code a bit.

Thank you.
3

Hi,

This is the code:

int index = pivotTables.Add("=" + Livro.Worksheets[OrigemDados].Name + "!A1:" + Livro.Worksheets[OrigemDados].Cells[Livro.Worksheets[OrigemDados].Cells.MaxDataRow, Livro.Worksheets[OrigemDados].Cells.MaxDataColumn].Name,
"A5", "PivotDados");

PivotTable pivotTable = pivotTables[index];
pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Classic;
pivotTable.ColumnGrand = true;

pivotTable.AddFieldToArea(PivotFieldType.Row, 0); // cliente_nome
pivotTable.AddFieldToArea(PivotFieldType.Row, 17); // meio
pivotTable.AddFieldToArea(PivotFieldType.Row, 4); // site
pivotTable.AddFieldToArea(PivotFieldType.Row, 5); // formato

pivotTable.IsAutoFormat = true;
pivotTable.ColumnGrand = true;
pivotTable.RowGrand = true;

pivotTable.AddFieldToArea(PivotFieldType.Data, 11);
pivotTable.DataFields[0].Function = ConsolidationFunction.Sum;
pivotTable.DataFields[0].NumberFormat = "#,##0.00 €";

pivotTable.AddFieldToArea(PivotFieldType.Data, 18);
pivotTable.DataFields[1].Function = ConsolidationFunction.Sum;
pivotTable.DataFields[1].NumberFormat = "#,##0.00 €";

pivotTable.GrandTotalName = "Total Global";

pivotTable.CalculateData();int index = pivotTables.Add("=" + Livro.Worksheets[OrigemDados].Name + "!A1:" + Livro.Worksheets[OrigemDados].Cells[Livro.Worksheets[OrigemDados].Cells.MaxDataRow, Livro.Worksheets[OrigemDados].Cells.MaxDataColumn].Name,
"A5", "PivotDados");

PivotTable pivotTable = pivotTables[index];
pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Classic;
pivotTable.ColumnGrand = true;

pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(PivotFieldType.Row, 17);
pivotTable.AddFieldToArea(PivotFieldType.Row, 4);
pivotTable.AddFieldToArea(PivotFieldType.Row, 5);

pivotTable.IsAutoFormat = true;
pivotTable.ColumnGrand = true;
pivotTable.RowGrand = true;

pivotTable.AddFieldToArea(PivotFieldType.Data, 11);
pivotTable.DataFields[0].Function = ConsolidationFunction.Sum;
pivotTable.DataFields[0].NumberFormat = "#,##0.00 €";

pivotTable.AddFieldToArea(PivotFieldType.Data, 18);
pivotTable.DataFields[1].Function = ConsolidationFunction.Sum;
pivotTable.DataFields[1].NumberFormat = "#,##0.00 €";

pivotTable.GrandTotalName = "Total Global";

pivotTable.CalculateData();

Thanks,
Pedro

4

Hi,


Thanks for sharing the code snippet.

Please add a line to your code (see the line in bold), it works fine according to your needs:

//…
pivotTable.AddFieldToArea(PivotFieldType.Data, 11);
pivotTable.DataFields[0].Function = ConsolidationFunction.Sum;
pivotTable.DataFields[0].NumberFormat = “#,##0.00 €”;

pivotTable.AddFieldToArea(PivotFieldType.Data, 18);
pivotTable.DataFields[1].Function = ConsolidationFunction.Sum;
pivotTable.DataFields[1].NumberFormat = “#,##0.00 €”;

pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);

pivotTable.GrandTotalName = “Total Global”;

Thank you.
5

Thanks Amjad,

It worked fine.

6

Hi,


Good to know that your issue is resolved now.

Feel free to contact us any time if you need further help or have other queries. We will be happy to assist you.

Thank you.