I need to loop through a PivotTable and colorize certain ranges. I'm trying to do that with the following code:
private void ColorizeContractItemBlocks(List contractItemDescs)
{
int FIRST_DESCRIPTION_ROW = 7;
int DESCRIPTION_COL = 0;
int ROWS_BETWEEN_DESCRIPTIONS = 4;
var pivot = pivotTableSheet.PivotTables[0];
// DataBodyRange returns CellArea that represents range between the header row & insert row
var dataBodyRange = pivot.DataBodyRange;
int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
int rowsUsed = dataBodyRange.EndRow;
// 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;
}
}
The problem is that it crashes on the second line below because “descriptionCell” is considered to be a null value:
Cell descriptionCell = pivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
String desc = descriptionCell.Value.ToString();
It crashes the first time through the loop, when the value of currentRowBeingExamined is 7. Keeping in mind that Aspose Cells rows and columns are 0-based, you can see that “A8” (In spreadsheet parlance), which corresponds to row 7, column 0, according to Aspose Cells’ take on it, does indeed have a value, namely, “ANISE, FENNEL 12 CT”:
Stepping through it, I see, "A8; ValueType: IsNull"
So why is the assignment to descriptionCell null rather than “ANISE, FENNEL 12 CT”?
Note: ColorizeContractItemBlocks() is called after the PivotTable has been generated. If I comment out the call to ColorizeContractItemBlocks(), the spreadsheet is generated, with the PivotTable on it, as you can see in the screenshot.