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
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”.
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.
TestAsposeDataFilterOptions.exe Classic.xlsx -f:All
TestAsposeDataFilterOptions.exe ClassicWithPivotTable.xlsx -f:All
TestAsposeDataFilterOptions.exe Classic.xlsx -f:CellValue
No problem. Produces same results as TEST #1.
TestAsposeDataFilterOptions.exe ClassicWithPivotTable.xlsx -f:CellValue
Workbook construct throws the null reference exception.
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:
TestAsposeDataFilterOptions.exe Classic.xls -f:CellValue
No problem, as expected.
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.