Pivot table question

henry8668 · November 22, 2022, 6:51pm

I have some difficulties to create a pivot table in my .net application with Aspose.Cells.

By following this example,

I am able to add one field in as data.

However I’d need to do more with that. See attached excel file.

1)use first column(name) as pivot row

2)use second column(q1) as pivot data(sum instead of count)

3)use third column(q2) as pivot data(sum instead of count)

4)sort pivot table by q1

5)format pivot table q1 column as currency.

I am able to do 1) and 2), but not sure how to do 3).

4) and 5) are nice to have.

anyone can help? Thanks!

shakeel.faiz · July 2, 2017, 5:07pm

Hi Henry,

Thanks for your posting and using Aspose.Cells for .NET.

Please see the following code. It creates a pivot table, add first column to row labels and second and third columns as pivot data. It then formats the q1 as currency. It also adds sorting on row field and autoformat the pivot table.

C#

string filePath = @“F:\Shak-Data-RW\Downloads\Book1.xlsx”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

PivotTable pt1 = worksheet.PivotTables[0];

int pt2Idx = worksheet.PivotTables.Add(“Sheet1!$A$1:$C$7”, “C13”, “MyPivot”);

PivotTable pt2 = worksheet.PivotTables[pt2Idx];

pt2.AddFieldToArea(PivotFieldType.Row, 0);

pt2.AddFieldToArea(PivotFieldType.Data, 1);

pt2.AddFieldToArea(PivotFieldType.Data, 2);

pt2.ColumnFields.Add(pt2.DataField);

pt2.DataFields[0].Number = 7;

pt2.PivotTableStyleName = “PivotStyleLight16”;

pt2.RowFields[0].IsAscendSort = true;

pt2.RowFields[0].IsAutoSort = true;

pt2.CalculateData();

pt2.RefreshData();

pt2.RefreshDataOnOpeningFile = true;

workbook.Save(filePath + “.out.xlsx”);

henry8668 · July 2, 2017, 5:07pm

[quote user=“mshakeel.faiz”]

pt2.PivotTableStyleName = “PivotStyleLight16”;
pt2.RowFields[0].IsAscendSort = true;
pt2.RowFields[0].IsAutoSort = true;

[/quote]

Thanks Mshakeel for your response.

It works except the styling and sorting. The above three lines seem

don’t work, since the pivot table is not sorted and styled. Could you check if

it works on your side?

How do I change the value field settings, for example, change from sum of q2

to count of q2 or average of q2?

Is there more detailed document that I can refer to?

Thanks!

shakeel.faiz · September 27, 2022, 7:05pm

Hi Henry,

Thanks for your posting and using Aspose.Cells for .NET.

Sorting is not working at my end too. However Styling is working fine. Please download and use the latest version: Aspose.Cells for .NET 7.6.0 and see if it resolves your issue.

For value field settings, changing from sum of q2 to count of q2, you can use the following code snippet.

PivotField pf = pt2.DataFields[1];
pf.Function = ConsolidationFunction.Count;

We have attached the output file for your reference.

For documentation, you can see the following articles for your needs.

Aspose Documentation

Insert Pivot Table

Create and format pivot tables of Excel spreadsheet files.
http://www.aspose.com/docs/display/cellsnet/How+to+Create+a+PivotTable
http://www.aspose.com/docs/display/cellsnet/Setting+PivotTables+Appearance

For sorting issue, we have logged the issue in our database for investigation. We will look into it and update you asap.

This issue has been logged as CELLSNET-42090.

Amjad_Sahi · January 2, 2021, 6:55pm

Hi,

Please download and try our latest version/fix: Aspose.Cells for .NET v7.6.0.2 and let us know your feedback.

Your issue should be fixed in it.

Thank you.

henry8668 · January 2, 2021, 6:44pm

[quote user=“Amjad Sahi”]

Hi,

Please download and try our latest version/fix: Aspose.Cells for .NET v7.6.0.2 and let us know your feedback.

<o:p>Your issue should be fixed in it.</o:p>

Thank you.

[/quote]

Thanks for the quick fix, I got the latest version seems still not working, could you create a sample program that show how to generate those sorting and styling with my sample excel file in previous post?

Amjad_Sahi · July 2, 2017, 5:07pm

Hi,

I have tested your scenario/case using the latest version v7.6.0.2 with the following code and using your template file.

Sample code:

string filePath = @“e:\test2\Book1.xlsx”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

PivotTable pt1 = worksheet.PivotTables[0];

int pt2Idx = worksheet.PivotTables.Add(“Sheet1!$A$1:$C$7”, “C13”, “MyPivot”);

PivotTable pt2 = worksheet.PivotTables[pt2Idx];

pt2.AddFieldToArea(PivotFieldType.Row, 0);

pt2.AddFieldToArea(PivotFieldType.Data, 1);

pt2.AddFieldToArea(PivotFieldType.Data, 2);

pt2.ColumnFields.Add(pt2.DataField);

pt2.DataFields[0].Number = 7;

pt2.PivotTableStyleName = “PivotStyleLight16”;

pt2.CalculateData();

pt2.RefreshData();

pt2.RefreshDataOnOpeningFile = true;

workbook.Save(filePath + “.out.xlsx”);

Currency formatting and Pivot table style work fine. But, I cannot sort the column field (“Sum of q1”). I have evaluated it further and found in MS Excel, I can’t do it as well. If you could sort this field in MS Excel, let me know, you may perform the task in your template file manually in MS Excel and re-save it to share it here, we will check how to do it using Aspose.Cells APIs.

Thank you.

henry8668 · July 2, 2017, 5:07pm

Sort on pivot data can be done in excel by this way, using my attached excel as example:

right click on cell F5, then select "sort->sort largest to smallest"

then i select column F and format it as currency.

I tried your code and the styling is still not show up, weird.

Amjad_Sahi · July 2, 2017, 5:07pm

Hi,

Thanks for the template file.

I tried the following sample code with your template file, it works but sorting on "Sum of q1 " is not working:

Sample code:

string filePath = @“e:\test2\Book1.xlsx”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

PivotTable pt1 = worksheet.PivotTables[0];

int pt2Idx = worksheet.PivotTables.Add(“Sheet1!$A$1:$C$7”, “C13”, “MyPivot”);

PivotTable pt2 = worksheet.PivotTables[pt2Idx];

pt2.AddFieldToArea(PivotFieldType.Row, 0);

pt2.AddFieldToArea(PivotFieldType.Data, 1);

pt2.AddFieldToArea(PivotFieldType.Data, 2);

pt2.ColumnFields.Add(pt2.DataField);

pt2.DataFields[0].Number = 7;

pt2.PivotTableStyleName = “PivotStyleLight16”;

pt2.CalculateData();

pt2.RefreshData();

pt2.RefreshDataOnOpeningFile = true;

workbook.Save(filePath + “.out1.xlsx”);

I have attached the output file. If you could open the file into MS Excel, you will see and confirm the following:

1) Currency formatting is applied fine on "Sum of q1"

2) Pivot Style Light16 is applied fine on the Pivot Table.

Please make sure that you are using latest version/fix i.e. v7.6.0.2

However, as I mentioned the sorting is not done on “Sum of q1” column data field. I have reopened the issue “CELLSNET-42090” again for sorting problem. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

John.He · June 30, 2021, 1:24am

@henry8668,
Please try our latest version/fix: Aspose.Cells for .NET v21.6.3 (attached)
Aspose.Cells21.6.3 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.6.3 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.6.3 For .NetStandard20.Zip (5.5 MB)

Your issue should be fixed in it.
Let us know your feedback.