How can I hide selected ranges AND sort the displayed results?


#1
I can sort (descending) my displayed results by a selected value using this code:

PivotField field = pivotTable.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 1;

The first screenshot below is what I see (Total Purchases displayed are indeed shown from most to least) with this code.

Or, I can only display Description ranges whose "Percentage of Total" value is at least 1% with this code:

private void HideItemsWithFewerThan1PercentOfSales()
{
int FIRST_TOTAL_PRICE_ROW = 8;
int ROWS_BETWEEN_PERCENTAGES = 4;
var pivot = pivotTableSheet.PivotTables[0];
var dataBodyRange = pivot.DataBodyRange;
int currentRowBeingExamined = FIRST_TOTAL_PRICE_ROW;
int rowsUsed = dataBodyRange.EndRow;

pivot.RefreshData();
pivot.CalculateData();

// Get grand total of purchases for all items and months, and calculate what 1% of that is
Cell totalTotalPurchasesCell = pivotTableSheet.Cells[rowsUsed - 2, _grandTotalsColumnPivotTable + 1];
double totalTotalPurchases = Convert.ToDouble(totalTotalPurchasesCell.Value);
var onePercentOfTotalPurchases = totalTotalPurchases / 100;

// Loop through PivotTable data, hiding where percentage < 0.01 (1%)
while (currentRowBeingExamined < rowsUsed)
{
Cell priceCell = pivotTableSheet.Cells[currentRowBeingExamined, _grandTotalsColumnPivotTable + 1];
String priceStr = priceCell.Value.ToString();
Double price = Convert.ToDouble(priceStr);
if (price < onePercentOfTotalPurchases)
{
pivotTableSheet.Cells.HideRows(currentRowBeingExamined - 1, ROWS_BETWEEN_PERCENTAGES);
}
currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_PERCENTAGES;
}
}

...as can be seen in the second screenshot below. But I can't get them both to work at the same time. So I can either hide the Descriptions with less than 1% of the percntage OR I can sort by Total Purchases descending, but I'm not able to accomplish both at the same time. My code to try to accomplish both is as follows:

. . .
pivotTable.AddFieldToArea(PivotFieldType.Row, DESCRIPTION_COLUMN);
pivotTable.RowHeaderCaption = "Description";

// Dragging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, MONTHYR_COLUMN);
pivotTable.ColumnHeaderCaption = "Months";

// Dragging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, TOTALQTY_COLUMN);
pivotTable.DataFields[0].DisplayName = "Total Packages";

pivotTable.AddFieldToArea(PivotFieldType.Data, TOTALPRICE_COLUMN);
pivotTable.DataFields[1].DisplayName = "Total Purchases";
. . .

// Sort by "Total Purchases" descending
PivotField field = pivotTable.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 1; // This is the "Total Purchases" field

pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.RefreshDataFlag = false;
List contractItemDescs = GetContractItemDescriptions();
ColorizeContractItemBlocks(contractItemDescs);
HideItemsWithFewerThan1PercentOfSales();
FreezePanePivotTable(HEADER_ROW, 2);
FormatPivotTableNumbers();
ConfigureForPrinting(pivotTableSheet.Cells.Rows.Count);

It's as if the sorting order is not being respected when HideItemsWithFewerThan1PercentOfSales() is called - the row numbers that method "sees" is not the row numbers according to the sorting that has been established.

How can I get both the sorting AND the hiding to work?

NOTE: Calling HideItemsWithFewerThan1PercentOfSales(); prior to the sorting code does NOT work - it still shows/hides some of the wrong things.

#2

Hi Clay,


Thank you for contacting Aspose support.

I am afraid, the current implementation of Aspose.Cells APIs do not support hiding the ranges as well as sorting the data fields. Reason being, Aspose.Cells APIs do not support to show DataField’s value as “% of Column Total”. We get the correct value relying on MS-Excel auto-refreshing therefore when comparing the value after calling RefreshData() and CalulateData(), we get the wrong compare result.

Please allow me some time to check if we can log it as a feature request in order to bring this feature on our road map. I will shortly get back with details in this regard.

#3

Hi again,

This is to update you that I have logged a formal request under the ticket CELLSNET-44956 for the product team’s review. Please spare us some time for the feasibility analysis. Once we have the results, we will be in a better position to provide the estimated time to provide the feature. As soon as we get any updates in this regard, we will post here for your kind reference.


#4

Thanks; I know the customer will miss either feature (sorting or hiding), so if it’s possible to add the feature, it would be great.


#5

Hi Clay,


Thank you for writing back.

Please note, as we have just logged a formal request for the said feature, we will require some time for the feasibility analysis. Once we have the results, we will be in a better position to comment when and how we can provide the said feature with future releases of Aspose.Cells APIs. We humbly request you to be patient. As soon as we receive any updates from the product team in this regard, we will post here for your kind reference.

#6

I am going on a hunger strike, and will take no sustenance until this new feature is implemented.


#7

Hi Clay,


It is good to know that you are not going on hunger strike any more. Please stay in touch for more updates regarding the requested feature.

#8

@cshannon,

Please try our latest version/fix: Aspose.Cells for .NET v19.10.4 (attached)
Aspose.Cells19.10.4 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells19.10.4 For .Net4.0.Zip (5.0 MB)

Your issue should be fixed in it.

Let us know your feedback.


#9

The issues you have found earlier (filed as CELLSNET-44956) have been fixed in Aspose.Cells for .NET v19.11. This message was posted using Bugs notification tool by Amjad_Sahi