Skip to content

Extending ExcelBuffer (NAV2013)

May 8, 2013

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 :
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:


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


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
    CustomFont.Color := CustomColor;

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

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

  Decorator.Font := CustomFont;


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=, 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(_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;

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

  _Fonts.Count.Value := _Fonts.Count.Value + 1;

  CustomFontPool.Add(_Index, _CustomFont);


Name	DataType	Subtype	Length
i	Integer
Arr	DotNet	System.Array.'mscorlib, Version=, 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):

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.

From → Dynamics NAV

  1. Marcos permalink

    Hi, you know how to apply a Fill to the cell? I’m trying the same thing and it doesnt work

    • Unfortunately not no. I already tried it but all i tried ended in a gray-dotted bakground fill, which seems like a placeholder for a fill that is not found. I guess somehow it can be done, but i don’t have the solution right now. But if you find the solution, feel free to share and I will update the code with it, of course with credits to you.

Trackbacks & Pingbacks

  1. Extending ExcelBuffer (NAV2013) |

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: