Color Implementation in Excel Buffer in NAV 2017
Hi All,This post is about how to implement color and font while using Excel buffer in Navision.
Why you need it :-
Microsoft has provided excel buffer but it does not include the functionality to implement color of column , font and font size.
My Solution includes :
- You can use AddColumnWithFonts & AddColumn both in report to export data from Table using excel buffer.
- Both function AddColumnWithFonts & AddColumn can export data with additional AddColumnwithfonts can export data with BackGroundColor,Size,font name also.
You can download all my implemented code from below.
Steps to achieve Export To Excel with Font, Color and Back Ground Color .
Step1. Add Five Fields Custom Font Name,Custom Font Size,Custom Font Color, Custom Background Color & Using Custom Decorator in Excel Buffer Table as shown below.
Step 2. Create Copy of Two Standard Functions - AddColumn & GetCellDecorator with New Name as below -
- AddColumnWithFonts
- GetCellDecoratorWithFonts
Step 3. Also change Code in Standard functions of this table Excel Buffer.
- WriteCellValue
//>>#Anand.09.02.ExcelFont Start
IF NOT "Using Custom Decorator" THEN
GetCellDecorator(Bold,Italic,Underline,"Double Underline",Decorator)
ELSE
GetCellDecoratorwithFonts(Bold,Italic,Underline,"Double Underline",Decorator,"Custom Font Name","Custom Font Size","Custom Font Color","Custom BGcolor");
////>>#Anand.09.02.ExcelFont END
-WriteCellFormula
//>>#Anand.09.02.ExcelFont Start
IF NOT "Using Custom Decorator" THEN
GetCellDecorator(Bold,Italic,Underline,"Double Underline",Decorator)
ELSE
GetCellDecoratorwithFonts(Bold,Italic,Underline,"Double Underline",Decorator,"Custom Font Name","Custom Font Size","Custom Font Color","Custom BGcolor");
////>>#Anand.09.02.ExcelFont END
- WriteCellFormula and WriteCellvalue Function Screenshot.
To add Code added in new Function of ExcelBufferTable GetCellDecoratorwithFonts
Firstly, delete all code from GetCellDecoratorwithFonts function and COPY PASTE this Code.
Decorator := XlWrkShtWriter.DefaultCellDecorator;
IF (FontName <> '') OR (FontSize <> 0) OR (FontColour <> 0) OR (BackgroundColour <>0) THEN
CustomFont := Decorator.Font.CloneNode(TRUE)
ELSE BEGIN
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;
EXIT;
END;
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('00FF0000');//RED
2 : CustomColour.Rgb := HexColour.HexBinaryValue('00FFFF00');//YELLOW
3 : CustomColour.Rgb := HexColour.HexBinaryValue('000000FF');//BLUE
4 : CustomColour.Rgb := HexColour.HexBinaryValue('00008000');//GREEN
5 : CustomColour.Rgb := HexColour.HexBinaryValue('00FFA500');//Orange
6 : CustomColour.Rgb := HexColour.HexBinaryValue('00FF00F4');//Pink
7 : CustomColour.Rgb := HexColour.HexBinaryValue('000B9014');//Light Green
ELSE
CustomColour.Rgb := HexColour.HexBinaryValue('00FF0000');
END;
CustomFont.Color := CustomColour;
END;
IF BackgroundColour <> 0 THEN BEGIN
HexBackgroundColour := '';
CASE BackgroundColour OF
1 : HexBackgroundColour := '00FF0000';//RED
2 : HexBackgroundColour := '00FFFF00';//YELLOW
3 : HexBackgroundColour := '000000FF';//BLUE
4 : HexBackgroundColour := '00008000';//GREEN
5 : HexBackgroundColour := '00FFA500';//Orange
6 : HexBackgroundColour := '00FF00F4';//Pink
7 : HexBackgroundColour := '000B9014';//Light Green
ELSE
HexBackgroundColour := '00FF0000';
END;
CustomCellFill := Decorator.Fill.CloneNode(TRUE);
CustomCellPatternFill := CustomCellPatternFill.PatternFill(
'<x:patternFill xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '+'patternType="'+'solid'+'">' +
'<x:fgColor rgb="' + HexBackgroundColour + '" /></x:patternFill>');
CustomCellFill.PatternFill := CustomCellPatternFill;
Decorator.Fill := CustomCellFill;
END;
IF IsBold THEN BEGIN
CustomFontBold := CustomFontBold.Bold;
CustomFontBold.Val := XmlBooleanValue.BooleanValue(TRUE);
CustomFont.Bold := CustomFontBold;
END;
IF IsItalic THEN BEGIN
CustomFontItalic := CustomFontItalic.Italic;
CustomFontItalic.Val := XmlBooleanValue.BooleanValue(TRUE);
CustomFont.Italic := CustomFontItalic;
END;
IF IsUnderlined THEN BEGIN
// CustomFontUnderline := CustomFontUnderline.Underline;
// CustomFontUnderline.Val := XmlBooleanValue.BooleanValue(TRUE);
// CustomFont.Underline := CustomFontUnderline;
END;
Fonts := XlWrkBkWriter.Workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Fonts;
Decorator.Font := CustomFont;
Now you need to Also declare these parameters in this function.
Var Name DataType Subtype Length
No IsBold Boolean
No IsItalic Boolean
No IsUnderlined Boolean
No IsDoubleUnderlined Boolean
Yes Decorator DotNet Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator.'Microsoft.Dynamics.Nav.OpenXml, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
No FontName Text 250
No FontSize Integer
No FontColour Integer
No BackgroundColour Integer
Also declare these Variable in this function.
Name DataType Subtype
ValueSet Boolean
CustomFont DotNet
DocumentFormat.OpenXml.Spreadsheet.Font.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomFontName DotNet
DocumentFormat.OpenXml.Spreadsheet.FontName.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomFontSize DotNet
DocumentFormat.OpenXml.Spreadsheet.FontSize.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomFontBold DotNet
DocumentFormat.OpenXml.Spreadsheet.Bold.'DocumentFormat.OpenXml, Version=2.0.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomFontItalic DotNet
DocumentFormat.OpenXml.Spreadsheet.Italic.'DocumentFormat.OpenXml, Version=2.0.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomFontUnderline DotNet
DocumentFormat.OpenXml.Spreadsheet.Underline.'DocumentFormat.OpenXml, Version=2.0.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
XmlStringValue DotNet
DocumentFormat.OpenXml.StringValue.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
XmlBooleanValue DotNet
DocumentFormat.OpenXml.BooleanValue.'DocumentFormat.OpenXml, Version=2.0.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
FontSizeValue DotNet
DocumentFormat.OpenXml.DoubleValue.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomColour DotNet
DocumentFormat.OpenXml.Spreadsheet.Color.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
HexColour DotNet
DocumentFormat.OpenXml.HexBinaryValue.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomCellFill DotNet
DocumentFormat.OpenXml.Spreadsheet.Fill.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomCellPatternFill DotNet
DocumentFormat.OpenXml.Spreadsheet.PatternFill.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
Fonts DotNet DocumentFormat.OpenXml.Spreadsheet.Fonts.'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
HexBackgroundColour Text 50
ScreenShot for local Variables inside GetcellDecorators
Now Adding Code in function AddColumnwithfonts
//>>#Divey.09.02.ExcelFont Start
"Custom Font Name" := FontName;
"Custom Font Size" := FontSize;
"Custom Font Color" := FontColor;
"Custom BGcolor" := BGColor;
"Using Custom Decorator" := Usingcustomformat;
////>>#Divey.09.02.ExcelFont END
ScreenShot for Adding code inside Addcolumnwithfonts
Add these Parameters to our created function Addcolumnwithfonts
Result :-
Define the new Function AddColumnwithfonts inside report to add color and other style in report as (Please check the parameters and sequene of AddColumnwithfonts)
Thanks To Saurabh Dhyani Sir and Stackoverflow link which initiates me to implement this blog.
StackoverFlow Link
Saurabh Dyhyani Sir Blog
Upto my understanding I had copied all code correctly if there is any issue or suggestions and please let me know in my comment section.
All Steps Shortcut :- Summarised Steps Download
FOB Object :- FOB Implemented
No comments:
Post a Comment