Adjust Width Of Column When Using Export To Excel Functionality In D365 BC

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 “Export to Excel” functionality. This procedure is available in the 370 “Excel Buffer” table.

(4) Let’s take an overview to use this feature in the previously discussed example.

How to adjust width of Column when using Export to Excel functionality in D365 BC

(5) By adding the above function in the “Export To Excel” functionality, you adjust the width of the column in the excel sheet.

Source Code:

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 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);
    end;

    Local procedure CreateExcelBook();
    begin
TempExcelBuffer.CreateNewBook(‘SalesRegister’);
TempExcelBuffer.WriteSheet(‘SalesRegister’, CompanyName, UserId);
        //SetColumn Width
TempExcelBuffer.SetColumnWidth(‘B’, 20);
TempExcelBuffer.SetColumnWidth(‘C’, 15);
        //SetColumn Width
TempExcelBuffer.CloseBook();
TempExcelBuffer.SetFriendlyFilename(‘SalesRegister’);
TempExcelBuffer.OpenExcel();
    end;

}

Copy Data from Production to Sandbox in D365 BC SAAS Version
How to Delete Environment in D365 BC SAAS
How to Open D365 BC Tablet or Phone Client in Browser
Error while Publishing Extension or downloading AL Symbol in Dynamics 365- Solved
Getting Started with AL– Microsoft Docs