Dynamics Community Forum Thread Details

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

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: