I need to order the ranges (each range covers four rows) by a value in one of that range's cells. This is how the first bunch of data looks:
I need to order these by the value in the cell at the juxtoposition of "Sum of TotalPrice" and "Grand Total". So in the data shown, the order should be:
ASPARAGUS, STANDARD 11/1#
LETTUCE, SPRING MIX 3#
ASPARAGUS, LARGE 11/1#
LETTUCE, ROMAINE HEARTS 48 CT
CUCUMBERS, ENGLISH (SDLS) 12-14 CT
TOMATOES, 5x6 LOOSE X-LG 25#
CARROTS, BABY PEELED W/TOPS 5# (IMPORT)
TOMATOES, CHERRI 12/1 PT
How can I do that, with Aspose Cells?
In Excel Interop (which is untenable for some of these reports, as it will not even generate the larger ones, even after hours, whereas Aspose Cells can generate them in several minutes), the code is this:
// These two lines don't seem that they would do so, but they do result in the items
// being sorted by (grand) total purchases descending
var fld = ((PivotField)pvt.PivotFields("Description"));
fld.AutoSort(2, "Total Purchases");
As the comments indicate, why this works is a mystery to me, and surely the way to do this in Aspose Cells, and probably more grokkable, but just how is the question.
UPDATE
The following code:
pivotTable.DataFields[0].DisplayName = "Total Packages";
pivotTable.DataFields[1].DisplayName = "Total Purchases";
pivotTable.DataFields[2].DisplayName = "Avg Purchase";
pivotTable.DataFields[3].DisplayName = "% of Total";
pivotTable.RowFields[0].ShowInOutlineForm = true;
pivotTable.RowFields[0].ShowCompact = true;
PivotField field = pivotTable.RowFields[1];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 1; // 0 based indexed position in the PivotTable.DataFields
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.RefreshDataFlag = false;
...but it did not work for me; it failed with, "You can't operate the field" on this line:
field.IsAscendSort = false;
