Get cell value in the pivot table in Excel worksheet using C#.NET

Aspose.Cells Product Family
1
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.


2

Hi,


Thanks for providing us sample code segment with details.

Well, you may call PivotTable.RefreshData() and PivotTable.CalculateData() methods before instantiating the cell to retrieve its value.

By the way, to apply formatting to pivot table report cells, you may try to use PivotTable.Format() and PivotTable.FormatAll() methods too.

Hope, this helps a bit.

Thank you.
3

Are both calls needed?

Just once, or within the while loop (multiple times)?
. . .
I see only need them once. Still wondering if I need both, though (Refresh and Calculate).
4

Hi,


Yes, it is better to use both methods and only once, you may move the relevant lines of code outside of loop.

Thank you.