Codeunit 6223 OpenXML Management
src/Layers/W1/BaseApp/System/OpenXMLManagement.Codeunit.al632 lines, Copyright (c) Microsoft Corporation. MIT
namespace System.Xml;using System;using System.IO;using System.Utilities;codeunit 6223 "OpenXML Management"{ trigger OnRun() begin end; var ExcelBuffer: Record "Excel Buffer"; FileMgt: Codeunit "File Management"; WrkShtHelper: DotNet WorksheetHelper; UID: Integer; CreateWrkBkFailedErr: Label 'Could not create the Excel workbook.'; OpenWrkBkFailedErr: Label 'Could not open the Excel workbook.'; MissingXMLMapErr: Label 'The Excel workbook must contain an XML map.'; VmlShapeAnchorTxt: Label '%1,15,%2,10,%3,31,%4,9', Locked = true; CommentVmlShapeXmlTxt: Label '<v:shape id="%1" type="#_x0000_t202" style=''position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:96pt;height:55.5pt;z-index:1; visibility:hidden'' fillcolor="#ffffe1" o:insetmode="auto"><v:fill color2="#ffffe1"/><v:shadow color="black" obscured="t"/><v:path o:connecttype="none"/><v:textbox style=''mso-direction-alt:auto''><div style=''text-align:left''/></v:textbox><x:ClientData ObjectType="Note"><x:MoveWithCells/><x:SizeWithCells/><x:Anchor>%2</x:Anchor><x:AutoFill>False</x:AutoFill><x:Row>%3</x:Row><x:Column>%4</x:Column></x:ClientData></v:shape>', Locked = true; CopyDataProgressTxt: Label 'Writing to Excel'; ProgressStatusTxt: Label '%1: %2 records out of %3', Comment = '%1 = table name; %2 = number of processed records (integer); %3 = total number records (integer).'; [Scope('OnPrem')] procedure AddAndInitializeCommentsPart(WorksheetWriter: DotNet WorksheetWriter; var VmlDrawingPart: DotNet VmlDrawingPart) var WorkSheetCommentsPart: DotNet WorksheetCommentsPart; Comments: DotNet Comments; begin WorkSheetCommentsPart := WorksheetWriter.Worksheet.WorksheetPart.WorksheetCommentsPart; if IsNull(WorkSheetCommentsPart) then WorkSheetCommentsPart := WorksheetWriter.CreateWorksheetCommentsPart(); AddVmlDrawingPart(WorksheetWriter, VmlDrawingPart); WorkSheetCommentsPart.Comments := Comments.Comments(); AddWorkSheetAuthor(WorkSheetCommentsPart.Comments, UserId); WorksheetWriter.CreateCommentList(WorkSheetCommentsPart.Comments); end; local procedure AddVmlDrawingPart(WorksheetWriter: DotNet WorksheetWriter; var VmlDrawingPart: DotNet VmlDrawingPart) var StringValue: DotNet StringValue; LegacyDrawing: DotNet LegacyDrawing; LocalWorksheet: DotNet Worksheet; LastChild: DotNet OpenXmlElement; VmlPartId: Text; begin VmlDrawingPart := WorksheetWriter.CreateVmlDrawingPart(); VmlPartId := WorksheetWriter.Worksheet.WorksheetPart.GetIdOfPart(VmlDrawingPart); LegacyDrawing := LegacyDrawing.LegacyDrawing(); LegacyDrawing.Id := StringValue.FromString(VmlPartId); LocalWorksheet := WorksheetWriter.Worksheet.WorksheetPart.Worksheet; LastChild := LocalWorksheet.LastChild(); if LastChild.GetType().Equals(LegacyDrawing.GetType()) then LastChild.Remove(); WrkShtHelper.AppendElementToOpenXmlElement(WorksheetWriter.Worksheet.WorksheetPart.Worksheet, LegacyDrawing); end; [Scope('OnPrem')] procedure AddColumnHeader(WorksheetWriter: DotNet WorksheetWriter; var "Table": DotNet Table; ColumnID: Integer; ColumnName: Text; var TableColumn: DotNet TableColumn) var TableColumns: DotNet TableColumns; begin TableColumns := Table.TableColumns; TableColumn := WorksheetWriter.CreateTableColumn(ColumnID, ColumnName, GetElementName(ColumnName)); WrkShtHelper.AppendElementToOpenXmlElement(TableColumns, TableColumn); end; [Scope('OnPrem')] procedure AddColumnHeaderWithXPath(WorksheetWriter: DotNet WorksheetWriter; var "Table": DotNet Table; ColumnID: Integer; ColumnName: Text; Type: Text; XPath: Text) var TableColumn: DotNet TableColumn; XmlColumnProperties: DotNet XmlColumnProperties; begin AddColumnHeader(WorksheetWriter, Table, ColumnID, ColumnName, TableColumn); XmlColumnProperties := WorksheetWriter.CreateXmlColumnProperties(1, XPath, WorksheetWriter.XmlDataType2XmlDataValues(Type)); TableColumn.XmlColumnProperties(XmlColumnProperties); end; [Scope('OnPrem')] procedure AddSingleXMLCellProperties(var SingleXMLCell: DotNet SingleXmlCell; CellReference: Text; XPath: Text; Mapid: Integer; ConnectionId: Integer) var XMLCellProperties: DotNet XmlCellProperties; XMLProperties: DotNet XmlProperties; UInt32Value: DotNet UInt32Value; StringValue: DotNet StringValue; XmlDataValues: DotNet XmlDataValues; WrkShtWriter2: DotNet WorksheetWriter; begin StringValue := StringValue.StringValue(CellReference); SingleXMLCell.CellReference := StringValue; UInt32Value := UInt32Value.UInt32Value(); UInt32Value.Value := ConnectionId; SingleXMLCell.ConnectionId := UInt32Value; XMLCellProperties := XMLCellProperties.XmlCellProperties(); WrkShtHelper.AppendElementToOpenXmlElement(SingleXMLCell, XMLCellProperties); UInt32Value.Value := 1; XMLCellProperties.Id := UInt32Value; StringValue := StringValue.StringValue(Format(SingleXMLCell.Id)); XMLCellProperties.UniqueName := StringValue; XMLProperties := XMLProperties.XmlProperties(); WrkShtHelper.AppendElementToOpenXmlElement(XMLCellProperties, XMLProperties); UInt32Value.Value := Mapid; XMLProperties.MapId := UInt32Value; StringValue := StringValue.StringValue(XPath); XMLProperties.XPath := StringValue; XmlDataValues := XmlDataValues.String; XMLProperties.XmlDataType := WrkShtWriter2.GetEnumXmlDataValues(XmlDataValues); end; [Scope('OnPrem')] procedure AddTable(WorksheetWriter: DotNet WorksheetWriter; StartFromRow: Integer; ColumnCount: Integer; RowCount: Integer; var "Table": DotNet Table) var TableColumns: DotNet TableColumns; TablePart: DotNet TablePart; TableParts: DotNet TableParts; TableDefinitionPart: DotNet TableDefinitionPart; Reference: Text; begin Reference := 'A' + Format(StartFromRow) + ':' + GetXLColumnID(ColumnCount) + Format(RowCount + StartFromRow); InitTable(WorksheetWriter, Table, Reference, 1); AppendAutoFilter(Table); AppendTableStyleInfo(Table); TableColumns := WorksheetWriter.CreateTableColumns(ColumnCount); Table.TableColumns(TableColumns); TableDefinitionPart := WorksheetWriter.CreateTableDefinitionPart(); TableDefinitionPart.Table := Table; TableParts := WorksheetWriter.CreateTableParts(1); WrkShtHelper.AppendElementToOpenXmlElement(WorksheetWriter.Worksheet, TableParts); TablePart := WorksheetWriter.CreateTablePart(WorksheetWriter.Worksheet.WorksheetPart.GetIdOfPart(TableDefinitionPart)); WrkShtHelper.AppendElementToOpenXmlElement(TableParts, TablePart); end; [Scope('OnPrem')] procedure AddWorkSheetAuthor(Comments: DotNet Comments; AuthorText: Text) var Author: DotNet Author; Authors: DotNet Authors; begin Authors := Authors.Authors(); WrkShtHelper.AppendElementToOpenXmlElement(Comments, Authors); Author := Author.Author(); Author.Text := AuthorText; WrkShtHelper.AppendElementToOpenXmlElement(Authors, Author); end; [Scope('OnPrem')] procedure AppendAutoFilter(var "Table": DotNet Table) var AutoFilter: DotNet AutoFilter; StringValue: DotNet StringValue; begin AutoFilter := AutoFilter.AutoFilter(); AutoFilter.Reference := StringValue.StringValue(Table.Reference.Value); WrkShtHelper.AppendElementToOpenXmlElement(Table, AutoFilter); end; [Scope('OnPrem')] procedure AppendTableStyleInfo(var "Table": DotNet Table) var BooleanValue: DotNet BooleanValue; StringValue: DotNet StringValue; TableStyleInfo: DotNet TableStyleInfo; begin TableStyleInfo := TableStyleInfo.TableStyleInfo(); TableStyleInfo.Name := StringValue.StringValue('TableStyleMedium2'); TableStyleInfo.ShowFirstColumn := BooleanValue.BooleanValue(false); TableStyleInfo.ShowLastColumn := BooleanValue.BooleanValue(false); TableStyleInfo.ShowRowStripes := BooleanValue.BooleanValue(true); TableStyleInfo.ShowColumnStripes := BooleanValue.BooleanValue(false); WrkShtHelper.AppendElementToOpenXmlElement(Table, TableStyleInfo); end; [Scope('OnPrem')] procedure CleanMapInfo(MapInfo: DotNet MapInfo) var MapInfoString: Text; begin MapInfoString := ReplaceSubString( Format(MapInfo.OuterXml), '<x:MapInfo SelectionNamespaces="" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">', ''); MapInfoString := ReplaceSubString(MapInfoString, '</x:MapInfo>', ''); MapInfoString := ReplaceSubString(MapInfoString, 'x:', ''); MapInfo.InnerXml(MapInfoString); end; procedure CreateBook(var TempBlob: Codeunit "Temp Blob"; var WrkBkWriter: DotNet WorkbookWriter) var IStream: InStream; begin Clear(WrkBkWriter); TempBlob.CreateInStream(IStream); WrkBkWriter := WrkBkWriter.Create(IStream); if IsNull(WrkBkWriter) then Error(CreateWrkBkFailedErr); end; [Scope('OnPrem')] procedure CreateSchemaConnection(var WrkbkWriter: DotNet WorkbookWriter; SetupDataFileName: Text) var ConnectionsPart: DotNet ConnectionsPart; Connections: DotNet Connections; Connection: DotNet Connection; UInt32Value: DotNet UInt32Value; StringValue: DotNet StringValue; BooleanTrueValue: DotNet BooleanValue; WebQueryProperties: DotNet WebQueryProperties; ByteValue: DotNet ByteValue; begin ConnectionsPart := WrkbkWriter.AddConnectionsPart(); Connections := Connections.Connections(); Connection := WrkbkWriter.CreateConnection(1); UInt32Value := UInt32Value.UInt32Value(); Connection.Name := StringValue.StringValue(FileMgt.GetFileName(SetupDataFileName)); UInt32Value.Value := 4; Connection.Type := UInt32Value; BooleanTrueValue := BooleanTrueValue.BooleanValue(true); Connection.Background := BooleanTrueValue; ByteValue := ByteValue.ByteValue(); ByteValue.Value := 0; Connection.RefreshedVersion := ByteValue; WebQueryProperties := WebQueryProperties.WebQueryProperties(); WebQueryProperties.XmlSource := BooleanTrueValue; WebQueryProperties.SourceData := BooleanTrueValue; WebQueryProperties.Url := StringValue.StringValue(SetupDataFileName); WebQueryProperties.HtmlTables := BooleanTrueValue; WrkShtHelper.AppendElementToOpenXmlElement(Connection, WebQueryProperties); WrkShtHelper.AppendElementToOpenXmlElement(Connections, Connection); ConnectionsPart.Connections := Connections; end; [Scope('OnPrem')] procedure CreateTableStyles(Workbook: DotNet Workbook) var Stylesheet: DotNet Stylesheet; Tablestyles: DotNet TableStyles; UInt32Value: DotNet UInt32Value; StringValue: DotNet StringValue; begin Tablestyles := Tablestyles.TableStyles(); UInt32Value := UInt32Value.UInt32Value(); UInt32Value.Value := 0; Tablestyles.Count := UInt32Value; Tablestyles.DefaultTableStyle := StringValue.StringValue('TableStyleMedium2'); Tablestyles.DefaultPivotStyle := StringValue.StringValue('PivotStyleLight16'); Stylesheet := Workbook.WorkbookPart.WorkbookStylesPart.Stylesheet; WrkShtHelper.AppendElementToOpenXmlElement(Stylesheet, Tablestyles); end; [Scope('OnPrem')] procedure CloseBook(var WrkBkWriter: DotNet WorkbookWriter) begin WrkBkWriter.Workbook.Save(); WrkBkWriter.Close(); Clear(WrkBkWriter); end; /// <summary> /// A CopyDataToExcelTable function overload with HideDialog parameter set to true. /// </summary> [Scope('OnPrem')] procedure CopyDataToExcelTable(WorksheetWriter: DotNet WorksheetWriter; DataTable: DotNet DataTable) begin CopyDataToExcelTable(WorksheetWriter, DataTable, true); end; [Scope('OnPrem')] procedure CopyDataToExcelTable(WorksheetWriter: DotNet WorksheetWriter; DataTable: DotNet DataTable; HideDialog: Boolean) var ConfigProgressBar: Codeunit "Config. Progress Bar"; DataColumn: DotNet DataColumn; DataRow: DotNet DataRow; DataTableRowsCount: Integer; RowsCount: Integer; ColumnsCount: Integer; DataTableColumnsCount: Integer; StepCount: Integer; ShowDialog: Boolean; CellDecorator: DotNet CellDecorator; begin ExcelBuffer.Init(); CellDecorator := WorksheetWriter.DefaultCellDecorator; DataTableRowsCount := DataTable.Rows.Count(); RowsCount := 0; DataTableColumnsCount := DataTable.Columns.Count(); ShowDialog := (not HideDialog) and (DataTableRowsCount > 1000); if ShowDialog then begin StepCount := Round(DataTableRowsCount / 100, 1); ConfigProgressBar.Init(DataTableRowsCount, StepCount, CopyDataProgressTxt); end; repeat DataRow := DataTable.Rows.Item(RowsCount); ColumnsCount := 0; repeat DataColumn := DataTable.Columns.Item(ColumnsCount); WriteCellValue(WorksheetWriter, Format(DataColumn.DataType), DataRow, RowsCount, ColumnsCount, CellDecorator); ColumnsCount += 1; until ColumnsCount = DataTableColumnsCount - 1; RowsCount += 1; if ShowDialog then ConfigProgressBar.Update(StrSubstNo(ProgressStatusTxt, WorksheetWriter.Name, RowsCount, DataTableRowsCount)); until RowsCount = DataTableRowsCount; if ShowDialog then ConfigProgressBar.Close(); end; [Scope('OnPrem')] procedure ExtractXMLSchema(WorkBookPart: DotNet WorkbookPart) XMLSchemaDataFile: Text var XMLWriter: DotNet XmlWriter; begin if IsNull(WorkBookPart.CustomXmlMappingsPart) then Error(MissingXMLMapErr); if IsNull(WorkBookPart.CustomXmlMappingsPart.MapInfo) then Error(MissingXMLMapErr); XMLSchemaDataFile := FileMgt.ServerTempFileName(''); FileMgt.IsAllowedPath(XMLSchemaDataFile, false); XMLWriter := XMLWriter.Create(XMLSchemaDataFile); WorkBookPart.CustomXmlMappingsPart.MapInfo.FirstChild.FirstChild.WriteTo(XMLWriter); XMLWriter.Close(); end; [Scope('OnPrem')] procedure FindTableDefinition(WrkShtReader: DotNet WorksheetReader; var "Table": DotNet Table): Boolean var TableDefinitionPart: DotNet TableDefinitionPart; Enumerable: DotNet IEnumerable; Enumerator: DotNet IEnumerator; begin Enumerable := WrkShtReader.Worksheet.WorksheetPart.TableDefinitionParts; Enumerator := Enumerable.GetEnumerator(); Enumerator.MoveNext(); TableDefinitionPart := Enumerator.Current; if IsNull(TableDefinitionPart) then exit(false); Table := TableDefinitionPart.Table; exit(true); end; procedure GetElementName(NameIn: Text): Text begin NameIn := DelChr(NameIn, '=', '?''`'); NameIn := ConvertStr(NameIn, '<>,./\+&()%:', ' '); NameIn := ConvertStr(NameIn, '-', '_'); NameIn := DelChr(NameIn, '=', ' '); exit(NameIn); end; procedure GetXLColumnID(ColumnNo: Integer): Text[10] var ExcelBuf: Record "Excel Buffer"; begin ExcelBuf.Init(); ExcelBuf.Validate("Column No.", ColumnNo); exit(ExcelBuf.xlColID); end; [Scope('OnPrem')] procedure ImportSchema(var WrkbkWriter: DotNet WorkbookWriter; SchemaFileName: Text; MapId: BigInteger; RootElementName: Text) var CustomXMLMappingsPart: DotNet CustomXmlMappingsPart; MapInfo: DotNet MapInfo; "Schema": DotNet Schema; StreamReader: DotNet StreamReader; Map: DotNet Map; DataBinding: DotNet DataBinding; UInt32Value: DotNet UInt32Value; StringValue: DotNet StringValue; BooleanValue: DotNet BooleanValue; StreamText: Text; begin StreamReader := StreamReader.StreamReader(SchemaFileName); StreamReader.ReadLine(); StreamText := StreamReader.ReadToEnd(); StreamReader.Close(); Schema := WrkbkWriter.CreateSchemaFromText(StreamText); Schema.Id := StringValue.StringValue('Schema1'); MapInfo := MapInfo.MapInfo(); MapInfo.SelectionNamespaces := StringValue.StringValue(''); WrkShtHelper.AppendElementToOpenXmlElement(MapInfo, Schema); Map := Map.Map(); UInt32Value := UInt32Value.UInt32Value(); UInt32Value.Value := MapId; Map.ID := UInt32Value; Map.Name := StringValue.StringValue(RootElementName + '_Map'); Map.RootElement := StringValue.StringValue(RootElementName); Map.SchemaId := Schema.Id; Map.ShowImportExportErrors := BooleanValue.BooleanValue(false); Map.AutoFit := BooleanValue.BooleanValue(true); Map.AppendData := BooleanValue.BooleanValue(false); Map.PreserveAutoFilterState := BooleanValue.BooleanValue(true); Map.PreserveFormat := BooleanValue.BooleanValue(true); DataBinding := DataBinding.DataBinding(); DataBinding.FileBinding := BooleanValue.BooleanValue(true); DataBinding.ConnectionId := Map.ID; UInt32Value.Value := 1; DataBinding.DataBindingLoadMode := UInt32Value; WrkShtHelper.AppendElementToOpenXmlElement(MapInfo, Map); WrkShtHelper.AppendElementToOpenXmlElement(Map, DataBinding); CustomXMLMappingsPart := WrkbkWriter.AddCustomXmlMappingsPart(); CustomXMLMappingsPart.MapInfo := MapInfo; end; local procedure InitTable(var WorksheetWriter: DotNet WorksheetWriter; var "Table": DotNet Table; Reference: Text; ConnectionId: Integer) var BooleanValue: DotNet BooleanValue; StringValue: DotNet StringValue; UInt32Value: DotNet UInt32Value; begin UID += 1; Table := WorksheetWriter.CreateTable(UID); Table.Name := StringValue.StringValue('Table' + Format(UID)); Table.DisplayName := StringValue.StringValue('Table' + Format(UID)); Table.Reference := StringValue.StringValue(Reference); Table.TotalsRowShown := BooleanValue.BooleanValue(false); UInt32Value := UInt32Value.UInt32Value(); UInt32Value.Value := ConnectionId; Table.ConnectionId := UInt32Value; end; procedure OpenBook(var TempBlob: Codeunit "Temp Blob"; var WrkBkReader: DotNet WorkbookReader) var IStream: InStream; begin Clear(WrkBkReader); TempBlob.CreateInStream(IStream); WrkBkReader := WrkBkReader.Open(IStream); if IsNull(WrkBkReader) then Error(OpenWrkBkFailedErr); end; local procedure ReplaceSubString(String: Text; Old: Text; New: Text): Text var Pos: Integer; begin Pos := StrPos(String, Old); while Pos <> 0 do begin String := DelStr(String, Pos, StrLen(Old)); String := InsStr(String, New, Pos); Pos := StrPos(String, Old); end; exit(String); end; [Scope('OnPrem')] procedure SetCellComment(WrkShtWriter: DotNet WorksheetWriter; CellReference: Text; CommentValue: Text) var Comment: DotNet Comment; CommentText: DotNet CommentText; Run: DotNet Run; UInt32Value: DotNet UInt32Value; StringValue: DotNet StringValue; Int32Value: DotNet Int32Value; CommentList: DotNet CommentList; Comments: DotNet Comments; SpreadsheetText: DotNet Text; RunProperties: DotNet RunProperties; CommentsPart: DotNet WorksheetCommentsPart; Bold: DotNet Bold; FontSize: DotNet FontSize; DoubleValue: DotNet DoubleValue; Color: DotNet "Spreadsheet.Color"; RunFont: DotNet RunFont; RunPropCharSet: DotNet RunPropertyCharSet; begin CommentsPart := WrkShtWriter.Worksheet.WorksheetPart.WorksheetCommentsPart; Comments := CommentsPart.Comments; if IsNull(Comments) then begin Comments := Comments.Comments(); CommentsPart.Comments := Comments; end; CommentList := Comments.CommentList; if IsNull(CommentList) then CommentList := WrkShtWriter.CreateCommentList(Comments); Comment := Comment.Comment(); Comment.AuthorId := UInt32Value.FromUInt32(0); Comment.Reference := StringValue.StringValue(CellReference); CommentText := CommentText.CommentText(); Run := Run.Run(); RunProperties := RunProperties.RunProperties(); Bold := Bold.Bold(); FontSize := FontSize.FontSize(); FontSize.Val := DoubleValue.FromDouble(9); Color := Color.Color(); Color.Indexed := UInt32Value.FromUInt32(81); RunFont := RunFont.RunFont(); RunFont.Val := StringValue.FromString('Tahoma'); RunPropCharSet := RunPropCharSet.RunPropertyCharSet(); RunPropCharSet.Val := Int32Value.FromInt32(1); WrkShtHelper.AppendElementToOpenXmlElement(RunProperties, Bold); WrkShtHelper.AppendElementToOpenXmlElement(RunProperties, FontSize); WrkShtHelper.AppendElementToOpenXmlElement(RunProperties, Color); WrkShtHelper.AppendElementToOpenXmlElement(RunProperties, RunFont); WrkShtHelper.AppendElementToOpenXmlElement(RunProperties, RunPropCharSet); SpreadsheetText := WrkShtWriter.AddText(CommentValue); SpreadsheetText.Text := CommentValue; WrkShtHelper.AppendElementToOpenXmlElement(Run, RunProperties); WrkShtHelper.AppendElementToOpenXmlElement(Run, SpreadsheetText); WrkShtHelper.AppendElementToOpenXmlElement(CommentText, Run); Comment.CommentText := CommentText; WrkShtWriter.AppendComment(CommentList, Comment); CommentsPart.Comments.Save(); WrkShtWriter.Worksheet.Save(); end; [Scope('OnPrem')] procedure SetSingleCellValue(var WrkShtWriter: DotNet WorksheetWriter; var SingleXMLCells: DotNet SingleXmlCells; RowNo: Integer; ColumnNo: Text; Value: Text; XPath: Text) var SingleXMLCell: DotNet SingleXmlCell; begin UID += 1; SingleXMLCell := WrkShtWriter.AddSingleXmlCell(UID); WrkShtHelper.AppendElementToOpenXmlElement(SingleXMLCells, SingleXMLCell); AddSingleXMLCellProperties(SingleXMLCell, ColumnNo + Format(RowNo), XPath, 1, 1); WrkShtWriter.SetCellValueText(RowNo, ColumnNo, Value, WrkShtWriter.DefaultCellDecorator); end; [Scope('OnPrem')] procedure SetupWorksheetHelper(WorkbookWriter: DotNet WorkbookWriter) begin WrkShtHelper := WrkShtHelper.WorksheetHelper(WorkbookWriter.FirstWorksheet.Worksheet); end; [Scope('OnPrem')] procedure WriteCellValue(var WrkShtWriter: DotNet WorksheetWriter; DataColumnDataType: Text; var DataRow: DotNet DataRow; RowsCount: Integer; ColumnsCount: Integer) var CellDecorator: DotNet CellDecorator; begin CellDecorator := WrkShtWriter.DefaultCellDecorator; WriteCellValue(WrkShtWriter, DataColumnDataType, DataRow, RowsCount, ColumnsCount, CellDecorator); end; [Scope('OnPrem')] procedure WriteCellValue(var WrkShtWriter: DotNet WorksheetWriter; DataColumnDataType: Text; var DataRow: DotNet DataRow; RowsCount: Integer; ColumnsCount: Integer; var CellDecorator: DotNet CellDecorator) begin OnBeforeWriteCellValue(RowsCount); // Generate Excel column ID for the current column id. // The code is manually inlined here to avoid calling the GetXLColumnID function // this is to save an allocation of the Excel Buffer record and a method call // since this method is called tables * rows * column times. ExcelBuffer.Validate(ExcelBuffer."Column No.", ColumnsCount + 1); case DataColumnDataType of 'System.DateTime': WrkShtWriter.SetCellValueDate( RowsCount + 4, ExcelBuffer.xlColID, DataRow.Item(ColumnsCount), '', CellDecorator); 'System.Time': WrkShtWriter.SetCellValueTime( RowsCount + 4, ExcelBuffer.xlColID, DataRow.Item(ColumnsCount), '', CellDecorator); 'System.Boolean': WrkShtWriter.SetCellValueBoolean( RowsCount + 4, ExcelBuffer.xlColID, DataRow.Item(ColumnsCount), CellDecorator); 'System.Integer', 'System.Int32': WrkShtWriter.SetCellValueNumber( RowsCount + 4, ExcelBuffer.xlColID, Format(DataRow.Item(ColumnsCount)), '', CellDecorator); else WrkShtWriter.SetCellValueText( RowsCount + 4, ExcelBuffer.xlColID, DataRow.Item(ColumnsCount), CellDecorator); end; end; [Scope('OnPrem')] procedure CreateCommentVmlShapeXml(ColId: Integer; RowId: Integer) CommentShape: Text var Guid: Guid; Anchor: Text; begin Guid := CreateGuid(); Anchor := CreateCommentVmlAnchor(ColId, RowId); CommentShape := StrSubstNo(CommentVmlShapeXmlTxt, Guid, Anchor, RowId - 1, ColId - 1); end; local procedure CreateCommentVmlAnchor(ColId: Integer; RowId: Integer): Text begin exit(StrSubstNo(VmlShapeAnchorTxt, ColId, RowId - 2, ColId + 2, RowId + 5)); end; [IntegrationEvent(false, false)] local procedure OnBeforeWriteCellValue(var RowsCount: Integer) begin end;}