Thursday, February 9, 2017

Top 10 Fantastic experience in Microsoft Dynamics Navision NAV 2016 - Export To Excel With Font & Color_files

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