Skip to main content

Report Export to Excel with Font & Colour

Hi all,

As required by most of the Blog visitor here is a post for export to excel with Font Size, Font Color, Background Color and Font Name.

If you are looking for the same solution in NAV 2013 R2 or any of the Role Tailored Client then Refer Here for the Updated Article for NAV 2013 R2.

Let's see how we can achieve them -

1. I have added some fields in Excel Buffer Table (370) listed Below.

Field No. Field Name         Data Type Length
50000 Font Size                  Integer
50001 BackGround Color Integer
50002 Font Name               Text                 100
50003 Font Color                Integer

2. Now I need to create a new function which will input values for these fields. I copied the standard function AddColumn and Created a New Function AddColumnNew with additional parameters as shown below.

Function AddColumnNew-

IF CurrentRow < 1 THEN

CurrentCol := CurrentCol + 1;
VALIDATE("Row No.",CurrentRow);
VALIDATE("Column No.",CurrentCol);
IF IsFormula THEN
  "Cell Value as Text" := FORMAT(Value);
Comment := CommentText;
Bold := IsBold;
Italic := IsItalics;
Underline := IsUnderline;
NumberFormat := NumFormat;
"Font Size" := FontSize;                                             //NEW LINES IN BOLD
"BackGround Color" := BGColour;                        
"Font Name" := FontName;                                     
"Font Color" := FontColor;                                       

Below is the list of local parameters in the function -

Name DataType Subtype Length
Value Variant
IsFormula         Boolean
CommentText Text                  1000
IsBold Boolean
IsItalics         Boolean
IsUnderline Boolean
NumFormat Text                30
FontSize         Integer                                     // NEW Parameter IN BOLD
BGColour Integer                                     
FontName Text                100              
FontColor Integer                                   

Now we need to add code in function CreateSheet as shown below -

Testing -
For testing i am using Standard Report 108 - Customer - Order Detail. i have changed the code in function MakeExcelDataBody().

The First four lines of the function are changed as shown below -

                                                 //Font Size Set to 20

                                                  //Background Colour Set to Green

                                                   //Font Changed to Cambria

//ExcelBuf.AddColumn(SalesHeader."Order Date",FALSE,'',FALSE,FALSE,FALSE,'');
                                                   //Font Color Set to Red

Output -
When i run the Report and select Export to Excel, below is the output that i got.

Column 1- Font is 20.
Column 2 - Background Color is green.
Column 3 - Font is Cambria.
Column 4 - Font color is Red.

You can download the FOB for NAV 2009 SP1 Table 370 Excel Buffer from Skypdrive.

File Name - Table 370 With Excel new Functions.rar

The Fob Contain the functions discussed in this post as well as the cell merging function.

Saurav Dhyani


  1. can we give size of colomn width

  2. Hi Saurav

    Is there any ideas how can we increase the row height, column width & wrap text with Excel Buffer in NAV 2013 using DotNet.

    Thanks for your thoughts on this.


Post a Comment

Popular posts from this blog

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

Dynamics 365 Business Central 2021 release wave 2 (BC19) Released.

Hi Readers, As we all were expecting Microsoft have Released Business Central 2021 Release Wave 2 Today a.k.a BC19. In this article we will see what all is announced with the General Availability.  Starting Today Customers & Partners can start using Business Central 19. #msdyn365bc #bc19

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