mibuso.com
Home› NAV Three Tier
Options

Create Excel Sheet data with Different Font Style in NAV2013

jigneshdhandhajigneshdhandha Member Posts: 41
edited 2015-03-02 in NAV Three Tier
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

Answers

  • Options
    csimoneaux0527csimoneaux0527 Member Posts: 21
    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
  • Options
    geordiegeordie Member Posts: 655
    Can you ensure that, before executing your function, CreateBook was called? It includes the following statement that instantiates the variable:
    XlWrkBkWriter := XlWrkBkWriter.Create(FileNameServer);
    * Daniele Rebussi * | * Rebu NAV Diary *
  • Options
    csimoneaux0527csimoneaux0527 Member Posts: 21
    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
    }
    }
  • Options
    shrekutshrekut Member Posts: 27
    Can someone please explain how to expand this Excel Buffer to include things like Merge Cells and Column Width?
Sign In or Register to comment.