Export to Excel on RTC using .Net Interop

24 Sep


Exporting to Excel via the Role Tailored Client of Microsoft Dynamics NAV can be very slow when using the standard export functions from Table 370 (Excel Buffer). The reason for this performance issue is explained in this blog post on the Microsoft Dynamics NAV Team Blog.

The recommended solution from Microsoft is to let the Service Tier do the hard work for you, instead of using the Excel Automation objects. Unfortunately, the Service Tier typically does not have Excel installed, so the only way to create an Excel file is to create flat text, comma separated file (.csv). The disadvantage of this purpose is of course the lack of styling possibilities like text styles, borders, etc.

When I read the post from Microsoft, I started to think about another approach. Why not using the possibilities of Office Open XML? Office documents can be created using the Office Open XML format, wich is an open standard.

By the way, did you know that files created by Office 2007 and above, are in fact .zip files? Just rename the .xlsx, .docx or .pptx extension to .zip and open the file with your favorite zip program. You will find several xml documents in a folder structure that together form the complete Office document.

So basically, it must be possible to create an Excel file by just creating a few xml files and putting them together. However, I can tell you for sure that it is far more complex.  Luckily, Microsoft provides the Open XML SDK for Microsoft Office. This .Net based SDK can be used to programmatically create Office Open XML files using an API. And since NAV 2009 supports .Net Interoperability, it must be possible to use this SDK to create an Excel file without the Excel application.

So, the approach of Microsoft to let the Service Tier do the hard work can be extended by using the Open XML SDK. That will combine the best of both worlds. No performance drop with using automation objects on the RTC, while still being able to create rich Excel files instead of flat .csv files.

To make a long story short, the Open XML SDK is still fairly complex to use. You need to know about al parts that an Office document is built of and how they work together, like WorkbookPart, WorksheetPart, StylePart, etc. I found myself creating a complete Excel application, just without user interface. Too much work and too complex to maintain.

Then I came across the project ClosedXML – The easy way to OpenXML on CodePlex. And that was exactly what I was looking for! An easy to use .Net assembly that implements nearly all the Excel features. Request #1 on the project site reads: ‘If you like this project please make an entry about it in your blog’. Well, hereby I do. Great work!

How can we use this with NAV 2009?

The first step is to prepare the development environment and the Service Tier. Otherwise you won’t be able to compile or run the software…

  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. (It is not needed to copy it to the Add-ins folder of the Classic Client)
  5. Download text export of Table 370 Excel Buffer
  6. Merge the changes of Table 370 into your database. Changes are marked with // .Net

Here is are some previews of Table 370 with .Net Interop. I decided to use ISSERVICETIER and create separate DotNet functions to let the standard behavior intact for the Classic Client.

Global Variables

[codesyntax lang=”progress” title=”Global Variables”]

XlWrkBkDotNet@150002024 : DotNet "'ClosedXML, Version=, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLWorkbook";
XlWrkShtDotNet@150002025 : DotNet "'ClosedXML, Version=, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheet";
XlWrkshtsDotNet@150002026 : DotNet "'ClosedXML, Version=, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheet";
XlRangeDotNet@150002027 : DotNet "'ClosedXML, Version=, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLRange";
XLStyleDotNet@150002029 : DotNet "'ClosedXML, Version=, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLStyle";



[codesyntax lang=”progress” title=”CreateBook”]

    PROCEDURE CreateBook@1();
      // .Net >>
        XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();
        XlWrkShtDotNet := XlWrkBkDotNet.Worksheets.Add('Sheet1');
      // .Net <<
        XlWrkBk := XlApp.Workbooks.Add;
        XlWrkSht := XlWrkBk.Worksheets.Add;



[codesyntax lang=”progress” title=”OpenBook”]

    PROCEDURE OpenBook@2(FileName@1000 : Text[250];SheetName@1001 : Text[250]);
      i@1002 : Integer;
      EndOfLoop@1003 : Integer;
      Found@1004 : Boolean;
      // .Net >>
      // .Net <<
    PROCEDURE OpenBookDotNet@150002024(FileName@1000 : Text[250];SheetName@1001 : Text[250]);
      i@1002 : Integer;
      EndOfLoop@1003 : Integer;
      Found@1004 : Boolean;
      FileRTC@150002024 : DotNet "'mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File" RUNONCLIENT;
      PathRTC@150002029 : DotNet "'mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.Path" RUNONCLIENT;
      ThreeTierMgt@150002025 : Codeunit 419;
      NVInStream@150002027 : InStream;
      MemoryStream@150002028 : DotNet "'mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      ClientTempFileName@150002026 : Text[1024];
      // .Net >>
      IF FileName = '' THEN
      IF SheetName = '' THEN
      //Use System.IO.File to check file on RTC
      IF NOT FileRTC.Exists(FileName) THEN
      //Copy file to RTC Temp folder and upload it to server in a stream
      ClientTempFileName := ThreeTierMgt.ClientTempFileName('',PathRTC.GetExtension(FileName));
      FileRTC.Copy(FileName, ClientTempFileName);
      MemoryStream := NVInStream;
      XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook(MemoryStream);
      i := 1;
      EndOfLoop := XlWrkBkDotNet.Worksheets.Count;
      WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN
        XlWrkshtsDotNet := XlWrkBkDotNet.Worksheet(i);
        IF SheetName = XlWrkshtsDotNet.Name THEN
          Found := TRUE;
        i := i + 1;
      IF Found THEN
        XlWrkShtDotNet := XlWrkBkDotNet.Worksheet(SheetName)
      // .Net <<



[codesyntax lang=”progress” title=”CreateSheet” highlight_lines=””]

    PROCEDURE CreateSheet@5(SheetName@1000 : Text[250];ReportHeader@1001 : Text[80];CompanyName@1002 : Text[30];UserID2@1003 : Text[30]);
      XlEdgeBottom@1004 : Integer;
      XlContinuous@1005 : Integer;
      XlLineStyleNone@1006 : Integer;
      XlLandscape@1007 : Integer;
      CRLF@1008 : Char;
      Window@1009 : Dialog;
      RecNo@1010 : Integer;
      InfoRecNo@1012 : Integer;
      TotalRecNo@1011 : Integer;
      // .Net >>
      // .Net <<
    PROCEDURE CreateSheetDotNet@150002030(SheetName@1000 : Text[250];ReportHeader@1001 : Text[80];CompanyName@1002 : Text[30];UserID2@1003 : Text[30]);
      CRLF@1008 : Char;
      Window@1009 : Dialog;
      RecNo@1010 : Integer;
      InfoRecNo@1012 : Integer;
      TotalRecNo@1011 : Integer;
      // .Net >>
        Text005 +
      CRLF := 10;
      RecNo := 1;
      TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;
      RecNo := 0;
      XlWrkShtDotNet.Name := SheetName;
      IF ReportHeader <> '' THEN
      XlWrkShtDotNet.PageSetup.PageOrientation := GetEnumValue(XlWrkShtDotNet.PageSetup.PageOrientation, 'Landscape');
          RecNo := RecNo + 1;
          Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
          IF NumberFormat <> '' THEN
            XlWrkShtDotNet.Cell("Row No.","Column No.").Style.NumberFormat.SetFormat(NumberFormat);
          IF Formula = '' THEN
            XlWrkShtDotNet.Cell("Row No.","Column No.").Value := "Cell Value as Text"
            XlWrkShtDotNet.Cell("Row No.","Column No.").FormulaA1 := GetFormula;
          //Comments are not yet supported by ClosedXML
          //IF Comment <> '' THEN
          //  XlWrkShtDotNet.Cell("Row No.","Column No.").Comment := Comment;
          XLStyleDotNet := XlWrkShtDotNet.Cell("Row No.","Column No.").Style;
          IF Bold THEN
          IF Italic THEN
          XLStyleDotNet.Border.OutsideBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'None');
          IF Underline THEN
            XLStyleDotNet.Border.BottomBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'Medium');
        UNTIL NEXT = 0;
        XlWrkShtDotNet.Columns(1, "Column No.").AdjustToContents();
      IF UseInfoSheed THEN BEGIN
        IF InfoExcelBuf.FIND('-') THEN BEGIN
          XlWrkShtDotNet := XlWrkBkDotNet.Worksheets.Add('Information');
          XlWrkShtDotNet.Name := Text023;
            InfoRecNo := InfoRecNo + 1;
            Window.UPDATE(1,ROUND((RecNo + InfoRecNo) / TotalRecNo * 10000,1));
            IF InfoExcelBuf.NumberFormat <> '' THEN
              XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Style.NumberFormat.SetFormat
            IF InfoExcelBuf.Formula = '' THEN
              XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Value := InfoExcelBuf."Cell Value as Text"
              XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").FormulaA1 := InfoExcelBuf.GetFormula;
            //Comments are not yet supported by ClosedXML
            //IF InfoExcelBuf.Comment <> '' THEN
            //  XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Comment := InfoExcelBuf.Comment;
            XLStyleDotNet := XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Style;
            IF InfoExcelBuf.Bold THEN
            IF InfoExcelBuf.Italic THEN
            XLStyleDotNet.Border.OutsideBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'None');
            IF InfoExcelBuf.Underline THEN
              XLStyleDotNet.Border.BottomBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'Medium');
          UNTIL InfoExcelBuf.NEXT = 0;
          XlWrkShtDotNet.Columns(1, InfoExcelBuf."Column No.").AdjustToContents();
      // .Net <<



[codesyntax lang=”progress” title=”GiveUserControl”]

    PROCEDURE GiveUserControl@3();
      TempFile@1000 : File;
      FileName@1001 : Text[1024];
      ToFile@1002 : Text[1024];
      // .Net >>
      // .Net <<
    PROCEDURE GiveUserControlDotNet@150002040();
      MemoryStream@150002024 : DotNet "'mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      ExcelApp@150002031 : DotNet "'Microsoft.Office.Interop.Excel, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Application" RUNONCLIENT;
      ExcelAppClass@150002030 : DotNet "'Microsoft.Office.Interop.Excel, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
      NVInStream@150002025 : InStream;
      ThreeTierMgt@150002027 : Codeunit 419;
      FileNameRTC@150002028 : Text[1024];
      // .Net >>
      MemoryStream := MemoryStream.MemoryStream();
      NVInStream := MemoryStream;
      ExcelApp := ExcelAppClass.ApplicationClass();
      // .Net <<


A few remarks:

  • Because the code is executed on the Service Tier, a created Excel file must be downloaded to the RTC before it can be opened by the local Excel application. This is done in the function GiveUserControlDotNet. The downloaded file is created with a temporary name. The Workbooks.Add function opens this file as a template. This forces Excel to ask for a new filename when the user saves the file. This is very close to the normal behavior of the Classic Client.
  • The opposite counts for files that are imported. They must be uploaded to the Service Tier before they can be opened. See the OpenBookDotNet function for an example.
  • When the Regional Settings of Windows does not match the installed language of Office, the Workbooks.Add function will throw an error. A solution for this is posted here.
  • Adding comments to specific cells is not yet support with ClosedXML. I’m pretty sure it will be available at short notice.


And then the question: is it faster? And the answer is: yes, it is very fast! It even outperforms the Classic Client! I did a quick compare between Classic and RTC with an export of 1500 rows and 10 columns:

ClientElapsed Time
Classic (Automation)27 sec
RTC (Automation)300 sec
RTC (.Net Interop)14 sec


40 thoughts on “Export to Excel on RTC using .Net Interop

  1. Pingback: Start Excel from RTC « Microsoft Dynamics NAV Thoughts

  2. ..it works perfect, thanks very much. But you should not forget to restart the service of the RTC before you try it!

  3. I have a codeunit that I use to create Excel-sheets, which works with Automation. Based on your Table370-example I have re-coded it to use ClosedXMl as well, using a variable that decides whether it should use the Automation- or DotNet-variant. I have managed to re-write all functions (like, Formatting, Grouping, almost anything that you could need in Excel, the ClosedML-page is a good summary of functions that my codeunit supports) except that formula’s do not work.

    The codeunit has a function “WriteFormula”, that is called with a a formula (the codeunit already knows which cell you are in), like


    The function WriteFormula then looks like:

    xlRangeDotNet := xlWrkshtDotNet.Cell(GetCurrentRowNo,GetCurrentColumnNo);
    xlRangeDotNet.FormulaA1 := UseValue;

    In fact, there is no difference with writing:

    xlRangeDotNet := xlWrkshtDotNet.Cell(GetCurrentRowNo,GetCurrentColumnNo);
    xlRangeDotNet.Value := UseValue;

    Have you got any suggestions what I should change to make this work? Thanks for your input.

    Theunis Modderman

  4. Hi Theunis,

    Unfortunately I cannot reproduce this scenario. I have tried to write a formula instead of a cell value, using Table 370 and it just works fine.

    Your code example is probably too short to detect the problem. I would be happy to help you out, but then I need some more code. You can drop me an email if you want (ajkauffmann (at) xperit.nl).


  5. Hi AJK,
    Thank you for this Post.
    It works fine in the machines using 32 bit Office, but I am having a problem with the machines using Office 64bit.
    I am getting an error that “Call to member Workbooks failed”. The error is occuring in the code

    Any idea what it could be?


  6. Pingback: Using OpenXML to create an Excel Document | Gunnar's Blog

  7. Hi AJK,
    first, i wan’t to thanks you a lot for this guide!
    Actually i’m having a problem when updating an existing Excel. It always brings up:
    “The call to member SaveAs failed: Object reference not set to an instance of an object.” in the function GiveUserControlDotNet. I Can’t find out why?! It works fine, if i created a new document. Could you please check that? I also tried different versions of closedXML, but it doesn’t work.

    Best regards

    • Hi Martin, i also had this message error. Did you manage to solve it? Thanks.

      Best regards,

      • Great tool and with the help from this site very easy to implement, only… i run into the same message error with SaveAs. If you have found the solution can tyou please share it?

        Best Regards Adalbert

  8. Hello
    I have the following problem: Number of characters needs to determine in an Excel cell. Under Classic Line I have the following code:

    XlSheet.Range (lrecMappZeile.Zelle + FORMAT (j)) Columns.AutoFit.;
    ltxtFormel: = ‘= LÄNGE (‘ + + lrecMappZeile.Zelle FORMAT (j) + ‘)’;
    XlSheet.Range (‘IV’ + FORMAT (j)) Formula: = ltxtFormel;.
    ltxtLänge: = FORMAT (XlSheet.Range (‘IV’ + FORMAT (j)) value.)
    EVALUATE (lintLaenge, ltxtLänge);

    In the variable ltxtLänge I get the desired value. OpenXML with this code:

    XlWrkShtDotNet.Columns (j, i) AdjustToContents ().;
    ltxtFormel: = ‘= LÄNGE (‘ + + lrecMappZeile.Zelle FORMAT (j) + ‘)’;
    XlWrkShtDotNet.Cell (j, i) FormulaA1 = ltxtFormel,.
    ltxtLänge: = FORMAT (XlWrkShtDotNet.Cell (j, i) value.)
    EVALUATE (lintLaenge, ltxtLänge);

    I get in the back ltxtLänge simple formula. Apparently this the value of the basic cell is not moved. Can you give me a hint how I should work with formulas in OpenXML SDK?

    I thank you for your help.

    M. Kostek

    • Hi,

      First at all you should realize that the concept of using OpenXML is different from using Excel. The Excel application really evaluates and calculates formulas on the fly, while the OpenXML SDK does not.
      See this explanation (the paragraph Application Integration): http://cdonner.com/introduction-to-microsofts-open-xml-format-sdk-20-with-a-focus-on-excel-documents.htm

      However, there are some posibilities according to this information:

      My guess is that, if the code op ClosedXML is capable of evaluating your formula, you should at least use the English version, not the German formulatext.


      • Hi

        We looked internally the VB code of the examples from the ClosedXML templates, but we have not seen, with which we can command in CA / L abut running the formula.
        The key point in DotNet:

        XLWorkbook var wb = new ();
        var ws = wb.AddWorksheet (“Sheet1”);
        ws.Cell (“A1”) SetValue (1) CellBelow () SetValue (1)…;
        ws.Cell (“B1”) SetValue (1) CellBelow () SetValue (1)…;

        ws.Cell (“C1”) FormulaA1 = “\” The total value is: \ “& SUM (A1: B2)”;.

        var r = ws.Cell(“C1”).Value; !!!!
        Assert.AreEqual (“The total value is: 4”, r.ToString ());

        We definitely know not, what should we replace the command-line DotNet.
        We have exactly the same problem, what Theunis has described a year ago.

        Thanks for your help.

  9. Maybe I’m missing something?

    I’ve merged the code above into Table 370, but it won’t compile, complaining that “GetEnumValue” does not exist.

    Where is the function defined, I cannot see it anywhere?

    • OK, found it, it was in Table 370 that was attached to this blog (I was merging with a already customised version).

  10. Different issue now – I just get “the call to ClosedXML failed – file contains corrupted data”. What does this mean?

    • Saved as an XLSX and it works, and then fails with “the automation variable has not been instantiated”.

      All it has to do is work!

  11. Hi i’m having a problem when updating an existing Excel. The error message –
    “The call to member SaveAs failed: Object reference not set to an instance of an object.” in the function GiveUserControlDotNet. I really don’t know how to fix this. Any ideas? Thanks.

    Best regards,

  12. Hi i’m having a problem when updating an existing Excel. The error message –
    “The call to member SaveAs failed: Object reference not set to an instance of an object.” in the function GiveUserControlDotNet. I really don’t know how to fix this. Any ideas? Thanks.

    Best regards,

  13. Hi,

    This looks ideal – have you got a modified version of table 370 for NAV2013? The one on this blog is V6 and am sure someone must have done it for V7??

    Grateful for any help.


  14. Hi, is it possible, that with NAV2009 R2 this is not a problem, when exporting data ?
    I only problems with the RTC Import from Excel, it takes really long. I tried your example, but on the import side, nothing changed. the export is as fast as before. …

  15. Hi,

    I tried your solution and its really fast when create a new Excel document.
    Have you tried to update a document ? When I try to update a existing Excel document, I have a message saying that i can’t update a closed file.

    Can you help me please.

    Thanks to you

  16. Hi,
    We are trying to speed up a Excel “pricelist” application using ClosedXML.
    On opening the workbook from a template file we get the following error :

    The call to member ClosedXML.Excel.XLWorkbook failed: Sequence contains no matching element.

    The only suggestions I was able to find involves “Culture” settings other then en-US. (ClosedXML can only handle en-US formatted Excel templates???).
    We would like to try that suggestion, but that brings the next question : how can you set culture properties in Excel

    Any suggestions on how to solve this would be welcome..


  17. Hello,

    I’m trying to use the code above, but when I call the function ‘OpenBookDotNet’ I get this error: ‘The message is for ‘C/AL programmers: Cannot create an instance of the following .NET Framework object: assembly ClosedXML, Version=, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b, type ClosedXml.Excel.XLWorkbook.

    When I use the debugger I get the error at this line: XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook(MemoryStream);

    What am I doing wrong?


  18. Pingback: NAV2013 | Excel Buffer | Brummel Dynamics Services B.V.

  19. Hello, I was wondering if there is any need to implement the code above for NAV 2013? We have a client who is using the native NAV export to excel Acct. Schedule functionality, but we have a customization to create multiple tabs on the Excel file based on different dimension filter data per tab. When I manually run the native NAV Acct. Schedule export for each set of dim filters to test individual tabs vs running one export for all tabs, the time adds up.

    I was not sure if your fix above would make the export to Excel run faster based on the NAV 2013? I am asking because the Excel Buffer and fnality for exporting is different in 2009 vs 2013 and is not just a quick copy and paste procedure as it is for 2009.

    Please let me know your thoughts.

    Thanks in advance,

  20. Have the same issue as Bram & Ken but ONLY for users with permissions. SUPERs are OK. I even created a users with all OBJECTS and same thing, it needs TableData 0 ALL.
    Any Solutions?

    ‘The message is for ‘C/AL programmers: Cannot create an instance of the following .NET Framework object: assembly ClosedXML, Version=, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b, type ClosedXml.Excel.XLWorkbook.

  21. I understand the programming from within Navision.
    Can someone please explain the install to Add-ins folder of your Classic Client and the Service Tier. How do I find these?

  22. I use to create Excel Workbooks in Microsoft NAV 2013R2. In my development environment everything works without problems. When I try to execute the function in the customer environment I get the following security error message:

    Microsoft Dynamics NAV

    A call to ClosedXML.Excel.XLWorkbook failed with this message: Request for the permission of type ‘System.Security.Permissions.EnvironmentPermission, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.

    Any ideas how to solve the problem?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.