Null reference exception on pivot Datafield

Aspose.Cells Product Family
1

Hi,
the following code send “System.NullReferenceException: ‘Object reference not set to an instance of an object.’” since 1.8.8 (it was working fine on 1.8.5 and before)

using (Workbook workbook = new Workbook(@“C:\Temp\TestPivot.xlsx”))
{
Worksheet sheet = workbook.Worksheets[“Pivot”];
int index = sheet.PivotTables.Add("=Data’!A1:E7", “A1”, “MyPivot”);
PivotTable pivot = sheet.PivotTables[index];
index = pivot.AddFieldToArea(PivotFieldType.Page, “H5”);
index = pivot.AddFieldToArea(PivotFieldType.Row, “H1”);
index = pivot.AddFieldToArea(PivotFieldType.Row, “H2”);
index = pivot.AddFieldToArea(PivotFieldType.Row, “H4”);
index = pivot.AddFieldToArea(PivotFieldType.Data, “H3”);
pivot.AddFieldToArea(PivotFieldType.Column, pivot.DataField);
pivot.RefreshData();
pivot.CalculateRange();
pivot.CalculateData();
workbook.Save(@“C:\Temp\TestPivotResult.xlsx”);
}

Test file: TestPivot.zip (6.2 KB)

Other question:
do you add our sample code to your unit-test?

2

@Chabie

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46360 - Null reference exception on pivot Datafield

About your question, we do test your code snippet with your template file after fixing it.

3

@Chabie,

When adding two or more PivotFields in data area, you can find PivotTable.DataField is not null.
Above sample code only adds one PivotField to data area, so the exception is produced. Please try the code snippet below on your end.

        using (Workbook workbook = new Workbook(filePath + "TestPivot.xlsx"))
        {
            Worksheet sheet = workbook.Worksheets["Pivot"];
            int index = sheet.PivotTables.Add("=Data!A1:E7", "A1", "MyPivot");
            PivotTable pivot = sheet.PivotTables[index];
            index = pivot.AddFieldToArea(PivotFieldType.Page, "H5");
            index = pivot.AddFieldToArea(PivotFieldType.Row, "H1");
            index = pivot.AddFieldToArea(PivotFieldType.Row, "H2");
            //add two or more PivotFields to data area
            index = pivot.AddFieldToArea(PivotFieldType.Data, "H4");
            index = pivot.AddFieldToArea(PivotFieldType.Data, "H3");
            pivot.AddFieldToArea(PivotFieldType.Column, pivot.DataField);
            pivot.RefreshData();
            pivot.CalculateRange();
            pivot.CalculateData();
            workbook.Save(filePath + "out.xlsx");
        }

Let us know your feedback.

4

Little remark,
in my experience and the current usage of this option in excel (manually or with aspose) in 90% of case is when pivot table have only one Data field and they would like only one data field.

But nice try :slight_smile:

5

@Chabie,

PivotTable.DataField only exists when adding two or more PivotFields to data area. If you are able to achieve these requirements with single PivotField manually with MS Excel then share the file and we will share our feedback after analysis.