CODEWITHSUNDEEP
c#excelpivot-tableaspose-cells
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

How can I determine how many rows a PivotTable generates (Aspose Cells)?

I need to conditionally colorize ranges in a PivotTable. I tried to do it this way:

private void ColorizeContractItemBlocks(List<string> contractItemDescs)
{
    int FIRST_DESCRIPTION_ROW = 7;
    int DESCRIPTION_COL = 1;
    int ROWS_BETWEEN_DESCRIPTIONS = 4;
    int rowsUsed = pivotTableSheet.Cells.Rows.Count;
    int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
    // Loop through PivotTable data, colorizing contract items
    while (currentRowBeingExamined < rowsUsed)
    {
        Cell descriptionCell = pivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
        String desc = descriptionCell.Value.ToString();
        if (contractItemDescs.Contains(desc))
        {
            // args are firstRow, firstColumn, totalRows, totalColumns
            Range rangeToColorize = pivotTableSheet.Cells.CreateRange(
                currentRowBeingExamined, 0,
                ROWS_BETWEEN_DESCRIPTIONS, _grandTotalsColumnPivotTable + 1);
            Style style = workBook.Styles[workBook.Styles.Add()];
            style.BackgroundColor = CONTRACT_ITEM_COLOR;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            rangeToColorize.ApplyStyle(style, styleFlag);
        }
        currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_DESCRIPTIONS;
    }
}

...but it doesn't work, because rowsUsed does not take into consideration the rows on the PivotTable on the pivotTableSheet, and so my while loop is never entered.

How can I determine how many rows the PivotTable takes up on the sheet, so that I can loop through the PivotTable?

Or, am I approaching this the wrong way? Is there a different standard way of manipulating the styles/formatting of a PivotTable after it has been generated?

Upvotes: 0

Views: 916

Answers (2)

Prorata
Prorata

Reputation: 483

@B. Clay Shannon, You may consider using any of the following APIs for your requirement. I have added comments to the code for your reference. 

var book = new Workbook(dir + "sample.xlsx");
var sheet = book.Worksheets[0];
var pivot = sheet.PivotTables[0];

// DataBodyRange returns CellArea that represents range between the header row & insert row
var dataBodyRange = pivot.DataBodyRange;
Console.WriteLine(dataBodyRange);
// TableRange1 returns complete Pivot Table area except page fields
var tableRange1 = pivot.TableRange1;
Console.WriteLine(tableRange1);
// TableRange2 returns complete Pivot Table area including page fields
var tableRange2 = pivot.TableRange2;
Console.WriteLine(tableRange2);
// ColumnRange returns range that represents the column area of the Pivot Table
var columnRange = pivot.ColumnRange;
Console.WriteLine(columnRange);
// RowRange returns range that represents the row area of the Pivot Table
var rowRange = pivot.RowRange;
Console.WriteLine(rowRange);

In case you still face any difficulty, please share your sample spreadsheet along with desired results (that you may create manually in Excel application) in a thread at Aspose.Cells support forum for thorough investigation.

Note: I am working as Developer Evangelist at Aspose.

Upvotes: 1

Hambone
Hambone

Reputation: 16377

The RowRange property of the pivot table should take you row by row through every element in the table:

Excel.Worksheet ws = wb.Sheets["Sheet1"];
Excel.PivotTable pt = ws.PivotTables("PivotTable1");
Excel.Range cell;

foreach (Excel.Range row in pt.RowRange)
{
    cell = ws.Cells[row.Row, 5];  // for example, whatever is in column E
    // do your formatting here
}

There are other ranges available -- for example, I typically only care about:

pt.DataBodyRange

Which is every cell within the actual pivot table (whatever is being aggregated).

Upvotes: 1

Related Questions