In NAV 2009 R2 I did a solution that used ClosedXML to create Excel Documents. ClosedXML is built to give an easy access to the office documents and is built on top of the Open XML SDK 2.0 for Microsoft Office.
I upgraded this solution to NAV 2013 without any problems. Then to NAV 2013 R2 and there is where the problem started.
With NAV 2013 Microsoft changed the way NAV creates Excel documents. They move away from the old COM model to this Open XML method. You can see the changes in Table 370 Excel Buffer.
In NAV 2013 Microsoft used version 2.0 of the Open XML SDK. In NAV 2013 R2 Microsoft is using version 2.5 and that is where my problems started.
When trying to save the Excel document with the SaveAs function I got a SmartTag error.
Could not load type ‘DocumentFormat.OpenXml.Spreadsheet.SmartTags’
I searched and found that the SmartTags are discontinued in version 2.5 but they are still used in ClosedXML. As usual there is a solution and I found it on the Closed XML forum.
A WORKAROUND TO GET CLOSEDXML working for DocumentFormat.OpenXml SDK 2.5:
- Download ClosedXML source code
- Open ClosedXML source with Visual Studio
- Browse to the References of “ClosedXML” project and remove the reference DocumentFormat.OpenXml
- Use Nuget to install the latest Document.OpenXml 2.5 SDK as reference into the “ClosedXML” project
- Open the file XLWSContentManager.cs and remove the line 86
- Compile the project
For some reason it does not work for that way for “ClosedXML_Net3.5” project.
I opened my NAV 2013 R2 Developement machine with Visual Studio 2012, opened the solution and removed the ClosedXML_Net3.5 project. Then replaced all references to Document.OpenXML with the pre-installed version 2.5 – yes the version 2.5 is installed with NAV Server.
The new ClosedXML 0.69.1.0 was built and it works with OpenXML 2.5 and I can now do the upgrade from NAV 2013 to NAV 2013 R2.