NAV 2013 - Modify Excel Buffer to apply border with OpenXML

Damien_DINE

Hello,

I try to use Excel Buffer to create an Excel file with format modifications to apply. More specially, to have cells with borders. After many tries and searches, i have modified the function GetCellDecorator and add this lines at the end :

Border := XlWrkShtWriter.DefaultCellDecorator.Border.CloneNode(TRUE);

BorderColor := BorderColor.Color;
BorderColor.Auto:=BooValue.FromBoolean(TRUE);

Border.LeftBorder.Style:=BorderStyle.Thin;
Border.LeftBorder.Color:=BorderColor;

Decorator.Border:=Border;

The variables created are :

Border DocumentFormat.OpenXml.Spreadsheet.Border
BorderColor DocumentFormat.OpenXml.Spreadsheet.Color
BorderStyle DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues
BooValue DocumentFormat.OpenXml.BooleanValue

But, i have this error raising : DocumentFormat.OpenXML.Spreadsheet.LeftBorder.set_style failed with this message. The type of one or more arguments does not match the method's parameter type.

Does someone know what i do wrong?

Thanks

asmilie2b

I wonder did you ever get this to work?
The only real limitation stopping me from using the now native openxml for excel output is not being able to do borders...

matteo_montanari

asmilie2b wrote:

I wonder did you ever get this to work?
The only real limitation stopping me from using the now native openxml for excel output is not being able to do borders...

Create an excel with OpenXml is a very complex task.
We re-implemented the excel buffer with closedxml (https://closedxml.codeplex.com/) and managing borders, cell merge, etc is more easy.

Matteo

pjllaneras

This is my first comment here... here we go!!!

I’ve found a solution. I was thinking about how Excel Buffer do “underline”, in fact is a BottomBorder… the solution was copying the style of BottomBorder to other border sides:

Local Variable:

Name		DataType	Subtype
CustomBorder	DotNet	DocumentFormat.OpenXml.Spreadsheet.Border

Function that replace Excel Buffer GetCellDecorator:

LOCAL GetCustomCellDecorator(IsBold : Boolean;IsItalic : Boolean;IsUnderlined : Boolean;HasBorder : Boolean;VAR Decorator : DotNet "Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator")

GetCellDecorator(IsBold, IsItalic, (IsUnderlined OR HasBorder), Decorator);

IF HasBorder THEN BEGIN

  CustomBorder := Decorator.Border.CloneNode(TRUE);

  CustomBorder.TopBorder.Style := CustomBorder.BottomBorder.Style;
  CustomBorder.RightBorder.Style := CustomBorder.BottomBorder.Style;
  CustomBorder.LeftBorder.Style := CustomBorder.BottomBorder.Style;

  Decorator.Border := CustomBorder;

END;

I’ve spend a lot of time with this problem… I hope help someone with this.

Kevinsanity

Hi pjllaneras,

Thank you for your solution

However, for me, my XlWrkShtWriter.DefaultUnderlinedCellDecorator created a top, left and right border but no bottom border which I thought was a bit weird

However, the princple was the same as I could just declare a bottom border by using the border objects top border (or left or right), so it all worked out for me in the end

Thanks