How to Hide a Specified Number of Contiguous Rows on Spreadsheets with Aspose Cells

Hidden in Plain Sight

To hide any number of contiguous rows based on some condition when using the Aspose Cells library, you can use code like this:

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

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

    // Loop through PivotTable data, hiding where percentage < 0.01 (1%)
    while (currentRowBeingExamined < rowsUsed)
    {
        Cell percentageCell = pivotTableSheet.Cells[currentRowBeingExamined, _grandTotalsColumnPivotTable];
        String prcntgStr = percentageCell.Value.ToString();
        Decimal prcntg = Convert.ToDecimal(prcntgStr);
        if (prcntg < 0.01M)
        {
            pivotTableSheet.Cells.HideRows(currentRowBeingExamined-3, ROWS_BETWEEN_PERCENTAGES);
        }
        currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_PERCENTAGES;
    }
}

The logic, of course, will differ based on your data and situation (how many rows at a time you want to hide - maybe just one! - and what condition you want to use as a gauge for what should be visible and what not, or whatnot), but these are the parts that you will need in any case:

var pivot = pivotTableSheet.PivotTables[0]; // change sheet named from "pivotTableSheet" to yours
var dataBodyRange = pivot.DataBodyRange;
int rowsUsed = dataBodyRange.EndRow;
nt currentRowBeingExamined = someStartingRow; // change "someStartingRow" to what you need

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

// Loop through PivotTable data, hiding where a particular condition is met
while (currentRowBeingExamined < rowsUsed)
{
    Cell someCell = pivotTableSheet.Cells[currentRowBeingExamined, _grandTotalsColumnPivotTable];
    if (yourCondition) // replace "yourCondition" with the logic you need
    {
    	// replace first arg with the first row you want to hide; replace the second arg with how many rows from there you 

want to hide - perhaps just 1
        pivotTableSheet.Cells.HideRows(currentRowBeingExamined, someNumberOfRows);
    }
    // If you're examining each row, and only hiding one when the condition is found, change to inc by 1
    currentRowBeingExamined = currentRowBeingExamined + someNumberOfRows;
    }
}