Extending ExcelBuffer (NAV2013)

Since NAV2013, the ExcelBuffer no longer uses automations to create Excel Sheets. Therefore if you have custom implementation, like color formating, or text size, you need to rewrite your solution with the use of openxml. I had to implement this for our product, so i’d like to present what i came up with.

First, please read the blog post from Lars-Bo Christensen : http://blogs.msdn.com/b/nav/archive/2012/10/05/use-open-xml-to-extend-the-excel-buffer-functionality-part-2-of-2.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+MicrosoftDynamicsNavTeamBlog+%28Microsoft+Dynamics+NAV+Team+Blog%29
It discribes the basic idea and the concept of it.

For my own implementation i used the same concept but pushed it a bit further and made it a bit simpler (in my opinion). My goal was to not rewrite my existing usage code from earlier versions. Therefore every manipulation needed to happen inside the basic functions of ExcelBuffer.

I ended up with these 2 functions:

Globals

Name	DataType	Subtype	Length
CustomFontPool	DotNet	System.Collections.Generic.Dictionary`2.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

GetCustomCellDecorator

GetCustomCellDecorator(IsBold : Boolean;IsItalic : Boolean;IsUnderlined : Boolean;Color : Integer;FontSize : Integer;VAR Decorator : DotNet "Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator")
GetCellDecorator(IsBold, IsItalic, IsUnderlined, BaseDecorator);
Decorator := BaseDecorator;

// Handle Extension
IF (Color <> 0) OR (FontSize <> 0) THEN BEGIN

  FontIndex := STRSUBSTNO('%1|%2|%3|%4|%5',IsBold, IsItalic, IsUnderlined, Color, FontSize);

  CustomFont := BaseDecorator.Font.CloneNode(TRUE);

  // Color
  IF Color <> 0 THEN BEGIN
    CustomColor := CustomColor.Color;
    CASE Color OF
      3 :   CustomColor.Rgb := HexColor.HexBinaryValue('00FF0000'); // Red
      5 :   CustomColor.Rgb := HexColor.HexBinaryValue('001B1BC3'); // Blue
      10 :  CustomColor.Rgb := HexColor.HexBinaryValue('0022B400'); // Green
    END;
    CustomFont.Color := CustomColor;
  END;

  // Font Size
  IF FontSize <> 0 THEN BEGIN
    CustomFontSize := CustomFontSize.FontSize;
    CustomFontSize.Val := FontSizeValue.DoubleValue(FontSize);
    CustomFont.FontSize := CustomFontSize;
  END;

  Fonts := XlWrkBkWriter.Workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Fonts;
  AddFontToCollection(Fonts, CustomFont, FontIndex);

  Decorator.Font := CustomFont;
END;

Locals:

Name	DataType	Subtype	Length
CustomFont	DotNet	DocumentFormat.OpenXml.Spreadsheet.Font.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomFontSize	DotNet	DocumentFormat.OpenXml.Spreadsheet.FontSize.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomColor	DotNet	DocumentFormat.OpenXml.Spreadsheet.Color.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
HexColor	DotNet	DocumentFormat.OpenXml.HexBinaryValue.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
BaseDecorator	DotNet	Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator.'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
Fonts	DotNet	DocumentFormat.OpenXml.Spreadsheet.Fonts.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
FontSizeValue	DotNet	DocumentFormat.OpenXml.DoubleValue.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
FontIndex	Text

AddFontToCollection

AddFontToCollection(_Fonts : DotNet "DocumentFormat.OpenXml.Spreadsheet.Fonts";VAR _CustomFont : DotNet "DocumentFormat.OpenXml.OpenXmlElement";_Index : Text) : Boolean
IF ISNULL(CustomFontPool) THEN
  CustomFontPool := CustomFontPool.Dictionary();

IF CustomFontPool.TryGetValue(_Index, TempFont) THEN BEGIN
  // Already in Collection
  _CustomFont := TempFont;
  EXIT;
END ELSE BEGIN

  // OpenXML Element Array
  Arr := Arr.CreateInstance(GETDOTNETTYPE(_CustomFont),1);
  Arr.SetValue(_CustomFont,0);

  _Fonts.Append(Arr);
  _Fonts.Count.Value := _Fonts.Count.Value + 1;

  CustomFontPool.Add(_Index, _CustomFont);
END;

Locals

Name	DataType	Subtype	Length
i	Integer
Arr	DotNet	System.Array.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
TempFont	DotNet	DocumentFormat.OpenXml.OpenXmlElement.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

The newly created fonts are ached in the CustomFontPool collection to ensure that only necessary fonts are created, otherwise already created fonts are reused.

To use the new Custom Decorator we just replace the two lines where ExcelBuffer refers to the base function GetDefaultDecorator() (which are in WriteCellValue & WriteCellFormula):

//GetCellDecorator(Bold,Italic,Underline,Decorator);
GetCustomCellDecorator(Bold,Italic,Underline,Color,"Font Size",Decorator);

Color & “Font Size” are fields on the ExcelBuffer table which can be set when needed.

As you can see, there is no need to create an assembly in this implementation (unlike the one from Lars), just plain C/AL with .net Interop. Also with this implementation you can continue to use ExcelBuffer as you did it in the past. We just changed the decorator used to visualize the cells.