I want to upgrade certain reports that were written to drive Excel output. These reports used the Excel Automation functionality in the 2009 classic client as we needed more control over the Excel output than could be achieved using the Excel buffer table e.g. merging columns, setting column widths.
With NAV2013, it seems the Automation works the same with a small change to the CREATE function. However, all the press indicates it is a lot faster to use the dotnet functionality. I have been trying without success to get this to work and wondered if there is any help out there on this subject.
The typical Automation variables that I am used to setting up are:
The typical functionality that I use in the existing code with Automation is:
XlWrkSht.Rows.Range('1:6').RowHeight := 20;
XlWrkSht.Range(PassedCellRef).HorizontalAlignment := PassedHorizAlign;
XlWrkSht.Range(PassedCellRef).VerticalAlignment := PassedVerticalAlign;
XlWrkSht.Range(PassedCellRef).WrapText := PassedWrapText;
The equivalent dotnet functionality seems to be there in the Microsoft.Office.Interop.Excel assemblies but I cannot seem to get them to work.
The first issue is that I am getting error messages when trying to create an "Application" variable - the message says:
".NET interop: internal error, bad id '27 : 1'."
This happens when creating a dotnet variable of subtype "Microsoft.Office.Interop.Excel.Application" or "Microsoft.Office.Interop.Excel.ApplicationClass" in Office 2013.
If you look in table 370 this Application variable exists but if I try and copy and paste it (even into the same table) I get the error message. In Office 2010 it errors on Application and in Office 2013 it errors on Applicationclass.
If I experiment with code in table 370 (trying to use the dotnet equivalents of xlWrkBk and xlWrksht) I invariably end up with a message telling me that the dotnet variable has not been instantiated.
I can see that in table 370 there is a line of code to instantiate the Application variable in the PreOpenExcel function
XlApp := XlApp.ApplicationClass;
This uses a configurator but in Office 2013, there is no configurator for Application but there is for Applicationclass so I assume it is used differently there.
If I try the above and then try and work with the workbook and worksheet variables, I get stuck on the instantiation message.
The new excel buffer table (370) seems to have been re-written with some NAV specific dotnet functions (Microsoft.Dynamics.Nav.OpenXml.Spreadsheet) . This leads me to believe that the developers have add to build specific assemblies and that the old functionality we used to have with Automation may not be so readily available??
I am not sure if we are supposed to use the OpenXML methodology now to do what we used to with the Automation method?
So, in summary, can anyone point me in the right direction as the best way forward to convert these reports using dotnet variables or should I just continue to use the Automation?