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