Codeunit 1482 Edit in Excel Impl.
src/System Application/App/Edit in Excel/src/EditinExcelImpl.Codeunit.al313 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.Environment;using System.Integration;using System.Reflection;codeunit 1482 "Edit in Excel Impl."{ Access = Internal; InherentEntitlements = X; InherentPermissions = X; var EditinExcel: Codeunit "Edit in Excel"; EditInExcelTelemetryCategoryTxt: Label 'Edit in Excel', Locked = true; CreateEndpointForObjectTxt: Label 'Creating endpoint for %1 %2.', Locked = true; EditInExcelHandledTxt: Label 'Edit in excel has been handled.', Locked = true; EditInExcelOnlySupportPageWebServicesTxt: Label 'Edit in Excel only support web services created from pages.', Locked = true; EditInExcelInvalidFilterErr: Label 'Certain filters applied on the page are not available in Office, so more rows will be shown compared to Business Central.\ \ Removed filters: %1', Comment = '%1 = The field filters we had to remove because they are not exposed through OData'; DialogTitleTxt: Label 'Export'; ExcelFileNameTxt: Text; XmlByteEncodingTok: Label '_x00%1_%2', Locked = true; XmlByteEncoding2Tok: Label '%1_x00%2_%3', Locked = true; XmlByteEncoding3Tok: Label '%1_%2_%3', Locked = true; procedure EditPageInExcel(PageCaption: Text[240]; PageId: Integer; EditinExcelFilters: Codeunit "Edit in Excel Filters"; FileName: Text) var ServiceName: Text[240]; Handled: Boolean; begin ServiceName := FindOrCreateWorksheetWebService(PageCaption, PageId); ExcelFileNameTxt := FileName; EditinExcel.OnEditInExcelWithFilters(ServiceName, EditinExcelFilters, '', Handled); if Handled then begin Session.LogMessage('0000IG7', EditInExcelHandledTxt, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit; end; GetEndPointAndCreateWorkbookWStructuredFilter(ServiceName, EditinExcelFilters, ''); end; #region JSON FILTER SPECIFIC procedure GetEndPointAndCreateWorkbookWStructuredFilter(ServiceName: Text[250]; EditinExcelFilters: Codeunit "Edit in Excel Filters"; SearchFilter: Text) var TenantWebService: Record "Tenant Web Service"; EditinExcelWorkbook: Codeunit "Edit in Excel Workbook"; begin if (not TenantWebService.Get(TenantWebService."Object Type"::Page, ServiceName)) then Error(EditInExcelOnlySupportPageWebServicesTxt); Session.LogMessage('0000DB6', StrSubstNo(CreateEndpointForObjectTxt, TenantWebService."Object Type", TenantWebService."Object ID"), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); EditinExcelWorkbook.Initialize(TenantWebService."Service Name"); SetupFieldColumnBindings(EditinExcelWorkbook, TenantWebService."Object ID"); EditinExcelWorkbook.SetFilters(EditinExcelFilters); ExportAndDownloadExcelFile(EditinExcelWorkbook.ExportToStream(), TenantWebService); end; local procedure SetupFieldColumnBindings(var EditinExcelWorkbook: Codeunit "Edit in Excel Workbook"; PageNo: Integer) var FieldsTable: Record "Field"; PageControlField: Record "Page Control Field"; PageMetadata: Record "Page Metadata"; DocumentSharing: Codeunit "Document Sharing"; RecordRef: RecordRef; VarFieldRef: FieldRef; VarKeyRef: KeyRef; AddedFields: List of [Integer]; KeyFieldNumber: Integer; DocumentSharingSource: Enum "Document Sharing Source"; begin // Add all fields on the page backed up by a table field PageControlField.SetRange(PageNo, PageNo); PageControlField.SetCurrentKey(Sequence); PageControlField.SetAscending(Sequence, true); EditinExcel.OnBeforePageControlFieldFindSet(PageControlField, PageNo); if PageControlField.FindSet() then repeat if FieldsTable.Get(PageControlField.TableNo, PageControlField.FieldNo) then if not AddedFields.Contains(PageControlField.FieldNo) then begin // Make sure we don't add the same field twice // Add field to Excel EditinExcelWorkbook.AddColumn(FieldsTable."Field Caption", ExternalizeODataObjectName(PageControlField.ControlName)); AddedFields.Add(PageControlField.FieldNo); end; until PageControlField.Next() = 0; PageMetadata.Get(PageNo); RecordRef.Open(PageMetadata.SourceTable); VarKeyRef := RecordRef.KeyIndex(1); for KeyFieldNumber := 1 to VarKeyRef.FieldCount do begin VarFieldRef := VarKeyRef.FieldIndex(KeyFieldNumber); if not AddedFields.Contains(VarFieldRef.Number) then begin // Make sure we don't add the same field twice // Add missing key fields at the beginning EditinExcelWorkbook.InsertColumn(0, VarFieldRef.Caption, ExternalizeODataObjectName(VarFieldRef.Name)); AddedFields.Add(VarFieldRef.Number); end; end; if DocumentSharing.ShareEnabled(DocumentSharingSource::System) then EditinExcelWorkbook.ImposeExcelOnlineRestrictions(); end; #endregion JSON FILTER SPECIFIC local procedure ExportAndDownloadExcelFile(InputStream: InStream; TenantWebService: Record "Tenant Web Service") begin if ExcelFileNameTxt = '' then ExcelFileNameTxt := GenerateExcelFileName(TenantWebService); ExcelFileNameTxt := ExcelFileNameTxt + '.xlsx'; DownloadExcelFile(InputStream, ExcelFileNameTxt); end; local procedure GenerateExcelFileName(TenantWebService: Record "Tenant Web Service") FileName: Text var PageMetadata: Record "Page Metadata"; QueryMetadata: Record "Query Metadata"; CodeunitMetadata: Record "CodeUnit Metadata"; begin case TenantWebService."Object Type" of TenantWebService."Object Type"::Page: if PageMetadata.Get(TenantWebService."Object ID") then FileName := PageMetadata.Caption; TenantWebService."Object Type"::Query: if QueryMetadata.Get(TenantWebService."Object ID") then FileName := QueryMetadata.Caption; TenantWebService."Object Type"::Codeunit: if CodeunitMetadata.Get(TenantWebService."Object ID") then FileName := CodeunitMetadata.Name; end; if FileName = '' then FileName := TenantWebService."Service Name".Replace('_Excel', ''); end; local procedure FindOrCreateWorksheetWebService(PageCaption: Text[240]; PageId: Integer): Text[240] var TenantWebService: Record "Tenant Web Service"; ServiceName: Text[240]; begin // Aligned with how platform finds and creates web services // The function returns the first web service name that matches: // 1. Name is PageCaption_Excel (this allows admin to Publish/Unpublish the web service and be in complete control over whether Edit in Excel works) // 2. Published flag = true (prefer enabled web services) // 3. Any web service for the page // 4. Create a new web service called PageCaption_Excel if NameBeginsWithADigit(PageCaption) then ServiceName := 'WS' + CopyStr(PageCaption, 1, 232) + '_Excel' else ServiceName := CopyStr(PageCaption, 1, 234) + '_Excel'; if TenantWebService.Get(TenantWebService."Object Type"::Page, ServiceName) and (TenantWebService."Object ID" = PageId) then exit(ServiceName); TenantWebService."Object Type" := TenantWebService."Object Type"::Page; TenantWebService."Object ID" := PageId; TenantWebService."Service Name" := ServiceName; TenantWebService.ExcludeFieldsOutsideRepeater := true; TenantWebService.ExcludeNonEditableFlowFields := true; TenantWebService.Published := true; TenantWebService.Insert(true); exit(ServiceName); end; local procedure NameBeginsWithADigit(Name: Text[240]): Boolean begin if Name[1] in ['0' .. '9'] then exit(true); exit(false); end; local procedure DownloadExcelFile(InputStream: InStream; FileName: Text) var DocumentSharing: Codeunit "Document Sharing"; DocumentSharingIntent: Enum "Document Sharing Intent"; DocumentSharingSource: Enum "Document Sharing Source"; begin if DocumentSharing.ShareEnabled(DocumentSharingSource::System) then begin DocumentSharing.Share(FileName, '.xlsx', InputStream, DocumentSharingIntent::Open, DocumentSharingSource::System); exit; end; DownloadFromStream(InputStream, DialogTitleTxt, '', '*.*', FileName); end; procedure ExternalizeODataObjectName(Name: Text) ConvertedName: Text var CurrentPosition: Integer; Convert: DotNet Convert; StartStr: Text; EndStr: Text; ByteValue: DotNet Byte; ConvertedByteValue: Text; IsByteValueUnderscore: Dictionary of [Integer, Boolean]; begin ConvertedName := Name; if NameBeginsWithADigit(ConvertedName[1]) then begin ByteValue := Convert.ToByte(ConvertedName[1]); ConvertedName := CopyStr(ConvertedName, 2); ConvertedName := StrSubstNo(XmlByteEncodingTok, Convert.ToString(ByteValue, 16), ConvertedName); end; // Mimics the behavior of the compiler when converting a field or web service name to OData. CurrentPosition := StrPos(ConvertedName, '%'); while CurrentPosition > 0 do begin ConvertedName := DelStr(ConvertedName, CurrentPosition, 1); ConvertedName := InsStr(ConvertedName, 'Percent', CurrentPosition); CurrentPosition := StrPos(ConvertedName, '%'); end; CurrentPosition := 1; while CurrentPosition <= StrLen(ConvertedName) do begin // Notice in the following line that – (en dash) is not a normal dash (em dash). // We need to handle this here because at least the norwegian translation uses en dash. if ConvertedName[CurrentPosition] in ['''', '+', '–'] then begin if ConvertedName[CurrentPosition] in ['–'] then begin StartStr := CopyStr(ConvertedName, 1, CurrentPosition - 1); EndStr := CopyStr(ConvertedName, CurrentPosition + 1); ConvertedName := StrSubstNo(XmlByteEncoding3Tok, StartStr, 'x2013', EndStr); // length of _x00nn_ minus one that will be added later end else begin ByteValue := Convert.ToByte(ConvertedName[CurrentPosition]); StartStr := CopyStr(ConvertedName, 1, CurrentPosition - 1); EndStr := CopyStr(ConvertedName, CurrentPosition + 1); ConvertedByteValue := Convert.ToString(ByteValue, 16); ConvertedByteValue := ConvertedByteValue.ToUpper(); ConvertedName := StrSubstNo(XmlByteEncoding2Tok, StartStr, ConvertedByteValue, EndStr); end; // length of _x00nn_ minus one that will be added later CurrentPosition += 6; IsByteValueUnderscore.Add(CurrentPosition, true); end else if ConvertedName[CurrentPosition] in [' ', '\', '/', '"', '.', '(', ')', '-', ':'] then if CurrentPosition > 1 then begin // The only cases where we allow 2 underscores in succession is when // we have substituted a symbol with its byte value and when we have an actual underscore // prefixed with a symbol that should be replaced with underscore. // This code below removes duplicate underscores but // needs to not remove underscores that was added via a byte value. if (ConvertedName[CurrentPosition - 1] = '_') and not IsByteValueUnderscore.ContainsKey(CurrentPosition - 1) then begin ConvertedName := DelStr(ConvertedName, CurrentPosition, 1); CurrentPosition -= 1; end else ConvertedName[CurrentPosition] := '_'; end else ConvertedName[CurrentPosition] := '_'; CurrentPosition += 1; end; ConvertedName := DelChr(ConvertedName, '>', '_'); // remove trailing underscore end; [EventSubscriber(ObjectType::Codeunit, Codeunit::"System Action Triggers", OnEditInExcelWithStructuredFilter, '', false, false)] local procedure OnEditInExcelWithStructuredFilterEvent(ServiceName: Text[240]; SearchString: Text; Filter: JsonObject; Payload: JsonObject) var TenantWebService: Record "Tenant Web Service"; EditinExcelFilters: Codeunit "Edit in Excel Filters"; FilterErrors: Dictionary of [Text, Boolean]; Handled: Boolean; begin EditinExcel.OnEditInExcelWithStructuredFilter(ServiceName, Filter, Payload, SearchString, Handled); if Handled then begin Session.LogMessage('0000I43', EditInExcelHandledTxt, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit; end; if not TenantWebService.Get(TenantWebService."Object Type"::Page, ServiceName) then exit; EditinExcelFilters.ReadFromJsonFilters(Filter, Payload, TenantWebService."Object ID", FilterErrors); EditinExcel.OnEditInExcelWithFilters(ServiceName, EditinExcelFilters, SearchString, Handled); if Handled then begin Session.LogMessage('0000IG8', EditInExcelHandledTxt, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit; end; if FilterErrors.Count() > 0 then Message(EditInExcelInvalidFilterErr, FormatFilterErrors(FilterErrors)); GetEndPointAndCreateWorkbookWStructuredFilter(ServiceName, EditinExcelFilters, SearchString); end; local procedure FormatFilterErrors(FilterErrors: Dictionary of [Text, Boolean]): Text var ConcatenatedErrors: Text; ErrorText: Text; begin foreach ErrorText in FilterErrors.Keys() do ConcatenatedErrors := ConcatenatedErrors + ErrorText + ', '; if StrLen(ConcatenatedErrors) > 0 then ConcatenatedErrors := DelStr(ConcatenatedErrors, StrLen(ConcatenatedErrors) - 1); exit(ConcatenatedErrors); end;}