I have some difficulties to create a pivot table in my .net application with Aspose.Cells.
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”);
[quote user=“mshakeel.faiz”]
pt2.PivotTableStyleName = “PivotStyleLight16”;
pt2.RowFields[0].IsAscendSort = true;
pt2.RowFields[0].IsAutoSort = true;
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.
- http://www.aspose.com/docs/display/cellsnet/Working+with+Pivot+Tables
- 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.
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.
[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]Hi,
Sort on pivot data can be done in excel by this way, using my attached excel as example:
Hi,
@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.