Table 370 Excel Buffer

Base Application, System.IO

src/Layers/W1/BaseApp/System/DataExchange/ExcelBuffer.Table.al1,404 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.IO;using Microsoft.EServices.EDocument;using Microsoft.Utilities;using System;using System.Integration;using System.Reflection;using System.Utilities;using System.Xml;table 370 "Excel Buffer"{    Caption = 'Excel Buffer';    ReplicateData = false;    DataClassification = CustomerContent;    fields    {        field(1; "Row No."; Integer)        {            Caption = 'Row No.';            DataClassification = SystemMetadata;            trigger OnValidate()            begin                xlRowID := '';                if "Row No." <> 0 then                    xlRowID := Format("Row No.");            end;        }        field(2; xlRowID; Text[10])        {            Caption = 'xlRowID';            DataClassification = SystemMetadata;        }        field(3; "Column No."; Integer)        {            Caption = 'Column No.';            DataClassification = SystemMetadata;            trigger OnValidate()            var                x: Integer;                i: Integer;                y: Integer;                c: Char;                t: Text[30];            begin                xlColID := '';                x := "Column No.";                while x > 26 do begin                    y := x mod 26;                    if y = 0 then                        y := 26;                    c := 64 + y;                    i := i + 1;                    t[i] := c;                    x := (x - y) div 26;                end;                if x > 0 then begin                    c := 64 + x;                    i := i + 1;                    t[i] := c;                end;                for x := 1 to i do                    xlColID[x] := t[1 + i - x];            end;        }        field(4; xlColID; Text[10])        {            Caption = 'xlColID';            DataClassification = SystemMetadata;        }        field(5; "Cell Value as Text"; Text[250])        {            Caption = 'Cell Value as Text';            DataClassification = SystemMetadata;        }        field(6; Comment; Text[250])        {            Caption = 'Comment';            DataClassification = SystemMetadata;        }        field(7; Formula; Text[250])        {            Caption = 'Formula';            DataClassification = SystemMetadata;        }        field(8; Bold; Boolean)        {            Caption = 'Bold';            DataClassification = SystemMetadata;        }        field(9; Italic; Boolean)        {            Caption = 'Italic';            DataClassification = SystemMetadata;        }        field(10; Underline; Boolean)        {            Caption = 'Underline';            DataClassification = SystemMetadata;        }        field(11; NumberFormat; Text[30])        {            Caption = 'NumberFormat';            DataClassification = SystemMetadata;        }        field(12; Formula2; Text[250])        {            Caption = 'Formula2';            DataClassification = SystemMetadata;        }        field(13; Formula3; Text[250])        {            Caption = 'Formula3';            DataClassification = SystemMetadata;        }        field(14; Formula4; Text[250])        {            Caption = 'Formula4';            DataClassification = SystemMetadata;        }        field(15; "Cell Type"; Option)        {            Caption = 'Cell Type';            DataClassification = SystemMetadata;            OptionCaption = 'Number,Text,Date,Time';            OptionMembers = Number,Text,Date,Time;        }        field(16; "Double Underline"; Boolean)        {            Caption = 'Double Underline';            DataClassification = SystemMetadata;        }        field(17; "Cell Value as Blob"; Blob)        {            Caption = 'Cell Value as Blob';            DataClassification = SystemMetadata;        }    }    keys    {        key(Key1; "Row No.", "Column No.")        {            Clustered = true;        }    }    fieldgroups    {    }    var        TempInfoExcelBuf: Record "Excel Buffer" temporary;        FileManagement: Codeunit "File Management";        OpenXMLManagement: Codeunit "OpenXML Management";        XlWrkBkWriter: DotNet WorkbookWriter;        XlWrkBkReader: DotNet WorkbookReader;        XlWrkShtWriter: DotNet WorksheetWriter;        XlWrkShtReader: DotNet WorksheetReader;        StringBld: DotNet StringBuilder;        RangeStartXlRow: Text[30];        RangeStartXlCol: Text[30];        RangeEndXlRow: Text[30];        RangeEndXlCol: Text[30];        FileNameServer: Text;        FriendlyName: Text;        CurrentRow: Integer;        CurrentCol: Integer;        UseInfoSheet: Boolean;        ErrorMessage: Text;        ReadDateTimeInUtcDate: Boolean;        FileNameMissingErr: Label 'You must enter a file name.';        WorksheetNameMissingErr: Label 'You must enter an Excel worksheet name.', Comment = '{Locked="Excel"}';#pragma warning disable AA0470        FileNotExistErr: Label 'The file %1 does not exist.';        WorksheetNotExistErr: Label 'The Excel worksheet %1 does not exist.', Comment = '{Locked="Excel"}';#pragma warning restore AA0470        CreatingExcelWorksheetTxt: Label 'Creating Excel worksheet...\\', Comment = '{Locked="Excel"}';        PageTxt: Label 'Page';        ReadingExcelWorksheetTxt: Label 'Reading Excel worksheet...\\', Comment = '{Locked="Excel"}';        BoldTokenTok: Label '&B';        DateTokenTok: Label '&D';        PageNumberTokenTok: Label '&P';        A1CellTok: Label 'A1';        SumIfTok: Label 'SUMIF';        NATok: Label '#N/A';        GLAccTok: Label 'GLAcc', Comment = 'Used to define an Excel range name. You must refer to Excel rules to change this term.', Locked = true;        PeriodTok: Label 'Period', Comment = 'Used to define an Excel range name. You must refer to Excel rules to change this term.', Locked = true;        BudgetTxt: Label 'Budget';        CostAccTok: Label 'CostAcc', Locked = true, Comment = 'Used to define an Excel range name. You must refer to Excel rules to change this term.';        InformationTxt: Label 'Information';        ExcelFilesFilterTxt: Label 'Excel Files (*.xls*)|*.xls*|All Files (*.*)|*.*', Comment = '{Split=r''\|\*\..{1,4}\|?''}{Locked="Excel"}';        OperationCanceledTxt: Label 'The operation was canceled.';        ExcelWorkbookCreateErr: Label 'Could not create the Excel workbook.', Comment = '{Locked="Excel"}';#pragma warning disable AA0470        GlobalVariableNotIncludedErr: Label 'Global variable %1 is not included for test.';#pragma warning restore AA0470        CellTypeNotSetErr: Label 'Cell type has not been set.';#pragma warning disable AA0470        SavingDocumentMsg: Label 'Saving the following document: %1.';#pragma warning restore AA0470        ExcelFileExtensionTok: Label '.xlsx', Locked = true;        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;        CellNotFoundErr: Label 'Cell %1 not found.', Comment = '%1 - cell name';    procedure SetReadDateTimeInUtcDate(NewValue: Boolean)    begin        ReadDateTimeInUtcDate := NewValue;    end;    [Scope('OnPrem')]    procedure CopySheet(SheetName: Text; ClonedSheetName: Text; ReferenceSheetName: Text; PasteBefore: Boolean)    begin        XlWrkBkWriter.CopySheet(SheetName, ClonedSheetName, ReferenceSheetName, PasteBefore);    end;    [Scope('OnPrem')]    procedure DeleteWorksheet(SheetName: Text)    begin        XlWrkBkWriter.DeleteWorksheet(SheetName);    end;    [Scope('OnPrem')]    procedure GetSheetsCount(): Integer    begin        exit(XlWrkBkWriter.SheetsCount);    end;    procedure EnterCellByCellName(CellName: Text; CellValueAsText: Text[250])    var        CellPosition: DotNet CellPosition;        RowInt: Integer;        ColumnInt: Integer;    begin        CellPosition := XlWrkBkWriter.GetCellPosition(CellName);        if IsNull(CellPosition) then begin            CloseBook();            Error(CellNotFoundErr, CellName);        end;        RowInt := CellPosition.Row;        ColumnInt := CellPosition.Column;        if Get(RowInt, ColumnInt) then begin            "Cell Value as Text" := CellValueAsText;            Modify();        end else begin            Init();            Validate("Row No.", RowInt);            Validate("Column No.", ColumnInt);            "Cell Value as Text" := CellValueAsText;            Insert();        end;    end;    [Scope('OnPrem')]    procedure SetActiveWriterSheet(SheetName: Text)    begin        if SheetName = '' then            exit;        if XlWrkBkWriter.HasWorksheet(SheetName) then            XlWrkShtWriter := XlWrkBkWriter.GetWorksheetByName(SheetName)        else begin            CloseBook();            Error(WorksheetNotExistErr, SheetName);        end;    end;    [Scope('OnPrem')]    procedure GetWriterSheetNameByNumber(SheetNo: Integer): Text    begin        if XlWrkBkWriter.HasWorksheet(SheetNo) then            exit(XlWrkBkWriter.GetWorksheetNameById(Format(SheetNo)))        else begin            CloseBook();            Error(WorksheetNotExistErr, SheetNo);        end;    end;    procedure CreateNewBook(SheetName: Text[250])    begin        CreateBook('', SheetName);    end;    [Scope('OnPrem')]    procedure CreateBook(FileName: Text; SheetName: Text)    begin        if SheetName = '' then            Error(WorksheetNameMissingErr);        if FileName = '' then            FileNameServer := FileManagement.ServerTempFileName('xlsx')        else begin            if Exists(FileName) then                Erase(FileName);            FileNameServer := FileName;        end;        FileManagement.IsAllowedPath(FileNameServer, false);        XlWrkBkWriter := XlWrkBkWriter.Create(FileNameServer);        if IsNull(XlWrkBkWriter) then            Error(ExcelWorkbookCreateErr);        XlWrkShtWriter := XlWrkBkWriter.FirstWorksheet;        if SheetName <> '' then            XlWrkShtWriter.Name := SheetName;        OpenXMLManagement.SetupWorksheetHelper(XlWrkBkWriter);    end;    procedure GetValueByCellName(CellName: Text): Text    var        CellPosition: DotNet CellPosition;        RowInt: Integer;        ColumnInt: Integer;    begin        CellPosition := CellPosition.CellPosition(CellName);        RowInt := CellPosition.Row;        ColumnInt := CellPosition.Column;        if Get(RowInt, ColumnInt) then            exit("Cell Value as Text");    end;    [Scope('OnPrem')]    procedure GetNextColumnCellName(CellName: Text[30]): Text    begin        if not IsNull(XlWrkShtReader) then            exit(XlWrkShtReader.GetNextColumnCellName(CellName));        exit(XlWrkShtWriter.GetNextColumnCellName(CellName));    end;    [Scope('OnPrem')]    procedure GetNextRowCellName(CellName: Text[30]): Text    begin        if not IsNull(XlWrkShtReader) then            exit(XlWrkShtReader.GetNextRowCellName(CellName));        exit(XlWrkShtWriter.GetNextRowCellName(CellName));    end;    [Scope('OnPrem')]    procedure OpenBook(FileName: Text; SheetName: Text)    begin        if FileName = '' then            Error(FileNameMissingErr);        if SheetName = '' then            Error(WorksheetNameMissingErr);        if SheetName = 'G/L Account' then            SheetName := 'GL Account';        FileManagement.IsAllowedPath(FileName, false);        XlWrkBkReader := XlWrkBkReader.Open(FileName);        if XlWrkBkReader.HasWorksheet(SheetName) then            XlWrkShtReader := XlWrkBkReader.GetWorksheetByName(SheetName)        else begin            CloseBook();            Error(WorksheetNotExistErr, SheetName);        end;    end;    procedure OpenBookStream(FileStream: InStream; SheetName: Text): Text    begin        if SheetName = '' then            exit(WorksheetNameMissingErr);        if SheetName = 'G/L Account' then            SheetName := 'GL Account';        XlWrkBkReader := XlWrkBkReader.Open(FileStream);        if XlWrkBkReader.HasWorksheet(SheetName) then            XlWrkShtReader := XlWrkBkReader.GetWorksheetByName(SheetName)        else begin            CloseBook();            ErrorMessage := StrSubstNo(WorksheetNotExistErr, SheetName);            exit(ErrorMessage);        end;    end;    [Scope('OnPrem')]    procedure OpenBookForUpdate(FileName: Text)    begin        FileNameServer := FileName;        FileManagement.IsAllowedPath(FileName, false);        XlWrkBkWriter := XlWrkBkWriter.Open(FileNameServer);    end;    [Scope('OnPrem')]    procedure UpdateBook(FileName: Text; SheetName: Text)    begin        UpdateBookExcel(FileName, SheetName, true);    end;    [Scope('OnPrem')]    procedure UpdateBookExcel(FileName: Text; SheetName: Text; PreserveDataOnUpdate: Boolean)    begin        if FileName = '' then            Error(FileNameMissingErr);        if SheetName = '' then            Error(WorksheetNameMissingErr);        FileNameServer := FileName;        FileManagement.IsAllowedPath(FileName, false);        XlWrkBkWriter := XlWrkBkWriter.Open(FileNameServer);        OnUpdateBookExcelOnAfterXlWrkBkWriterOpen(Rec, FileName, SheetName);        if XlWrkBkWriter.HasWorksheet(SheetName) then begin            XlWrkShtWriter := XlWrkBkWriter.GetWorksheetByName(SheetName);            // Set PreserverDataOnUpdate to false if the sheet writer should clear all empty cells            // in which NAV does not have new data. Notice that the sheet writer will only clear Excel            // cells that are addressed by the writer. All other cells will be left unmodified.            XlWrkShtWriter.PreserveDataOnUpdate := PreserveDataOnUpdate;            OpenXMLManagement.SetupWorksheetHelper(XlWrkBkWriter);        end else begin            CloseBook();            Error(WorksheetNotExistErr, SheetName);        end;    end;    procedure UpdateBookStream(var ExcelStream: InStream; SheetName: Text; PreserveDataOnUpdate: Boolean)    begin        FileNameServer := FileManagement.InstreamExportToServerFile(ExcelStream, 'xlsx');        UpdateBookExcel(FileNameServer, SheetName, PreserveDataOnUpdate);    end;    procedure CloseBook()    begin        if not IsNull(XlWrkBkWriter) then begin            XlWrkBkWriter.ClearFormulaCalculations();            XlWrkBkWriter.ValidateDocument();            XlWrkBkWriter.Close();            Clear(XlWrkShtWriter);            Clear(XlWrkBkWriter);        end;        if not IsNull(XlWrkBkReader) then begin            Clear(XlWrkShtReader);            Clear(XlWrkBkReader);        end;    end;    procedure SelectOrAddSheet(NewSheetName: Text)    begin        if NewSheetName = '' then            exit;        if IsNull(XlWrkBkWriter) then            Error(ExcelWorkbookCreateErr);        if XlWrkBkWriter.HasWorksheet(NewSheetName) then            XlWrkShtWriter := XlWrkBkWriter.GetWorksheetByName(NewSheetName)        else            XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(NewSheetName);    end;    procedure SetActiveReaderSheet(NewSheetName: Text)    begin        if NewSheetName = '' then            exit;        if XlWrkBkReader.HasWorksheet(NewSheetName) then            XlWrkShtReader := XlWrkBkReader.GetWorksheetByName(NewSheetName)        else begin            CloseBook();            Error(WorksheetNotExistErr, NewSheetName);        end;    end;    procedure WriteSheet(ReportHeader: Text; CompanyName2: Text; UserID2: Text)    var        TypeHelper: Codeunit "Type Helper";        OrientationValues: DotNet OrientationValues;        XmlTextWriter: DotNet XmlTextWriter;        FileMode: DotNet FileMode;        Encoding: DotNet Encoding;        VmlDrawingPart: DotNet VmlDrawingPart;        IsHandled: Boolean;    begin        XlWrkShtWriter.AddPageSetup(OrientationValues.Landscape, 9); // 9 - default value for Paper Size - A4        if ReportHeader <> '' then            XlWrkShtWriter.AddHeader(              true,              StrSubstNo('%1%2%1%3%4', GetExcelReference(1), ReportHeader, TypeHelper.LFSeparator(), CompanyName2));        XlWrkShtWriter.AddHeader(          false,          StrSubstNo('%1%3%4%3%5 %2', GetExcelReference(2), GetExcelReference(3), TypeHelper.LFSeparator(), UserID2, PageTxt));        IsHandled := false;        OnWriteSheetOnBeforeAddAndInitializeCommentsPart(Rec, IsHandled);        if not IsHandled then            OpenXMLManagement.AddAndInitializeCommentsPart(XlWrkShtWriter, VmlDrawingPart);        StringBld := StringBld.StringBuilder();        StringBld.Append(VmlDrawingXmlTxt);        WriteAllToCurrentSheet(Rec);        StringBld.Append(EndXmlTokenTxt);        IsHandled := false;        OnWriteSheetOnBeforeUseXmlTextWriter(Rec, IsHandled);        if not IsHandled then begin            XmlTextWriter := XmlTextWriter.XmlTextWriter(VmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8);            XmlTextWriter.WriteRaw(StringBld.ToString());            XmlTextWriter.Flush();            XmlTextWriter.Close();        end;        if UseInfoSheet then            if not TempInfoExcelBuf.IsEmpty() then begin                SelectOrAddSheet(InformationTxt);                WriteAllToCurrentSheet(TempInfoExcelBuf);            end;    end;    procedure WriteAllToCurrentSheet(var ExcelBuffer: Record "Excel Buffer")    var        ExcelBufferDialogMgt: Codeunit "Excel Buffer Dialog Management";        RecNo: Integer;        TotalRecNo: Integer;        LastUpdate: DateTime;    begin        if ExcelBuffer.IsEmpty() then            exit;        ExcelBufferDialogMgt.Open(CreatingExcelWorksheetTxt);        LastUpdate := CurrentDateTime;        TotalRecNo := ExcelBuffer.Count();        if ExcelBuffer.FindSet() then            repeat                RecNo := RecNo + 1;                if not UpdateProgressDialog(ExcelBufferDialogMgt, LastUpdate, RecNo, TotalRecNo) then begin                    CloseBook();                    Error(OperationCanceledTxt)                end;                if ExcelBuffer.Formula = '' then                    WriteCellValueInternal(ExcelBuffer)                else                    WriteCellFormula(ExcelBuffer)            until ExcelBuffer.Next() = 0;        ExcelBufferDialogMgt.Close();    end;    procedure WriteCellValue(ExcelBuffer: Record "Excel Buffer")    begin        WriteCellValueInternal(ExcelBuffer);    end;    local procedure WriteCellValueInternal(var ExcelBuffer: Record "Excel Buffer")    var        Decorator: DotNet CellDecorator;        RecInStream: Instream;        CellTextValue: Text;    begin        GetCellDecorator(ExcelBuffer.Bold, ExcelBuffer.Italic, ExcelBuffer.Underline, ExcelBuffer."Double Underline", Decorator);        CellTextValue := ExcelBuffer."Cell Value as Text";        if ExcelBuffer."Cell Value as Blob".HasValue() then begin            ExcelBuffer.CalcFields("Cell Value as Blob");            ExcelBuffer."Cell Value as Blob".CreateInStream(RecInStream, TextEncoding::Windows);            RecInStream.ReadText(CellTextValue);        end;        OnWriteCellValueOnBeforeSetCellValue(Rec, CellTextValue);        case ExcelBuffer."Cell Type" of            ExcelBuffer."Cell Type"::Number:                XlWrkShtWriter.SetCellValueNumber(ExcelBuffer."Row No.", ExcelBuffer.xlColID, CellTextValue, ExcelBuffer.NumberFormat, Decorator);            ExcelBuffer."Cell Type"::Text:                XlWrkShtWriter.SetCellValueText(ExcelBuffer."Row No.", ExcelBuffer.xlColID, CellTextValue, Decorator);            ExcelBuffer."Cell Type"::Date:                XlWrkShtWriter.SetCellValueDate(ExcelBuffer."Row No.", ExcelBuffer.xlColID, CellTextValue, ExcelBuffer.NumberFormat, Decorator);            ExcelBuffer."Cell Type"::Time:                XlWrkShtWriter.SetCellValueTime(ExcelBuffer."Row No.", ExcelBuffer.xlColID, CellTextValue, ExcelBuffer.NumberFormat, Decorator);            else                Error(CellTypeNotSetErr)        end;        if ExcelBuffer.Comment <> '' then begin            OpenXMLManagement.SetCellComment(XlWrkShtWriter, StrSubstNo('%1%2', ExcelBuffer.xlColID, ExcelBuffer."Row No."), ExcelBuffer.Comment);            StringBld.Append(OpenXMLManagement.CreateCommentVmlShapeXml(ExcelBuffer."Column No.", ExcelBuffer."Row No."));        end;    end;    procedure WriteCellFormula(ExcelBuffer: Record "Excel Buffer")    var        Decorator: DotNet CellDecorator;        IsHandled: Boolean;    begin        IsHandled := false;        OnBeforeWriteCellFormula(Rec, ExcelBuffer, IsHandled);        if IsHandled then            exit;        GetCellDecorator(ExcelBuffer.Bold, ExcelBuffer.Italic, ExcelBuffer.Underline, ExcelBuffer."Double Underline", Decorator);        XlWrkShtWriter.SetCellFormula(ExcelBuffer."Row No.", ExcelBuffer.xlColID, ExcelBuffer.GetFormula(), ExcelBuffer.NumberFormat, Decorator);    end;    local procedure GetCellDecorator(IsBold: Boolean; IsItalic: Boolean; IsUnderlined: Boolean; IsDoubleUnderlined: Boolean; var Decorator: DotNet CellDecorator)    begin        if IsBold and IsItalic then begin            if IsDoubleUnderlined then begin                Decorator := XlWrkShtWriter.DefaultBoldItalicDoubleUnderlinedCellDecorator;                exit;            end;            if IsUnderlined then begin                Decorator := XlWrkShtWriter.DefaultBoldItalicUnderlinedCellDecorator;                exit;            end;        end;        if IsBold and IsItalic then begin            Decorator := XlWrkShtWriter.DefaultBoldItalicCellDecorator;            exit;        end;        if IsBold then begin            if IsDoubleUnderlined then begin                Decorator := XlWrkShtWriter.DefaultBoldDoubleUnderlinedCellDecorator;                exit;            end;            if IsUnderlined then begin                Decorator := XlWrkShtWriter.DefaultBoldUnderlinedCellDecorator;                exit;            end;        end;        if IsBold then begin            Decorator := XlWrkShtWriter.DefaultBoldCellDecorator;            exit;        end;        if IsItalic then begin            if IsDoubleUnderlined then begin                Decorator := XlWrkShtWriter.DefaultItalicDoubleUnderlinedCellDecorator;                exit;            end;            if IsUnderlined then begin                Decorator := XlWrkShtWriter.DefaultItalicUnderlinedCellDecorator;                exit;            end;        end;        if IsItalic then begin            Decorator := XlWrkShtWriter.DefaultItalicCellDecorator;            exit;        end;        if IsDoubleUnderlined then            Decorator := XlWrkShtWriter.DefaultDoubleUnderlinedCellDecorator        else            if IsUnderlined then                Decorator := XlWrkShtWriter.DefaultUnderlinedCellDecorator            else                Decorator := XlWrkShtWriter.DefaultCellDecorator;    end;    procedure SetColumnWidth(ColName: Text[10]; NewColWidth: Decimal)    begin        if not IsNull(XlWrkShtWriter) then            XlWrkShtWriter.SetColumnWidth(ColName, NewColWidth);    end;    [Scope('OnPrem')]    procedure CreateRangeName(RangeName: Text[30]; FromColumnNo: Integer; FromRowNo: Integer)    var        TempExcelBuf: Record "Excel Buffer" temporary;        ToxlRowID: Text[10];    begin        SetCurrentKey("Row No.", "Column No.");        if Find('+') then            ToxlRowID := xlRowID;        TempExcelBuf.Validate("Row No.", FromRowNo);        TempExcelBuf.Validate("Column No.", FromColumnNo);        XlWrkShtWriter.AddRange(          RangeName,          GetExcelReference(4) + TempExcelBuf.xlColID + GetExcelReference(4) + TempExcelBuf.xlRowID +          ':' +          GetExcelReference(4) + TempExcelBuf.xlColID + GetExcelReference(4) + ToxlRowID);    end;    procedure ReadSheet()    begin        ReadSheetContinous('', true);    end;    procedure ReadSheetContinous(SheetName: Text; CloseBookOnCompletion: Boolean)    var        ColumnList: List of [Integer];        RowList: List of [Integer];    begin        ReadSheetContinous(SheetName, CloseBookOnCompletion, ColumnList, RowList, 0);    end;    procedure ReadSheetContinous(SheetName: Text; CloseBookOnCompletion: Boolean; ColumnList: List of [Integer]; RowList: List of [Integer]; MaxRowNo: Integer)    var        ExcelBufferDialogMgt: Codeunit "Excel Buffer Dialog Management";        CellData: DotNet CellData;        Enumerator: DotNet IEnumerator;        RowCount: Integer;        LastUpdate: DateTime;        ReadData: Boolean;    begin        // Allows reading Excel files with more than one sheet without closing and reopening file        if SheetName <> '' then            SetActiveReaderSheet(SheetName);        LastUpdate := CurrentDateTime;        ExcelBufferDialogMgt.Open(ReadingExcelWorksheetTxt);        DeleteAll();        Enumerator := XlWrkShtReader.GetEnumerator();        RowCount := XlWrkShtReader.RowCount;        ReadData := Enumerator.MoveNext();        while ReadData do begin            CellData := Enumerator.Current;            if CellData.HasValue() and ShouldReadCellData(CellData.ColumnNumber, CellData.RowNumber, ColumnList, RowList) then begin                Validate("Row No.", CellData.RowNumber);                Validate("Column No.", CellData.ColumnNumber);                ParseCellValue(CellData.Value, CellData.Format);                Insert();                if not UpdateProgressDialog(ExcelBufferDialogMgt, LastUpdate, CellData.RowNumber, RowCount) then begin                    CloseBook();                    Error(OperationCanceledTxt)                end;            end;            ReadData := Enumerator.MoveNext();            if MaxRowNo = CellData.RowNumber then                ReadData := false;        end;        if CloseBookOnCompletion then            CloseBook();        ExcelBufferDialogMgt.Close();    end;    protected procedure ParseCellValue(Value: Text; FormatString: Text)    var        OutStream: OutStream;        Decimal: Decimal;        RoundingPrecision: Decimal;        IsHandled: Boolean;    begin        // The format contains only en-US number separators, this is an OpenXML standard requirement        // The algorithm sieves the data based on formatting as follows (the steps must run in this order)        // 1. FormatString = '@' -> Text        // 2. FormatString.Contains(':') -> Time        // 3. FormatString.ContainsOneOf('y', 'm', 'd') && FormatString.DoesNotContain('Red') -> Date        // 4. anything else -> Decimal        IsHandled := false;        OnBeforeParseCellValue(Rec, Value, FormatString, IsHandled);        if IsHandled then            exit;        NumberFormat := CopyStr(FormatString, 1, 30);        Clear("Cell Value as Blob");        if FormatString = '@' then begin            "Cell Type" := "Cell Type"::Text;            "Cell Value as Text" := CopyStr(Value, 1, MaxStrLen("Cell Value as Text"));            if StrLen(Value) <= MaxStrLen("Cell Value as Text") then                exit; // No need to store anything in the blob            "Cell Value as Blob".CreateOutStream(OutStream, TEXTENCODING::Windows);            OutStream.Write(Value);            exit;        end;        Evaluate(Decimal, Value);        if StrPos(FormatString, ':') <> 0 then begin            // Excel Time is stored in OADate format            "Cell Type" := "Cell Type"::Time;            "Cell Value as Text" := Format(DT2Time(ConvertDateTimeDecimalToDateTime(Decimal)));            exit;        end;        if ((StrPos(FormatString, 'y') <> 0) or            (StrPos(FormatString, 'm') <> 0) or            (StrPos(FormatString, 'd') <> 0)) and           (StrPos(FormatString, 'Red') = 0)        then begin            "Cell Type" := "Cell Type"::Date;            "Cell Value as Text" := Format(DT2Date(ConvertDateTimeDecimalToDateTime(Decimal)));            exit;        end;        "Cell Type" := "Cell Type"::Number;        RoundingPrecision := 0.000001;        OnParseCellValueOnBeforeRoundDecimal(Rec, Decimal, RoundingPrecision);        "Cell Value as Text" := Format(Round(Decimal, RoundingPrecision), 0, 1);    end;    [Scope('OnPrem')]    procedure SelectSheetsName(FileName: Text): Text[250]    var        TempBlob: Codeunit "Temp Blob";        InStr: InStream;    begin        if FileName = '' then            Error(FileNameMissingErr);        FileManagement.IsAllowedPath(FileName, false);        FileManagement.BLOBImportFromServerFile(TempBlob, FileName);        TempBlob.CreateInStream(InStr);        exit(SelectSheetsNameStream(InStr));    end;    procedure SelectSheetsNameStream(FileStream: InStream): Text[250]    var        TempNameValueBuffer: Record "Name/Value Buffer" temporary;        SelectedSheetName: Text[250];    begin        if GetSheetsNameListFromStream(FileStream, TempNameValueBuffer) then            if TempNameValueBuffer.Count = 1 then                SelectedSheetName := TempNameValueBuffer.Value            else begin                TempNameValueBuffer.FindFirst();                if PAGE.RunModal(PAGE::"Name/Value Lookup", TempNameValueBuffer) = ACTION::LookupOK then                    SelectedSheetName := TempNameValueBuffer.Value;            end;        exit(SelectedSheetName);    end;    procedure GetExcelReference(Which: Integer): Text[250]    begin        case Which of            1:                exit(BoldTokenTok);            // DO NOT TRANSLATE: &B is the Excel code to turn bold printing on or off for customized Header/Footer.            2:                exit(DateTokenTok);            // DO NOT TRANSLATE: &D is the Excel code to print the current date in customized Header/Footer.            3:                exit(PageNumberTokenTok);            // DO NOT TRANSLATE: &P is the Excel code to print the page number in customized Header/Footer.            4:                exit('$');// DO NOT TRANSLATE: $ is the Excel code for absolute reference to cells.            5:                exit(A1CellTok);            // DO NOT TRANSLATE: A1 is the Excel reference of the first cell.            6:                exit(SumIfTok);            // DO NOT TRANSLATE: SUMIF is the name of the Excel function used to summarize values according to some conditions.            7:                exit(NATok);            // DO NOT TRANSLATE: The #N/A Excel error value occurs when a value is not available to a function or formula.            8:                exit(GLAccTok);            // DO NOT TRANSLATE: GLAcc is used to define an Excel range name. You must refer to Excel rules to change this term.            9:                exit(PeriodTok);// DO NOT TRANSLATE: Period is used to define an Excel range name. You must refer to Excel rules to change this term.            10:                exit(BudgetTxt);            // DO NOT TRANSLATE: Budget is used to define an Excel worksheet name. You must refer to Excel rules to change this term.            11:                exit(CostAccTok);            // DO NOT TRANSLATE: CostAcc is used to define an Excel range name. You must refer to Excel rules to change this term.            12:                exit('!');            // ! is the Excel code for reference to sheet.        end;    end;    procedure ExportBudgetFilterToFormula(var ExcelBuf: Record "Excel Buffer"): Boolean    var        TempExcelBufFormula: Record "Excel Buffer" temporary;        TempExcelBufFormula2: Record "Excel Buffer" temporary;        FirstRow: Integer;        LastRow: Integer;        HasFormulaError: Boolean;        ThisCellHasFormulaError: Boolean;    begin        FirstRow := 0;        ExcelBuf.SetFilter(Formula, '<>%1', '');        if ExcelBuf.FindSet() then            repeat                TempExcelBufFormula := ExcelBuf;                TempExcelBufFormula.Insert();            until ExcelBuf.Next() = 0;        ExcelBuf.Reset();        if TempExcelBufFormula.FindFirst() then            repeat                ThisCellHasFormulaError := false;                ExcelBuf.SetRange("Column No.", 1);                ExcelBuf.SetFilter("Row No.", '<>%1', TempExcelBufFormula."Row No.");                ExcelBuf.SetFilter("Cell Value as Text", TempExcelBufFormula.Formula);                TempExcelBufFormula2 := TempExcelBufFormula;                if ExcelBuf.FindSet() then                    repeat                        if not TempExcelBufFormula.Get(ExcelBuf."Row No.", TempExcelBufFormula."Column No.") then                            ExcelBuf.Mark(true);                    until ExcelBuf.Next() = 0;                TempExcelBufFormula := TempExcelBufFormula2;                TempExcelBufFormula.ClearFormula();                ExcelBuf.SetRange("Cell Value as Text");                ExcelBuf.SetRange("Row No.");                if ExcelBuf.FindSet() then                    repeat                        if ExcelBuf.Mark() then begin                            LastRow := ExcelBuf."Row No.";                            if FirstRow = 0 then                                FirstRow := LastRow;                        end else                            if FirstRow <> 0 then begin                                if FirstRow = LastRow then                                    ThisCellHasFormulaError := TempExcelBufFormula.AddToFormula(TempExcelBufFormula.xlColID + Format(FirstRow))                                else                                    ThisCellHasFormulaError :=                                      TempExcelBufFormula.AddToFormula('SUM(' + TempExcelBufFormula.xlColID + Format(FirstRow) + ':' + TempExcelBufFormula.xlColID + Format(LastRow) + ')');                                FirstRow := 0;                                if ThisCellHasFormulaError then                                    TempExcelBufFormula.SetFormula(ExcelBuf.GetExcelReference(7));                            end;                    until ThisCellHasFormulaError or (ExcelBuf.Next() = 0);                if not ThisCellHasFormulaError and (FirstRow <> 0) then begin                    if FirstRow = LastRow then                        ThisCellHasFormulaError := TempExcelBufFormula.AddToFormula(TempExcelBufFormula.xlColID + Format(FirstRow))                    else                        ThisCellHasFormulaError :=                          TempExcelBufFormula.AddToFormula('SUM(' + TempExcelBufFormula.xlColID + Format(FirstRow) + ':' + TempExcelBufFormula.xlColID + Format(LastRow) + ')');                    FirstRow := 0;                    if ThisCellHasFormulaError then                        TempExcelBufFormula.SetFormula(ExcelBuf.GetExcelReference(7));                end;                ExcelBuf.Reset();                ExcelBuf.Get(TempExcelBufFormula."Row No.", TempExcelBufFormula."Column No.");                ExcelBuf.SetFormula(TempExcelBufFormula.GetFormula());                ExcelBuf.Modify();                HasFormulaError := HasFormulaError or ThisCellHasFormulaError;            until TempExcelBufFormula.Next() = 0;        exit(HasFormulaError);    end;    procedure AddToFormula(Text: Text[30]): Boolean    var        Overflow: Boolean;        LongFormula: Text[1000];    begin        LongFormula := GetFormula();        if LongFormula = '' then            LongFormula := '=';        if LongFormula <> '=' then            if StrLen(LongFormula) + 1 > MaxStrLen(LongFormula) then                Overflow := true            else                LongFormula := LongFormula + '+';        if StrLen(LongFormula) + StrLen(Text) > MaxStrLen(LongFormula) then            Overflow := true        else            SetFormula(LongFormula + Text);        exit(Overflow);    end;    procedure GetFormula(): Text[1000]    begin        exit(Formula + Formula2 + Formula3 + Formula4);    end;    procedure SetFormula(LongFormula: Text[1000])    begin        ClearFormula();        if LongFormula = '' then            exit;        Formula := CopyStr(LongFormula, 1, MaxStrLen(Formula));        if StrLen(LongFormula) > MaxStrLen(Formula) then            Formula2 := CopyStr(LongFormula, MaxStrLen(Formula) + 1, MaxStrLen(Formula2));        if StrLen(LongFormula) > MaxStrLen(Formula) + MaxStrLen(Formula2) then            Formula3 := CopyStr(LongFormula, MaxStrLen(Formula) + MaxStrLen(Formula2) + 1, MaxStrLen(Formula3));        if StrLen(LongFormula) > MaxStrLen(Formula) + MaxStrLen(Formula2) + MaxStrLen(Formula3) then            Formula4 := CopyStr(LongFormula, MaxStrLen(Formula) + MaxStrLen(Formula2) + MaxStrLen(Formula3) + 1, MaxStrLen(Formula4));    end;    procedure ClearFormula()    begin        Formula := '';        Formula2 := '';        Formula3 := '';        Formula4 := '';    end;    procedure NewRow()    begin        SetCurrent(CurrentRow + 1, 0);    end;    procedure AddColumn(Value: Variant; IsFormula: Boolean; CommentText: Text; IsBold: Boolean; IsItalics: Boolean; IsUnderline: Boolean; NumFormat: Text[30]; CellType: Option)    begin        AddColumnToBuffer(Rec, Value, IsFormula, CommentText, IsBold, IsItalics, IsUnderline, NumFormat, CellType);    end;    procedure AddInfoColumn(Value: Variant; IsFormula: Boolean; IsBold: Boolean; IsItalics: Boolean; IsUnderline: Boolean; NumFormat: Text[30]; CellType: Option)    begin        AddColumnToBuffer(TempInfoExcelBuf, Value, IsFormula, '', IsBold, IsItalics, IsUnderline, NumFormat, CellType);    end;    local procedure AddColumnToBuffer(var ExcelBuffer: Record "Excel Buffer"; Value: Variant; IsFormula: Boolean; CommentText: Text; IsBold: Boolean; IsItalics: Boolean; IsUnderline: Boolean; NumFormat: Text[30]; CellType: Option)    begin        if CurrentRow < 1 then            NewRow();        CurrentCol := CurrentCol + 1;        ExcelBuffer.Init();        ExcelBuffer.Validate("Row No.", CurrentRow);        ExcelBuffer.Validate("Column No.", CurrentCol);        if IsFormula then            ExcelBuffer.SetFormula(Format(Value))        else            ExcelBuffer."Cell Value as Text" := Format(Value);        ExcelBuffer.Comment := CopyStr(CommentText, 1, MaxStrLen(ExcelBuffer.Comment));        ExcelBuffer.Bold := IsBold;        ExcelBuffer.Italic := IsItalics;        ExcelBuffer.Underline := IsUnderline;        ExcelBuffer.NumberFormat := NumFormat;        ExcelBuffer."Cell Type" := CellType;        ExcelBuffer.Insert();        OnAfterAddColumnToBuffer(ExcelBuffer, Value, IsFormula, CommentText, IsBold, IsItalics, IsUnderline, NumFormat, CellType);    end;    procedure EnterCell(var ExcelBuffer: Record "Excel Buffer"; RowNo: Integer; ColumnNo: Integer; Value: Variant; IsBold: Boolean; IsItalics: Boolean; IsUnderline: Boolean)    begin        ExcelBuffer.Init();        ExcelBuffer.Validate("Row No.", RowNo);        ExcelBuffer.Validate("Column No.", ColumnNo);        case true of            Value.IsDecimal or Value.IsInteger:                ExcelBuffer.Validate("Cell Type", ExcelBuffer."Cell Type"::Number);            Value.IsDate:                ExcelBuffer.Validate("Cell Type", ExcelBuffer."Cell Type"::Date);            else                ExcelBuffer.Validate("Cell Type", ExcelBuffer."Cell Type"::Text);        end;        ExcelBuffer."Cell Value as Text" := CopyStr(Format(Value), 1, MaxStrLen(ExcelBuffer."Cell Value as Text"));        ExcelBuffer.Bold := IsBold;        ExcelBuffer.Italic := IsItalics;        ExcelBuffer.Underline := IsUnderline;        ExcelBuffer.Insert(true);    end;    procedure StartRange()    var        DummyExcelBuf: Record "Excel Buffer";    begin        DummyExcelBuf.Validate("Row No.", CurrentRow);        DummyExcelBuf.Validate("Column No.", CurrentCol);        RangeStartXlRow := DummyExcelBuf.xlRowID;        RangeStartXlCol := DummyExcelBuf.xlColID;    end;    procedure EndRange()    var        DummyExcelBuf: Record "Excel Buffer";    begin        DummyExcelBuf.Validate("Row No.", CurrentRow);        DummyExcelBuf.Validate("Column No.", CurrentCol);        RangeEndXlRow := DummyExcelBuf.xlRowID;        RangeEndXlCol := DummyExcelBuf.xlColID;    end;    procedure CreateRange(RangeName: Text[250])    begin        XlWrkShtWriter.AddRange(          RangeName,          GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +          ':' +          GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow);    end;    procedure ClearNewRow()    begin        SetCurrent(0, 0);    end;    procedure SetUseInfoSheet()    begin        UseInfoSheet := true;    end;    procedure UTgetGlobalValue(globalVariable: Text[30]; var value: Variant)    var        IsHandled: Boolean;    begin        IsHandled := false;        OnBeforeUTgetGlobalValue(globalVariable, value, IsHandled);        if IsHandled then            exit;        case globalVariable of            'CurrentRow':                value := CurrentRow;            'CurrentCol':                value := CurrentCol;            'RangeStartXlRow':                value := RangeStartXlRow;            'RangeStartXlCol':                value := RangeStartXlCol;            'RangeEndXlRow':                value := RangeEndXlRow;            'RangeEndXlCol':                value := RangeEndXlCol;            'XlWrkSht':                value := XlWrkShtWriter;            'ExcelFile':                value := FileNameServer;            else                Error(GlobalVariableNotIncludedErr, globalVariable);        end;    end;    procedure SetCurrent(NewCurrentRow: Integer; NewCurrentCol: Integer)    begin        CurrentRow := NewCurrentRow;        CurrentCol := NewCurrentCol;    end;    procedure CreateValidationRule(Range: Code[20])    begin        XlWrkShtWriter.AddRangeDataValidation(          Range,          GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +          ':' +          GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow);    end;    procedure CreateValidationRule(Range: Code[20]; SheetName: Text[250])    begin        XlWrkShtWriter.AddRangeDataValidation(            Range,            SheetName + GetExcelReference(12) +            GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +            ':' +            GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow);    end;    procedure QuitExcel()    begin        CloseBook();    end;    procedure OpenExcel()    begin        if OpenUsingDocumentService('') then            exit;        FileManagement.DownloadHandler(FileNameServer, '', '', ExcelFilesFilterTxt, GetFriendlyFilename());    end;    [Scope('OnPrem')]    procedure DownloadAndOpenExcel()    begin        OpenExcelWithName(GetFriendlyFilename());    end;    [Scope('OnPrem')]    procedure OpenExcelWithName(FileName: Text)    begin        if FileName = '' then            Error(FileNameMissingErr);        if OpenUsingDocumentService(FileName) then            exit;        FileManagement.DownloadHandler(FileNameServer, '', '', ExcelFilesFilterTxt, FileName);    end;    local procedure OpenUsingDocumentService(FileName: Text) Result: Boolean    var        DocumentServiceMgt: Codeunit "Document Service Management";        FileMgt: Codeunit "File Management";        PathHelper: DotNet Path;        DialogWindow: Dialog;        DocumentUrl: Text;        IsHandled: Boolean;    begin        IsHandled := false;        OnBeforeOpenUsingDocumentService(FileNameServer, Filename, Result, IsHandled);        if IsHandled then            exit(Result);        if not Exists(FileNameServer) then            Error(FileNotExistErr, FileNameServer);        // if document service is configured we save the generated document to SharePoint and open it from there.        if DocumentServiceMgt.IsConfigured() then begin            if FileName = '' then                FileName := 'Book.' + PathHelper.ChangeExtension(PathHelper.GetRandomFileName(), 'xlsx')            else begin                // if file is not applicable for the service it can not be opened using the document service.                if not DocumentServiceMgt.IsServiceUri(FileName) then                    exit(false);                FileName := FileMgt.GetFileName(FileName);            end;            DialogWindow.Open(StrSubstNo(SavingDocumentMsg, FileName));            DocumentUrl := DocumentServiceMgt.SaveFile(FileNameServer, FileName, Enum::"Doc. Sharing Conflict Behavior"::Replace);            DocumentServiceMgt.OpenDocument(DocumentUrl);            DialogWindow.Close();            exit(true);        end;        exit(false);    end;    [Scope('OnPrem')]    procedure CreateBookAndOpenExcel(FileName: Text; SheetName: Text[250]; ReportHeader: Text; CompanyName2: Text; UserID2: Text)    begin        CreateBook(FileName, SheetName);        WriteSheet(ReportHeader, CompanyName2, UserID2);        CloseBook();        OpenExcel();    end;    local procedure UpdateProgressDialog(var ExcelBufferDialogManagement: Codeunit "Excel Buffer Dialog Management"; var LastUpdate: DateTime; CurrentCount: Integer; TotalCount: Integer) Result: Boolean    var        CurrentTime: DateTime;        IsHandled: Boolean;    begin        IsHandled := false;        OnBeforeUpdateProgressDialog(ExcelBufferDialogManagement, Result, IsHandled);        if IsHandled then            exit(Result);        // Refresh at 100%, and every second in between 0% to 100%        // Duration is measured in miliseconds -> 1 sec = 1000 ms        CurrentTime := CurrentDateTime;        if (CurrentCount = TotalCount) or (CurrentTime - LastUpdate >= 1000) then begin            LastUpdate := CurrentTime;            if not ExcelBufferDialogManagement.SetProgress(Round(CurrentCount / TotalCount * 10000, 1)) then                exit(false);        end;        exit(true)    end;    local procedure GetFriendlyFilename(): Text    begin        if FriendlyName = '' then            exit('Book1' + ExcelFileExtensionTok);        exit(FileManagement.StripNotsupportChrInFileName(FriendlyName) + ExcelFileExtensionTok);    end;    procedure SetFriendlyFilename(Name: Text)    begin        FriendlyName := Name;    end;    procedure ConvertDateTimeDecimalToDateTime(DateTimeAsOADate: Decimal): DateTime    var        DotNetDateTime: DotNet DateTime;        DateTimeResult: DateTime;        DotNetDateTimeKind: DotNet DateTimeKind;    begin        DotNetDateTime := DotNetDateTime.FromOADate(DateTimeAsOADate);        if ReadDateTimeInUtcDate then            Evaluate(DateTimeResult, DotNetDateTime.ToString())        else            DateTimeResult := DotNetDateTime.DateTime(DotNetDateTime.Ticks, DotNetDateTimeKind.Local);        exit(DateTimeResult);    end;    procedure SaveToStream(var ResultStream: OutStream; EraseFileAfterCompletion: Boolean)    var        TempBlob: Codeunit "Temp Blob";        BlobStream: InStream;    begin        FileManagement.BLOBImportFromServerFile(TempBlob, FileNameServer);        TempBlob.CreateInStream(BlobStream);        CopyStream(ResultStream, BlobStream);        if EraseFileAfterCompletion then            FILE.Erase(FileNameServer);    end;    procedure GetSheetsNameListFromStream(FileStream: InStream; var TempNameValueBufferOut: Record "Name/Value Buffer" temporary) SheetsFound: Boolean    var        SheetNames: DotNet ArrayList;        SheetName: Text[250];        i: Integer;    begin        XlWrkBkReader := XlWrkBkReader.Open(FileStream);        TempNameValueBufferOut.Reset();        TempNameValueBufferOut.DeleteAll();        SheetNames := SheetNames.ArrayList(XlWrkBkReader.SheetNames());        if IsNull(SheetNames) then            exit(false);        SheetsFound := SheetNames.Count > 0;        if not SheetsFound then            exit(false);        for i := 0 to SheetNames.Count - 1 do begin            SheetName := SheetNames.Item(i);            if SheetName <> '' then begin                TempNameValueBufferOut.Init();                TempNameValueBufferOut.ID := i;                TempNameValueBufferOut.Name := Format(i + 1);                TempNameValueBufferOut.Value := SheetName;                TempNameValueBufferOut.Insert();            end;        end;        CloseBook();    end;    local procedure ShouldReadCellData(ColumnNo: Integer; RowNo: Integer; ColumnList: List of [Integer]; RowList: List of [Integer]): Boolean    begin        if (ColumnList.Count = 0) and (RowList.Count = 0) then            exit(true);        if ColumnList.Count = 0 then            exit(RowList.Contains(RowNo));        if RowList.Count = 0 then            exit(ColumnList.Contains(ColumnNo));        exit(ColumnList.Contains(ColumnNo) and RowList.Contains(RowNo));    end;    [IntegrationEvent(false, false)]    local procedure OnAfterAddColumnToBuffer(var ExcelBuffer: Record "Excel Buffer"; Value: Variant; IsFormula: Boolean; CommentText: Text; IsBold: Boolean; IsItalics: Boolean; IsUnderline: Boolean; NumFormat: Text[30]; CellType: Option)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeOpenUsingDocumentService(FileNameServer: Text; FileName: Text; var Result: Boolean; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeParseCellValue(var ExcelBuffer: Record "Excel Buffer"; var Value: Text; var FormatString: Text; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeUTgetGlobalValue(GlobalVariable: Text[30]; var Variant_Value: Variant; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeWriteCellFormula(var Rec: Record "Excel Buffer"; var ExcelBuffer: Record "Excel Buffer"; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnParseCellValueOnBeforeRoundDecimal(var ExcelBuffer: Record "Excel Buffer"; DecimalValue: Decimal; var RoundingPrecision: Decimal)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnUpdateBookExcelOnAfterXlWrkBkWriterOpen(var ExcelBuffer: Record "Excel Buffer"; FileName: Text; SheetName: Text)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnWriteSheetOnBeforeUseXmlTextWriter(var ExcelBuffer: Record "Excel Buffer"; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnWriteSheetOnBeforeAddAndInitializeCommentsPart(var ExcelBuffer: Record "Excel Buffer"; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnWriteCellValueOnBeforeSetCellValue(var ExcelBuffer: Record "Excel Buffer"; var CellTextValue: Text)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeUpdateProgressDialog(var ExcelBufferDialogManagement: Codeunit "Excel Buffer Dialog Management"; var Result: Boolean; var IsHandled: Boolean)    begin    end;}