Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV (Archived)
Answered

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

(0) ShareShare
ReportReport
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?



*This post is locked for comments

  • 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 14,178 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,675 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 14,178 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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,661 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,379 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans