One of our clients asked us to create an advanced data export to an Excel file. The report had one sheet with raw data and many sheets with graphs, formulas, pivot tables etc. that were calculated based on the first sheet. We decided that the most efficient way to fulfill the request will be using example file provided by customer as a template. The idea was to perform the following process:
- prepare source data
- open template file stored as resource and fulfill sheet with raw data
- recalculate excel
- save the file
Everything went fine till the last step. When we tried to execute SaveAs method:
We received following error
error:System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.NullReferenceException: Object reference not set to an instance of an object. at OfficeOpenXml.ExcelWorksheet.SavePivotTables() at OfficeOpenXml.ExcelWorksheet.Save() at OfficeOpenXml.ExcelWorkbook.Save() at OfficeOpenXml.ExcelPackage.Save() at OfficeOpenXml.ExcelPackage.SaveAs(Stream OutputStream)
We removed pivot tables from our template file and everything worked fine. After investigation we’ve noticed that D365 has very old version of EPPlus library – 4.0.4 (it was released in 2015) current version is 4.5.3. After add of new EPPlus library the version in has been updated. We’ve tested the report export in Development environment, Test and UAT – it worked fine everywhere – except Production environment. We kept to receive the same error like with out of date library. Despite of the fact that code on UAT and Production was equal and Production environment was built several times the error persisted.
The issue has been reported to Microsoft, but we’ve been told that they don’t have capabilities to test it in environment configured as Production.
Because EPPlus is open source project, we’ve got the source project and changed the namespace and build new .dll . In this way we’ve managed to use the latest version of EPPlus under new name.