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.
(3) Add “Sales Invoice Header” dataitem.
(4) Create a Procedure for export data from the “Sales Invoice Header”.
- MakeHeader.
- MakeBody.
- CreatExcelBook.
(5) After following the above procedures. Then call these procedures on the “dataitem” and report triggers as shown.
- Call “MakeHeader” procedure.
- Call “MakeBody” procedure.
- Call “CreateExcelBook” procedure.
- After this create Request Page for filtering data on “Sales Invoice Header”.
- 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; ProcessingOnly = True; 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).
