Skip to main content

#msdyn365bc - Export To Excel Report.

Hi Readers,

This is one of the common question that I keep on skipping for a while to write an article about how to export data in Business Central.

The Process is similar but during the Upgrade or writing code we kind of used a function C/AL World CreateBookAndOpenExcel which is not supported with Business Central.

The type or method 'CreateBookAndOpenExcel' cannot be used for 'Extension' development.AL(AL0296)


In this article we will try to understand the whole export to excel process and find a replacement for CreateBookAndOpenExcel in SaaS World.

You can still use this function on-Prem by changing "target": "OnPrem" in your app.json. 

But I always feel that my solutions should be similar for on-Prem and SaaS as customer can opt to upgrade to SaaS.

For Demo, I will be export few fields from item table to Excel.

Let's start by creating a New Processing Only Report for Exporting Data to Excel.

Use snippet treport to get an skeleton of Report. set basic property of Report as shown below and also add a new data item in Report for Item Table.


Next we want to create Headers for our Excel File.

  • Create a temporary variable for Excel Buffer.
  • Clear Temp Variable in onInit Report Trigger.
  • Add a method to create Excel Header with name - CreateExcelHeader.

To add Data in Excel Columns, Excel Buffer Provides a Method AddColumn which Requires Following Parameters -

  • Value - Value you want to Export.
  • IsFormula - If You are using a formula instead of value.
  • CommentText - If you want to add Comments to Column added in Excel File.
  • IsBold - Make Text Bold in Excel.
  • IsItalics - Make Text Italics in Excel.
  • IsUnderline - Make Cell Value as Underline in Excel.
  • NumFormat - If you want to use Custom Formatting in Excel.
  • CellType - It will use default formatting in excel for - Date, Number, Text and Time.

So Let's add Header and Instead for Fix Text, I am planning to use Field Captions for Excel Header as shown below. We will call it onPreDataItem of our Data Item.


Now as we have headers ready, lets try to add records into excel file.

  • For this we will add a new method with Name - CreateExcelBody.
  • As we have already added a row and we want to print our data after one line, we will call Excel Buffer Method to Add New Row.
  • We will call this method from Onaftergetrecord of our Dataitem.
  • This method will also use the Add column function from Excel Buffer but, I am planning to export a Flowfield "Inventory". 

As we all understand that Flowfield are virtual and need to calculated before we plan to use them you can either use the legacy way of using calcfields which is slower or you can use the new faster way to call SetAutoCalcfields.

The Cool part is that you don't need to call SetAutoCalcfields in Onaftergetrecord, you can just call it in onPreDataItem and it will calculate value for the Flowfield, As shown below.


Once we are ready with Excel Header and Body, lets call a method to save our excel file. 

  • We will write a new method to Generate Excel Book with name - CreateExcelBook.
  • We will call it an the end of Report that is OnPostReport trigger.

In this method instead of using CreateBookandSaveExcel, we will use -

  • CreateNewBook - Which will create a New Excel Book.
  • WriteSheet - Write Data in Excel Sheet in New Book.
  • CloseBook - As Writing is complete we will close the book.
  • SetFriendlyFilename - Optional to provide a valid Name to Excel File.
  • OpenExcel - Opens Downloads the Excel File for user.

Once we are done with all our changes, lets map this report to Item List page using Page Extension.

Let's publish our app and test the Report.

Hope you like the article. Let me know your views in article comments.

You can also download the source code from GitHub.

Stay Connected and keep suggesting what you would like me to write about.

Regards,
Saurav Dhyani

www.sauravdhyani.com

Comments

  1. Just an addition to it, can you please provide some sort of code or guidance on how to attach this Excel Buffer generated Excel Report to Email and send that to with New Email functionality.

    ReplyDelete
    Replies
    1. Great Idea,
      I will try to write an article when I have some time.

      Delete
    2. Watch this - https://youtu.be/18IRBkfEG54

      Delete

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 - 

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

Future - What Next After NAV 2016. Code Name - Madeira...

Hi All, While Browsing web I came across some article which says about next release of Microsoft Dynamics NAV. Let's see the History of Code Names Given to Previous Version  - NAV 2013 - Code Name 'Sicily' NAV 2015 - Code Name 'CRETE' NAV 2016 - Code Name 'CORFU' And as we all know that all these are the Islands.