Table 370 Excel Buffer
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;}