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.
Here is the ClosedXML version 0.69.1.0 rebuilt for Document.OpenXML 2.5
Could you not redo your solution with the NAV OpenXML? Maybe a dumb question but after looking at Table 370 I thought to use this to upgrade my AddOn.
Hi Erik
I am using a lot of formatting that is not supported in NAV OpenXML
ok, didn’t know that. Can you open an existing excel document, add data and close?
sorry, just found it..Nice.
http://closedxml.codeplex.com/wikipage?title=Loading%20and%20Modifying%20Files&referringTitle=Documentation
I am trying to get it working in NAV 2013 R2, but am getting the message – a call to ClosedXML.ExcelXLWorkbook failes – could not load file or assembly. I have it copied to the service add-ins directory. Do you think it is a version problem or how I have defined it. I have version .59 loaded – it gives me an error when I try to reference .69 – could not find type.
Where are you installing it and do you have to do any registration to make it available?
Dave
Hi
In the NAV 2013 R2 DVD undir “Prerequisite Components” you will find “Open XML SDK 2.5 for Microsoft Office”. This is installed with the NAV server. You will most likely need this on your developement machine.
On the server you will only need the attached .69 dll file in the add-ins folder. Make sure that you don’t have any other version in there and make sure you don’t have any version of OpenXML in there.
The error you have is most likely because you are missing the correct version of OpenXML.
I have tried your recommendations, but must still be doing something wrong since my NAV variable only recognizes the old ClosedXML version even though I deleted the old version. The new one gets the “.type” error even though I got rid of the file cam from another computer message.
Any other suggestions?
Since this is currently a show stopper for me, I would be interested in some paid consulting help if you are available.
I am on Skype if you can do it. (US Central time)
This error ?
https://dynamics.is/?p=1672
Yes – I received this error. I read your post when I first got started and followed it to solve my initial installation problem. When I installed the new version of ClosedXML, I went through it again, but I keep getting the error even though it shows it is not blocked. I am still able to connect to the old version even though I renamed the old directory, so I think I am missing a step.
There is also a ClosedXML.xml file and I did the same to it as well.
Am I meant to unregister it to remove it entirely?
I found the other computer settings came back on the xml file, so I kept doing the process until they went away. Now I can define the new version of closedxl, but most of the methods and the structure do not appear in NAV when I point to the new version.
Am I missing a step when I pull in the new version?
OK – figured it out – too little sleep – missed the obvious – after defining the new closedxml version, had to point it to the right assembly.
Do have a question still – why so many constructors with the same name? Does it choose the one to use based on the parameters used?
Yes, that is a normal .net behaviour. You can have many functions with the same name but different parameters (and/or different parameter types).
I can start a new spreadsheet and return it to the user. I am having problem with opening an existing one and saving it. When the code tries to save it, it gets an error that the DotNet variable has not been instantiated in the same place that works for created spreadsheets. What am I meant to do to make sure it persists?
Plus when I get past that, the Excel.Workbooks.Open is requiring 12 parameters – 11 optional. It would not accept commas only, but compiled with empty strings (”). Is this correct?
Hi David. Take a look at this post on MSDN.
http://blogs.msdn.com/b/nav/archive/2012/10/04/excel-buffer-using-openxml-instead-of-excel-automation.aspx
So I would mix OpenXML to read the sheet, then use ClosedXML to update it?
What I am trying to do is read the financial statement spreadsheets exported to Excel previously and overlay them with the new version, with one worksheet tab per statement (running from a batch job).
I don’t care about the contents – just replace the previous contents for the worksheets chosen for the columns used (other columns contain data, formulas, and macros that were entered by the user and stay static).
I am trying to use closedxml so I can use the extended capabilities it offers, including formatting.
Should I just be sticking to OpenXML?
The OpenXML integration for NAV 2013 and NAV 2013 R2 is a good option. If you are using NAV 2009 you need to use ClosedXML only. Both are based on the OpenXML Document standard. For NAV 2009 take a look at this http://www.kauffmann.nl/blog/index.php/2011/09/24/export-to-excel-on-rtc-using-net-interop/