EPPlus Sort Pivot Table on DataField instead of just RowField

Question

I have created an Excel Pivot Table in a worksheet using the EPPlus toolkit, version 3.1. I am able to sort the resulting table on the row field but would like to be able to do it on the Data Fields. As an example, I downloaded the 3.1 source code from here:

EEPlus 3.1 Source Code

which has a unit test called CreatePivotTable(). It contains a 'Data' tab that looks like so:

var ws = _pck.Workbook.Worksheets.Add("Data");
ws.Cells["K1"].Value = "Item";
ws.Cells["L1"].Value = "Category";
ws.Cells["M1"].Value = "Stock";
ws.Cells["N1"].Value = "Price";
ws.Cells["O1"].Value = "Date for grouping";
...

The test then adds 9 pivot tables sheets. Looking at the first on, the code is this:

var pt = wsPivot1.PivotTables.Add(wsPivot1.Cells["A1"], ws.Cells["K1:N11"], "Pivottable1");
pt.GrandTotalCaption = "Total amount";
pt.RowFields.Add(pt.Fields[1]);
pt.RowFields.Add(pt.Fields[0]);
pt.DataFields.Add(pt.Fields[3]);
pt.DataFields.Add(pt.Fields[2]);
pt.DataFields[0].Function = DataFieldFunctions.Product;
pt.DataOnRows = false;

As is, there is no sorting. If I apply a sort to the first field of the main pivot field collection which is also the second of the two row fields it works:

pt.Fields[0].Sort = eSortType.Descending;

But what if I want to sort on one of the DataFields like so (both pointing to "Price":

pt.Fields[2].Sort = eSortType.Descending;
//or
pt.DataFields[0].Field.Sort = eSortType.Ascending;

The sorting does not apply. It works fine if I do it in excel and add it manually. Also, I use NetOffice on another project and that can do it as well. Is that not something that EPPLus can do yet?

Gloria Li Gloria Li · Accepted Answer · 2021-12-08T23:37:53

I was facing the same issue and found a solution. Hope it helps if anyone is searching for it:)

var rowField = pivotTable.RowFields.Add(pivotTable.Fields[rowFieldName]);
// or var rowField = pivotTable.Fields[rowFieldName]; if rowField is already added
var dataField = pivotTable.DataFields.Add(pivotTable.Fields[dataFieldName]);
rowField.SetAutoSort(dataField, eSortType.Ascending);
rowField.Items.Refresh();

Reference: https://epplussoftware.com/en/Developers/SortingRangesAndTables

EPPlus Sort Pivot Table on DataField instead of just RowField

1 Answers