Create Excel Sheet data with Different Font Style in NAV2013

jigneshdhandha

Dear All

In Navision 2009 R2 We can develop functionality to Export your Data into Excel With Different Font Style easily.
In NAV 2013 as you all know Excel Buffer Structure is totally Changed and you still want to Export your Data into Excel With Different Font Style

For that I have developed Functionality and also prepare technical design document to develop this functionality

Attached Object Details :

Type ID Name
Table 370 Excel Buffer
Report 80000 Export to Excel-Font Style


Create Excel Sheet data with Different Font Style in NAV 2013 through Export to Excel Functionality
After apply this Customization to your NAV 2013 Database you can change following font style into exported excel sheet

1) Font Name
2) Font Size
3) Font Color

1) Add below Three field in Table – 370 Excel Buffer
Enabled Field No. Field Name Data Type Length Description
Yes 80000 Font Name Text 100 New Field
Yes 80001 Font Size Integer New Field
Yes 80002 Font Color Integer New Field

2) Add below Global Variable into Table 370 – Excel Buffer
Name DataType Length Subtype
CustomFontPool_gDot DotNet System.Collections.Generic.Dictionary`2.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

3) Add below Function in Table – 370 Excel Buffer
Function Name Local
AddColumnModified_gFnc No

AddFontToCollection_gFnc No
GetCustomCellDecorator_gFnc Yes

Function Definition

1) AddColumnModified_gFnc

1) Function Parameters:
Var Name DataType Subtype Length
No Value Variant
No IsFormula Boolean
No CommentText Text 1000
No IsBold Boolean
No IsItalics Boolean
No IsUnderline Boolean
No NumFormat Text 30
No CellType Option
No FontName_iTxt Text 100
No FontSize_iInt Integer
No FontColor_iInt Integer
2) Function Code :

//New Code
IF CurrentRow < 1 THEN
NewRow;

CurrentCol := CurrentCol + 1;
INIT;
VALIDATE("Row No.",CurrentRow);
VALIDATE("Column No.",CurrentCol);
IF IsFormula THEN
SetFormula(FORMAT(Value))
ELSE
"Cell Value as Text" := FORMAT(Value);
Comment := CommentText;
Bold := IsBold;
Italic := IsItalics;
Underline := IsUnderline;
NumberFormat := NumFormat;
"Cell Type" := CellType;
"Font Name" := FontName_iTxt;
"Font Size" := FontSize_iInt;
"Font Color" := FontColor_iInt;
INSERT;
//New Code

2) AddFontToCollection_gFnc

1) Function Parameters :
Var Name DataType Subtype Length
No Fonts_iDot DotNet DocumentFormat.OpenXml.Spreadsheet.Fonts.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
Yes CustomFont_vDot DotNet DocumentFormat.OpenXml.OpenXmlElement.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
No Index_iInt Text


2) Return Value :

Return Type : Boolean

3) Function Variables :

Name DataType Subtype Length
Array_lDot DotNet System.Array.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
TempFont_lDot DotNet DocumentFormat.OpenXml.OpenXmlElement.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'


4) Function Code :

//New Code
IF ISNULL(CustomFontPool_gDot) THEN
CustomFontPool_gDot := CustomFontPool_gDot.Dictionary();

IF CustomFontPool_gDot.TryGetValue(Index_iInt,TempFont_lDot) THEN BEGIN
// Already in Collection
CustomFont_vDot := TempFont_lDot;
EXIT;
END ELSE BEGIN
// OpenXML Element Array
Array_lDot := Array_lDot.CreateInstance(GETDOTNETTYPE(CustomFont_vDot),1);
Array_lDot.SetValue(CustomFont_vDot,0);

Fonts_iDot.Append(Array_lDot);
Fonts_iDot.Count.Value := Fonts_iDot.Count.Value + 1;

CustomFontPool_gDot.Add(Index_iInt, CustomFont_vDot);
END;
//New Code

3) GetCustomCellDecorator_gFnc

1) Function Parameters :
Var Name DataType Subtype Length
No IsBold_iBln Boolean
No IsItalic_iBln Boolean
No IsUnderlined_iBln Boolean
No FontName_iTxt Text 100
No FontSize_iInt Integer
No Color_iInt Integer
Yes Decorator_vDot DotNet Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator.'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
Yes ExcelBuffer_vRec Record Excel Buffer

2) Function Variables :
Name DataType Subtype Length
CustomFont_lDot DotNet DocumentFormat.OpenXml.Spreadsheet.Font.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomFontSize_lDot DotNet DocumentFormat.OpenXml.Spreadsheet.FontSize.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomColor_lDot DotNet DocumentFormat.OpenXml.Spreadsheet.Color.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
HexColor_lDot DotNet DocumentFormat.OpenXml.HexBinaryValue.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
Fonts_lDot DotNet DocumentFormat.OpenXml.Spreadsheet.Fonts.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
FontSizeValue_lDot DotNet DocumentFormat.OpenXml.DoubleValue.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CustomFontName_lDot DotNet DocumentFormat.OpenXml.Spreadsheet.FontName.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
XMLStringValue_lDot DotNet DocumentFormat.OpenXml.StringValue.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
FontIndex_lTxt Text


3) Function Code :

//New Code
WITH ExcelBuffer_vRec DO BEGIN
CLEAR(Decorator_vDot);
CLEAR(CustomFont_lDot);
CLEAR(CustomFontSize_lDot);
CLEAR(CustomColor_lDot);
CLEAR(HexColor_lDot);
CLEAR(Fonts_lDot);
CLEAR(FontSizeValue_lDot);
FontIndex_lTxt := '';
CLEAR(CustomFontPool_gDot);
GetCellDecorator(IsBold_iBln,IsItalic_iBln,IsUnderlined_iBln,Decorator_vDot);

// Handle Extension
IF (Color_iInt <> 0) OR (FontSize_iInt <> 0) THEN BEGIN
FontIndex_lTxt := STRSUBSTNO('%1|%2|%3|%4|%5',IsBold_iBln,IsItalic_iBln,IsUnderlined_iBln,Color_iInt,FontSize_iInt);
CustomFont_lDot := Decorator_vDot.Font.CloneNode(TRUE);

//Font Name
IF "Font Name" <> '' THEN BEGIN
//Replacing font should be the first alteration
CustomFont_lDot := CustomFont_lDot.Font; // (*) Initialisation of variable (default constructor)
CustomFontName_lDot := CustomFontName_lDot.FontName; // (*)
CustomFontName_lDot.Val := XMLStringValue_lDot.StringValue("Font Name");
CustomFont_lDot.FontName := CustomFontName_lDot;
END;

// Font Size
IF FontSize_iInt <> 0 THEN BEGIN
CustomFontSize_lDot := CustomFontSize_lDot.FontSize;
CustomFontSize_lDot.Val := FontSizeValue_lDot.DoubleValue(FontSize_iInt);
CustomFont_lDot.FontSize := CustomFontSize_lDot;
END;

// Color
IF Color_iInt <> 0 THEN BEGIN
CustomColor_lDot := CustomColor_lDot.Color;
CASE Color_iInt OF
1 :
CustomColor_lDot.Rgb := HexColor_lDot.HexBinaryValue('00FF00FF'); //Magenta
2 :
CustomColor_lDot.Rgb := HexColor_lDot.HexBinaryValue('000000FF'); //Blue
3 :
CustomColor_lDot.Rgb := HexColor_lDot.HexBinaryValue('00FF0000'); //Red
END;
CustomFont_lDot.Color := CustomColor_lDot;
END;

Fonts_lDot := XlWrkBkWriter.Workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Fonts;
AddFontToCollection_gFnc(Fonts_lDot,CustomFont_lDot,FontIndex_lTxt);
Decorator_vDot.Font := CustomFont_lDot;
END;
END;
//New Code

4) Change Code as per Below in WriteCellValue Function in Table 370 –Excel Buffer
WITH ExcelBuffer DO BEGIN
//GetCellDecorator(Bold,Italic,Underline,Decorator); //Old Code
GetCustomCellDecorator_gFnc(Bold,Italic,Underline,"Font Name","Font Size","Font Color",Decorator,ExcelBuffer); //New Code
CASE "Cell Type" OF
"Cell Type"::Number:
XlWrkShtWriter.SetCellValueNumber("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
"Cell Type"::Text:
XlWrkShtWriter.SetCellValueText("Row No.",xlColID,"Cell Value as Text",Decorator);
"Cell Type"::Date:
XlWrkShtWriter.SetCellValueDate("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
"Cell Type"::Time:
XlWrkShtWriter.SetCellValueTime("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
ELSE
ERROR(Text039)
END;
END;
5) Change Code as per Below in WriteCellFormula Function in Table 370 –Excel Buffer

WITH ExcelBuffer DO BEGIN
//GetCellDecorator(Bold,Italic,Underline,Decorator); //Old Code
GetCustomCellDecorator_gFnc(Bold,Italic,Underline,"Font Name","Font Size","Font Color",Decorator,ExcelBuffer); //New Code
XlWrkShtWriter.SetCellFormula("Row No.",xlColID,GetFormula,NumberFormat,Decorator);
END;

6) Create Report 80000 – Export to Excel-Font Style

1) Add One Data Item – Table – 27
2) Add Code as per below

Item - OnPreDataItem()
MakeExcelHeader_gFnc;

Item - OnAfterGetRecord()
MakeExcelHeaderBody_gFnc;

Item - OnPostDataItem()
CreateExcelbook_gFnc;

MakeExcelHeader_gFnc()
ExcelBuf_gRec.AddColumnModified_gFnc('Item No.',
FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf_gRec."Cell Type"::Text,'Arial',10,0);

1) First Colored Argument = Font Style
2) Second Colored Argument = Font Size
3) Third Colored Argument = Font Color

ExcelBuf_gRec.AddColumnModified_gFnc('Description',
FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf_gRec."Cell Type"::Text,'Times New Roman',15,1);
ExcelBuf_gRec.AddColumnModified_gFnc('Unit Cost',
FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf_gRec."Cell Type"::Text,'Verdana',20,2);
ExcelBuf_gRec.AddColumnModified_gFnc('Unit Price',
FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf_gRec."Cell Type"::Text,'Arial Black',25,3);
ExcelBuf_gRec.NewRow;

MakeExcelHeaderBody_gFnc()
ExcelBuf_gRec.AddColumnModified_gFnc(Item."No.",
FALSE,'',FALSE,FALSE,FALSE,'@',ExcelBuf_gRec."Cell Type"::Text,'Arial',10,0);
ExcelBuf_gRec.AddColumnModified_gFnc(Item.Description,
FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf_gRec."Cell Type"::Text,'Times New Roman',15,1);
ExcelBuf_gRec.AddColumnModified_gFnc(Item."Unit Cost",
FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf_gRec."Cell Type"::Number,'Verdana',20,2);
ExcelBuf_gRec.AddColumnModified_gFnc(Item."Unit Price",
FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf_gRec."Cell Type"::Number,'Arial Black',25,3);
ExcelBuf_gRec.NewRow;

CreateExcelbook_gFnc()
ExcelBuf_gRec.CreateBookAndOpenExcel('Data','Item Excel',COMPANYNAME,USERID);

7) When you Run Report you got Formatted output in Excel Worksheet

csimoneaux0527

I have created a new table called New Excel Buffer to test this and when running the report I get and error message:

Break On Error Message:
Function ID 1000000002 was called. The object with ID 50009 does not have a member with that ID.

This is the line that where it stops and generates this error
GetCustomCellDecorator_gFnc(Bold,Italic,Underline,"Font Name","Font Size","Font Color",Decorator,ExcelBuffer); //New Code

Table 50009 is the number of the "New Excel Buffer"

Function ID 1000000002 is the ID of GetCustomCellDecorator_gFnc@1000000002
PROCEDURE GetCustomCellDecorator_gFnc@1000000002 (IsBold_iBln@1000000000 : Boolean;IsItalic_iBln@1000000001 : Boolean;IsUnderlined_iBln@1000000002 : Boolean;FontName_iTxt@1000000003 : Text[100];FontSize_iInt@1000000004 : Integer;Color_iInt@1000000005 : Integer;VAR Decorator_vDot@1000000006 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator";VAR ExcelBuffer_vRec@1000000007 : Record 50009);

So I changed from
Function Name Local
GetCustomCellDecorator_gFnc Yes
to
GetCustomCellDecorator_gFnc No

Then I got another error message -
This message is for C/AL programmers: A DotNet variable has not been instantiated. Attempting to call
Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetWriter.DefaultBoldUnderlinedCellDecorator in Table New Excel Buffer: GetCellDecorator.

When running the Debugger this is the line that produces this error
This is under function GetCellDecorator(IsBold : Boolean;IsItalic : Boolean;IsUnderlined : Boolean;VAR Decorator : DotNet "Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator")

IF IsBold AND IsUnderlined THEN BEGIN
Decorator := XlWrkShtWriter.DefaultBoldUnderlinedCellDecorator; //Problem with this line
EXIT;
END;



I tried to find something on the internet. This is what I found

A Dotnet Variable has not been Instantiated. Attempting to call - Dynamics NAV 2013/ NAV 2009

A Dotnet Variable has not been Instantiated. Attempting to call, this error message will pop up when trying to call a .Net Framework Assembly from CAL code.

when you calling a .Net Framework Assembly without constructor call you will get above mentioned error message.

A constructor is a method that creates an instance of an object and prepares it for use. Before you use a DotNet variable, you should verify whether the variable is static or is an object instance because this determines whether you have to use a constructor.
•Static variables do not require a constructor because they do not have an instance.

•Object instance variables require a constructor because they must be created before they can be accessed.

Example Code with constructor Call from CAL code

Asmbly := Asmbly.ConverttoUppercase(); // Constructor Call

ConvsertyedString := Asmbly.ConverttoUp('marshal'); // Method Call

MESSAGE(ConvsertyedString);

Name DataType Subtype

Asmbly DotNet UpperCase.ConverttoUppercase.'UpperCase, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'

I am still at a loss on what I need to do next. I really need this feature. Can you help? I am using Nav 2013

geordie

Can you ensure that, before executing your function, CreateBook was called? It includes the following statement that instantiates the variable:

XlWrkBkWriter := XlWrkBkWriter.Create(FileNameServer);

csimoneaux0527

I wrote the report exactly as listed above.
The CreateExcelBook is on the OnPostDataItem trigger like you have listed.
I created a New Excel Buffer to test. I copies table370 and created table 50009.
See details below.
Thank you for your help.

OBJECT Table 50009 New Excel Buffer
{
OBJECT-PROPERTIES
{
Date=07/09/14;
Time=12:42:32 PM;
Modified=Yes;
Version List=NAVW17.00;
}
PROPERTIES
{
CaptionML=[ENU=Excel Buffer;
ESM=Mem. inter. Excel;
FRC=Tampon Excel;
ENC=Excel Buffer];
}
FIELDS
{
{ 1 ; ;Row No. ;Integer ;OnValidate=BEGIN
xlRowID := '';
IF "Row No." <> 0 THEN
xlRowID := FORMAT("Row No.");
END;

CaptionML=[ENU=Row No.;
ESM=N§ fila;
FRC=Nø rang‚e;
ENC=Row No.] }
{ 2 ; ;xlRowID ;Text10 ;CaptionML=[ENU=xlRowID;
ESM=xlRowID;
FRC=xlIDLigne;
ENC=xlRowID] }
{ 3 ; ;Column No. ;Integer ;OnValidate=VAR
x@1000 : Integer;
i@1001 : Integer;
y@1003 : Integer;
c@1002 : Char;
t@1102601000 : 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 := c;
x := (x - y) DIV 26;
END;
IF x > 0 THEN BEGIN
c := 64 + x;
i := i + 1;
t := c;
END;
FOR x := 1 TO i DO
xlColID[x] := t[1 + i - x];
END;

CaptionML=[ENU=Column No.;
ESM=N§ columna;
FRC=Nø de colonne;
ENC=Column No.] }
{ 4 ; ;xlColID ;Text10 ;CaptionML=[ENU=xlColID;
ESM=xlColID;
FRC=xlIDColonne;
ENC=xlColID] }
{ 5 ; ;Cell Value as Text ;Text250 ;CaptionML=[ENU=Cell Value as Text;
ESM=Valor celda como texto;
FRC=Valeur cellule texte;
ENC=Cell Value as Text] }
{ 6 ; ;Comment ;Text250 ;CaptionML=[ENU=Comment;
ESM=Comentario;
FRC=Commentaire;
ENC=Comment] }
{ 7 ; ;Formula ;Text250 ;CaptionML=[ENU=Formula;
ESM=F¢rmula;
FRC=Formule;
ENC=Formula] }
{ 8 ; ;Bold ;Boolean ;CaptionML=[ENU=Bold;
ESM=Negrita;
FRC=Gras;
ENC=Bold] }
{ 9 ; ;Italic ;Boolean ;CaptionML=[ENU=Italic;
ESM=It lica;
FRC=Italique;
ENC=Italic] }
{ 10 ; ;Underline ;Boolean ;CaptionML=[ENU=Underline;
ESM=Subrayar;
FRC=Soulign‚;
ENC=Underline] }
{ 11 ; ;NumberFormat ;Text30 ;CaptionML=[ENU=NumberFormat;
ESM=FormatoN£mero;
FRC=Format num‚ro;
ENC=NumberFormat] }
{ 12 ; ;Formula2 ;Text250 ;CaptionML=[ENU=Formula2;
ESM=F¢rmula2;
FRC=Formule2;
ENC=Formula2] }
{ 13 ; ;Formula3 ;Text250 ;CaptionML=[ENU=Formula3;
ESM=F¢rmula3;
FRC=Formule3;
ENC=Formula3] }
{ 14 ; ;Formula4 ;Text250 ;CaptionML=[ENU=Formula4;
ESM=F¢rmula4;
FRC=Formule4;
ENC=Formula4] }
{ 15 ; ;Cell Type ;Option ;CaptionML=[ENU=Cell Type;
ESM=Tipo celda;
FRC=Type de cellule;
ENC=Cell Type];
OptionCaptionML=[ENU=Number,Text,Date,Time;
ESM=N£mero,Texto,Fecha,Hora;
FRC=Nombre,Texte,Date,Heure;
ENC=Number,Text,Date,Time];
OptionString=Number,Text,Date,Time }
{ 80000; ;Font Name ;Text100 }
{ 80001; ;Font Size ;Integer }
{ 80002; ;Font Color ;Integer }
}
KEYS
{
{ ;Row No.,Column No. ;Clustered=Yes }
}
FIELDGROUPS
{
}
CODE
{
VAR
Text000@1000 : TextConst 'ENU=Excel not found.;ESM=No encontr¢ Excel.;FRC=Programme Excel non trouv‚.;ENC=Excel not found.';
Text001@1001 : TextConst 'ENU=You must enter a file name.;ESM=Debe introd. nombre fichero;FRC=Vous devez entrer un nom de fichier.;ENC=You must enter a file name.';
Text002@1002 : TextConst 'ENU=You must enter an Excel worksheet name.;ESM=Debe introducir un nombre hoja Excel.;FRC=Vous devez entrer un nom de feuille Excel.;ENC=You must enter an Excel worksheet name.';
Text003@1003 : TextConst 'ENU=The file %1 does not exist.;ESM=El fichero %1 no existe.;FRC=Le fichier %1 n''existe pas.;ENC=The file %1 does not exist.';
Text004@1004 : TextConst 'ENU=The Excel worksheet %1 does not exist.;ESM=La hoja Excel %1 no existe.;FRC=La feuille Excel %1 n''existe pas.;ENC=The Excel worksheet %1 does not exist.';
Text005@1005 : TextConst 'ENU=Creating Excel worksheet...\\;ESM=Creando hoja Excel...\\;FRC=Cr‚ation feuille Excel...\\;ENC=Creating Excel worksheet...\\';
Text006@1006 : TextConst 'ENU=%1%3%4%3Page %2;ESM=%1%3%4%3P g. %2;FRC=%1%3%4%3Page %2;ENC=%1%3%4%3Page %2';
Text007@1007 : TextConst 'ENU=Reading Excel worksheet...\\;ESM=Leyendo hoja Excel...\\;FRC=Lecture feuille Excel...\\;ENC=Reading Excel worksheet...\\';
Text013@1013 : TextConst 'ENU=&B;ESM=&B;FRC=&B;ENC=&B';
Text014@1014 : TextConst 'ENU=&D;ESM=&D;FRC=&D;ENC=&D';
Text015@1015 : TextConst 'ENU=&P;ESM=&P;FRC=&P;ENC=&P';
Text016@1016 : TextConst 'ENU=A1;ESM=A1;FRC=A1;ENC=A1';
Text017@1017 : TextConst 'ENU=SUMIF;ESM=SUMIF;FRC=SUMIF;ENC=SUMIF';
Text018@1018 : TextConst 'ENU=#N/A;ESM=#N/A;FRC=#N/A;ENC=#N/A';
Text019@1019 : TextConst 'ENU=GLAcc;ESM=GLAcc;FRC=GLAcc;ENC=GLAcc';
Text020@1020 : TextConst 'ENU=Period;ESM=Period;FRC=Period;ENC=Period';
Text021@1021 : TextConst 'ENU=Budget;ESM=Budget;FRC=Budget;ENC=Budget';
InfoExcelBuf@1036 : TEMPORARY Record 50009;
FileManagement@1045 : Codeunit 419;
XlWrkBkWriter@1022 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookWriter";
XlWrkBkReader@1023 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookReader";
XlWrkShtWriter@1024 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetWriter";
XlWrkShtReader@1043 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetReader";
XlApp@1044 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
XlWrkBk@1051 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Workbook" RUNONCLIENT;
XlWrkSht@1009 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Worksheet" RUNONCLIENT;
XlHelper@1052 : DotNet "'Microsoft.Dynamics.Nav.Integration.Office, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.Integration.Office.Excel.ExcelHelper" RUNONCLIENT;
ActiveSheetName@1049 : Text[250];
RangeStartXlRow@1034 : Text[30];
RangeStartXlCol@1033 : Text[30];
RangeEndXlRow@1032 : Text[30];
RangeEndXlCol@1031 : Text[30];
FileNameServer@1046 : Text;
CurrentRow@1029 : Integer;
CurrentCol@1030 : Integer;
UseInfoSheet@1035 : Boolean;
Text022@1041 : TextConst 'ENU=CostAcc;ESM=CtaCoste;FRC=CostAcc;ENC=CostAcc';
Text023@1037 : TextConst 'ENU=Information;ESM=Informaci¢n;FRC=Information;ENC=Information';
Text034@1039 : TextConst 'ENU=Excel Files (*.xls*)|*.xls*|All Files (*.*)|*.*;ESM=Archivos de Excel (*.xls*)|*.xls*|Todos los archivos (*.*)|*.*;FRC=Fichiers Excel (*.xls*)|*.xls*|Tous les fichiers (*.*)|*.*;ENC=Excel Files (*.xls*)|*.xls*|All Files (*.*)|*.*';
Text035@1040 : TextConst 'ENU=The operation was canceled.;ESM=Se cancel¢ la operaci¢n.;FRC=L''op‚ration a ‚t‚ annul‚e.;ENC=The operation was cancelled.';
Text036@1042 : TextConst 'ENU=The Excel workbook does not exist.;ESM=El libro Excel no existe.;FRC=Le classeur Excel n''existe pas.;ENC=The Excel workbook does not exist.';
Text037@1047 : TextConst 'ENU=Could not create the Excel workbook.;ESM=No se pudo crear el libro Excel.;FRC=Impossible de cr‚er le classeur Excel.;ENC=Could not create the Excel workbook.';
Text038@1048 : TextConst 'ENU=Global variable %1 is not included for test.;ESM=La variable global %1 no est incluida para pruebas.;FRC=La variable globale %1 n''est pas incluse … des fins de test.;ENC=Global variable %1 is not included for test.';
Text039@1050 : TextConst 'ENU=Cell type has not been set.;ESM=No se ha establecido el tipo de celda.;FRC=Le type de cellule n''a pas ‚t‚ d‚fini.;ENC=Cell type has not been set.';
Text040@1008 : TextConst 'ENU=Export Excel File;ESM=Exportar archivo de Excel;FRC=Exporter fichier Excel;ENC=Export Excel File';
CustomFontPool_gDot@1000000000 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Collections.Generic.Dictionary`2";

PROCEDURE CreateBook@1(SheetName@1000 : Text[250]);
BEGIN
IF SheetName = '' THEN
ERROR(Text002);

FileNameServer := FileManagement.ServerTempFileName('xlsx');
XlWrkBkWriter := XlWrkBkWriter.Create(FileNameServer);
IF ISNULL(XlWrkBkWriter) THEN
ERROR(Text037);

XlWrkShtWriter := XlWrkBkWriter.FirstWorksheet;
IF SheetName <> '' THEN BEGIN
XlWrkShtWriter.Name := SheetName;
ActiveSheetName := SheetName;
END
END;

PROCEDURE OpenBook@2(FileName@1000 : Text;SheetName@1001 : Text[250]);
BEGIN
IF FileName = '' THEN
ERROR(Text001);

IF SheetName = '' THEN
ERROR(Text002);

XlWrkBkReader := XlWrkBkReader.Open(FileName);
IF XlWrkBkReader.HasWorksheet(SheetName) THEN BEGIN
XlWrkShtReader := XlWrkBkReader.GetWorksheetByName(SheetName);
END ELSE BEGIN
QuitExcel;
ERROR(Text004,SheetName);
END;
END;

PROCEDURE UpdateBook@5(FileName@1000 : Text;SheetName@1001 : Text[250]);
BEGIN
IF FileName = '' THEN
ERROR(Text001);

IF SheetName = '' THEN
ERROR(Text002);

FileNameServer := FileName;
XlWrkBkWriter := XlWrkBkWriter.Open(FileNameServer);
IF XlWrkBkWriter.HasWorksheet(SheetName) THEN BEGIN
XlWrkShtWriter := XlWrkBkWriter.GetWorksheetByName(SheetName);
ActiveSheetName := SheetName;
END ELSE BEGIN
QuitExcel;
ERROR(Text004,SheetName);
END;
END;

PROCEDURE CloseBook@30();
BEGIN
IF NOT ISNULL(XlWrkBkWriter) THEN BEGIN
XlWrkBkWriter.ValidateDocument;
XlWrkBkWriter.Close;
CLEAR(XlWrkShtWriter);
CLEAR(XlWrkBkWriter);
END;

IF NOT ISNULL(XlWrkBkReader) THEN BEGIN
CLEAR(XlWrkShtReader);
CLEAR(XlWrkBkReader);
END;
END;

PROCEDURE WriteSheet@37(ReportHeader@1001 : Text[80];CompanyName@1002 : Text[30];UserID2@1003 : Text[30]);
VAR
ExcelBufferDialogMgt@1009 : Codeunit 5370;
OrientationValues@1000 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.OrientationValues";
CRLF@1008 : Char;
RecNo@1010 : Integer;
InfoRecNo@1012 : Integer;
TotalRecNo@1011 : Integer;
LastUpdate@1004 : DateTime;
BEGIN
LastUpdate := CURRENTDATETIME;
ExcelBufferDialogMgt.Open(Text005);

CRLF := 10;
RecNo := 1;
TotalRecNo := COUNT + InfoExcelBuf.COUNT;
RecNo := 0;

XlWrkShtWriter.AddPageSetup(OrientationValues.Landscape);

// commit is required because of the result boolean check of ExcelBufferDialogMgt.RUN
COMMIT;

IF FINDSET THEN
REPEAT
RecNo := RecNo + 1;
IF NOT UpdateProgressDialog(ExcelBufferDialogMgt,LastUpdate,RecNo,TotalRecNo) THEN BEGIN
QuitExcel;
ERROR(Text035)
END;
IF Formula = '' THEN
WriteCellValue(Rec)
ELSE
WriteCellFormula(Rec)
UNTIL NEXT = 0;

IF ReportHeader <> '' THEN
XlWrkShtWriter.AddHeader(
TRUE,
STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName));

XlWrkShtWriter.AddHeader(
FALSE,
STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2));

IF UseInfoSheet THEN BEGIN
IF InfoExcelBuf.FINDSET THEN BEGIN
XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(Text023);
REPEAT
InfoRecNo := InfoRecNo + 1;
IF NOT UpdateProgressDialog(ExcelBufferDialogMgt,LastUpdate,RecNo + InfoRecNo,TotalRecNo) THEN BEGIN
QuitExcel;
ERROR(Text035)
END;
IF InfoExcelBuf.Formula = '' THEN
WriteCellValue(InfoExcelBuf)
ELSE
WriteCellFormula(InfoExcelBuf)
UNTIL InfoExcelBuf.NEXT = 0;
END;
END;

ExcelBufferDialogMgt.Close;
END;

LOCAL PROCEDURE WriteCellValue@28(ExcelBuffer@1000 : Record 50009);
VAR
Decorator@1001 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator";
BEGIN
WITH ExcelBuffer DO BEGIN
//GetCellDecorator(Bold,Italic,Underline,Decorator); //New Code
GetCustomCellDecorator_gFnc(Bold,Italic,Underline,"Font Name","Font Size","Font Color",Decorator,ExcelBuffer); //New Code

CASE "Cell Type" OF
"Cell Type"::Number:
XlWrkShtWriter.SetCellValueNumber("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
"Cell Type"::Text:
XlWrkShtWriter.SetCellValueText("Row No.",xlColID,"Cell Value as Text",Decorator);
"Cell Type"::Date:
XlWrkShtWriter.SetCellValueDate("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
"Cell Type"::Time:
XlWrkShtWriter.SetCellValueTime("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
ELSE
ERROR(Text039)
END;
END;
END;

LOCAL PROCEDURE WriteCellFormula@38(ExcelBuffer@1000 : Record 50009);
VAR
Decorator@1001 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator";
BEGIN
WITH ExcelBuffer DO BEGIN
//GetCellDecorator(Bold,Italic,Underline,Decorator); //Old Code
GetCustomCellDecorator_gFnc(Bold,Italic,Underline,"Font Name","Font Size","Font Color",Decorator,ExcelBuffer); //New Code
XlWrkShtWriter.SetCellFormula("Row No.",xlColID,GetFormula,NumberFormat,Decorator);
END;
END;

LOCAL PROCEDURE GetCellDecorator@33(IsBold@1000 : Boolean;IsItalic@1001 : Boolean;IsUnderlined@1002 : Boolean;VAR Decorator@1003 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator");
BEGIN
IF IsBold AND IsItalic AND IsUnderlined THEN BEGIN
Decorator := XlWrkShtWriter.DefaultBoldItalicUnderlinedCellDecorator;
EXIT;
END;

IF IsBold AND IsItalic THEN BEGIN
Decorator := XlWrkShtWriter.DefaultBoldItalicCellDecorator;
EXIT;
END;

IF IsBold AND IsUnderlined THEN BEGIN
Decorator := XlWrkShtWriter.DefaultBoldUnderlinedCellDecorator; //Problem with this line
EXIT;
END;

IF IsBold THEN BEGIN
Decorator := XlWrkShtWriter.DefaultBoldCellDecorator;
EXIT;
END;

IF IsItalic AND IsUnderlined THEN BEGIN
Decorator := XlWrkShtWriter.DefaultItalicUnderlinedCellDecorator;
EXIT;
END;

IF IsItalic THEN BEGIN
Decorator := XlWrkShtWriter.DefaultItalicCellDecorator;
EXIT;
END;

IF IsUnderlined THEN
Decorator := XlWrkShtWriter.DefaultUnderlinedCellDecorator
ELSE
Decorator := XlWrkShtWriter.DefaultCellDecorator;
END;

PROCEDURE CreateRangeName@9(RangeName@1000 : Text[30];FromColumnNo@1001 : Integer;FromRowNo@1002 : Integer);
VAR
TempExcelBuf@1005 : TEMPORARY Record 370;
ToxlRowID@1004 : 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 GiveUserControl@3();
BEGIN
IF NOT ISNULL(XlApp) THEN BEGIN
XlApp.Visible := TRUE;
XlApp.UserControl := TRUE;
CLEAR(XlApp);
END;
END;

PROCEDURE ReadSheet@4();
VAR
ExcelBufferDialogMgt@1003 : Codeunit 5370;
CellData@1002 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellData";
Enumerator@1001 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Collections.IEnumerator";
i@1000 : Integer;
RowCount@1009 : Integer;
LastUpdate@1004 : DateTime;
BEGIN
LastUpdate := CURRENTDATETIME;
ExcelBufferDialogMgt.Open(Text007);
DELETEALL;

Enumerator := XlWrkShtReader.GetEnumerator;
RowCount := XlWrkShtReader.RowCount;
WHILE Enumerator.MoveNext DO BEGIN
CellData := Enumerator.Current;
IF CellData.HasValue THEN BEGIN
VALIDATE("Row No.",CellData.RowNumber);
VALIDATE("Column No.",CellData.ColumnNumber);
ParseCellValue(CellData.Value,CellData.Format);
INSERT;
END;

i := i + 1;
COMMIT;
IF NOT UpdateProgressDialog(ExcelBufferDialogMgt,LastUpdate,i,RowCount) THEN BEGIN
QuitExcel;
ERROR(Text035)
END;
END;

QuitExcel;
ExcelBufferDialogMgt.Close;
END;

LOCAL PROCEDURE ParseCellValue@40(Value@1000 : Text;FormatString@1001 : Text);
VAR
DateTimeHelper@1006 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.DateTime";
DateTime@1005 : DateTime;
Decimal@1004 : Decimal;
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

NumberFormat := COPYSTR(FormatString,1,30);

IF FormatString = '@' THEN BEGIN
"Cell Type" := "Cell Type"::Text;
"Cell Value as Text" := Value;
EXIT;
END;

EVALUATE(Decimal,Value);

IF STRPOS(FormatString,':') <> 0 THEN BEGIN
// Excel Time is stored in OADate format
DateTime := DateTimeHelper.FromOADate(Decimal);
"Cell Type" := "Cell Type"::Time;
"Cell Value as Text" := FORMAT(DT2TIME(DateTime));
EXIT;
END;

IF ((STRPOS(FormatString,'y') <> 0) OR
(STRPOS(FormatString,'m') <> 0) OR
(STRPOS(FormatString,'d') <> 0)) AND
(STRPOS(FormatString,'Red') = 0)
THEN BEGIN
// Excel Date is stored in OATime format
DateTime := DateTimeHelper.FromOADate(Decimal);
"Cell Type" := "Cell Type"::Date;
"Cell Value as Text" := FORMAT(DT2DATE(DateTime));
EXIT;
END;

"Cell Type" := "Cell Type"::Number;
"Cell Value as Text" := FORMAT(ROUND(Decimal,0.000001),0,1);
END;

PROCEDURE SelectSheetsName@6(FileName@1000 : Text) : Text[250];
VAR
SheetNames@1008 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Collections.ArrayList";
SheetName@1002 : Text[250];
SelectedSheetName@1007 : Text[250];
SheetsList@1004 : Text[250];
EndOfLoop@1003 : Integer;
i@1001 : Integer;
OptionNo@1005 : Integer;
BEGIN
IF FileName = '' THEN
ERROR(Text001);

XlWrkBkReader := XlWrkBkReader.Open(FileName);

SheetNames := SheetNames.ArrayList(XlWrkBkReader.SheetNames);
IF NOT ISNULL(SheetNames) THEN BEGIN
i := 0;
EndOfLoop := SheetNames.Count - 1;
WHILE i <= EndOfLoop DO BEGIN
SheetName := SheetNames.Item(i);
IF (SheetName <> '') AND (STRLEN(SheetsList) + STRLEN(SheetName) < 250) THEN
SheetsList := SheetsList + SheetName + ','
ELSE
i := EndOfLoop;
i := i + 1;
END;

IF SheetsList <> '' THEN BEGIN
OptionNo := STRMENU(SheetsList,1);
IF OptionNo <> 0 THEN
SelectedSheetName := SELECTSTR(OptionNo,SheetsList);
END
END;

QuitExcel;
EXIT(SelectedSheetName);
END;

PROCEDURE GetExcelReference@10(Which@1000 : Integer) : Text[250];
BEGIN
CASE Which OF
1:
EXIT(Text013);
// DO NOT TRANSLATE: &B is the Excel code to turn bold printing on or off for customized Header/Footer.
2:
EXIT(Text014);
// DO NOT TRANSLATE: &D is the Excel code to print the current date in customized Header/Footer.
3:
EXIT(Text015);
// 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(Text016);
// DO NOT TRANSLATE: A1 is the Excel reference of the first cell.
6:
EXIT(Text017);
// DO NOT TRANSLATE: SUMIF is the name of the Excel function used to summarize values according to some conditions.
7:
EXIT(Text018);
// DO NOT TRANSLATE: The #N/A Excel error value occurs when a value is not available to a function or formula.
8:
EXIT(Text019);
// DO NOT TRANSLATE: GLAcc is used to define an Excel range name. You must refer to Excel rules to change this term.
9:
EXIT(Text020);
// DO NOT TRANSLATE: Period is used to define an Excel range name. You must refer to Excel rules to change this term.
10:
EXIT(Text021);
// DO NOT TRANSLATE: Budget is used to define an Excel worksheet name. You must refer to Excel rules to change this term.
11:
EXIT(Text022);
// DO NOT TRANSLATE: CostAcc is used to define an Excel range name. You must refer to Excel rules to change this term.
END;
END;

PROCEDURE ExportBudgetFilterToFormula@11(VAR ExcelBuf@1000 : Record 370) : Boolean;
VAR
ExcelBufFormula@1001 : TEMPORARY Record 370;
FirstRow@1002 : Integer;
LastRow@1003 : Integer;
HasFormulaError@1005 : Boolean;
ThisCellHasFormulaError@1006 : Boolean;
ExcelBufFormula2@1004 : TEMPORARY Record 370;
BEGIN
ExcelBuf.SETFILTER(Formula,'<>%1','');
IF ExcelBuf.FINDSET THEN
REPEAT
ExcelBufFormula := ExcelBuf;
ExcelBufFormula.INSERT;
UNTIL ExcelBuf.NEXT = 0;
ExcelBuf.RESET;

WITH ExcelBufFormula DO
IF FINDSET THEN
REPEAT
ThisCellHasFormulaError := FALSE;
ExcelBuf.SETRANGE("Column No.",1);
ExcelBuf.SETFILTER("Row No.",'<>%1',"Row No.");
ExcelBuf.SETFILTER("Cell Value as Text",Formula);
ExcelBufFormula2 := ExcelBufFormula;
IF ExcelBuf.FINDSET THEN
REPEAT
IF NOT GET(ExcelBuf."Row No.","Column No.") THEN
ExcelBuf.MARK(TRUE);
UNTIL ExcelBuf.NEXT = 0;
ExcelBufFormula := ExcelBufFormula2;
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 := AddToFormula(xlColID + FORMAT(FirstRow))
ELSE
ThisCellHasFormulaError :=
AddToFormula('SUM(' + xlColID + FORMAT(FirstRow) + ':' + xlColID + FORMAT(LastRow) + ')');
FirstRow := 0;
IF ThisCellHasFormulaError THEN
SetFormula(ExcelBuf.GetExcelReference(7));
END;
UNTIL ThisCellHasFormulaError OR (ExcelBuf.NEXT = 0);

IF NOT ThisCellHasFormulaError AND (FirstRow <> 0) THEN BEGIN
IF FirstRow = LastRow THEN
ThisCellHasFormulaError := AddToFormula(xlColID + FORMAT(FirstRow))
ELSE
ThisCellHasFormulaError :=
AddToFormula('SUM(' + xlColID + FORMAT(FirstRow) + ':' + xlColID + FORMAT(LastRow) + ')');
FirstRow := 0;
IF ThisCellHasFormulaError THEN
SetFormula(ExcelBuf.GetExcelReference(7));
END;

ExcelBuf.RESET;
ExcelBuf.GET("Row No.","Column No.");
ExcelBuf.SetFormula(GetFormula);
ExcelBuf.MODIFY;
HasFormulaError := HasFormulaError OR ThisCellHasFormulaError;
UNTIL NEXT = 0;

EXIT(HasFormulaError);
END;

PROCEDURE AddToFormula@12(Text@1001 : Text[30]) : Boolean;
VAR
Overflow@1002 : Boolean;
LongFormula@1000 : 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@13() : Text[1000];
BEGIN
EXIT(Formula + Formula2 + Formula3 + Formula4);
END;

PROCEDURE SetFormula@22(LongFormula@1000 : 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@18();
BEGIN
Formula := '';
Formula2 := '';
Formula3 := '';
Formula4 := '';
END;

PROCEDURE NewRow@14();
BEGIN
CurrentRow := CurrentRow + 1;
CurrentCol := 0;
END;

PROCEDURE AddColumn@16(Value@1000 : Variant;IsFormula@1001 : Boolean;CommentText@1002 : Text[1000];IsBold@1003 : Boolean;IsItalics@1004 : Boolean;IsUnderline@1005 : Boolean;NumFormat@1006 : Text[30];CellType@1007 : Option);
BEGIN
IF CurrentRow < 1 THEN
NewRow;

CurrentCol := CurrentCol + 1;
INIT;
VALIDATE("Row No.",CurrentRow);
VALIDATE("Column No.",CurrentCol);
IF IsFormula THEN
SetFormula(FORMAT(Value))
ELSE
"Cell Value as Text" := FORMAT(Value);
Comment := CommentText;
Bold := IsBold;
Italic := IsItalics;
Underline := IsUnderline;
NumberFormat := NumFormat;
"Cell Type" := CellType;
INSERT;
END;

PROCEDURE StartRange@19();
VAR
TempExcelBuf@1000 : TEMPORARY Record 370;
BEGIN
TempExcelBuf.VALIDATE("Row No.",CurrentRow);
TempExcelBuf.VALIDATE("Column No.",CurrentCol);

RangeStartXlRow := TempExcelBuf.xlRowID;
RangeStartXlCol := TempExcelBuf.xlColID;
END;

PROCEDURE EndRange@23();
VAR
TempExcelBuf@1000 : TEMPORARY Record 370;
BEGIN
TempExcelBuf.VALIDATE("Row No.",CurrentRow);
TempExcelBuf.VALIDATE("Column No.",CurrentCol);

RangeEndXlRow := TempExcelBuf.xlRowID;
RangeEndXlCol := TempExcelBuf.xlColID;
END;

PROCEDURE CreateRange@45(RangeName@1000 : Text[250]) : Boolean;
BEGIN
XlWrkShtWriter.AddRange(
RangeName,
GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +
':' +
GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow);
END;

PROCEDURE AutoFit@20(RangeName@1000 : Text[50]);
BEGIN
IF NOT ISNULL(XlWrkBk) THEN
XlHelper.AutoFitRangeColumns(XlWrkBk,ActiveSheetName,RangeName);
END;

PROCEDURE BorderAround@39(RangeName@1000 : Text[50]);
BEGIN
IF NOT ISNULL(XlWrkBk) THEN
XlHelper.BorderAroundRange(XlWrkBk,ActiveSheetName,RangeName,1);
END;

PROCEDURE ClearNewRow@26();
BEGIN
CurrentRow := 0;
CurrentCol := 0;
END;

PROCEDURE SetUseInfoSheet@25();
BEGIN
UseInfoSheet := TRUE;
END;

PROCEDURE AddInfoColumn@24(Value@1006 : Variant;IsFormula@1005 : Boolean;CommentText@1004 : Text[1000];IsBold@1003 : Boolean;IsItalics@1002 : Boolean;IsUnderline@1001 : Boolean;NumFormat@1000 : Text[30];CellType@1007 : Option);
BEGIN
IF CurrentRow < 1 THEN
NewRow;

CurrentCol := CurrentCol + 1;
INIT;
InfoExcelBuf.VALIDATE("Row No.",CurrentRow);
InfoExcelBuf.VALIDATE("Column No.",CurrentCol);
IF IsFormula THEN
InfoExcelBuf.SetFormula(FORMAT(Value))
ELSE
InfoExcelBuf."Cell Value as Text" := FORMAT(Value);
InfoExcelBuf.Bold := IsBold;
InfoExcelBuf.Italic := IsItalics;
InfoExcelBuf.Underline := IsUnderline;
InfoExcelBuf.NumberFormat := NumFormat;
InfoExcelBuf."Cell Type" := CellType;
InfoExcelBuf.INSERT;
END;

PROCEDURE UTgetGlobalValue@35(globalVariable@1001 : Text[30];VAR value@1000 : Variant);
BEGIN
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(Text038,globalVariable);
END;
END;

PROCEDURE SetCurrent@27(NewCurrentRow@1000 : Integer;NewCurrentCol@1001 : Integer);
BEGIN
CurrentRow := NewCurrentRow;
CurrentCol := NewCurrentCol;
END;

PROCEDURE CreateValidationRule@17(Range@1000 : Code[20]);
BEGIN
XlWrkShtWriter.AddRangeDataValidation(
Range,
GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +
':' +
GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow);
END;

PROCEDURE QuitExcel@29();
BEGIN
// Close and clear the OpenXml book
CloseBook;

// Clear the worksheet automation
IF NOT ISNULL(XlWrkSht) THEN
CLEAR(XlWrkSht);

// Clear the workbook automation
IF NOT ISNULL(XlWrkBk) THEN
CLEAR(XlWrkBk);

// Clear and quit the Excel application automation
IF NOT ISNULL(XlApp) THEN BEGIN
XlHelper.CallQuit(XlApp);
CLEAR(XlApp);
END;
END;

PROCEDURE OpenExcel@31();
VAR
FileNameClient@1000 : Text;
BEGIN
IF NOT PreOpenExcel THEN
EXIT;

// rename the Temporary filename into a more UI friendly name in a new subdirectory
FileNameClient := FileManagement.DownloadTempFile(FileNameServer);
FileNameClient := FileManagement.MoveAndRenameClientFile(FileNameClient,'Book1.xlsx',FORMAT(CREATEGUID));

XlWrkBk := XlHelper.CallOpen(XlApp,FileNameClient);

PostOpenExcel;
END;

PROCEDURE OverwriteAndOpenExistingExcel@15(FileName@1000 : Text);
BEGIN
IF FileName = '' THEN
ERROR(Text001);

IF NOT PreOpenExcel THEN
EXIT;

FileManagement.DownloadToFile(FileNameServer,FileName);
XlWrkBk := XlHelper.CallOpen(XlApp,FileName);

PostOpenExcel;
END;

LOCAL PROCEDURE PreOpenExcel@7() : Boolean;
BEGIN
IF NOT EXISTS(FileNameServer) THEN
ERROR(Text003,FileNameServer);

// Download file, if none RTC it should return a filename, and use client automation instead.
IF NOT FileManagement.CanRunDotNetOnClient THEN BEGIN
IF NOT FileManagement.DownloadHandler(FileNameServer,Text040,'',Text034,'Book1.xlsx') THEN
ERROR(Text001);
EXIT(FALSE);
END;

XlApp := XlApp.ApplicationClass;
IF ISNULL(XlApp) THEN
ERROR(Text000);

EXIT(TRUE);
END;

LOCAL PROCEDURE PostOpenExcel@8();
BEGIN
XlWrkBk := XlApp.ActiveWorkbook;

IF ISNULL(XlWrkBk) THEN
ERROR(Text036);

// autofit all columns on all worksheets
XlHelper.AutoFitColumnsInAllWorksheets(XlWrkBk);

// activate the previous saved sheet name in the workbook
XlHelper.ActivateSheet(XlWrkBk,ActiveSheetName);
END;

PROCEDURE CreateBookAndOpenExcel@32(SheetName@1000 : Text[250];ReportHeader@1003 : Text[80];CompanyName@1002 : Text[30];UserID2@1001 : Text[30]);
BEGIN
CreateBook(SheetName);
WriteSheet(ReportHeader,CompanyName,UserID2);
CloseBook;
OpenExcel;
GiveUserControl;
END;

LOCAL PROCEDURE UpdateProgressDialog@36(VAR ExcelBufferDialogManagement@1000 : Codeunit 5370;VAR LastUpdate@1001 : DateTime;CurrentCount@1002 : Integer;TotalCount@1004 : Integer) : Boolean;
VAR
CurrentTime@1003 : DateTime;
BEGIN
// 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;
ExcelBufferDialogManagement.SetProgress(ROUND(CurrentCount / TotalCount * 10000,1));
IF NOT ExcelBufferDialogManagement.RUN THEN
EXIT(FALSE);
END;

EXIT(TRUE)
END;

PROCEDURE AddColumnModified_gFnc@1000000000(Value@1000000000 : Variant;IsFormula@1000000001 : Boolean;CommentText@1000000002 : Text[1000];IsBold@1000000003 : Boolean;IsItalics@1000000004 : Boolean;IsUnderline@1000000005 : Boolean;NumFormat@1000000006 : Text[30];CellType@1000000007 : 'Number,Text,Date,Time';FontName_iTxt@1000000008 : Text[100];FontSize_iInt@1000000009 : Integer;FontColor_iInt@1000000010 : Integer);
BEGIN
//New Code
IF CurrentRow < 1 THEN
NewRow;

CurrentCol := CurrentCol + 1;
INIT;
VALIDATE("Row No.",CurrentRow);
VALIDATE("Column No.",CurrentCol);
IF IsFormula THEN
SetFormula(FORMAT(Value))
ELSE
"Cell Value as Text" := FORMAT(Value);
Comment := CommentText;
Bold := IsBold;
Italic := IsItalics;
Underline := IsUnderline;
NumberFormat := NumFormat;
"Cell Type" := CellType;
"Font Name" := FontName_iTxt;
"Font Size" := FontSize_iInt;
"Font Color" := FontColor_iInt;
INSERT;
//New Code
END;

PROCEDURE AddFontToCollection_gFnc@1000000001(Fonts_iDot@1000000000 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.Fonts";CustomFont_vDot@1000000001 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.OpenXmlElement";Index_iInt@1000000002 : Text[100]) : Boolean;
VAR
Array_lDot@1000000003 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
TempFont_lDot@1000000004 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.OpenXmlElement";
BEGIN
//New Code
IF ISNULL(CustomFontPool_gDot) THEN
CustomFontPool_gDot := CustomFontPool_gDot.Dictionary();

IF CustomFontPool_gDot.TryGetValue(Index_iInt,TempFont_lDot) THEN BEGIN
// Already in Collection
CustomFont_vDot := TempFont_lDot;
EXIT;
END ELSE BEGIN
// OpenXML Element Array
Array_lDot := Array_lDot.CreateInstance(GETDOTNETTYPE(CustomFont_vDot),1);
Array_lDot.SetValue(CustomFont_vDot,0);

Fonts_iDot.Append(Array_lDot);
Fonts_iDot.Count.Value := Fonts_iDot.Count.Value + 1;

CustomFontPool_gDot.Add(Index_iInt, CustomFont_vDot);
END;
//New Code
END;

LOCAL PROCEDURE GetCustomCellDecorator_gFnc@1000000002(IsBold_iBln@1000000000 : Boolean;IsItalic_iBln@1000000001 : Boolean;IsUnderlined_iBln@1000000002 : Boolean;FontName_iTxt@1000000003 : Text[100];FontSize_iInt@1000000004 : Integer;Color_iInt@1000000005 : Integer;VAR Decorator_vDot@1000000006 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator";VAR ExcelBuffer_vRec@1000000007 : Record 50009);
VAR
CustomFont_lDot@1000000008 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.Font";
CustomFontSize_lDot@1000000009 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.FontSize";
CustomColor_lDot@1000000010 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.Color";
HexColor_lDot@1000000011 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.HexBinaryValue";
Fonts_lDot@1000000012 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.Fonts";
FontSizeValue_lDot@1000000013 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.DoubleValue";
CustomFontName_lDot@1000000014 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.FontName";
XMLStringValue_lDot@1000000015 : DotNet "'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.StringValue";
FontIndex_lTxt@1000000016 : Text[100];
BEGIN
//New Code
WITH ExcelBuffer_vRec DO BEGIN
CLEAR(Decorator_vDot);
CLEAR(CustomFont_lDot);
CLEAR(CustomFontSize_lDot);
CLEAR(CustomColor_lDot);
CLEAR(HexColor_lDot);
CLEAR(Fonts_lDot);
CLEAR(FontSizeValue_lDot);
FontIndex_lTxt := '';
CLEAR(CustomFontPool_gDot);
GetCellDecorator(IsBold_iBln,IsItalic_iBln,IsUnderlined_iBln,Decorator_vDot);

// Handle Extension
IF (Color_iInt <> 0) OR (FontSize_iInt <> 0) THEN BEGIN
FontIndex_lTxt := STRSUBSTNO('%1|%2|%3|%4|%5',IsBold_iBln,IsItalic_iBln,IsUnderlined_iBln,Color_iInt,FontSize_iInt);
CustomFont_lDot := Decorator_vDot.Font.CloneNode(TRUE);

//Font Name
IF "Font Name" <> '' THEN BEGIN
//Replacing font should be the first alteration
CustomFont_lDot := CustomFont_lDot.Font; // (*) Initialisation of variable (default constructor)
CustomFontName_lDot := CustomFontName_lDot.FontName; // (*)
CustomFontName_lDot.Val := XMLStringValue_lDot.StringValue("Font Name");
CustomFont_lDot.FontName := CustomFontName_lDot;
END;

// Font Size
IF FontSize_iInt <> 0 THEN BEGIN
CustomFontSize_lDot := CustomFontSize_lDot.FontSize;
CustomFontSize_lDot.Val := FontSizeValue_lDot.DoubleValue(FontSize_iInt);
CustomFont_lDot.FontSize := CustomFontSize_lDot;
END;

// Color
IF Color_iInt <> 0 THEN BEGIN
CustomColor_lDot := CustomColor_lDot.Color;
CASE Color_iInt OF
1 :
CustomColor_lDot.Rgb := HexColor_lDot.HexBinaryValue('00FF00FF'); //Magenta
2 :
CustomColor_lDot.Rgb := HexColor_lDot.HexBinaryValue('000000FF'); //Blue
3 :
CustomColor_lDot.Rgb := HexColor_lDot.HexBinaryValue('00FF0000'); //Red
END;
CustomFont_lDot.Color := CustomColor_lDot;
END;

Fonts_lDot := XlWrkBkWriter.Workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Fonts;
AddFontToCollection_gFnc(Fonts_lDot,CustomFont_lDot,FontIndex_lTxt);
Decorator_vDot.Font := CustomFont_lDot;
END;
END;
//New Code
END;

BEGIN
END.
}
}

OBJECT Report 50020 Testing NewExcelBuffer Fonts
{
OBJECT-PROPERTIES
{
Date=07/09/14;
Time=11:41:46 AM;
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
DATASET
{
{ 1000000000;;DataItem; ;
DataItemTable=Table18;
OnPreDataItem=BEGIN
MakeExcelHeader_gFnc;
END;

OnAfterGetRecord=BEGIN
MakeExcelHeaderBody_gFnc;
END;

OnPostDataItem=BEGIN
CreateExcelbook_gFnc;
END;
}

}
REQUESTPAGE
{
PROPERTIES
{
}
CONTROLS
{
}
}
LABELS
{
}
CODE
{
VAR
ExcelBuf_gRec@1000000000 : Record 50009;

PROCEDURE MakeExcelHeader_gFnc@1000000000();
BEGIN
ExcelBuf_gRec.DELETEALL;
ExcelBuf_gRec.AddColumnModified_gFnc('Customer No.',
FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf_gRec."Cell Type"::Text,'Arial',10,0);

{
1) First Colored Argument = Font Style
2) Second Colored Argument = Font Size
3) Third Colored Argument = Font Color
}

ExcelBuf_gRec.AddColumnModified_gFnc('Name',
FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf_gRec."Cell Type"::Text,'Times New Roman',15,1);
ExcelBuf_gRec.AddColumnModified_gFnc('Address',
FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf_gRec."Cell Type"::Text,'Verdana',20,2);
ExcelBuf_gRec.AddColumnModified_gFnc('Balance',
FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf_gRec."Cell Type"::Text,'Arial Black',25,3);
ExcelBuf_gRec.NewRow;
END;

PROCEDURE MakeExcelHeaderBody_gFnc@1000000001();
BEGIN
ExcelBuf_gRec.AddColumnModified_gFnc(Customer."No.",
FALSE,'',FALSE,FALSE,FALSE,'@',ExcelBuf_gRec."Cell Type"::Text,'Arial',10,0);
ExcelBuf_gRec.AddColumnModified_gFnc(Customer.Name,
FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf_gRec."Cell Type"::Text,'Times New Roman',15,1);
ExcelBuf_gRec.AddColumnModified_gFnc(Customer."Balance (LCY)",
FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf_gRec."Cell Type"::Number,'Verdana',20,2);
ExcelBuf_gRec.AddColumnModified_gFnc(Customer."Sales (LCY)",
FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf_gRec."Cell Type"::Number,'Arial Black',25,3);
ExcelBuf_gRec.NewRow;
END;

PROCEDURE CreateExcelbook_gFnc@1000000002();
BEGIN
ExcelBuf_gRec.CreateBookAndOpenExcel('Data','Cust Excel',COMPANYNAME,USERID);
END;

BEGIN
END.
}
RDLDATA
{
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner&quot; xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"&gt;
<DataSources>
<DataSource Name="DataSource">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString />
</ConnectionProperties>
</DataSource>
</DataSources>
<Body>
<Height>2in</Height>
<Style />
</Body>
<Code>Public Function BlankZero(ByVal Value As Decimal)
if Value = 0 then
Return ""
end if
Return Value
End Function

Public Function BlankPos(ByVal Value As Decimal)
if Value > 0 then
Return ""
end if
Return Value
End Function

Public Function BlankZeroAndPos(ByVal Value As Decimal)
if Value >= 0 then
Return ""
end if
Return Value
End Function

Public Function BlankNeg(ByVal Value As Decimal)
if Value < 0 then
Return ""
end if
Return Value
End Function

Public Function BlankNegAndZero(ByVal Value As Decimal)
if Value <= 0 then
Return ""
end if
Return Value
End Function
</Code>
<Width>6.5in</Width>
<Page>
<Style />
</Page>
<Language>=User!Language</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportID>0eeb6585-38ae-40f1-885b-8d50088d51b4</rd:ReportID>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
</Report>
END_OF_RDLDATA
}
}

shrekut

Can someone please explain how to expand this Excel Buffer to include things like Merge Cells and Column Width?