Skip to main content

NAV 2013 R2 - Export To Excel With Font & Color Version 2.

Hi All,

This post is re-written on the already published article due to some features were not present in the first article.

Thanks to +Valerie Metcalf , Nuno Silva  & Bruno Guedes for identifying the missing features. Thank you all.

Let's List Down what all was missing -

 - If you are using AddColumnWithFonts insted of AddColumn the Bold, Italics and Underline property were not working.

 - You Cannot actually use both AddColumnWithFonts & AddColumn in same Report.


In this article we will discuss how to achieve Export To Excel with Font, Color and Back Ground Color with above listed  issue resolved.

If you are looking for same solution in Navision Classic Version refer the article.

1. 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.



2. Create Copy of Two Standard Functions - AddColumn & GetCellDecorator with New Name as below -
   - AddColumnWithFonts
   - GetCellDecoratorWithFonts

3. Now Let's change the calling of Decorator Function in Excel Buffer Table. The Function is called in Two Functions Listed Below -
   - WriteCellValue
   - WriteCellFormula
 
Change the Formula as below - 

-- OLD CODE
GetCellDecorator(Bold,Italic,Underline,Decorator);

-- NEW CODE
IF NOT "Using Custom Decorator" THEN
  GetCellDecorator(Bold,Italic,Underline,Decorator)
ELSE
  GetCellDecoratorWithFonts(Bold,Italic,Underline,Decorator,"Custom Font Name","Custom Font Size","Custom Font Color","Custom Background Color");



5. What Exactly New Added Function Do?

-- AddColumnWithFonts -
Assign Values in Excel Buffer Fields from the Report, as shown below.



 -- GetCellDecoratorWithFonts

Set the Decorator for the Row as per the User Input in the Report Object. The Decorator set for Bold, Italics, Underline, FontName, FontSize, Font Colour & Background Color. Below are details for same.

1. Set Underline, Font Name and Font Size =



2. Set Font Color and Background Color.


3. Set Bold and Italics.

7. Let's Test with a Report for creating a Report with Customer Table.

8. While Exporting data we use AddColumn Function of Excel Buffer Table, now with the extended code that we have discussed we can use both the function i.e AddColumn and AddColumnwithFonts.

9. Below is a Sample Code For Exporting Customer Captions using standard Excel Functions.


10. Below is a Sample Code for Exporting Customer Data, using Custom Excel Functions with Fonts.


11. Let's try to execute the Report and See Output.


Hope you find the article useful and it will help you.

You can download the sample discussed above from Dynamics User Group or from my SkyDrive 

File Name - Saurav-NAV.17.10.ExcelFont_V2.0.rar

Your Comments are awaited.

Regards,
Saurav Dhyani
www.sauravdhyani.com

Comments

Post a Comment

Popular posts from this blog

VIEW SERVER STATE permission on SQL Server?

Hi all, Sometime While trying to Login into a database we face an error message as shown below. --------------------------- Microsoft Dynamics NAV Classic --------------------------- You cannot start Microsoft Dynamics NAV Classic because you do not have the VIEW SERVER STATE permission on SQL Server. Contact your system administrator. --------------------------- OK    ---------------------------

NAV 2013 R2 - Cumulative update 12 Released.

Hi all, Please find below the details of  Cumulative Update 12 released for Microsoft Dynamics NAV 2013 R2. Title - Cumulative Update 12 for Microsoft Dynamics NAV 2013 R2 Build No. - 38053 Release Date - October, 2014 Local Version Included - AU, AT, BE, CH, DE, DK, ES, FI, FR, IS, IT, NA, NL, NO, NZ, SE, UK, RU Download Link Note: Implementing this cumulative update will require a database conversion unless you have already implemented update rollup 5.

Error After Restore SQL Backup of NAV 2013 Database

Hi all, we are facing a conman issue during restoring a SQL Database backup restored for NAV 2013. While Trying to run object from Object Designer we get below listed error (even the service is configured properly) - --------------------------- Microsoft Dynamics NAV Development Environment --------------------------- There are no NAV Server instances available for this database. You must ensure that a NAV Server instance is running and is configured to use the database before you perform this activity. --------------------------- OK    ---------------------------