Skip to main content

Export to Excel in Microsoft Dynamics 365 Business Central

Hi folks,

In this article we will see how we can export data using Excel Buffer table in Excel format.

Here, i am using the Excel Buffer as a temporary table to store data from master table to Buffer table on temporary basis and then write it to Excel file.

Step-(1) Based on the data format and columns, create a new Procedure/function on Codeunit to export data and download it in excel file.

Codeunit 50002 "Export to Excel"
{
    trigger OnRun()
    begin
    end;

    procedure ExportSalesHeaderInfo()
    var
        TempExcelBuffer: Record "Excel Buffer" temporary;
        RecSalesHeader: Record "Sales Header";
        FullAddress: Text;
    begin
        RecSalesHeader.Reset();
        TempExcelBuffer.Reset();
        TempExcelBuffer.DeleteAll();
        TempExcelBuffer.NewRow();
        TempExcelBuffer.AddColumn(RecSalesHeader.FieldCaption("No."), false, '', true, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(RecSalesHeader.FieldCaption("Bill-to Name"), false, '', true, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(RecSalesHeader.FieldCaption("Order Date"), false, '', true, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn(RecSalesHeader.FieldCaption(Amount), false, '', true, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn('Time', false, '', true, false, false, '', TempExcelBuffer."Cell Type"::Text);
        TempExcelBuffer.AddColumn('Address', false, '', true, true, true, '', TempExcelBuffer."Cell Type"::Text);
        if RecSalesHeader.FindSet() then begin
            repeat
                TempExcelBuffer.NewRow();
                TempExcelBuffer.AddColumn(RecSalesHeader."No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(RecSalesHeader."Bill-to Name", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
                TempExcelBuffer.AddColumn(RecSalesHeader."Order Date", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Date);
                RecSalesHeader.CalcFields(Amount);
                TempExcelBuffer.AddColumn(RecSalesHeader.Amount, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
                TempExcelBuffer.AddColumn(System.Time, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Time);
                FullAddress := RecSalesHeader."Bill-to Address" + ',' + RecSalesHeader."Bill-to Address 2" + ',' + RecSalesHeader."Bill-to City";
                TempExcelBuffer.AddColumn(FullAddress, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
            until RecSalesHeader.Next() = 0;
            TempExcelBuffer.CreateNewBook('Sales Header Info');
            TempExcelBuffer.WriteSheet('Sales Header S1', CompanyName, UserId);
            TempExcelBuffer.CloseBook();
            TempExcelBuffer.SetFriendlyFilename(StrSubstNo('Sales Header_%1,_%2', CurrentDateTime, UserId));
            Message('Excel file exported successfully!');
            TempExcelBuffer.OpenExcel();
        end;
    end;
}

Step-(2) Now create new Sales Order List Page Extension, and add new action button and call the required procedure:

pageextension 50002 "Sales Order List Ext" extends "Sales Order List"
{
    layout
    {
        // Add changes to page layout here
    }

    actions
    {
        // Add changes to page actions here\
        addfirst(Processing)
        {
            action("Export Excel - Sales Header")
            {
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;

                trigger OnAction()
                var
                    ExportToExcel_CU: Codeunit "Export to Excel";
                begin
                    ExportToExcel_CU.ExportSalesHeaderInfo();
                end;
            }
        }
    }
}

Output- Open Sales Order List Page and click on action button "Export Excel - Sales Header" and this will download the excel file with required data as:

Hope this will Help you ;)

Thanks for Reading!!

Labels:

Comments

Post a Comment

Popular posts from this blog

Flow Custom field to G/L Entry and other Posted tables : From Purchase Order to Posted Purchase invoice

Hi Readers, In this blog we will see how we can transfer data from " Purchase Header" to "Purchase Invoice Header" & "G/L Entry" table in Business Central. In this example, will create "Remarks" field on Purchase Header table and after posting, flow it to Purchase Invoice Header & G/L Entry tables: Firsly, create required fields ("Remarks") in below mentioned tables by creating table extensions: Purchase Header (38) Purch. Inv. Header (122) Gen. Journal Line (81)  G/L Entry (17) For the Purch. Inv. Header table, you can just create copy the field from purchase header and paste it in  Purch. Inv. Header  table. If the field ID No. is same for that field for both the tables then you don't have to code i.e, it automatically transfer that field data from Purch. Header to Purch. Inv. Header . Step-(1)   tableextension   50104  "Purchase Header Ext"  extends  "Purchase Header" {      fields   ...
3 comments
Read more

Amount In Words in Navision / Business Central

Hi Guys, In this article, we will see how to display amount in words in Microsoft Dynamics Business Central. Here, I am taking an example of Report object  which is widely used in Navision for converting decimal amount field into words. Procedure :-  Step-1 Declare the following Global Variables :         RepCheck:  Report  "Check";         NoText:  array [ 2 ]  of   Text ;         AmountInWords:  Text ; where, (1) RepCheck is a variable of  Report DataType Named as " Check " which is 1401. (2) NoText is a variable of DataType " Text " with array Dimension value as 2 which is defined as shown above. (3) AmountInWords is a variable of DataType " Text " which will store final result of amount in words. Step-2 Now, add the following code in OnAfterGetRecord() trigger of DataItem containing that decimal field value. So in my cu...
9 comments
Read more

The Txt2Al Conversion Tool: How to Export Dynamics NAV objects (.txt) and converting to AL

Hi folks, In this article, I will show you how you can use Txt2Al conversion tool that is the part of NAV 2018 and Dynamics 365 Business Central On-Premise. Using this Txt2Al conversion tool you can take existing Dynamics NAV objects that have been exported in .txt format and convert them into the new .al format and use these .al format file directly using Visual Studio Code to develop/build extensions for Dynamics 365 Business Central. Converting the NAV objects consists of following 2 steps: 1. Exporting the objects from C/SIDE [Either from Development Environment or using PowerShell Command] 2. Converting the objects to the new syntax. You can find Txt2Al tool in following location if you installed Dynamics 365 Business Central On-Premise on your machine: C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\140\RoleTailored Client \Txt2Al.exe Steps to use the Txt2Al conversion tool:  Reference Step 1:  Compile your Dynamics NAV DB Step 2: ...
Post a Comment
Read more