Skip to main content

Notifications

Microsoft Dynamics NAV forum
Answered

Can I extend the Excel Buffer table to set fonts and colours while retaining the Bold/Underline/Italics?

Posted on by Microsoft Employee

I am using the MS Dynamics Navision 2013 R2 Excel Buffer table to generate an Excel spreadsheet and would like to extend it to allow for the setting of font name, font size, font colour and fill colour.

I have managed to set these additional properties using the solution given by Saurav Dhyani in his NAV 2013 R2 - Export To Excel With Font & Color blog post but that solution doesn't retain the setting of the Bold, Italics and Underline properties when the font is changed. Comments on his post suggest that this is something that hasn't yet been resolved.

My implementation of Saurav's solution involves adding the fields "Font Name" (Text 100), "Font Size" (Integer), "Font Colour" (Integer) and "Background Colour" (Integer) to the Excel Buffer table (Table 370). These fields can be set by the calling module. All calls to the GetCellDecorator function in the Excel Buffer table are then replaced by calls to a new GetCellDecoratorWithFont function instead. This function takes the same parameters as the existing GetCellDecorator function plus a parameter for each of the new table fields.

The GetCellDecoratorWithFont function looks like this:

IF IsBold AND IsItalic AND IsUnderlined THEN
  Decorator := XlWrkShtWriter.DefaultBoldItalicUnderlinedCellDecorator
ELSE IF IsBold AND IsItalic THEN
  Decorator := XlWrkShtWriter.DefaultBoldItalicCellDecorator
ELSE IF IsBold AND IsUnderlined THEN
  Decorator := XlWrkShtWriter.DefaultBoldUnderlinedCellDecorator
ELSE IF IsBold THEN
  Decorator := XlWrkShtWriter.DefaultBoldCellDecorator
ELSE IF IsItalic AND IsUnderlined THEN
  Decorator := XlWrkShtWriter.DefaultItalicUnderlinedCellDecorator
ELSE IF IsItalic THEN
  Decorator := XlWrkShtWriter.DefaultItalicCellDecorator
ELSE IF IsUnderlined THEN
  Decorator := XlWrkShtWriter.DefaultUnderlinedCellDecorator
ELSE
  Decorator := XlWrkShtWriter.DefaultCellDecorator;

IF (FontName <> '') OR (FontSize <> 0) OR (FontColour <> 0) OR (BackgroundColour <> 0) THEN
  CustomFont := Decorator.Font.CloneNode(TRUE)
ELSE
  EXIT;

IF FontName <> '' THEN BEGIN
  CustomFont := CustomFont.Font;
  CustomFontName := CustomFontName.FontName;
  CustomFontName.Val := XmlStringValue.StringValue(FontName);
  CustomFont.FontName := CustomFontName;
END;

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

IF FontColour <> 0 THEN BEGIN
  CustomColour := CustomColour.Color;
  CASE FontColour OF
    1 : CustomColour.Rgb := HexColour.HexBinaryValue(ColourBlack);
    2 : CustomColour.Rgb := HexColour.HexBinaryValue(ColourWhite);
    3 : CustomColour.Rgb := HexColour.HexBinaryValue(ColourRed);
    4 : CustomColour.Rgb := HexColour.HexBinaryValue(ColourBlue);
    5 : CustomColour.Rgb := HexColour.HexBinaryValue(ColourGreen);
    6 : CustomColour.Rgb := HexColour.HexBinaryValue(ColourRose);
    7 : CustomColour.Rgb := HexColour.HexBinaryValue(ColourLightGrey);
    ELSE
      CustomColour.Rgb := HexColour.HexBinaryValue(ColourBlack);
  END;
  CustomFont.Color := CustomColour;
END;

IF BackgroundColour <> 0 THEN BEGIN
  HexBackgroundColour := '';

  CASE BackgroundColour OF
    1 : HexBackgroundColour := ColourBlack;
    2 : HexBackgroundColour := ColourWhite;
    3 : HexBackgroundColour := ColourRed;
    4 : HexBackgroundColour := ColourBlue;
    5 : HexBackgroundColour := ColourGreen;
    6 : HexBackgroundColour := ColourRose;
    7 : HexBackgroundColour := ColourLightGrey;
    ELSE
      HexBackgroundColour := ColourWhite;
  END;

  CustomCellFill := Decorator.Fill.CloneNode(TRUE);
  CustomCellPatternFill := CustomCellPatternFill.PatternFill(
    '<x:patternFill xmlns:x="schemas.openxmlformats.org/.../main" '+'patternType="'+'solid'+'">' +
    '<x:fgColor rgb="' + HexBackgroundColour + '" /></x:patternFill>');
  CustomCellFill.PatternFill := CustomCellPatternFill;
  Decorator.Fill := CustomCellFill;
END;

Fonts := XlWrkBkWriter.Workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Fonts;
Decorator.Font := CustomFont;

ColourBlack, ColourWhite etc are text constants using the colour's hex value.

When calling this code the font and the fill colour are all set as expected, but the Bold, Italics and Underline aren't. Removing the last line of the function:

Decorator.Font := CustomFont;

restores the Bold, Italics and Underline but loses all the other formatting.

Is there a way of adding the additional properties while retaining the original ones?



  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Can I extend the Excel Buffer table to set fonts and colours while retaining the Bold/Underline/Italics?

      What are the datatypes of 

      HexBackgroundColour, CustomeCell, CustomCellPatternFill
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Can I extend the Excel Buffer table to set fonts and colours while retaining the Bold/Underline/Italics?

    Thanks for the response, Jonathan. This was the approach that Saurav used in his update to his original solution (see link in OP). I have implemented it in my solution and it is now working as required.

    Thanks for the update, Saurav. As I commented on your blog I used your updates but with some modifications.

    This customisation is now working exactly as required. Thanks!

  • Verified answer
    Saurav.Dhyani Profile Picture
    Saurav.Dhyani 17,957 Super User 2024 Season 2 on at
    RE: Can I extend the Excel Buffer table to set fonts and colours while retaining the Bold/Underline/Italics?

    Hi,

    The Issues have been resolved. Below is the link of updated article link -

    saurav-nav.blogspot.in/.../nav-2013-r2-export-to-excel-with-font.html

    If this resolves all your issues, please mark the issue in forum as resolved.

  • Verified answer
    keoma Profile Picture
    keoma 32,671 on at
    RE: Can I extend the Excel Buffer table to set fonts and colours while retaining the Bold/Underline/Italics?

    instead of the standard code for setting e.g. a text to bold

    try following

    // variable

    BoldValue, DotNet, DocumentFormat.OpenXml.Spreadsheet.Bold.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

    BoldValue := BoldValue.Bold;

    CustomFont.Bold := BoldValue;

    Decorator.Font := CustomFont;

  • Verified answer
    Saurav.Dhyani Profile Picture
    Saurav.Dhyani 17,957 Super User 2024 Season 2 on at
    RE: Can I extend the Excel Buffer table to set fonts and colours while retaining the Bold/Underline/Italics?

    HI,

    Please read the comment section of the same article. Its partially resolved.

    I will come back to you and other comments asap on the article.

    Let me know your feedback once you used it and mark the issue as resolved in the community.

Helpful resources

Quick Links

Dynamics 365 Community Update

Welcome to the inaugural Community Platform Update. As part of our commitment to…

Dynamics 365 Community Newsletter - August 2024

Catch up on the latest D365 Community news

Community Spotlight of the Month

Kudos to Mohana Yadav!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,107 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 227,954 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans