Codeunit 8618 Config. Excel Exchange

Base Application, System.IO

src/Layers/W1/BaseApp/System/RapidStart/ConfigExcelExchange.Codeunit.al897 lines, Copyright (c) Microsoft Corporation. MIT

namespace System.IO;using Microsoft.Finance.Dimension;using System;using System.Reflection;using System.Utilities;using System.Xml;codeunit 8618 "Config. Excel Exchange"{    trigger OnRun()    begin    end;    var        SelectedConfigPackage: Record "Config. Package";        TempSelectedTable: Record "Integer" temporary;        ConfigXMLExchange: Codeunit "Config. XML Exchange";        FileMgt: Codeunit "File Management";        ConfigProgressBar: Codeunit "Config. Progress Bar";        ConfigValidateMgt: Codeunit "Config. Validate Management";        OpenXMLManagement: Codeunit "OpenXML Management";        TypeHelper: Codeunit "Type Helper";        WrkbkReader: DotNet WorkbookReader;        WrkbkWriter: DotNet WorkbookWriter;        WrkShtWriter: DotNet WorksheetWriter;        Worksheet: DotNet Worksheet;        Workbook: DotNet Workbook;        WorkBookPart: DotNet WorkbookPart;        CreateWrkBkFailedErr: Label 'Could not create the Excel workbook.';        WrkShtHelper: DotNet WorksheetHelper;        DataSet: DotNet DataSet;        DataTable: DotNet DataTable;        DataColumn: DotNet DataColumn;        StringBld: DotNet StringBuilder;        id: BigInteger;        HideDialog: Boolean;        FileOnServer: Boolean;        CannotCreateXmlSchemaErr: Label 'Could not create XML Schema.';        CreatingExcelMsg: Label 'Creating Excel worksheet';        VmlDrawingXmlTxt: Label '<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"><o:shapelayout v:ext="edit"><o:idmap v:ext="edit" data="1"/></o:shapelayout><v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202"  path="m,l,21600r21600,l21600,xe"><v:stroke joinstyle="miter"/><v:path gradientshapeok="t" o:connecttype="rect"/></v:shapetype>', Locked = true;        EndXmlTokenTxt: Label '</xml>', Locked = true;        FileExtensionFilterTok: Label 'Excel Files (*.xlsx)|*.xlsx|All Files (*.*)|*.*';        ExcelFileNameTok: Label '*%1.xlsx', Comment = '%1 = String generated from current datetime to make sure file names are unique ';        ExcelFileExtensionTok: Label '.xlsx';        InvalidDataInSheetMsg: Label 'Data in sheet ''%1'' could not be imported, because the sheet has an unexpected format.', Comment = '%1=excel sheet name';        ImportFromExcelMsg: Label 'Import from Excel';        RapidStartTxt: Label 'RapidStart', Locked = true;    procedure ExportExcelFromConfig(var ConfigLine: Record "Config. Line"): Text    var        ConfigPackageTable: Record "Config. Package Table";        ConfigMgt: Codeunit "Config. Management";        FileName: Text;        "Filter": Text;    begin        ConfigLine.FindFirst();        ConfigPackageTable.SetRange("Package Code", ConfigLine."Package Code");        Filter := ConfigMgt.MakeTableFilter(ConfigLine, true);        if Filter <> '' then            ConfigPackageTable.SetFilter("Table ID", Filter);        ConfigPackageTable.SetRange("Dimensions as Columns", true);        if ConfigPackageTable.FindSet() then            repeat                if not (ConfigPackageTable.DimensionPackageDataExist() or (ConfigPackageTable.DimensionFieldsCount() > 0)) then                    ConfigPackageTable.InitDimensionFields();            until ConfigPackageTable.Next() = 0;        ConfigPackageTable.SetRange("Dimensions as Columns");        OnExportExcelFromConfigOnBeforeExportExcel(ConfigLine, ConfigPackageTable);        ExportExcel(FileName, ConfigPackageTable, true, false);        exit(FileName);    end;    procedure ExportExcelFromPackage(ConfigPackage: Record "Config. Package"): Boolean    var        ConfigPackageTable: Record "Config. Package Table";        FileName: Text;    begin        ConfigPackageTable.SetRange("Package Code", ConfigPackage.Code);        exit(ExportExcel(FileName, ConfigPackageTable, false, false));    end;    procedure ExportExcelFromTables(var ConfigPackageTable: Record "Config. Package Table"): Boolean    var        FileName: Text;    begin        exit(ExportExcel(FileName, ConfigPackageTable, false, false));    end;    procedure ExportExcelTemplateFromTables(var ConfigPackageTable: Record "Config. Package Table") Result: Boolean    var        FileName: Text;        IsHandled: Boolean;    begin        IsHandled := false;        OnBeforeExportExcelTemplateFromTables(ConfigPackageTable, Result, IsHandled);        if IsHandled then            exit(Result);        exit(ExportExcel(FileName, ConfigPackageTable, false, true));    end;    procedure ExportExcel(var FileName: Text; var ConfigPackageTable: Record "Config. Package Table"; ExportFromWksht: Boolean; SkipData: Boolean): Boolean    var        TempBlob: Codeunit "Temp Blob";        VmlDrawingPart: DotNet VmlDrawingPart;        TableDefinitionPart: DotNet TableDefinitionPart;        TableParts: DotNet TableParts;        TablePart: DotNet TablePart;        SingleXMLCells: DotNet SingleXmlCells;        XmlTextWriter: DotNet XmlTextWriter;        FileMode: DotNet FileMode;        Encoding: DotNet Encoding;        Caption: Text;        RootElementName: Text;        TempSetupDataFileName: Text;        TempSchemaFileName: Text;        ExcelExportStartMsg: Label 'Export of Excel data started.', Locked = true;        ExcelExportFinishMsg: Label 'Export of Excel data finished. Duration: %1 milliseconds.', Locked = true;        DurationAsInt: BigInteger;        StartTime: DateTime;        DataTableCounter: Integer;    begin        StartTime := CurrentDateTime();        Session.LogMessage('00009QA', ExcelExportStartMsg, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', RapidStartTxt);        OnBeforeExportExcel(ConfigPackageTable, FileName);        Clear(ExportFromWksht); // Obsolete parameter        TempSchemaFileName := CreateSchemaFile(ConfigPackageTable, RootElementName);        TempSetupDataFileName := BuildDataSetForPackageTable(ConfigPackageTable);        CreateBook(TempBlob);        WrkShtHelper := WrkShtHelper.WorksheetHelper(WrkbkWriter.FirstWorksheet.Worksheet);        OpenXMLManagement.ImportSchema(WrkbkWriter, TempSchemaFileName, 1, RootElementName);        OpenXMLManagement.CreateSchemaConnection(WrkbkWriter, TempSetupDataFileName);        DataTableCounter := 1;        if not HideDialog then            ConfigProgressBar.Init(ConfigPackageTable.Count, 1, CreatingExcelMsg);        DataTable := DataSet.Tables.Item(1);        if ConfigPackageTable.FindSet() then            repeat                if IsNull(StringBld) then begin                    StringBld := StringBld.StringBuilder();                    StringBld.Append(VmlDrawingXmlTxt);                end;                ConfigPackageTable.CalcFields("Table Caption");                if not HideDialog then                    ConfigProgressBar.Update(ConfigPackageTable."Table Caption");                // Initialize WorkSheetWriter                Caption := DelChr(ConfigPackageTable."Table Caption", '=', '/');                if id < 1 then begin                    WrkShtWriter := WrkbkWriter.FirstWorksheet;                    WrkShtWriter.Name := Caption;                end else                    WrkShtWriter := WrkbkWriter.AddWorksheet(GetExcelWorksheetName(Caption, Format(ConfigPackageTable."Table ID")));                Worksheet := WrkShtWriter.Worksheet;                // Add and initialize SingleCellTable part                WrkShtWriter.AddSingleCellTablePart();                SingleXMLCells := SingleXMLCells.SingleXmlCells();                Worksheet.WorksheetPart.SingleCellTablePart.SingleXmlCells := SingleXMLCells;                id += 3;                OpenXMLManagement.AddAndInitializeCommentsPart(WrkShtWriter, VmlDrawingPart);                AddPackageAndTableInformation(ConfigPackageTable, SingleXMLCells);                AddAndInitializeTableDefinitionPart(ConfigPackageTable, DataTableCounter, TableDefinitionPart, SkipData);                if not SkipData then                    OpenXMLManagement.CopyDataToExcelTable(WrkShtWriter, DataTable, HideDialog);                DataTableCounter += 2;                OnExportExcelOnBeforeWrkShtWriterCreateTableParts(ConfigPackageTable, DataTableCounter);                TableParts := WrkShtWriter.CreateTableParts(1);                WrkShtHelper.AppendElementToOpenXmlElement(Worksheet, TableParts);                TablePart := WrkShtWriter.CreateTablePart(Worksheet.WorksheetPart.GetIdOfPart(TableDefinitionPart));                WrkShtHelper.AppendElementToOpenXmlElement(TableParts, TablePart);                StringBld.Append(EndXmlTokenTxt);                XmlTextWriter := XmlTextWriter.XmlTextWriter(VmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8);                XmlTextWriter.WriteRaw(StringBld.ToString());                XmlTextWriter.Flush();                XmlTextWriter.Close();                Clear(StringBld);            until ConfigPackageTable.Next() = 0;        FILE.Erase(TempSchemaFileName);        FILE.Erase(TempSetupDataFileName);        OpenXMLManagement.CleanMapInfo(WrkbkWriter.Workbook.WorkbookPart.CustomXmlMappingsPart.MapInfo);        WrkbkWriter.Workbook.Save();        WrkbkWriter.Close();        ClearOpenXmlVariables();        if not HideDialog then            ConfigProgressBar.Close();        if FileName = '' then            FileName :=              StrSubstNo(ExcelFileNameTok, Format(CurrentDateTime, 0, '<Day,2>_<Month,2>_<Year4>_<Hours24>_<Minutes,2>_<Seconds,2>'));        DurationAsInt := CurrentDateTime() - StartTime;        Session.LogMessage('00009QB', StrSubstNo(ExcelExportFinishMsg, DurationAsInt), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', RapidStartTxt);        OnBeforeBLOBExport(TempBlob);        if not FileOnServer then            FileName := FileMgt.BLOBExport(TempBlob, FileName, not HideDialog)        else            FileMgt.BLOBExportToServerFile(TempBlob, FileName);        exit(FileName <> '');    end;    procedure ImportExcelFromConfig(ConfigLine: Record "Config. Line")    var        ConfigPackage: Record "Config. Package";        TempBlob: Codeunit "Temp Blob";    begin        ConfigLine.TestField("Line Type", ConfigLine."Line Type"::Table);        ConfigLine.TestField("Table ID");        if ConfigPackage.Get(ConfigLine."Package Code") and IsFileImportedToBLOB(TempBlob) then            ImportExcel(TempBlob);    end;    procedure ImportExcelFromPackage(): Boolean    var        TempBlob: Codeunit "Temp Blob";        ConfigPackageManagement: Codeunit "Config. Package Management";    begin        if not IsFileImportedToBLOB(TempBlob) then            exit(false);        if GuiAllowed() then            if ConfigPackageManagement.ShowWarningOnImportingBigConfPackageFromExcel(TempBlob.Length()) = Action::Cancel then                exit(false);        exit(ImportExcel(TempBlob));    end;    procedure ImportExcelFromSelectedPackage(PackageCode: Code[20]): Boolean    var        TempBlob: Codeunit "Temp Blob";    begin        SelectedConfigPackage.Get(PackageCode);        if IsFileImportedToBLOB(TempBlob) then            exit(ImportExcel(TempBlob));        exit(false)    end;    procedure SetSelectedTables(var ConfigPackageTable: Record "Config. Package Table")    begin        if ConfigPackageTable.FindSet() then            repeat                TempSelectedTable.Number := ConfigPackageTable."Table ID";                if TempSelectedTable.Insert() then;            until ConfigPackageTable.Next() = 0;    end;    local procedure IsTableSelected(TableId: Integer): Boolean    begin        if TempSelectedTable.IsEmpty() then            exit(true);        exit(TempSelectedTable.Get(TableId));    end;    local procedure IsWorksheetSelected(var TempConfigPackageTable: Record "Config. Package Table" temporary; WrksheetId: Integer): Boolean    begin        TempConfigPackageTable.Reset();        TempConfigPackageTable.SetRange("Processing Order", WrksheetId);        exit(not TempConfigPackageTable.IsEmpty);    end;    local procedure IsImportFromExcelConfirmed(var TempConfigPackageTable: Record "Config. Package Table" temporary): Boolean    var        ConfigPackageImportPreview: Page "Config. Package Import Preview";        ShowDialog, Result, IsHandled : Boolean;    begin        ShowDialog := GuiAllowed() and not HideDialog;        if ReadPackageTableKeysFromExcel(TempConfigPackageTable, ShowDialog) and ShowDialog then begin            OnIsImportFromExcelConfirmedOnAfterReadFromExcel(Result, IsHandled);            if IsHandled then                exit(Result);            ConfigPackageImportPreview.SetData(SelectedConfigPackage.Code, TempConfigPackageTable);            ConfigPackageImportPreview.RunModal();            exit(ConfigPackageImportPreview.IsImportConfirmed());        end;        exit(true);    end;    [TryFunction]    local procedure ReadPackageTableKeysFromExcel(var TempConfigPackageTable: Record "Config. Package Table" temporary; ShowDialog: Boolean)    var        WrkShtReader: DotNet WorksheetReader;        Enumerator: DotNet IEnumerator;        CellData: DotNet CellData;        Window: Dialog;        WrkSheetId: Integer;        SheetCount: Integer;    begin        if ShowDialog then            Window.Open(ImportFromExcelMsg);        WrkSheetId := WrkbkReader.FirstSheetId;        SheetCount := WrkbkReader.Workbook.Sheets.ChildElements.Count + WrkSheetId;        repeat            WrkShtReader := WrkbkReader.GetWorksheetById(Format(WrkSheetId));            Enumerator := WrkShtReader.GetEnumerator();            while NextCellInRow(Enumerator, CellData, 1) do                FillImportPreviewBuffer(TempConfigPackageTable, WrkSheetId, CellData.ColumnNumber, CellData.Value);            WrkSheetId += 1;        until WrkSheetId >= SheetCount;        TempSelectedTable.DeleteAll();        if TempConfigPackageTable.FindFirst() then;        if ShowDialog then            Window.Close();    end;    local procedure FillImportPreviewBuffer(var TempConfigPackageTable: Record "Config. Package Table" temporary; WrkSheetId: Integer; ColumnNo: Integer; Value: Text)    var        ConfigPackage: Record "Config. Package";        ConfigPackageTable: Record "Config. Package Table";    begin        case ColumnNo of            1:                // first column contains Package Code                TempConfigPackageTable."Package Code" := CopyStr(Value, 1, MaxStrLen(TempConfigPackageTable."Package Code"));            3:                // third column contains Table ID                begin                    TempConfigPackageTable."Processing Order" := WrkSheetId;                    Evaluate(TempConfigPackageTable."Table ID", Value);                    TempConfigPackageTable."Delayed Insert" := not ConfigPackage.Get(TempConfigPackageTable."Package Code");                    // New Package flag value                    TempConfigPackageTable.Validated := not ConfigPackageTable.Get(TempConfigPackageTable."Package Code", TempConfigPackageTable."Table ID");                    // New Table flag value                    if IsTableSelected(TempConfigPackageTable."Table ID") then                        TempConfigPackageTable.Insert();                end;        end;    end;    local procedure NextCellInRow(Enumerator: DotNet IEnumerator; CellData: DotNet CellData; RowNo: Integer): Boolean    begin        if Enumerator.MoveNext() then begin            CellData := Enumerator.Current;            exit(CellData.RowNumber = RowNo);        end;    end;    procedure ImportExcel(var TempBlob: Codeunit "Temp Blob") Imported: Boolean    var        TempConfigPackageTable: Record "Config. Package Table" temporary;        WorkBookPart: DotNet WorkbookPart;        InStream: InStream;        XMLSchemaDataFile: Text;        WrkSheetId: Integer;        DataColumnTableId: Integer;        SheetCount: Integer;        ExcelImportStartMsg: Label 'Converting Excel data started.', Locked = true;        ExcelImportFinishMsg: Label 'Converting Excel data finished. Duration: %1 milliseconds. File size: %2.', Locked = true;        DurationAsInt: BigInteger;        StartTime: DateTime;        FileSize: Integer;    begin        TempBlob.CreateInStream(InStream);        FileSize := TempBlob.Length();        WrkbkReader := WrkbkReader.Open(InStream);        if not IsImportFromExcelConfirmed(TempConfigPackageTable) then begin            Clear(WrkbkReader);            exit(false);        end;        StartTime := CurrentDateTime();        Session.LogMessage('00009QC', ExcelImportStartMsg, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', RapidStartTxt);        WorkBookPart := WrkbkReader.Workbook.WorkbookPart;        XMLSchemaDataFile := OpenXMLManagement.ExtractXMLSchema(WorkBookPart);        WrkSheetId := WrkbkReader.FirstSheetId;        SheetCount := WorkBookPart.Workbook.Sheets.ChildElements.Count + WrkSheetId;        DataSet := DataSet.DataSet();        DataSet.ReadXmlSchema(XMLSchemaDataFile);        WrkSheetId := WrkbkReader.FirstSheetId;        DataColumnTableId := 0;        repeat            if IsWorksheetSelected(TempConfigPackageTable, WrkSheetId) then                ReadWorksheetData(WrkSheetId, DataColumnTableId);            WrkSheetId += 1;            DataColumnTableId += 2;        until WrkSheetId >= SheetCount;        Clear(TempBlob);        TempBlob.CreateInStream(InStream);        DataSet.WriteXml(InStream);        ConfigXMLExchange.SetExcelMode(true);        DurationAsInt := CurrentDateTime() - StartTime;        Session.LogMessage('00009QD', StrSubstNo(ExcelImportFinishMsg, DurationAsInt, FileSize), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', RapidStartTxt);        if ConfigXMLExchange.ImportPackageXMLFromStream(InStream) then            Imported := true;        exit(Imported);    end;    local procedure ReadWorksheetData(WrkSheetId: Integer; DataColumnTableId: Integer)    var        TempXMLBuffer: Record "XML Buffer" temporary;        CellData: DotNet CellData;        DataRow: DotNet DataRow;        DataRow2: DotNet DataRow;        Enumerator: DotNet IEnumerator;        Type: DotNet Type;        WrkShtReader: DotNet WorksheetReader;        SheetHeaderRead: Boolean;        ColumnInt: Integer;        ColumnCount: Integer;        TotalColumnCount: Integer;        RowIn: Integer;        CurrentRowIndex: Integer;        RowChanged: Boolean;        FirstDataRow: Integer;        CellValueText: Text;    begin        WrkShtReader := WrkbkReader.GetWorksheetById(Format(WrkSheetId));        if InitColumnMapping(WrkShtReader, TempXMLBuffer) then begin            Enumerator := WrkShtReader.GetEnumerator();            if GetDataTable(DataColumnTableId) then begin                DataColumn := DataTable.Columns.Item(1);                DataColumn.DataType := Type.GetType('System.String');                DataTable.BeginLoadData();                DataRow := DataTable.NewRow();                SheetHeaderRead := false;                DataColumn := DataTable.Columns.Item(1);                RowIn := 1;                ColumnCount := 0;                TotalColumnCount := 0;                CurrentRowIndex := 1;                FirstDataRow := 4;                while Enumerator.MoveNext() do begin                    CellData := Enumerator.Current;                    CellValueText := CellData.Value();                    RowChanged := CurrentRowIndex <> CellData.RowNumber;                    if not SheetHeaderRead then begin // Read config and table information                        if (CellData.RowNumber = 1) and (CellData.ColumnNumber = 1) then begin                            DataRow.Item(1, CellValueText);                            OnReadWorksheetDataOnAfterPackageCodeRead(CellValueText, FirstDataRow);                        end;                        if (CellData.RowNumber = 1) and (CellData.ColumnNumber = 3) then begin                            DataColumn := DataTable.Columns.Item(0);                            DataRow.Item(0, CellValueText);                            DataTable.Rows.Add(DataRow);                            DataColumn := DataTable.Columns.Item(2);                            DataColumn.AllowDBNull(true);                            DataTable := DataSet.Tables.Item(DataColumnTableId + 1);                            ColumnCount := 0;                            TotalColumnCount := DataTable.Columns.Count - 1;                            repeat                                DataColumn := DataTable.Columns.Item(ColumnCount);                                DataColumn.DataType := Type.GetType('System.String');                                ColumnCount += 1;                            until ColumnCount = TotalColumnCount;                            ColumnCount := 0;                            DataRow2 := DataTable.NewRow();                            DataRow2.SetParentRow(DataRow);                            SheetHeaderRead := true;                        end;                    end else begin // Read data rows                        if (RowIn = 1) and (CellData.RowNumber >= FirstDataRow) and (CellData.ColumnNumber = 1) then begin                            TotalColumnCount := ColumnCount;                            ColumnCount := 0;                            RowIn += 1;                            FirstDataRow := CellData.RowNumber;                        end;                        if RowChanged and (CellData.RowNumber > FirstDataRow) and (RowIn <> 1) then begin                            DataTable.Rows.Add(DataRow2);                            DataTable.EndLoadData();                            DataRow2 := DataTable.NewRow();                            DataRow2.SetParentRow(DataRow);                            RowIn += 1;                            ColumnCount := 0;                        end;                        if RowIn <> 1 then                            ColumnInt := CellData.ColumnNumber;                        if TempXMLBuffer.Get(ColumnInt) then begin                            DataColumn := DataTable.Columns.Item(TempXMLBuffer."Parent Entry No.");                            DataColumn.AllowDBNull(true);                            DataRow2.Item(TempXMLBuffer."Parent Entry No.", CellValueText);                        end;                        ColumnCount := CellData.ColumnNumber + 1;                    end;                    CurrentRowIndex := CellData.RowNumber;                end;                // Add the last row                DataTable.Rows.Add(DataRow2);                DataTable.EndLoadData();            end else                Message(InvalidDataInSheetMsg, WrkShtReader.Name);        end;    end;    procedure ClearOpenXmlVariables()    begin        Clear(WrkbkReader);        Clear(WrkbkWriter);        Clear(WrkShtWriter);        Clear(Workbook);        Clear(WorkBookPart);        Clear(WrkShtHelper);    end;    procedure CreateBook(var TempBlob: Codeunit "Temp Blob")    var        InStream: InStream;    begin        TempBlob.CreateInStream(InStream);        WrkbkWriter := WrkbkWriter.Create(InStream);        if IsNull(WrkbkWriter) then            Error(CreateWrkBkFailedErr);        Workbook := WrkbkWriter.Workbook;        WorkBookPart := Workbook.WorkbookPart;    end;    procedure SetHideDialog(NewHideDialog: Boolean)    begin        HideDialog := NewHideDialog;    end;    local procedure CreateSchemaFile(var ConfigPackageTable: Record "Config. Package Table"; var RootElementName: Text): Text    var        ConfigDataSchema: XMLport "Config. Data Schema";        OStream: OutStream;        TempSchemaFile: File;        TempSchemaFileName: Text;    begin        TempSchemaFile.CreateTempFile();        TempSchemaFileName := TempSchemaFile.Name + '.xsd';        TempSchemaFile.Close();        TempSchemaFile.Create(TempSchemaFileName);        TempSchemaFile.CreateOutStream(OStream);        RootElementName := ConfigDataSchema.GetRootElementName();        ConfigDataSchema.SetDestination(OStream);        ConfigDataSchema.SetTableView(ConfigPackageTable);        if not ConfigDataSchema.Export() then            Error(CannotCreateXmlSchemaErr);        TempSchemaFile.Close();        exit(TempSchemaFileName);    end;    local procedure CreateXMLPackage(TempSetupDataFileName: Text; var ConfigPackageTable: Record "Config. Package Table"): Text    begin        Clear(ConfigXMLExchange);        ConfigXMLExchange.SetExcelMode(true);        ConfigXMLExchange.SetCalledFromCode(true);        ConfigXMLExchange.SetPrefixMode(true);        ConfigXMLExchange.ExportPackageXML(ConfigPackageTable, TempSetupDataFileName);        ConfigXMLExchange.SetExcelMode(false);        exit(TempSetupDataFileName);    end;    local procedure CreateTableColumnNames(var ConfigPackageField: Record "Config. Package Field"; var ConfigPackageTable: Record "Config. Package Table"; TableColumns: DotNet TableColumns)    var        "Field": Record "Field";        Dimension: Record Dimension;        RecRef: RecordRef;        FieldRef: FieldRef;        XmlColumnProperties: DotNet XmlColumnProperties;        TableColumn: DotNet TableColumn;        WrkShtWriter2: DotNet WorksheetWriter;        TableColumnName: Text;        ColumnID: Integer;        IsHandled: Boolean;        ShouldRunIteration: Boolean;        ShouldSetCellComment: Boolean;        ColumnNamesRowNo: Integer;    begin        ColumnNamesRowNo := 3;        IsHandled := false;        OnBeforeCreateTableColumnNames(ConfigPackageField, ConfigPackageTable, TypeHelper, ConfigXMLExchange, OpenXMLManagement, IsHandled, ColumnNamesRowNo);        if IsHandled then            exit;        RecRef.Open(ConfigPackageTable."Table ID");        ConfigPackageField.SetCurrentKey("Package Code", "Table ID", "Processing Order");        if ConfigPackageField.FindSet() then begin            ColumnID := 1;            repeat                ShouldRunIteration := TypeHelper.GetField(ConfigPackageField."Table ID", ConfigPackageField."Field ID", Field) or ConfigPackageField.Dimension;                OnCreateTableColumnNamesOnAfterCalcShouldRunIteration(ConfigPackageField, ShouldRunIteration);                if ShouldRunIteration then begin                    TableColumnName := GetTableColumnName(ConfigPackageField, Dimension);                    XmlColumnProperties := WrkShtWriter2.CreateXmlColumnProperties(                        1,                        '/DataList/' + (ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Caption") + 'List') +                        '/' + ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Caption") +                        '/' + ConfigPackageField.GetElementName(),                        WrkShtWriter.XmlDataType2XmlDataValues(                          ConfigXMLExchange.GetXSDType(ConfigPackageTable."Table ID", ConfigPackageField."Field ID")));                    TableColumn := WrkShtWriter.CreateTableColumn(                        ColumnID,                        TableColumnName,                        ConfigXMLExchange.GetElementName(ConfigPackageField."Field Caption"));                    WrkShtHelper.AppendElementToOpenXmlElement(TableColumn, XmlColumnProperties);                    WrkShtHelper.AppendElementToOpenXmlElement(TableColumns, TableColumn);                    WrkShtWriter.SetCellValueText(                      ColumnNamesRowNo, OpenXMLManagement.GetXLColumnID(ColumnID), TableColumnName, WrkShtWriter.DefaultCellDecorator);                    ShouldSetCellComment := not ConfigPackageField.Dimension;                    OnCreateTableColumnNamesOnAfterCalcShouldSetCellComment(ConfigPackageField, ConfigPackageTable, ShouldSetCellComment);                    if ShouldSetCellComment then begin                        FieldRef := RecRef.Field(ConfigPackageField."Field ID");                        OpenXMLManagement.SetCellComment(                          WrkShtWriter, OpenXMLManagement.GetXLColumnID(ColumnID) + Format(ColumnNamesRowNo), ConfigValidateMgt.AddComment(FieldRef));                        StringBld.Append(OpenXMLManagement.CreateCommentVmlShapeXml(ColumnID, ColumnNamesRowNo));                    end;                end;                ColumnID += 1;            until ConfigPackageField.Next() = 0;        end;        RecRef.Close();    end;    local procedure GetTableColumnName(ConfigPackageField: Record "Config. Package Field"; Dimension: Record Dimension) TableColumnName: Text    var        IsHandled: Boolean;    begin        IsHandled := false;        OnBeforeGetTableColumnName(ConfigPackageField, Dimension, TableColumnName, IsHandled);        if IsHandled then            exit(TableColumnName);        if ConfigPackageField.Dimension then            TableColumnName := ConfigPackageField."Field Caption" + ' ' + StrSubstNo('(%1)', Dimension.TableCaption())        else            TableColumnName := ConfigPackageField."Field Caption";        if TableColumnName = '' then            TableColumnName := ConfigPackageField."Field Name";    end;    local procedure AddPackageAndTableInformation(var ConfigPackageTable: Record "Config. Package Table"; var SingleXMLCells: DotNet SingleXmlCells)    var        RecRef: RecordRef;        SingleXMLCell: DotNet SingleXmlCell;        TableCaptionString: Text;    begin        // Add package name        SingleXMLCell := WrkShtWriter.AddSingleXmlCell(id);        WrkShtHelper.AppendElementToOpenXmlElement(SingleXMLCells, SingleXMLCell);        OpenXMLManagement.AddSingleXMLCellProperties(SingleXMLCell, 'A1', '/DataList/' +          (ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Caption") + 'List') + '/' +          ConfigXMLExchange.GetElementName(ConfigPackageTable.FieldName("Package Code")), 1, 1);        WrkShtWriter.SetCellValueText(1, 'A', ConfigPackageTable."Package Code", WrkShtWriter.DefaultCellDecorator);        // Add Table name        RecRef.Open(ConfigPackageTable."Table ID");        TableCaptionString := RecRef.Caption;        RecRef.Close();        WrkShtWriter.SetCellValueText(1, 'B', TableCaptionString, WrkShtWriter.DefaultCellDecorator);        // Add Table id        id += 1;        SingleXMLCell := WrkShtWriter.AddSingleXmlCell(id);        WrkShtHelper.AppendElementToOpenXmlElement(SingleXMLCells, SingleXMLCell);        OpenXMLManagement.AddSingleXMLCellProperties(SingleXMLCell, 'C1', '/DataList/' +          (ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Caption") + 'List') + '/' +          ConfigXMLExchange.GetElementName(ConfigPackageTable.FieldName("Table ID")), 1, 1);        WrkShtWriter.SetCellValueText(1, 'C', Format(ConfigPackageTable."Table ID"), WrkShtWriter.DefaultCellDecorator);    end;    local procedure BuildDataSetForPackageTable(var ConfigPackageTable: Record "Config. Package Table"): Text    var        TempSetupDataFileName: Text;    begin        TempSetupDataFileName := CreateXMLPackage(FileMgt.ServerTempFileName(''), ConfigPackageTable);        DataSet := DataSet.DataSet();        DataSet.ReadXml(TempSetupDataFileName);        exit(TempSetupDataFileName);    end;    local procedure AddAndInitializeTableDefinitionPart(var ConfigPackageTable: Record "Config. Package Table"; DataTableCounter: Integer; var TableDefinitionPart: DotNet TableDefinitionPart; SkipData: Boolean)    var        ConfigPackageField: Record "Config. Package Field";        TableColumns: DotNet TableColumns;        "Table": DotNet Table;        BooleanValue: DotNet BooleanValue;        StringValue: DotNet StringValue;        RowsCount: Integer;        ColumnNameAndRowNo: Code[10];        RowNo: Integer;    begin        TableDefinitionPart := WrkShtWriter.CreateTableDefinitionPart();        ConfigPackageField.Reset();        ConfigPackageField.SetRange("Package Code", ConfigPackageTable."Package Code");        ConfigPackageField.SetRange("Table ID", ConfigPackageTable."Table ID");        ConfigPackageField.SetRange("Include Field", true);        ColumnNameAndRowNo := 'A3:';        RowNo := 3;        OnAddAndInitializeTableDefinitionPartOnBeforeGetDataTable(ConfigPackageTable, DataTableCounter, ColumnNameAndRowNo, RowNo);        DataTable := DataSet.Tables.Item(DataTableCounter);        id += 1;        if SkipData then            RowsCount := 1        else            RowsCount := DataTable.Rows.Count();        Table := WrkShtWriter.CreateTable(id);        Table.TotalsRowShown := BooleanValue.BooleanValue(false);        Table.Reference :=          StringValue.StringValue(            ColumnNameAndRowNo + OpenXMLManagement.GetXLColumnID(ConfigPackageField.Count) + Format(RowsCount + RowNo));        Table.Name := StringValue.StringValue('Table' + Format(id));        Table.DisplayName := StringValue.StringValue('Table' + Format(id));        OpenXMLManagement.AppendAutoFilter(Table);        TableColumns := WrkShtWriter.CreateTableColumns(ConfigPackageField.Count);        CreateTableColumnNames(ConfigPackageField, ConfigPackageTable, TableColumns);        WrkShtHelper.AppendElementToOpenXmlElement(Table, TableColumns);        OpenXMLManagement.AppendTableStyleInfo(Table);        TableDefinitionPart.Table := Table;    end;    [TryFunction]    local procedure GetDataTable(TableId: Integer)    begin        DataTable := DataSet.Tables.Item(TableId);    end;    local procedure InitColumnMapping(WrkShtReader: DotNet WorksheetReader; var TempXMLBuffer: Record "XML Buffer" temporary): Boolean    var        "Table": DotNet Table;        TableColumn: DotNet TableColumn;        Enumerable: DotNet IEnumerable;        Enumerator: DotNet IEnumerator;        XmlColumnProperties: DotNet XmlColumnProperties;        TableStartColumnIndex: Integer;        Index: Integer;    begin        TempXMLBuffer.DeleteAll();        if not OpenXMLManagement.FindTableDefinition(WrkShtReader, Table) then            exit(false);        TableStartColumnIndex := GetTableStartColumnIndex(Table);        Index := 0;        Enumerable := Table.TableColumns;        Enumerator := Enumerable.GetEnumerator();        while Enumerator.MoveNext() do begin            TableColumn := Enumerator.Current;            XmlColumnProperties := TableColumn.XmlColumnProperties();            if not IsNull(XmlColumnProperties) then                // identifies column to xsd mapping.                if not IsNull(XmlColumnProperties.XPath) then                    InsertXMLBuffer(Index + TableStartColumnIndex, TempXMLBuffer);            Index += 1;        end;        // RowCount > 2 means sheet has datarow(s)        exit(WrkShtReader.RowCount > 2);    end;    local procedure GetTableStartColumnIndex("Table": DotNet Table): Integer    var        String: DotNet String;        Index: Integer;        Length: Integer;        ColumnIndex: Integer;    begin        // <x:table id="5" ... ref="A3:E6" ...>        // table.Reference = "A3:E6" (A3 - top left table corner, E6 - bottom right corner)        // we convert "A" - to column index        String := Table.Reference.Value();        Length := String.IndexOf(':');        String := DelChr(String.Substring(0, Length), '=', '0123456789');        Length := String.Length - 1;        for Index := 0 to Length do            ColumnIndex += (String.Chars(Index) - 64) + Index * 26;        exit(ColumnIndex);    end;    local procedure InsertXMLBuffer(ColumnIndex: Integer; var TempXMLBuffer: Record "XML Buffer" temporary)    begin        TempXMLBuffer.Init();        TempXMLBuffer."Entry No." := ColumnIndex; // column index in table definition        TempXMLBuffer."Parent Entry No." := TempXMLBuffer.Count(); // column index in dataset        TempXMLBuffer.Insert();    end;    procedure SetFileOnServer(NewFileOnServer: Boolean)    begin        FileOnServer := NewFileOnServer;    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeExportExcel(var ConfigPackageTable: Record "Config. Package Table"; var FileName: Text)    begin    end;    local procedure GetExcelWorksheetName(Caption: Text; TableID: Text): Text    var        WorksheetNameMaxLen: Integer;    begin        // maximum Worksheet Name length in Excel        WorksheetNameMaxLen := 31;        Caption := CopyStr(TableID + ' ' + Caption, 1, WorksheetNameMaxLen);        exit(Caption);    end;    local procedure IsFileImportedToBLOB(var TempBlob: Codeunit "Temp Blob"): Boolean    var        IsHandled: Boolean;    begin        OnImportExcelFile(TempBlob, IsHandled);        if IsHandled then            exit(true);        exit(FileMgt.BLOBImportWithFilter(TempBlob, ImportFromExcelMsg, '', FileExtensionFilterTok, ExcelFileExtensionTok) <> '');    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeBLOBExport(var TempBlob: Codeunit "Temp Blob")    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeCreateTableColumnNames(var ConfigPackageField: Record "Config. Package Field"; var ConfigPackageTable: Record "Config. Package Table"; var TypeHelper: Codeunit "Type Helper"; var ConfigXMLExchange: Codeunit "Config. XML Exchange"; var OpenXMLManagement: Codeunit "OpenXML Management"; var IsHandled: Boolean; var ColumnNamesRowNo: Integer)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeExportExcelTemplateFromTables(var ConfigPackageTable: Record "Config. Package Table"; var Result: Boolean; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeGetTableColumnName(ConfigPackageField: Record "Config. Package Field"; Dimension: Record Dimension; var TableColumnName: Text; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnCreateTableColumnNamesOnAfterCalcShouldRunIteration(var ConfigPackageField: Record "Config. Package Field"; var ShouldRunIteration: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnCreateTableColumnNamesOnAfterCalcShouldSetCellComment(var ConfigPackageField: Record "Config. Package Field"; var ConfigPackageTable: Record "Config. Package Table"; var ShouldSetCellComment: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnExportExcelFromConfigOnBeforeExportExcel(var ConfigLine: Record "Config. Line"; var ConfigPackageTable: Record "Config. Package Table")    begin    end;    [IntegrationEvent(false, false)]    local procedure OnImportExcelFile(var TempBlob: Codeunit "Temp Blob"; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnIsImportFromExcelConfirmedOnAfterReadFromExcel(var Result: Boolean; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnAddAndInitializeTableDefinitionPartOnBeforeGetDataTable(var ConfigPackageTable: Record "Config. Package Table"; var DataTableCounter: Integer; var ColumnNameAndRowNo: Code[10]; var RowNo: Integer)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnExportExcelOnBeforeWrkShtWriterCreateTableParts(var ConfigPackageTable: Record "Config. Package Table"; var DataTableCounter: Integer)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnReadWorksheetDataOnAfterPackageCodeRead(CellValueText: Text; var FirstDataRow: Integer)    begin    end;}