{"id":7075,"date":"2022-05-27T03:40:28","date_gmt":"2022-05-27T03:40:28","guid":{"rendered":"https:\/\/erpconsultors.com\/?p=7075"},"modified":"2022-05-27T03:40:29","modified_gmt":"2022-05-27T03:40:29","slug":"adjust-width-of-column-when-using-export-to-excel-functionality-in-d365-bc","status":"publish","type":"post","link":"https:\/\/erpconsultors.com\/adjust-width-of-column-when-using-export-to-excel-functionality-in-d365-bc\/","title":{"rendered":"How to adjust width of Column when using Export to Excel functionality in D365 BC"},"content":{"rendered":"\n
(1) In this blog we will see the adjustment of Column width when using Export to Excel functionality in Business Central D365.<\/p>\n\n\n\n
(2) In the previous blog we developed Export to excel functionality<\/strong><\/a>.<\/p>\n\n\n\n (3) Now add one more procedure while developing the \u201cExport to Excel\u201d functionality. This procedure is available in the 370 \u201cExcel Buffer\u201d table.<\/p>\n\n\n\n (4) Let\u2019s take an overview to use this feature in the previously discussed example.<\/p>\n\n\n (5) By adding the above function in the \u201cExport To Excel\u201d functionality, you adjust the width of the column in the excel sheet.<\/p>\n\n\n\n Source Code:<\/strong><\/p>\n\n\n\nreport 50001 “Sales Register”
{
UsageCategory = Administration;
ApplicationArea = All;
ProcessingOnly = True;
\u00a0\u00a0\u00a0 dataset
\u00a0\u00a0\u00a0 {
dataitem(“Sales Invoice Header”; “Sales Invoice Header”)
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
DataItemTableView = SORTING(“No.”);
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trigger OnPreDataItem()
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 begin
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/Date Filter
SETFILTER(“Posting Date”, ‘%1..%2’, StartDate, EndDate);
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/Customer Filter
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if CustomerNo<> ” then
setfilter(“Sell-to Customer No.”, CustomerNo);
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end;
\u00a0
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trigger OnAfterGetRecord()
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 begin
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/Create Body or Line Part
MakeBody;
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end;
\u00a0
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\u00a0
\u00a0\u00a0\u00a0 }
requestpage
\u00a0\u00a0\u00a0 {
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 layout
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
area(Content)
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
group(Options)
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Caption = ‘Options’;
field(CustomerNo; CustomerNo)
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
ApplicationArea = Basic, Suite;
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Caption = ‘Customer No.’;
TableRelation = Customer;
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
field(StartDate; StartDate)
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
ApplicationArea = Basic, Suite;
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Caption = ‘Start Date’;
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
field(EndDate; EndDate)
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
ApplicationArea = Basic, Suite;
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Caption = ‘End Date’;
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\u00a0
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 actions
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 area(processing)
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
action(ActionName)
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
ApplicationArea = All;
\u00a0
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\u00a0
\u00a0\u00a0\u00a0 }
\u00a0\u00a0\u00a0 trigger OnPreReport()
\u00a0\u00a0\u00a0 begin
TempExcelBuffer.Reset();
TempExcelBuffer.DELETEALL();
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF StartDate = 0D THEN \/\/Validation
ERROR(‘Start Date must not be blank’);\/\/Validation
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF EndDate = 0D THEN \/\/Validation
ERROR(‘End Date must not be blank’);\/\/Validation
MakeHeader; \/\/Create Header Part
\u00a0\u00a0\u00a0 end;
\u00a0
\u00a0\u00a0\u00a0 trigger OnPostReport()
\u00a0\u00a0\u00a0 begin
CreateExcelBook; \/\/ Create Excel Book
\u00a0\u00a0\u00a0 end;
\u00a0
\u00a0\u00a0\u00a0 var
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 StartDate: Date;
EndDate: Date;
CustomerNo: Code[250];
TempExcelBuffer: Record “Excel Buffer” temporary;
\u00a0
\u00a0\u00a0\u00a0 local procedure MakeHeader()
\u00a0\u00a0\u00a0 var
myInt: Integer;
\u00a0\u00a0\u00a0 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);
\u00a0\u00a0\u00a0 end;
\u00a0
\u00a0\u00a0\u00a0 local procedure MakeBody()
\u00a0\u00a0\u00a0 var
myInt: Integer;
\u00a0\u00a0\u00a0 begin
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(“Sales Invoice Header”.”Sell-to Customer No.”, FALSE, ”, FALSE, FALSE, FALSE, ”, TempExcelBuffer.”Cell Type”::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);
\u00a0\u00a0\u00a0 end;
\u00a0
\u00a0\u00a0\u00a0 Local procedure CreateExcelBook();
\u00a0\u00a0\u00a0 begin
TempExcelBuffer.CreateNewBook(‘SalesRegister’);
TempExcelBuffer.WriteSheet(‘SalesRegister’, CompanyName, UserId);
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/SetColumn Width
TempExcelBuffer.SetColumnWidth(‘B’, 20);<\/strong>
TempExcelBuffer.SetColumnWidth(‘C’, 15);<\/strong>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/SetColumn Width
TempExcelBuffer.CloseBook();
TempExcelBuffer.SetFriendlyFilename(‘SalesRegister’);
TempExcelBuffer.OpenExcel();
\u00a0\u00a0\u00a0 end;
\u00a0
}<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n