NAV 2013 - Modify Excel Buffer to apply border with OpenXML

Damien_DINE
Damien_DINE Member Posts: 2
edited 2014-03-27 in NAV Three Tier
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

Comments

  • asmilie2b
    asmilie2b Member Posts: 122
    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...
    Bruce Anderson
  • matteo_montanari
    matteo_montanari Member Posts: 189
    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
    Reno Sistemi Navision Developer
  • pjllaneras
    pjllaneras Member Posts: 1
    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
    Kevinsanity Member Posts: 8
    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

Categories