Skip to main content

Business Central 14 - Export To Excel.

Hi Readers,

I am writing an article after a long time. 

There are multiple reasons behind that. I am busy with projects and I am spending more time in #bcopendiscussion. 

If you are not part of  #bcopendiscussion or are unable to join due to overlapping schedule I would recommend you to visit YouTube for all session recordings.

This article is based on a request from one of our community member, which talks about Export data to Excel in Business Central 14. So if you know it please save your time.

There are changes in functions related to Export to Excel feature. In this article, we will try to list down methods required to export data to excel using Excel Buffer.

For Demo, let's assume we would like to Export data from Customer Table with associated open customer ledger entries if exist.

Let's create a Report 50000 & and add two data Items Customer & Cust. Ledger Entries. Add Relation to Cust. Ledger Entries based on Customer No. using property DataItemLink as shown below.

If you would like to filter records then apply filter on data items that you would like. We will apply the Filter in Customer Ledger Entries using property DataItemTableView.


For Exporting Data using Excel Buffer, create a global variable TempExcelBuffer which should always be set to Temporary as shown below.


Lets Define five Functions to  - 

  1. WriteExcelHeader - Function to write Headers in Excel File. 
  2. WriteCustomerExcelBody - Write Data From Customer Record.
  3. WriteCustomerLedgerExcelBody - Write Data from Customer Ledger Entries.
  4. WriteReportInfo - Optional if you want to add information tab with additional information.
  5. WriteExcelBook - Function to Close and Open Excel Book.


The Function that is used to write data into excel buffer is AddColumn which requires following parameters - 

  1. Value - Pass the value that you want to print in Excel.
  2. IsFormula - If the Value passed is a Excel Formula then set it to True.
  3. CommentText - If you would like to add excel comment then pass a text.
  4. IsBold - If you would like to set the column Text to be Bold.
  5. IsItalics - If you would like to set the column Text to be Italics.
  6. IsUnderline - If you would like to set the column Text to be Underline.
  7. NumFormat  - If you would like to set the column with a Number formatting.
  8. CellType - This parameter set the Cell Type in Excel, Options are - Text, Date, Time, Number.
Function WriteExcelHeader - 
  • In this function we would like to add headers in Excel. I am using Field Captions for fields in Customer Table, but you can also use fixed text if required.
  • The Headers will be Bold and Underline and That's why Parameter 4 and 6 are set to True.


Function WriteCustomerExcelBody - 
  • To add a New Row after every record, you will require to call function NewRow. Then using AddColumn you can add data into the Excel Buffer Table.


Function WriteCustomerLedgerExcelBody -
  • If you would like to add a Flowfield from a table in Excel Buffer you will require to call the calcfields to calculate the value before we write in Excel Buffer.
  • To Distinguish customer Ledger Entry Records, We are keeping first column as blank and Text will be italics (parameter 5 is set to True). 
  • Additionally there are numeric and Date field, so last field is set appropriately based on Value passed.


Function WriteReportInfo - 
  • This is optional function. If you would like to add Information about report, like Report No, Name and user who ran it then you can add details in this function.
  • To Add Additional Sheet, Call function SetUseInfoSheet and instead of AddColumn use AddInfoColumn.


Function WriteExcelBook - 
  • This function is created to Save and Open Excel File as shown below.


Now Let's call these function in appropriate places in Report Triggers.



Last but on least, as this report has no printable output then we should set report property ProcessingOnly to true.



Let's Run the Report to View output of Report. 



Hope you find this article useful.

If required you can download objects from OneDrive.

Regards,
Saurav Dhyani

Comments

  1. Can you please help or provide some guidance for using Excel Buffer on BC OnCloud specifically Exporting Data and attaching it to Email with New Email functionality. I am not able to attach Excel and send through Email using Excel Buffer.

    ReplyDelete
  2. Sir, Very Nice Example, Its really helped me, and sir please elaborate about filtering in it, location wise, entry type wise etc.. :)

    ReplyDelete
    Replies
    1. You can add fields in ReqFilterFields property of Data Item and user will be able to filter records before running the report.

      Delete

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.