{"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

Adjust width of Column when using Export to Excel functionality in D365 BC:<\/h2>\n\n\n\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

\n
\"How<\/figure><\/div>\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\n

report 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
Copy Data from Production to Sandbox in D365 BC SAAS Version<\/a>
How to Delete Environment in D365 BC SAAS<\/a>
How to Open D365 BC Tablet or Phone Client in Browser<\/a>
Error while Publishing Extension or downloading AL Symbol in Dynamics 365- Solved<\/a>
Getting Started with AL<\/a>\u2013 Microsoft Docs<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"

Adjust width of Column when using Export to Excel functionality in D365 BC: (1) In this blog we will see the adjustment of Column width when using Export to Excel functionality in Business Central D365. (2) In the previous blog we developed Export to excel functionality. (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. (4) Let\u2019s take an overview to use this feature in the previously discussed example. (5) By adding the above function in the \u201cExport To Excel\u201d functionality, you adjust the width of <\/p>\n","protected":false},"author":1,"featured_media":7087,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[798,797],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/erpconsultors.com\/wp-content\/uploads\/2022\/05\/Adjust-width-of-Column-when-using-Export-to-Excel-functionality-in-D365-BC.jpg?fit=350%2C270&ssl=1","_links":{"self":[{"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/posts\/7075"}],"collection":[{"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/comments?post=7075"}],"version-history":[{"count":0,"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/posts\/7075\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/media\/7087"}],"wp:attachment":[{"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/media?parent=7075"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/categories?post=7075"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/erpconsultors.com\/wp-json\/wp\/v2\/tags?post=7075"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}