In one of my projects I needed to create a complex Excel document from Dynamics NAV.  I did this by using COM automation in the same way that Microsoft is doing in the Excel Buffer table no. 370.  The problem is that this is dead slow.  It could take up to two minutes to create a single Excel document on the Role Tailored Client.

I saw in Kauffmann’s blog that it is possible to use OpenXML on the server side to create the Excel document.  Great blog and a big help.  The same Excel document is now created in under two seconds.

As I started the batch to create the OpenXML Excel documents I received an error: “hexadecimal value 0x1F, is an invalid character”.  I was inserting an invalid character into the XML.  I did a quick search and found a solution, I needed to filter the data through a white list.  Here is my AddCell function:

 

The line

 

is used to clean the string that is passed to the XML.

The GetEnumValue function is

 

When constructing the OpenXML dotnet object I also construct the white characther check object.

 

Attached is the Add-in needed on the server side.

XMLCharWhiteList Add-in

 

 

4 thoughts on “Using OpenXML to create an Excel Document

  1. Dave Machanick says:

    There is a method missing in .69.1 that is in .59
    Do you know if there is a fix or workaround for it?
    XLValidationDotNet := XLNamedRangeDotNet.Ranges.DataValidation;
    I looked but could not find an equivalent.

    BTW – I did find a way to fix my other problem – I had to pull all my code together in one routine, otherwise it had problems referencing the XlWrkBkDotNet .net variable. Seems to lose track of it.

    1. F&T says:

      Is there a solution for this missing method?
      I get a compile error on this line too…

      XLValidationDotNet := XLNamedRangeDotNet.Ranges.DataValidation;

  2. Cheryl Simoneaux says:

    I tried this and I can not get it to work when I run it I get the following error message
    This message is for C/AL programmers: A DotNet variable has not been instantiated. Attempting to call ClosedXML.Excel.IXL.Worksheet.Cell in the Excel Buffer_CloseXML:AddCell
    What am I missing? I do not understand.I was told I needed a Constructor. I have tried several and failed.
    Can you help? PLEASE

  3. Send me the details in email please.

Leave a Reply

%d bloggers like this: