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

CurrentCol := CurrentCol + 1;
INIT;
VALIDATE("Row No.",CurrentRow);
VALIDATE("Column No.",CurrentCol);
IF IsFormula THEN
  SetFormula(FORMAT(Value))
ELSE
  "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;                                       
INSERT;

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 -


//ExcelBuf.AddColumn(Customer."No.",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumnNew(Customer."No.",FALSE,'',FALSE,FALSE,FALSE,'',20,0,'',0);
                                                 //Font Size Set to 20

//ExcelBuf.AddColumn(Customer.Name,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumnNew(Customer.Name,FALSE,'',FALSE,FALSE,FALSE,'',0,10,'',0);
                                                  //Background Colour Set to Green

//ExcelBuf.AddColumn(SalesHeader."No.",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumnNew(SalesHeader."No.",FALSE,'',FALSE,FALSE,FALSE,'',0,0,'Cambria',0);
                                                   //Font Changed to Cambria

//ExcelBuf.AddColumn(SalesHeader."Order Date",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumnNew(SalesHeader."OrderDate",FALSE,'',FALSE,FALSE,FALSE,'',0,0,'',-16776961);                                            
                                                   //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.

Regards,

Saurav Dhyani

www.sauravdhyani.com