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