Create Export to Excel Report in Business Central

Export to Excel Report in Business Central:

For creating “Export to Excel Report”, follow the below steps. For example-

(1) Export Sales Invoice Header data via report.

(2) Create New report already explained.

create new report in business central

(3) Add “Sales Invoice Header” dataitem.

add sales invoice header data item in bc d365

(4) Create a Procedure for export data from the “Sales Invoice Header”.

  • MakeHeader.
MakeHeader in Export to Excel Report in Business Central
  • MakeBody.
MakeBody in Export to Excel Report in Business Central
  • CreatExcelBook.
CreatExcelBook for Export to Excel Report in Business Central

(5) After following the above procedures. Then call these procedures on the “dataitem” and report triggers as shown.

  • Call “MakeHeader” procedure.
Call MakeHeader Procedure for Export to Excel Report in Business Central
  • Call “MakeBody” procedure.
Call MakeBody procedure for Export to Excel Report in Business Central
  • Call “CreateExcelBook” procedure.
Call CreateExcelBook Procedure for Export to Excel Report in Business Central
  • After this create Request Page for filtering data on “Sales Invoice Header”.
create Request Page for filtering data in Export to Excel Report in BC
  • After doing the above steps, Export to Excel Report developed and attached in the menu suit as per the preferred folder.

Source Code of “Export to Excel Report”:

report 50001 “Sales Register”
{
    UsageCategory = Administration;
    ApplicationArea = All;
 
    dataset
    {
        dataitem(“Sales Invoice Header”; “Sales Invoice Header”)
        {
            DataItemTableView = SORTING(“No.”);
            trigger OnPreDataItem()
            begin

//Date Filter
                SETFILTER(“Posting Date”, ‘%1..%2’, StartDate, EndDate);
                //Customer Filter
                if CustomerNo<> ” then
                    setfilter(“Sell-to Customer No.”, CustomerNo);
            end;
 
            trigger OnAfterGetRecord()
            begin
                //Create Body or Line Part
                MakeBody;
            end;
 
        }
 
    }
    requestpage
    {
        layout
        {
            area(Content)
            {
                group(Options)
                {
                    Caption = ‘Options’;
                    field(CustomerNo; CustomerNo)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = ‘Customer No.’;
                        TableRelation = Customer;
                    }
                    field(StartDate; StartDate)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = ‘Start Date’;
                    }
                    field(EndDate; EndDate)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = ‘End Date’;
                    }
 
                }
            }
        }

actions
        {
            area(processing)
            {
                action(ActionName)
                {
                    ApplicationArea = All;
 
                }
            }
        }
 
    }
    trigger OnPreReport()
    begin
        TempExcelBuffer.Reset();
        TempExcelBuffer.DELETEALL();
        IF StartDate = 0D THEN //Validation
            ERROR(‘Start Date must not be blank’);//Validation
        IF EndDate = 0D THEN //Validation
            ERROR(‘End Date must not be blank’);//Validation
        MakeHeader; //Create Header Part
    end;
 
    trigger OnPostReport()
    begin
        CreateExcelBook; // Create Excel Book
    end;
 
    var
        StartDate: Date;
        EndDate: Date;
        CustomerNo: Code[250];
        TempExcelBuffer: Record “Excel Buffer” temporary;
    local procedure MakeHeader()
    var
        myInt: Integer;
    begin
        TempExcelBuffer.NewRow();
        TempExcelBuffer.AddColumn(‘Customer No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
        TempExcelBuffer.AddColumn(‘Customer Name’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
        TempExcelBuffer.AddColumn(‘Invoice Date’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Invoice No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
        TempExcelBuffer.AddColumn(‘Amount’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
    end;
    local procedure MakeBody()
    var
        myInt: Integer;
    begin
        TempExcelBuffer.NewRow();
        TempExcelBuffer.AddColumn(“Sales Invoice Header”.”Sell-to Customer No.”, FALSE, ”, FALSE, FALSE, FALSE, ”, TempExcelBuffer.”Cell SalesType”::Text);
        TempExcelBuffer.AddColumn(“Sales Invoice Header”.”Sell-to Customer Name”, FALSE, ”, FALSE, FALSE, FALSE, ”, TempExcelBuffer.”Cell Type”::Text);
        TempExcelBuffer.AddColumn(“Sales Invoice Header”.”Posting Date”, FALSE, ”, FALSE, FALSE, FALSE, ”, TempExcelBuffer.”Cell Type”::Number);
        TempExcelBuffer.AddColumn(“Sales Invoice Header”.”No.”, FALSE, ”, FALSE, FALSE, FALSE, ”, TempExcelBuffer.”Cell Type”::Number);
        TempExcelBuffer.AddColumn(“Sales Invoice Header”.Amount, FALSE, ”, FALSE, FALSE, FALSE, ”, TempExcelBuffer.”Cell Type”::Number);
    end;
    Local procedure CreateExcelBook();
    begin
        TempExcelBuffer.CreateNewBook(‘SalesRegister’);
        TempExcelBuffer.WriteSheet(‘SalesRegister’, CompanyName, UserId);
        TempExcelBuffer.CloseBook();
        TempExcelBuffer.SetFriendlyFilename(‘Sales Register’);
        TempExcelBuffer.OpenExcel();
    end;
 
}

Note:
When you add columns, you can choose some default font and cell type.

AddColumn Function:procedure AddColumn(Value: Variant, IsFormula: Boolean, CommentText:Text, IsBold: Boolean, IsItalics: Boolean, IsUnderline: Boolean, NumFormat:Text[30], CellType: Option).


D365 Business Central Essentials vs Premium License
Usage of Keys in Business Central
How to Move fields in Page via AL Extension in D365 BC
Exporting Your Business Data to Excel– Microsoft Docs

Leave a Reply