Dotnet and Excel in NAV2013

chrisdf
chrisdf Member Posts: 82
edited 2014-07-22 in NAV Three Tier
Hi,

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:
xlApp
xlWrkBook
xlWrkSheet
etc.

The typical functionality that I use in the existing code with Automation is:

XlWrkSht.Rows.Range('1:6').RowHeight := 20;
XlWrkSht.Range('B:B').ColumnWidth(17);
XlWrkSht.Range('A2:A3').Merge;
XlWrkSht.Range(PassedCellRef).HorizontalAlignment := PassedHorizAlign;
XlWrkSht.Range(PassedCellRef).VerticalAlignment := PassedVerticalAlign;
XlWrkSht.Range(PassedCellRef).WrapText := PassedWrapText;
etc.

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?

Thanks!

](*,)

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Same problem here.

    I'm not a hero in OpenXML either but it seems that all the tweaks we made in table 370 have to be redone.

    Reasons for the change are performance and the fact that Automation is a relatively old technology.

    On this blog it is explained how to do this with NAV2009.

    http://www.kauffmann.nl/blog/index.php/ ... t-interop/

    Yes, you can go back to Automation, but that will only run on the client side and have poor performance. Likely in the future this feature will be removed eventually.
    Marije Brummel | Freelance Business Central Specialist
  • deV.ch
    deV.ch Member Posts: 543
    I would not recommend using automations. It is very possible to do what you want to do with plain .net interop.
    I would like to help you since i already done an excel import with plain interop, but i don't understand what exactly your problem is.

    Here is an example of my import, let me know if this helps you and where you still have problems:
    Name	DataType	Subtype	Length
xlApp	DotNet	'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass	
xlWorkBook	DotNet	'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Workbook	
xlWorkSheet	DotNet	'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Worksheet	
xlRange	DotNet	'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.IRange	
xlPaltform	DotNet	'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.XlPlatform	


  // Crate Excel interop
  xlApp := xlApp.ApplicationClass();

  // Open Workbook
  xlWorkBook := xlApp.Workbooks.Open(Filename, 0,TRUE,5,'','',TRUE,EnumToInt(xlPlatformFullName, 'xlWindows'),'\t',FALSE,FALSE,0,
                                     TRUE,1,0);

  // Open Worksheet
  xlWorkSheet := xlWorkBook.Worksheets.Item(1);

  // Get Range
  xlRange :=  xlWorkSheet.Range('A1', 'A1');

    Important is the EnumToInt Function, its a function to get the integer value from an enumeration , i explained it on my blog: http://devch.wordpress.com/2013/01/18/net-interop-using-enumeration/
    You can skip that and just pass the integer value of xlWindows but for me this is a lot more readable.
    My Blog
  • kauffmann
    kauffmann Member Posts: 56
    chrisdf wrote:

    ......
    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.
    ......

    I was pointed to this question by Luc van Vugt, because my blog was mentioned here, and he was wondering if I had a solution for the .Net interop error.

    After some investigation I found out a workaround. It is a very strange one that I cannot explain. But it is working:

    Steps to overcome the .Net interop error:

    1. Ignore the errors when creating the .Net variable of type ApplicationClass. (There are two errors, bad id 27 and 29).
    2. Save the object without compiling
    3. Export the object to text
    4. Import the object from the text export
    5. Compile it

    Again: I can't explain this behaviour, it sounds very strange to me, but it definitely works. :-#

    The ApplicationClass is needed because the constructor of this class returns the Application object, which is the Excel application that can be made visible to the user.
    A good programmer makes al the right mistakes
    My blog
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Last week I was in Vedbaek at MDCC and they asked me about my most common enhancements to the Excel Buffer table.

    Here they are.
      Multiple Sheets Font Size Backgroundcolor (preferably HTML coded) Foregroundcolor Hyperlinks

    http://dynamicsuser.net/blogs/mark_brum ... uffer.aspx

    Do you have more?
    Marije Brummel | Freelance Business Central Specialist
  • bbrown
    bbrown Member Posts: 3,268
    A few others I've done with the Excel Buffer table (these were done in classic)

    Wrap Text
    Column Width
    Merge Cells
    Cell Justify
    There are no bugs - only undocumented features.
  • chrisdf
    chrisdf Member Posts: 82
    Hi Mark,

    In addition to what's been mentioned to the excel buffer table:

    Font Size
    Font Name
    Horizontal Align
    Vertical Align

    and have used the following calls for pictures and pivots

    XlWrkSht.Shapes.AddPicture

    XlWrkBk.PivotCaches.Add
    XlWrkSht.PivotTables
    PivotField := PivotTable.PivotFields
    PivotField.Orientation
    PivotField.Function
  • thegunzo
    thegunzo Member Posts: 274
    Hi Mark

    I use the following commands in the ClosedXML. I want the Excel document to be properly setup for printing and viewing.
      XlWrkShtDotNet.Rows('10:10').Height := 30;
  XlWrkShtDotNet.Rows('10:10').Style.Alignment.WrapText := TRUE;
  XlWrkShtDotNet.ColumnWidth := 7;
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Merge;

  XlWrkShtDotNet.SheetView.FreezeRows(10);
  XlWrkShtDotNet.PageSetup.SetPaperSize := GetEnumValue(XlWrkShtDotNet.PageSetup.PaperSize,'A4Paper');
  XlWrkShtDotNet.PageSetup.SetPagesTall := 1 + GlobalRowNo DIV 62;
  XlWrkShtDotNet.PageSetup.SetPagesWide := 1;
  XlWrkShtDotNet.PageSetup.SetPageOrientation := GetEnumValue(XlWrkShtDotNet.PageSetup.PageOrientation,'Portrait');
  XlWrkShtDotNet.PageSetup.SetRowsToRepeatAtTop('$1:$10');
  XlWrkShtDotNet.PageSetup.PrintAreas.Add(1,1,GlobalRowNo,15);
  XlWrkShtDotNet.PageSetup.Footer.Center.AddText(FORMAT(Text038));
  XlWrkShtDotNet.PageSetup.Header.Right.AddText(FORMAT(Text054));
  XlWrkShtDotNet.PageSetup.Margins.Top := 0.4;

    MergeCells(RangeStartRowNo : Integer;RangeStartColNo : Integer;RangeEndRowNo : Integer;RangeEndColNo : Integer)
XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Merge;

MergeAndCenterCells(RangeStartRowNo : Integer;RangeStartColNo : Integer;RangeEndRowNo : Integer;RangeEndColNo : Integer)
MergeCells(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo);
XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Alignment.SetHorizontal := 
  GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Alignment.Horizontal,
               'Center');
XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Alignment.SetVertical := 
  GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Alignment.Vertical,
               'Bottom');
XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Alignment.WrapText := TRUE;

SetFontSize(RowNo : Integer;ColNo : Integer;FontSize : Integer)
XlWrkShtDotNet.Range(RowNo,ColNo,RowNo,ColNo).Style.Font.SetFontSize := FontSize

SetCellsThinBorder(RangeStartRowNo : Integer;RangeStartColNo : Integer;RangeEndRowNo : Integer;RangeEndColNo : Integer;Left : Boolean;T
IF Left THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetLeftBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.LeftBorder,
                 'Thin');
IF Top THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetTopBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.TopBorder,
                 'Thin');
IF Right THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetRightBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.RightBorder,
                 'Thin');
IF Bottom THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetBottomBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.BottomBorder,
                 'Thin');

SetCellsThickBorder(RangeStartRowNo : Integer;RangeStartColNo : Integer;RangeEndRowNo : Integer;RangeEndColNo : Integer;Left : Boolean;
IF Left THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetLeftBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.LeftBorder,
                 'Thick');
IF Top THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetTopBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.TopBorder,
                 'Thick');
IF Right THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetRightBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.RightBorder,
                 'Thick');
IF Bottom THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetBottomBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.BottomBorder,
                 'Thick');

SetCellsDoubleBorder(RangeStartRowNo : Integer;RangeStartColNo : Integer;RangeEndRowNo : Integer;RangeEndColNo : Integer;Left : Boolean
IF Left THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetLeftBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.LeftBorder,
                 'Double');
IF Top THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetTopBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.TopBorder,
                 'Double');
IF Right THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetRightBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.RightBorder,
                 'Double');
IF Bottom THEN
  XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.SetBottomBorder := 
    GetEnumValue(XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Border.BottomBorder,
                 'Double');

SetCellsFillColorByName(RangeStartRowNo : Integer;RangeStartColNo : Integer;RangeEndRowNo : Integer;RangeEndColNo : Integer;ColorName :
XlColor := XlColor.FromName(ColorName);
XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Fill.BackgroundColor := XlColor;

SetCellsFillColorAntiqueWhite(RangeStartRowNo : Integer;RangeStartColNo : Integer;RangeEndRowNo : Integer;RangeEndColNo : Integer)
XlColor := XlColor.AntiqueWhite;
XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Fill.BackgroundColor := XlColor;

SetCellsFillColorYellow(RangeStartRowNo : Integer;RangeStartColNo : Integer;RangeEndRowNo : Integer;RangeEndColNo : Integer)
XlColor := XlColor.Yellow;
XlWrkShtDotNet.Range(RangeStartRowNo,RangeStartColNo,RangeEndRowNo,RangeEndColNo).Style.Fill.BackgroundColor := XlColor;

SetColumnWidth(ColumnNo : Integer;Width : Integer)
XlWrkShtDotNet.Column(ColumnNo).Width := Width;

SetRowHeight(RowNo : Integer;Height : Integer)
XlWrkShtDotNet.Rows(STRSUBSTNO('%1:%1',RowNo)).Height := Height;

SetPageBreak(RowNo : Integer)
XlWrkShtDotNet.PageSetup.AddHorizontalPageBreak(RowNo);

SetTabColorByName(ColorName : Text[30])
XlColor := XlColor.FromName(ColorName);
XlWrkShtDotNet.SetTabColor := XlColor;
    ________________________________
    Gunnar Gestsson
    Microsoft Certified IT Professional
    Dynamics NAV MVP
    http://www.dynamics.is
    http://Objects4NAV.com
  • deV.ch
    deV.ch Member Posts: 543
    Grouping!!

    By the way: I've implemented font & color with pure C/AL & .net Interop, the code can be found in the tips&tricks section:
    http://www.mibuso.com/forum/viewtopic.php?f=5&t=57925
    My Blog
  • csimoneaux0527
    csimoneaux0527 Member Posts: 21
    I Love this topic. I understand all of the code in Navision but I do not understand Kauffmann instructions

    1.Download ClosedXML for .Net Framework 3.5.
    2.Unzip the file and copy ClosedXML.dll to the Add-ins folder of your Classic Client and the Service Tier.
    3.Download DocumentFormat.OpenXml.dll. (Part of the Open XML SDK 2.0 for Microsoft Office, you can download and install the complete SDK as well)
    4.Locate the downloaded (or installed) DocumentFormat.OpenXml.dll and copy it to the Add-ins folder of the Service Tier

    I have done #1 and I have copied it to C:\Program Files (x86)\Microsoft Dynamics NAV\70\RoleTailored Client\Add-ins,
    but I do not have a clue for the rest Would someone please provide a little more instruction.
    I know the Service Tier referres to NavServ, NavSQL and NavClient(listed above)
    How can I find out wher the Add-in folder is on NavSQL and NavServ?

    I really need this addin. It is just what I have been looking for.

    Please help.
  • CGaralde
    CGaralde Member Posts: 19
    Hi,

    I am sorry of I hijacked this thread, but I am also trying to replicate the Excel Buffer using plain dotnet interop.

    [url="http://forum.mibuso.com/discussion/65275/import-to-nav-from-excel-using-interop#latest[/url]

    Looking at DevCh example, I am getting stuck on the XlRange part of the code, getting the error:
    Cannot serialize an instance of the following .NET Framework object: assembly mscorlib, Version=4.0.0.0, Culture=neutral,
    PublicKeyToken=b77a5c561934e089,type System._ComObject.

    Any help is appreciated. :smiley:

Categories