Table 370 Excel Buffer, source in 29
Source29
src/Layers/W1/BaseApp/System/DataExchange/ExcelBuffer.Table.al1404 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;
}