Codeunit 1489 Edit in Excel Workbook Impl.

System Application, System.Integration.Excel

src/System Application/App/Edit in Excel/src/EditInExcelWorkbookImpl.Codeunit.al241 lines, Copyright (c) Microsoft Corporation. MIT

// ------------------------------------------------------------------------------------------------// Copyright (c) Microsoft Corporation. All rights reserved.// Licensed under the MIT License. See License.txt in the project root for license information.// ------------------------------------------------------------------------------------------------namespace System.Integration.Excel;using System;using System.Azure.Identity;using System.Environment;using System.Integration;/// <summary>/// This codeunit provides an interface to create Workbook using the Excel Add-in./// </summary>codeunit 1489 "Edit in Excel Workbook Impl."{    Access = Internal;    InherentEntitlements = X;    InherentPermissions = X;    var        EnvironmentInformation: Codeunit "Environment Information";        DataEntityExportInfo: DotNet DataEntityExportInfo; // Stateful        BindingInfo: DotNet BindingInfo; // Stateful        DataEntityInfo: DotNet DataEntityInfo; // Stateful        DataEntityExportGenerator: DotNet DataEntityExportGenerator; // Global var used for export, to avoid cleaning up before output is read from returned InputStream        MemoryStream: DotNet MemoryStream; // Global var used for export, to avoid cleaning up before output is read from returned InputStream        EditInExcelTelemetryCategoryTxt: Label 'Edit in Excel', Locked = true;        EditInExcelUsageWithCentralizedDeploymentsTxt: Label 'Edit in Excel invoked with "Use Centralized deployments" = %1', Locked = true;        WebServiceHasBeenDisabledErr: Label 'You can''t edit this page in Excel because it''s not set up for it. To use the Edit in Excel feature, you must publish the web service called ''%1''. Contact your system administrator for help.', Comment = '%1 = Web service name';        CreatingExcelDocumentWithIdTxt: Label 'Creating excel document with id %1.', Locked = true;        WebServiceDoesNotExistErr: Label 'Cannot initialize Edit in Excel workbook since the web service ''%1'' does not exist.', Comment = '%1 = name of the web service';        NoColumnsExistErr: Label 'No columns were added to the workbook.';        ExternalizedServiceName: Text;    procedure Initialize(ServiceName: Text[250])    var        TenantWebService: Record "Tenant Web Service";        EditInExcelImpl: Codeunit "Edit in Excel Impl.";    begin        // Ensure web service exist and is published        if (not TenantWebService.Get(TenantWebService."Object Type"::Page, ServiceName)) then            Error(WebServiceDoesNotExistErr, ServiceName);        if not TenantWebService.Published then            Error(WebServiceHasBeenDisabledErr, TenantWebService."Service Name");        InitializeDataEntityExportInfo(TenantWebService);        // Create DataEntityInfo        DataEntityInfo := DataEntityInfo.DataEntityInfo();        // Align the entity name to resemble how OData processes the service name        ExternalizedServiceName := EditInExcelImpl.ExternalizeODataObjectName(ServiceName);        DataEntityInfo.Name := ExternalizedServiceName;        DataEntityInfo.PublicName := ExternalizedServiceName;        DataEntityExportInfo.Entities.Add(DataEntityInfo);        BindingInfo := BindingInfo.BindingInfo();        BindingInfo.EntityName := DataEntityInfo.Name;        DataEntityExportInfo.Bindings.Add(BindingInfo);    end;    procedure SetFilters(EditInExcelFilters: Codeunit "Edit in Excel Filters")    var        EntityFilterCollectionNode: DotNet FilterCollectionNode;        ChildFilterCollectionNode: DotNet FilterCollectionNode;        FieldFilters: DotNet GenericDictionary2;    begin        EntityFilterCollectionNode := EntityFilterCollectionNode.FilterCollectionNode();  // One filter collection node for entire entity        EntityFilterCollectionNode.Operator := Format("Excel Filter Node Type"::"and");        EditInExcelFilters.GetFilters(FieldFilters);        if not IsNull(FieldFilters) then            foreach ChildFilterCollectionNode in FieldFilters.Values do                if ChildFilterCollectionNode.Collection.Count() > 0 then                    EntityFilterCollectionNode.Collection.Add(ChildFilterCollectionNode);        ReduceRedundantFilterCollectionNodes(EntityFilterCollectionNode);        DataEntityInfo.Filter(EntityFilterCollectionNode);    end;    procedure SetSearchText(SearchText: Text)    begin        if SearchText <> '' then            DataEntityExportInfo.Headers.Add('pageSearchString', DelChr(SearchText, '=', '@*'));    end;    procedure AddColumn(FieldCaption: Text; OdataFieldName: Text)    var        FieldInfo: DotNet "Office.FieldInfo";    begin        FieldInfo := FieldInfo.FieldInfo();        FieldInfo.Name := OdataFieldName;        FieldInfo.Label := FieldCaption;        BindingInfo.Fields.Add(FieldInfo);    end;    procedure InsertColumn(Index: Integer; FieldCaption: Text; OdataFieldName: Text)    var        FieldInfo: DotNet "Office.FieldInfo";    begin        FieldInfo := FieldInfo.FieldInfo();        FieldInfo.Name := OdataFieldName;        FieldInfo.Label := FieldCaption;        BindingInfo.Fields.Insert(Index, FieldInfo);    end;    procedure ImposeExcelOnlineRestrictions()    begin        while BindingInfo.Fields.Count() > GetExcelOnlineColumnLimit() do            BindingInfo.Fields.RemoveAt(BindingInfo.Fields.Count() - 1); // If we use excel online, only include supported number of columns    end;    procedure ExportToStream(): InStream    begin        if BindingInfo.Fields.Count() = 0 then            Error(NoColumnsExistErr);        DataEntityExportGenerator := DataEntityExportGenerator.DataEntityExportGenerator();        MemoryStream := MemoryStream.MemoryStream();        DataEntityExportGenerator.GenerateWorkbook(DataEntityExportInfo, MemoryStream);        exit(MemoryStream);    end;    internal procedure ReduceRedundantFilterCollectionNodes(var EntityFilterCollectionNode: DotNet FilterCollectionNode)    var        Type: DotNet FilterCollectionNode;    begin        Type := Type.FilterCollectionNode(); // In order to only iterate over CollectionNode and not BinaryNode when reducing the nodes        while (EntityFilterCollectionNode.Collection.Count() = 1)        do begin            if not EntityFilterCollectionNode.Collection.Item(0).GetType().Equals(Type.GetType()) then                break;            EntityFilterCollectionNode := EntityFilterCollectionNode.Collection.Item(0); // No need to keep collections with just one entry        end;    end;    local procedure InitializeDataEntityExportInfo(TenantWebService: Record "Tenant Web Service")    var        Company: Record Company;        AzureADTenant: Codeunit "Azure AD Tenant";        AuthenticationOverrides: DotNet AuthenticationOverrides;        ConnectionInfo: DotNet ConnectionInfo;        OfficeAppInfo: DotNet OfficeAppInfo;        HostName: Text;        DocumentId: Text;    begin        CreateOfficeAppInfo(OfficeAppInfo);        AuthenticationOverrides := AuthenticationOverrides.AuthenticationOverrides();        AuthenticationOverrides.Tenant := AzureADTenant.GetAadTenantId();        DataEntityExportInfo := DataEntityExportInfo.DataEntityExportInfo();        DataEntityExportInfo.AppReference := OfficeAppInfo;        DataEntityExportInfo.Authentication := AuthenticationOverrides;        ConnectionInfo := ConnectionInfo.ConnectionInfo();        HostName := GetHostName();        if StrPos(HostName, '?') <> 0 then            HostName := CopyStr(HostName, 1, StrPos(HostName, '?') - 1);        ConnectionInfo.HostName := HostName;        DataEntityExportInfo.Connection := ConnectionInfo;        DataEntityExportInfo.Language := LanguageIDToCultureName(GlobalLanguage());        DataEntityExportInfo.EnableDesign := true;        DataEntityExportInfo.RefreshOnOpen := true;        DataEntityExportInfo.DateCreated := CurrentDateTime();        DataEntityExportInfo.GenerationActivityId := Format(SessionId());        DocumentId := Format(CreateGuid(), 0, 4);        DataEntityExportInfo.DocumentId := DocumentId;        Session.LogMessage('0000GYB', StrSubstNo(CreatingExcelDocumentWithIdTxt, DocumentId), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt);        if EnvironmentInformation.IsSaaS() then            DataEntityExportInfo.Headers.Add('BCEnvironment', EnvironmentInformation.GetEnvironmentName());        if Company.Get(TenantWebService.CurrentCompany) then            DataEntityExportInfo.Headers.Add('Company', Format(Company.Id, 0, 4))        else            DataEntityExportInfo.Headers.Add('Company', TenantWebService.CurrentCompany);    end;    local procedure CreateOfficeAppInfo(var OfficeAppInfo: DotNet OfficeAppInfo)  // Note: Keep this in sync with BaseApp - ODataUtility    var        EditinExcelSettings: Record "Edit in Excel Settings";    begin        OfficeAppInfo := OfficeAppInfo.OfficeAppInfo();        if EditinExcelSettings.Get() and EditinExcelSettings."Use Centralized deployments" then begin            OfficeAppInfo.Id := '61bcc63f-b860-4280-8280-3e4fb5ea7726';            OfficeAppInfo.Store := 'EXCatalog';            OfficeAppInfo.StoreType := 'EXCatalog';            OfficeAppInfo.Version := '1.3.0.0';        end else begin            OfficeAppInfo.Id := 'WA104379629';            OfficeAppInfo.Store := 'en-US';            OfficeAppInfo.StoreType := 'OMEX';            OfficeAppInfo.Version := '1.3.0.0';        end;        Session.LogMessage('0000F7M', StrSubstNo(EditInExcelUsageWithCentralizedDeploymentsTxt, EditinExcelSettings."Use Centralized deployments"), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt);    end;    local procedure IsPPE(): Boolean    var        Url: Text;    begin        Url := LowerCase(GetUrl(ClientType::Web));        exit(          (StrPos(Url, 'projectmadeira-test') <> 0) or (StrPos(Url, 'projectmadeira-ppe') <> 0) or          (StrPos(Url, 'financials.dynamics-tie.com') <> 0) or (StrPos(Url, 'financials.dynamics-ppe.com') <> 0) or          (StrPos(Url, 'invoicing.officeppe.com') <> 0) or (StrPos(Url, 'businesscentral.dynamics-tie.com') <> 0) or          (StrPos(Url, 'businesscentral.dynamics-ppe.com') <> 0));    end;    local procedure GetExcelAddinProviderServiceUrl(): Text    begin        if IsPPE() then            exit('https://exceladdinprovider.smb.dynamics-tie.com');        exit('https://exceladdinprovider.smb.dynamics.com');    end;    local procedure GetHostName(): Text    begin        if EnvironmentInformation.IsSaaS() then            exit(GetExcelAddinProviderServiceUrl());        exit(GetUrl(ClientType::Web));    end;    local procedure GetExcelOnlineColumnLimit(): Integer    begin        exit(99);    end;    local procedure LanguageIDToCultureName(LanguageID: Integer): Text    var        CultureInfo: DotNet CultureInfo;    begin        CultureInfo := CultureInfo.GetCultureInfo(LanguageID);        exit(CultureInfo.Name);    end;}