Extending ExcelBuffer in NAV2013 (Without external Assembly)

deV.ch · 2013-05-23

I'd like to share my solution for extending the excel buffer in NAV2013, since this was a common thing we've done in previous versions and now the whole ExcelBuffer handling completly changed!

First of all, i already covered the topic in a blogpost here: http://devch.wordpress.com/2013/05/08/extending-excelbuffer-nav2013/

But i'd like to share it here, so that more people can profit.

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.

radek.bb · 2013-07-17

Hi,

I followed your example - and it is working great

I have added changing font to it - and it is still working as expected.

IF "Font Name" <> '' THEN BEGIN
  //Replacing font should be the first alteration
  CustomFont := CustomFont.Font;           // (*) Initialisation of variable (default constructor)
  CustomFontName := CustomFontName.FontName;    // (*)
  CustomFontName.Val := XMLStringValue.StringValue("Font Name");
  CustomFont.FontName := CustomFontName;
  ApplyFontChange := TRUE;
END;

Then I wanted to add Fill Color for each cell.
Setting the fill colour was similar to other alterations - but I hit the wall when trying to set FillType to 'Solid':

IF "Fill Color" <> -1 THEN BEGIN
  CustomCellFill := CustomCellFill.Fill; // (*)
  CustomCellPatternFill := CustomCellPatternFill.PatternFill;  // (*)
  // ForegroundColour colour is used when fill type is 'Solid'
  CustomForegroundColour := CustomForegroundColour.ForegroundColor;  // (*)
  CustomForegroundColour.Rgb := HexColorValue.HexBinaryValue(IntegerToHexText("Fill Color"));
  CustomCellPatternFill.ForegroundColor := CustomForegroundColour;
  
  //  Setting the type of pattern fill - <<<< PROBLEM >>>>
  {1111}CustomCellPatternFill.PatternType := PatternListValue.EnumValue;
  {2222}CustomCellPatternFill.PatternType.Value := CustomPatternType.Solid;
    
  CustomCellFill.PatternFill := CustomCellPatternFill;
  Decorator.Fill := CustomCellFill;
END;

I think I know how this supposed to look like... but I just cannot figure out how I supposed to use / create PatternType object.

Starting from the top:

Decorator
---> Fill ...............................................(DocumentFormat.OpenXml.Spreadsheet.Fill )

> PatternFill.............................(DocumentFormat.OpenXml.Spreadsheet.PatternFill)

> ForegroundColour.......(DocumentFormat.OpenXml.Spreadsheet.ForegroundColor)

> PatternType................(DocumentFormat.OpenXml.EnumValue(Of PatternValues))

If I comment out line {1111} I receive an error:

A DotNet variable has not been instantiated.

If I leave it there is another:

Cannot create an instance of the following .NET Framework object: assembly DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, type DocumentFormat.OpenXml.EnumValue`1

Looks like constructor does not work... or I don't know how to use it here.

PatternType property supposed to be filled with object of type: DocumentFormat.OpenXml.EnumValue(Of PatternValues) where PatternValues is enumeration of values.

In My example variable CustomPatternType is DocumentFormat.OpenXml.Spreadsheet.PatternValues and I can see all possible values - including 'Solid' which I want to use.

Variable PatternListValue is DocumentFormat.OpenXml.EnumValue`1 which supposed to match a type of PatternType property which I am trying to set.
](*,) ](*,) ](*,)
Any suggestions?... I probably miss some silly notation which will make everything work as expected.... :oops:

bruno77 · 2013-09-10

Having the exact same issue, can't set the pattern to solid. Did you ever figure this out?

radek.bb · 2013-09-10

Well... I managed to get some results by using another constructor for this variable - the one with (String outerXml) parameter...
But it didn't work in 100%... in fact it works very strange...
First cell having a fill - is always incorrect... and any next if the fill type stays the same.
But after changing fill type each next cell is working correctly.
I ended up with setting some crazy fill type for first cell - where I do not care how it looks like and then - for any other cells fill is correct.
A bit crazy... I know

CustomCellFill := Decorator.Fill.CloneNode(TRUE);
CustomCellPatternFill := CustomCellPatternFill.PatternFill(
                            '<x:patternFill xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '+
                            'patternType="'+FORMAT("Fill Type")+'">' +
                            '<x:fgColor rgb="'+IntegerToHexText("Fill Color")+'" /></x:patternFill>');
CustomCellFill.PatternFill := CustomCellPatternFill;
Decorator.Fill := CustomCellFill;

makina · 2014-04-22

Hi guys!!!
I'm trying to make this work on nav 2013 but I'm facing an error stating that "... : A dotnet variable has not been instantiated. Attempting to call Microsoft.dynamics.nav.openxmlspreadsheet.worksheetwriter.defaultcelldecorator in table excel buffer: GetCellDecorator".

After some debugging I have detected that the error raises in the call to the function GetCellDecorator from the new function GetCustomCellDecorator.

Any idea of what may be happening? I have went over every step but I can't find the bug.
Thanks in advance.

Maria-S · 2014-05-26

Hi!

Any hints how to implement vertical/horizontal align?

Miklos_Hollender · 2014-09-03

Can this be used to create multiple sheets inside one Excel file?

Louis · 2014-09-24

Hi Folks it's a long time ago.
](*,) As Makina (Apr 22, 2014), I'm trying to make this work on nav 2013 but I'm facing an error stating that "... : A dotnet variable has not been instantiated. Attempting to call Microsoft.dynamics.nav.openxmlspreadsheet.worksheetwriter.defaultcelldecorator in table excel buffer: GetCellDecorator".

[-o< Can someone who has implemented this post explain what happens with the function GetCellDecorator called from the new function GetCustomCellDecorator ?
:idea: Where and how should it be instantiated ?

:thumbsup: I hope that someone can post a fob with just the new working T00370 functions ?

Kind regards,
L

:?: ps : is it still the same in NAV2015 ?

stevedivimast · 2015-01-28

I got the same error: A dotnet variable has not been instantiated. Attempting to call Microsoft.dynamics.nav.openxmlspreadsheet.worksheetwriter.defaultcelldecorator in table excel buffer: GetCellDecorator".
I fixed it defining functions GetCustomCellDecorator and AddFontToCollection as local (NAV2013R2).
Now everything is working fine.