We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

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 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?

Hi,


Thanks for your posting and using Aspose.Cells.

The following properties might be useful for you. Please see the sample code, its source excel file and the console output for your reference.

  • PivotTable.ColumnRange
  • PivotTable.DataBodyRange
  • PivotTable.RowRange
  • PivotTable.TableRange1
  • PivotTable.TableRange2

C#
Workbook wb = new Workbook(“sample.xlsx”);

Worksheet ws = wb.Worksheets[1];

PivotTable pt = ws.PivotTables[0];

Debug.WriteLine(pt.ColumnRange);
Debug.WriteLine(pt.DataBodyRange);
Debug.WriteLine(pt.RowRange);
Debug.WriteLine(pt.TableRange1);
Debug.WriteLine(pt.TableRange2);


Console Output
Aspose.Cells.CellArea(B3:H4)[2,1,3,7]
Aspose.Cells.CellArea(B5:H47)[4,1,46,7]
Aspose.Cells.CellArea(A5:A47)[4,0,46,0]
Aspose.Cells.CellArea(A3:H47)[2,0,46,7]
Aspose.Cells.CellArea(A1:H47)[0,0,46,7]