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.