How to set pivot table attribute dataOnRows?

Aspose.Cells Product Family
1

In EPPlus there is an option for setting property DataOnRows for pivot tables so datafields are placed in columns instead of rows - how do you obtain the same in Aspose.Cells for .NET?pivotTableDefinition.jpg (98.9 KB)
ColumnStacked now.jpg (144.5 KB)
ColumnStacked to be.jpg (137.4 KB)

2

@alexdesouza26

Thanks for using Aspose APIs.

Please use the PivotTable.DataField for your needs. Please see the following sample code, its input and output Excel files as well as screenshots for a reference.

Download Link:
Input and Output Excel Files.zip (17.1 KB)

C#

Workbook wb = new Workbook("source.xlsx");

Worksheet ws = wb.Worksheets[0];

PivotTable pt = ws.PivotTables[0];

PivotField pdf = pt.DataField;

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

pt.RefreshData();
pt.CalculateData();

wb.Save("output.xlsx");

Screenshot - Before

Screenshot - After

3

Thank you, it helped :slight_smile: I couldn’t figure out the syntax.

By the way, is it possible to create a slicer or should it be inserted on beforehand? It wasn’t neither possible in EPPlus but while evaluating your product I noticed it is able to update the slicer values automatically.

4

@alexdesouza26

Thanks for your feedback.

You cannot create slicer using Aspose.Cells nor you can manipulate it. So, you must create it manually using Microsoft Excel beforehand.

I have not tried it yet but it is good to know that this thing works.