Null Reference Exception When Reading XLSX Using LoadFilter Containing Pivot Table

Aspose.Cells Product Family
1

Hi

The test application downloadable from the link below demonstrates what we believe to be a bug regarding the PivotTable load filter option.

For purposes of our real application we want to set the LoadFilter to bring in only the bare minimum we need
from the workbook, namely, the cell values.

We use the LightCellsDataHandler to extract the actual data we
desire, but we’ve discovered that setting the LoadFilter to include just CellValue does not work for all
Excel files.

Specifically, if the spreadsheet includes a pivot table, then it is necessary to also include
PivotTable in the LoadFilter, otherwise the Workbook constructor throws an exception “Object reference
not set to an instance of an object”.

http://tinyurl.com/yabyxcsh

I have included some small sample files which may be used to demonstrate the problem:

Classic.xlsx A simple file having just one sheet and no pivot table.
Classic.xls Same a Classic.xlsx, but in Excel 97-2003 format.
ClassicWithPivotTable.xlsx Same as Classic.xlsx, but includes a pivot table.
ClassicWithPivotTable.xls Same as ClassicWithPivotTable.xlsx, but in Excel 97-2003 format.

Run the test application with the following command lines to see the problem.

TEST #1
TestAsposeDataFilterOptions.exe Classic.xlsx -f:All
No problem.

TEST #2
TestAsposeDataFilterOptions.exe ClassicWithPivotTable.xlsx -f:All
No problem.

TEST #3
TestAsposeDataFilterOptions.exe Classic.xlsx -f:CellValue
No problem. Produces same results as TEST #1.

TEST #4
TestAsposeDataFilterOptions.exe ClassicWithPivotTable.xlsx -f:CellValue
Workbook construct throws the null reference exception.

TEST #5
TestAsposeDataFilterOptions.exe ClassicWithPivotTable.xlsx -f:CellValue -f:PivotTable
No problem. Produces same results as TEST #2.

Curiously, there isn’t a problem with xls (i.e., Excel 97-2003) format files. Thus:

TEST #6
TestAsposeDataFilterOptions.exe Classic.xls -f:CellValue
No problem, as expected.

TEST #7
TestAsposeDataFilterOptions.exe ClassicWithPivotTable.xls -f:CellValue
No problem. So the pivot table null reference exception doesn’t happen with xls (as opposed to xlsx) files.

Thanks

Gareth

2

@datwat,

Thanks for the sample project, template files and details.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample project with your template file(s). I found NullReferenceException occurred when reading XLSX using LoadFilter option containing Pivot Table. It looks like if a spreadsheet contains PivotTables in it, we cannot extract just the values (CellValue as filters). Also, we cannot extract data except PivotTable from the spreadsheet. I have logged a ticket with an id “CELLSNET-45610” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

3

@datwat,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-45610”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

Thank you.

4

@datwat

Please download and try the latest fix and let us know your feedback.

5

The issues you have found earlier (filed as CELLSNET-45610) have been fixed in Aspose.Cells for .NET 17.9.