Using OpenXML to create an Excel Document

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:

LOCAL PROCEDURE AddCell@16(CurrentRow@1200050001 : Integer;CurrentCol@1200050000 : Integer;Value@1000 : Variant;IsFormula@1001 : Boolean;CommentText@1002 : BigText;IsBold@1003 : Boolean;IsItalics@1004 : Boolean;IsUnderline@1005 : Boolean;NumFormat@1006 : Text[30];Attention@1200050002 : Boolean;Alignment@1200050006 : 'General,Left,Right,Center');
VAR
XlColor@1200050008 : DotNet "'ClosedXML, Version=0.64.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLColor";
CellValueAsText@1200050007 : Text[1024];
CommentLine@1200050003 : Text[1024];
CommentPosition@1200050004 : Integer;
CommentLength@1200050005 : Integer;
BEGIN
IF CurrentRow = 0 THEN
GlobalRowNo := GlobalRowNo + 1
ELSE
GlobalRowNo := CurrentRow;
IF CurrentCol = 0 THEN
GlobalColumnNo := GlobalColumnNo + 1
ELSE
GlobalColumnNo := CurrentCol;

//Comments are not yet supported by ClosedXML
//CommentLength := CommentText.LENGTH;
//IF CommentLength > 0 THEN BEGIN
// CommentPosition := 1;
// WHILE CommentPosition < CommentLength DO BEGIN
// CommentPosition := CommentPosition + CommentText.GETSUBTEXT(CommentLine,CommentPosition,MAXSTRLEN(CommentLine));
// XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Comment.AddText := CommentLine;
// END;
//END;

XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Bold := IsBold;
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Italic := IsItalics;
IF IsUnderline THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.SetBottomBorder :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.BottomBorder,'Continuous');

XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.NumberFormat.SetFormat := NumFormat;

IF Attention THEN BEGIN
XlColor := XlColor.FromName('Red');
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.SetFontColor := XlColor;
END;

IF IsFormula THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).FormulaA1 := Value
ELSE
CASE TRUE OF
Value.ISTEXT,Value.ISCHAR,Value.ISCODE :
BEGIN
CellValueAsText := FORMAT(Value);
CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := CellValueAsText;
END;
ELSE IF FORMAT(Value) <> '0' THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := Value;
END;

CASE Alignment OF
Alignment::General:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'General');
Alignment::Center:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Center');
Alignment::Right:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Right');
Alignment::Left:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Left');
END;
END;

 

The line

CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);

 

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

The GetEnumValue function is

LOCAL PROCEDURE GetEnumValue@150002034(Enum@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Enum";Value@150002026 : Text[30]) ReturnValue : Integer;
VAR
Convert@150002025 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
BEGIN
ReturnValue := Convert.ToInt32(Enum.Parse(Enum.GetType(),Value));
END;

 

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

XMLWhiteCharCheck := XMLWhiteCharCheck.XMLCharWhiteList;
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();

 

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

XMLCharWhiteList Add-in

 

 

VB.NET NAV Application Server

Most of my clients require a running NAV Application Server.  The NAS that is included in NAV 2009 R2 requires a license that is included in most licenses today.  However, there are cases where more than one NAS is needed.  That requires additional NAS licenses.  Where the customer is running NAV 2009 R2 middle tier service this changes.  By running a VB.NET NAV Application Server it is possible to setup multiple services on a single CAL license.  The CAL license is not as expensive as the NAS license.  Here is the solution that I offer.

First, I create a codeunit in NAV

[code htmlscript=”false”]ExecuteCodeunit(CodeunitID : Integer;Log : Boolean) Success : Boolean

IF Log THEN LogEntryNo := InsertLogEntry(5,CodeunitID);
Success := CODEUNIT.RUN(CodeunitID);
IF Log THEN
UpdateLogEntry(LogEntryNo,Success)
ELSE IF NOT Success THEN BEGIN
LogEntryNo := InsertLogEntry(5,CodeunitID);
UpdateLogEntry(LogEntryNo,Success)
END;

InsertLogEntry(ObjectType : ‘,,,Report,,Codeunit’;ObjectNo : Integer) : Integer
WITH JobQueueLogEntry DO BEGIN
INIT;
ID := CREATEGUID;
“User ID” := USERID;
“Start Date/Time” := CURRENTDATETIME;
“Object Type to Run” := ObjectType;
“Object ID to Run” := ObjectNo;
INSERT(TRUE);
COMMIT;
EXIT(“Entry No.”);
END;

UpdateLogEntry(LogEntryNo : Integer;WasSuccess : Boolean)
WITH JobQueueLogEntry DO BEGIN
GET(LogEntryNo);
“End Date/Time” := CURRENTDATETIME;
IF WasSuccess THEN
Status := Status::Success
ELSE BEGIN
Status := Status::Error;
SetErrorMessage(COPYSTR(GETLASTERRORTEXT,1,1000));
END;
MODIFY;
COMMIT;
END;[/code]

This codeunit uses the Job Queue Log to log the execution.  Next step is to publish this codeunit as a web service in table no. 2000000076.  Default web service name is NAVAppServer.

On the server you install the following files (in my case to C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server)


Next step is to edit the VB.NET NAV Application Server.exe.config file and customize the values.

 <applicationSettings>
        <NAV_Application_Server.My.MySettings>
            <setting name="NAVAppServer" serializeAs="String">
                <value>http://<Middle Tiere Host Name>:7047/DynamicsNAV/WS/<CompanyName>/Codeunit/NAVAppServer</value>
            </setting>
            <setting name="CodeunitID" serializeAs="String">
                <value>81004</value>
            </setting>
            <setting name="LogMode" serializeAs="String">
                <value>False</value>
            </setting>
            <setting name="TimerInterval" serializeAs="String">
                <value>90000</value>
            </setting>
            <setting name="LogFrequency" serializeAs="String">
                <value>15:00:00</value>
            </setting>
            <setting name="SMTPHost" serializeAs="String">
                <value>mail.dynamics.is</value>
            </setting>
            <setting name="FromAddress" serializeAs="String">
                <value>gunnar@dynamics.is</value>
            </setting>
            <setting name="ToAddress" serializeAs="String">
                <value>gunnar@dynamics.is</value>
            </setting>
            <setting name="RetryTimerInterval" serializeAs="String">
                <value>45000</value>
            </setting>
        </NAV_Application_Server.My.MySettings>
    </applicationSettings>

To install as a service start command prompt in elevated mode and execute installutil.exe command.  The install will prompt for the user to start the service.

C:\Windows\Microsoft.NET\Framework\v2.0.50727>InstallUtil.exe "C:\Program Files(x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe"

Microsoft (R) .NET Framework Installation utility Version 2.0.50727.5420
Copyright (c) Microsoft Corporation.  All rights reserved.

Running a transacted installation.

Beginning the Install phase of the installation.
See the contents of the log file for the C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe assembly's progress.

The file is located at C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog.
Installing assembly 'C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe'.
Affected parameters are:
   logtoconsole =
   assemblypath = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe
   logfile = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog
Installing service VB.NET-NAVAppServer$DEFAULT...
Service VB.NET-NAVAppServer$DEFAULT has been successfully installed.
Creating EventLog source VB.NET-NAVAppServer$DEFAULT in log Application...

The Install phase completed successfully, and the Commit phase is beginning.
See the contents of the log file for the C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe assembly's progress.

The file is located at C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog.
Committing assembly 'C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe'.
Affected parameters are:
   logtoconsole =
   assemblypath = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe
   logfile = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog

The Commit phase completed successfully.

The transacted install has completed.

The last step is to change the service startup and start the service.

The service will create entries in the Application Log.

NAV Web Service Codeunit

The following ZIP files are encrypted.

VB.NET NAV Application Server DEFAULT Executables

NAV App Server Visual Studio 2008 Project