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

Comments

  1. can we give size of colomn width

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

    ReplyDelete

Post a Comment

Popular posts from this blog

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

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 - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.