mibuso.com
Home› NAV Three Tier

ClosedXML ExcelApplicationClass error, NAV2013R2

Miklos_HollenderMiklos_Hollender Member Posts: 1,598
edited 2015-01-14 in NAV Three Tier
Since you still cannot make a good looking Excel File with the NAS (for automatic e-maiing of Excel reports) in NAV2013R2 I took the ClosedXML solution that was kicking around on the blogs, made it (in a quick and dirty way, I am in a hurry, during a go-live) compatible with NAV2013R2 and actually it works on the NAS now, you can borrow it for that purpose! Using the Save As function, then sleep for five secs and send an e-mail with the SMPT codeunit, easy way to auto-send reports!

But when I also want to use it for the user-ran reports they want to see on the screen, with the GiveUserControlDotNet, I get this error message:

Cannot create an instance of the following .NET Framework object: assembly Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c, type Microsoft.Office.Interop.Excel.ApplicationClass.

Excel Version 12 = Version 2007 and I have exactly that installed, so I don't understand the problem!

File:
OBJECT Table 50004 ClosedXML Excel Buffer
{
  OBJECT-PROPERTIES
  {
    Date=07-01-15;
    Time=10:26:12;
    Modified=Yes;
    Version List=NAVW16.00.01;
  }
  PROPERTIES
  {
    CaptionML=ENU=Excel Buffer;
  }
  FIELDS
  {
    { 1   ;   ;Row No.             ;Integer       ;OnValidate=BEGIN
                                                                xlRowID := '';
                                                                IF "Row No." <> 0 THEN
                                                                  xlRowID := FORMAT("Row No.");
                                                              END;

                                                   CaptionML=ENU=Row No. }
    { 2   ;   ;xlRowID             ;Text10        ;CaptionML=ENU=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[i] := c;
                                                                  x := (x - y) DIV 26;
                                                                END;
                                                                IF x > 0 THEN BEGIN
                                                                  c := 64 + x;
                                                                  i := i + 1;
                                                                  t[i] := c;
                                                                END;
                                                                FOR x := 1 TO i DO
                                                                  xlColID[x] := t[1 + i - x];
                                                              END;

                                                   CaptionML=ENU=Column No. }
    { 4   ;   ;xlColID             ;Text10        ;CaptionML=ENU=xlColID }
    { 5   ;   ;Cell Value as Text  ;Text250       ;CaptionML=ENU=Cell Value as Text }
    { 6   ;   ;Comment             ;Text250       ;CaptionML=ENU=Comment }
    { 7   ;   ;Formula             ;Text250       ;CaptionML=ENU=Formula }
    { 8   ;   ;Bold                ;Boolean       ;CaptionML=ENU=Bold }
    { 9   ;   ;Italic              ;Boolean       ;CaptionML=ENU=Italic }
    { 10  ;   ;Underline           ;Boolean       ;CaptionML=ENU=Underline }
    { 11  ;   ;NumberFormat        ;Text30        ;CaptionML=ENU=NumberFormat }
    { 12  ;   ;Formula2            ;Text250       ;CaptionML=ENU=Formula2 }
    { 13  ;   ;Formula3            ;Text250       ;CaptionML=ENU=Formula3 }
    { 14  ;   ;Formula4            ;Text250       ;CaptionML=ENU=Formula4 }
  }
  KEYS
  {
    {    ;Row No.,Column No.                      ;Clustered=Yes }
  }
  FIELDGROUPS
  {
  }
  CODE
  {
    VAR
      Text000@1000 : TextConst 'ENU=Excel not found.';
      Text001@1001 : TextConst 'ENU=You must enter a file name.';
      Text002@1002 : TextConst 'ENU=You must enter an Excel worksheet name.';
      Text003@1003 : TextConst 'ENU=The file %1 does not exist.';
      Text004@1004 : TextConst 'ENU=The Excel worksheet %1 does not exist.';
      Text005@1005 : TextConst 'ENU=Creating Excel worksheet...\\';
      Text006@1006 : TextConst 'ENU=%1%3%4%3Page %2';
      Text007@1007 : TextConst 'ENU=Reading Excel worksheet...\\';
      Text008@1008 : TextConst 'ENU=": Filter "';
      Text009@1009 : TextConst 'ENU=" can not be converted into an Excel formula."';
      Text010@1010 : TextConst 'ENU=": Operator "';
      Text011@1011 : TextConst 'ENU=" is not valid."';
      Text012@1012 : TextConst 'ENU=: Filter containing more than 1 comparison operator can not be converted into an Excel formula.';
      Text013@1013 : TextConst 'ENU=&B';
      Text014@1014 : TextConst 'ENU=&D';
      Text015@1015 : TextConst 'ENU=&P';
      Text016@1016 : TextConst 'ENU=A1';
      Text017@1017 : TextConst 'ENU=SUMIF';
      Text018@1018 : TextConst 'ENU=#N/A';
      Text019@1019 : TextConst 'ENU=GLAcc';
      Text020@1020 : TextConst 'ENU=Period';
      Text021@1021 : TextConst 'ENU=Budget';
      InfoExcelBuf@1036 : TEMPORARY Record 370;
      XlApp@1022 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00024500-0000-0000-C000-000000000046}:Unknown Automation Server.Application";
      XlWrkBk@1023 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020819-0000-0000-C000-000000000046}:Unknown Automation Server.Workbook";
      XlWrkSht@1024 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020820-0000-0000-C000-000000000046}:Unknown Automation Server.Worksheet";
      XlWrkshts@1025 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{000208D8-0000-0000-C000-000000000046}:Unknown Automation Server._Worksheet";
      XlRange@1026 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020846-0000-0000-C000-000000000046}:Unknown Automation Server.Range";
      FormulaUnitErr@1027 : Text[250];
      RangeStartXlRow@1034 : Text[30];
      RangeStartXlCol@1033 : Text[30];
      RangeEndXlRow@1032 : Text[30];
      RangeEndXlCol@1031 : Text[30];
      CurrentRow@1029 : Integer;
      CurrentCol@1030 : Integer;
      UseInfoSheed@1035 : Boolean;
      Text023@1037 : TextConst 'ENU=Information';
      Text026@1028 : TextConst 'ENU=Default.xls';
      Text033@1038 : TextConst 'ENU=Download';
      Text034@1039 : TextConst 'ENU=Excel file (*.xls)|*.xls';
      XlWrkBkDotNet@150002024 : DotNet "'ClosedXML, Version=0.69.1.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLWorkbook";
      XlWrkShtDotNet@150002025 : DotNet "'ClosedXML, Version=0.69.1.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheet";
      XlWrkshtsDotNet@150002026 : DotNet "'ClosedXML, Version=0.69.1.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheets";
      XlRangeDotNet@150002027 : DotNet "'ClosedXML, Version=0.69.1.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLRange";
      XLStyleDotNet@150002029 : DotNet "'ClosedXML, Version=0.69.1.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLStyle";

    PROCEDURE CreateBook@1();
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN BEGIN
        XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();
        XlWrkShtDotNet := XlWrkBkDotNet.Worksheets.Add('Sheet1');
      END ELSE BEGIN
      // .Net <<
        IF NOT CREATE(XlApp,TRUE,TRUE) THEN
          ERROR(Text000);
        XlApp.Visible(FALSE);
        XlWrkBk := XlApp.Workbooks.Add;
        XlWrkSht := XlWrkBk.Worksheets.Add;
      END;
    END;

    PROCEDURE OpenBook@2(FileName@1000 : Text[250];SheetName@1001 : Text[250]);
    VAR
      i@1002 : Integer;
      EndOfLoop@1003 : Integer;
      Found@1004 : Boolean;
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN BEGIN
        OpenBookDotNet(FileName,SheetName);
        EXIT;
      END;
      // .Net <<
      IF FileName = '' THEN
        ERROR(Text001);

      IF SheetName = '' THEN
        ERROR(Text002);

      IF NOT ISSERVICETIER THEN
        IF NOT EXISTS(FileName) THEN
          ERROR(Text003,FileName);

      IF NOT CREATE(XlApp,TRUE,TRUE) THEN
        ERROR(Text000);

      XlApp.Workbooks.Open(FileName);
      XlWrkBk := XlApp.ActiveWorkbook;
      i := 1;
      EndOfLoop := XlWrkBk.Worksheets.Count;
      WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN
        XlWrkshts := XlWrkBk.Worksheets.Item(i);
        IF SheetName = XlWrkshts.Name THEN
          Found := TRUE;
        i := i + 1;
      END;
      IF Found THEN
        XlWrkSht := XlWrkBk.Worksheets.Item(SheetName)
      ELSE BEGIN
        XlWrkBk.Close(FALSE);
        XlApp.Quit;
        CLEAR(XlApp);
        ERROR(Text004,SheetName);
      END;
    END;

    PROCEDURE OpenBookDotNet@150002024(FileName@1000 : Text[250];SheetName@1001 : Text[250]);
    VAR
      i@1002 : Integer;
      EndOfLoop@1003 : Integer;
      Found@1004 : Boolean;
      FileRTC@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File" RUNONCLIENT;
      PathRTC@150002029 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.Path" RUNONCLIENT;
      ThreeTierMgt@150002025 : Codeunit 419;
      NVInStream@150002027 : InStream;
      MemoryStream@150002028 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      ClientTempFileName@150002026 : Text[1024];
    BEGIN
      // .Net >>
      IF FileName = '' THEN
        ERROR(Text001);

      IF SheetName = '' THEN
        ERROR(Text002);

      //Use System.IO.File to check file on RTC
      IF NOT FileRTC.Exists(FileName) THEN
        ERROR(Text003,FileName);

      //Copy file to RTC Temp folder and upload it to server in a stream

      //
      //ClientTempFileName := ThreeTierMgt.ClientTempFileName('',PathRTC.GetExtension(FileName));
      ClientTempFileName := ThreeTierMgt.ClientTempFileName(PathRTC.GetExtension(FileName));


      FileRTC.Copy(FileName, ClientTempFileName);
      UPLOADINTOSTREAM('',ThreeTierMgt.Magicpath,'',ClientTempFileName,NVInStream);
      MemoryStream := NVInStream;

      XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook(MemoryStream);
      i := 1;
      EndOfLoop := XlWrkBkDotNet.Worksheets.Count;
      WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN
        XlWrkShtDotNet := XlWrkBkDotNet.Worksheet(i);
        IF SheetName = XlWrkShtDotNet.Name THEN
          Found := TRUE;
        i := i + 1;
      END;
      IF Found THEN
        XlWrkShtDotNet := XlWrkBkDotNet.Worksheet(SheetName)
      ELSE BEGIN
        CLEAR(XlWrkBkDotNet);
        ERROR(Text004,SheetName);
      END;
      // .Net <<
    END;

    PROCEDURE CreateSheet@5(SheetName@1000 : Text[250];ReportHeader@1001 : Text[80];CompanyName@1002 : Text[30];UserID2@1003 : Text[30]);
    VAR
      XlEdgeBottom@1004 : Integer;
      XlContinuous@1005 : Integer;
      XlLineStyleNone@1006 : Integer;
      XlLandscape@1007 : Integer;
      CRLF@1008 : Char;
      Window@1009 : Dialog;
      RecNo@1010 : Integer;
      InfoRecNo@1012 : Integer;
      TotalRecNo@1011 : Integer;
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN BEGIN
        CreateSheetDotNet(SheetName,ReportHeader,CompanyName,UserID2);
        EXIT;
      END;
      // .Net <<

      Window.OPEN(
        Text005 +
        '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
      Window.UPDATE(1,0);

      XlEdgeBottom := 9;
      XlContinuous := 1;
      XlLineStyleNone := -4142;
      XlLandscape := 2;
      CRLF := 10;
      RecNo := 1;
      TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;
      RecNo := 0;

      XlWrkSht.Name := SheetName;
      IF ReportHeader <> '' THEN
        XlWrkSht.PageSetup.LeftHeader :=
          STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName);
      XlWrkSht.PageSetup.RightHeader :=
        STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2);
      XlWrkSht.PageSetup.Orientation := XlLandscape;
      IF FIND('-') THEN BEGIN
        REPEAT
          RecNo := RecNo + 1;
          Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
          IF NumberFormat <> '' THEN
            XlWrkSht.Range(xlColID + xlRowID).NumberFormat := NumberFormat;
          IF Formula = '' THEN
            XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"
          ELSE
            XlWrkSht.Range(xlColID + xlRowID).Formula := GetFormula;
          IF Comment <> '' THEN
            XlWrkSht.Range(xlColID + xlRowID).AddComment := Comment;
          IF Bold THEN
            XlWrkSht.Range(xlColID + xlRowID).Font.Bold := Bold;
          IF Italic THEN
            XlWrkSht.Range(xlColID + xlRowID).Font.Italic := Italic;
          XlWrkSht.Range(xlColID + xlRowID).Borders.LineStyle := XlLineStyleNone;
          IF Underline THEN
            XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;
        UNTIL NEXT = 0;
        XlWrkSht.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;
      END;

      IF UseInfoSheed THEN BEGIN
        IF InfoExcelBuf.FIND('-') THEN BEGIN
          XlWrkSht := XlWrkBk.Worksheets.Add();
          XlWrkSht.Name := Text023;
          REPEAT
            InfoRecNo := InfoRecNo + 1;
            Window.UPDATE(1,ROUND((RecNo + InfoRecNo) / TotalRecNo * 10000,1));
            IF InfoExcelBuf.NumberFormat <> '' THEN
              XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).NumberFormat :=
                InfoExcelBuf.NumberFormat;
            IF InfoExcelBuf.Formula = '' THEN
              XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Value :=
                InfoExcelBuf."Cell Value as Text"
            ELSE
              XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Formula :=
                InfoExcelBuf.GetFormula;
            IF InfoExcelBuf.Comment <> '' THEN
              XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).AddComment :=
                InfoExcelBuf.Comment;
            IF InfoExcelBuf.Bold THEN
              XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Font.Bold :=
                InfoExcelBuf.Bold;
            IF InfoExcelBuf.Italic THEN
              XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Font.Italic :=
                InfoExcelBuf.Italic;
            XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Borders.LineStyle :=
              XlLineStyleNone;
            IF InfoExcelBuf.Underline THEN
              XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Borders.Item(XlEdgeBottom).LineStyle :=
               XlContinuous;
          UNTIL InfoExcelBuf.NEXT = 0;
          XlWrkSht.Range('A' + FORMAT(1) + ':' + xlColID + xlRowID).Columns.AutoFit;
        END;
      END;
      Window.CLOSE;
    END;

    PROCEDURE CreateSheetDotNet@150002030(SheetName@1000 : Text[250];ReportHeader@1001 : Text[80];CompanyName@1002 : Text[30];UserID2@1003 : Text[30]);
    VAR
      CRLF@1008 : Char;
      Window@1009 : Dialog;
      RecNo@1010 : Integer;
      InfoRecNo@1012 : Integer;
      TotalRecNo@1011 : Integer;
    BEGIN
      // .Net >>
      Window.OPEN(
        Text005 +
        '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
      Window.UPDATE(1,0);

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

      XlWrkShtDotNet.Name := SheetName;
      IF ReportHeader <> '' THEN
        XlWrkShtDotNet.PageSetup.Header.Left.AddText(
          STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName));
      XlWrkShtDotNet.PageSetup.Header.Right.AddText(
        STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2));
      XlWrkShtDotNet.PageSetup.PageOrientation := GetEnumValue(XlWrkShtDotNet.PageSetup.PageOrientation, 'Landscape');
      IF FIND('-') THEN BEGIN
        REPEAT
          RecNo := RecNo + 1;
          Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
          IF NumberFormat <> '' THEN
            XlWrkShtDotNet.Cell("Row No.","Column No.").Style.NumberFormat.SetFormat(NumberFormat);
          IF Formula = '' THEN
            XlWrkShtDotNet.Cell("Row No.","Column No.").Value := "Cell Value as Text"
          ELSE
            XlWrkShtDotNet.Cell("Row No.","Column No.").FormulaA1 := GetFormula;
          //Comments are not yet supported by ClosedXML
          //IF Comment <> '' THEN
          //  XlWrkShtDotNet.Cell("Row No.","Column No.").Comment := Comment;
          XLStyleDotNet := XlWrkShtDotNet.Cell("Row No.","Column No.").Style;
          IF Bold THEN
            XLStyleDotNet.Font.SetBold(Bold);
          IF Italic THEN
            XLStyleDotNet.Font.SetItalic(Italic);
          XLStyleDotNet.Border.OutsideBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'None');
          IF Underline THEN
            XLStyleDotNet.Border.BottomBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'Medium');
        UNTIL NEXT = 0;
        XlWrkShtDotNet.Columns(1, "Column No.").AdjustToContents();
      END;

      IF UseInfoSheed THEN BEGIN
        IF InfoExcelBuf.FIND('-') THEN BEGIN
          XlWrkShtDotNet := XlWrkBkDotNet.Worksheets.Add('Information');
          XlWrkShtDotNet.Name := Text023;
          REPEAT
            InfoRecNo := InfoRecNo + 1;
            Window.UPDATE(1,ROUND((RecNo + InfoRecNo) / TotalRecNo * 10000,1));
            IF InfoExcelBuf.NumberFormat <> '' THEN
              XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Style.NumberFormat.SetFormat
                (InfoExcelBuf.NumberFormat);
            IF InfoExcelBuf.Formula = '' THEN
              XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Value := InfoExcelBuf."Cell Value as Text"
            ELSE
              XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").FormulaA1 := InfoExcelBuf.GetFormula;
            //Comments are not yet supported by ClosedXML
            //IF InfoExcelBuf.Comment <> '' THEN
            //  XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Comment := InfoExcelBuf.Comment;
            XLStyleDotNet := XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Style;
            IF InfoExcelBuf.Bold THEN
              XLStyleDotNet.Font.SetBold(InfoExcelBuf.Bold);
            IF InfoExcelBuf.Italic THEN
              XLStyleDotNet.Font.SetItalic(InfoExcelBuf.Italic);
            XLStyleDotNet.Border.OutsideBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'None');
            IF InfoExcelBuf.Underline THEN
              XLStyleDotNet.Border.BottomBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'Medium');
          UNTIL InfoExcelBuf.NEXT = 0;
          XlWrkShtDotNet.Columns(1, InfoExcelBuf."Column No.").AdjustToContents();
        END;
      END;
      Window.CLOSE;
      // .Net <<
    END;

    PROCEDURE CreateRangeName@9(RangeName@1000 : Text[30];FromColumnNo@1001 : Integer;FromRowNo@1002 : Integer);
    VAR
      TempExcelBuf@1003 : 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);
      // .Net >>
      IF ISSERVICETIER THEN
        XlWrkShtDotNet.NamedRanges.Add(
          RangeName,
          '=' + GetExcelReference(4) + TempExcelBuf.xlColID + GetExcelReference(4) + TempExcelBuf.xlRowID +
          ':' + GetExcelReference(4) + TempExcelBuf.xlColID + GetExcelReference(4) + ToxlRowID)
      ELSE
      // .Net <<
        XlWrkSht.Names.Add(
          RangeName,
          '=' + GetExcelReference(4) + TempExcelBuf.xlColID + GetExcelReference(4) + TempExcelBuf.xlRowID +
          ':' + GetExcelReference(4) + TempExcelBuf.xlColID + GetExcelReference(4) + ToxlRowID);
    END;

    PROCEDURE GiveUserControl@3();
    VAR
      TempFile@1000 : File;
      FileName@1001 : Text[1024];
      ToFile@1002 : Text[1024];
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN BEGIN
        GiveUserControlDotNet;
        EXIT;
      END;
      // .Net <<
      XlApp.Visible(TRUE);
      XlApp.UserControl(TRUE);
      CLEAR(XlApp);
    END;

    PROCEDURE GiveUserControlDotNet@150002040();
    VAR
      NVInStream@150002025 : InStream;
      ThreeTierMgt@150002027 : Codeunit 419;
      FileNameRTC@150002028 : Text[1024];
      MemoryStream@1000000000 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      ExcelApp@1000000001 : DotNet "'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Application";
      ExcelAppClass@1000000002 : DotNet "'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass";
    BEGIN
      // .Net >>
      MemoryStream := MemoryStream.MemoryStream();
      XlWrkBkDotNet.SaveAs(MemoryStream);
      NVInStream := MemoryStream;
      DOWNLOADFROMSTREAM(NVInStream,'',ThreeTierMgt.Magicpath,'',FileNameRTC);

      ExcelApp := ExcelAppClass.ApplicationClass();
      ExcelApp.Workbooks.Add(FileNameRTC);
      ExcelApp.Visible(TRUE);
      ExcelApp.UserControl(TRUE);
      // .Net <<
    END;

    PROCEDURE ReadSheet@4();
    VAR
      i@1000 : Integer;
      j@1001 : Integer;
      Maxi@1002 : Integer;
      Maxj@1003 : Integer;
      Window@1004 : Dialog;
      CellValueDecimal@1005 : Decimal;
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN BEGIN
        ReadSheetDotNet;
        EXIT;
      END;
      // .Net <<
      Window.OPEN(
        Text007 +
        '@1@@@@@@@@@@@@@@@@@@@@@@@@\');
      Window.UPDATE(1,0);

      DELETEALL;
      XlRange := XlWrkSht.Range(GetExcelReference(5)).SpecialCells(11);
      Maxi := XlRange.Row;
      Maxj := XlRange.Column;
      i := 1;
      REPEAT
        j := 1;
        VALIDATE("Row No.",i);
        REPEAT
          VALIDATE("Column No.",j);
          "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');
          IF "Cell Value as Text" <> '' THEN BEGIN
            IF "Cell Value as Text"[1] <> '0' THEN
              IF IsDecimal("Cell Value as Text") THEN
                IF EVALUATE(CellValueDecimal,"Cell Value as Text") THEN
                  "Cell Value as Text" := FORMAT(ROUND(CellValueDecimal,0.000001),0,1);
            INSERT;
          END;
          j := j + 1;
        UNTIL j > Maxj;
        i := i + 1;
        Window.UPDATE(1,ROUND(i / Maxi * 10000,1));
      UNTIL i > Maxi;
      XlWrkBk.Close(FALSE);
      XlApp.Quit;
      CLEAR(XlApp);
      Window.CLOSE;
    END;

    PROCEDURE ReadSheetDotNet@150002042();
    VAR
      i@1000 : Integer;
      j@1001 : Integer;
      Maxi@1002 : Integer;
      Maxj@1003 : Integer;
      Window@1004 : Dialog;
      CellValueDecimal@1005 : Decimal;
    BEGIN
      // .Net >>
      Window.OPEN(
        Text007 +
        '@1@@@@@@@@@@@@@@@@@@@@@@@@\');
      Window.UPDATE(1,0);

      DELETEALL;
      Maxi := XlWrkShtDotNet.LastRowUsed.RowNumber;
      Maxj := XlWrkShtDotNet.LastColumnUsed.ColumnNumber;
      i := 1;
      REPEAT
        j := 1;
        VALIDATE("Row No.",i);
        REPEAT
          VALIDATE("Column No.",j);
          "Cell Value as Text" := DELCHR(FORMAT(XlWrkShtDotNet.Cell("Row No.","Column No.").Value),'<',' ');
          IF "Cell Value as Text" <> '' THEN BEGIN
            IF "Cell Value as Text"[1] <> '0' THEN
              IF IsDecimal("Cell Value as Text") THEN
                IF EVALUATE(CellValueDecimal,"Cell Value as Text") THEN
                  "Cell Value as Text" := FORMAT(ROUND(CellValueDecimal,0.000001),0,1);
            INSERT;
          END;
          j := j + 1;
        UNTIL j > Maxj;
        i := i + 1;
        Window.UPDATE(1,ROUND(i / Maxi * 10000,1));
      UNTIL i > Maxi;
      CLEAR(XlWrkBkDotNet);
      Window.CLOSE;
      // .Net <<
    END;

    PROCEDURE SelectSheetsName@6(FileName@1000 : Text[250]) : Text[250];
    VAR
      i@1001 : Integer;
      SheetName@1002 : Text[250];
      EndOfLoop@1003 : Integer;
      SheetsList@1004 : Text[250];
      OptionNo@1005 : Integer;
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN
        EXIT(SelectSheetsNameDotNet(FileName));
      // .Net <<

      IF NOT ISSERVICETIER THEN BEGIN
        IF FileName <> '' THEN BEGIN
          IF NOT EXISTS(FileName) THEN
            ERROR(Text003,FileName);
        END ELSE
          EXIT('');
      END;

      IF NOT CREATE(XlApp,TRUE,TRUE) THEN
        ERROR(Text000);
      XlApp.Workbooks.Open(FileName);
      XlWrkBk := XlApp.ActiveWorkbook;
      i := 1;
      EndOfLoop := XlWrkBk.Worksheets.Count;
      WHILE i <= EndOfLoop DO BEGIN
        XlWrkshts := XlWrkBk.Worksheets.Item(i);
        SheetName := XlWrkshts.Name;
        IF (SheetName <> '') AND (STRLEN(SheetsList) + STRLEN(SheetName) < 250) THEN
          SheetsList := SheetsList + SheetName + ','
        ELSE
          i := EndOfLoop;
        i := i + 1;
      END;
      XlWrkBk.Close(FALSE);
      XlApp.Quit;
      CLEAR(XlApp);
      OptionNo := STRMENU(SheetsList,1);
      IF OptionNo <> 0 THEN
        EXIT(SELECTSTR(OptionNo,SheetsList))
      ELSE
        EXIT('');
    END;

    PROCEDURE SelectSheetsNameDotNet@150002044(FileName@1000 : Text[250]) : Text[250];
    VAR
      i@1001 : Integer;
      SheetName@1002 : Text[250];
      EndOfLoop@1003 : Integer;
      SheetsList@1004 : Text[250];
      OptionNo@1005 : Integer;
      FileRTC@150002029 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File" RUNONCLIENT;
      PathRTC@150002028 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.Path" RUNONCLIENT;
      ThreeTierMgt@150002027 : Codeunit 419;
      NVInStream@150002026 : InStream;
      MemoryStream@150002025 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      ClientTempFileName@150002024 : Text[1024];
    BEGIN
      // .Net >>
      //Use System.IO.File to check file on RTC
      IF FileName <> '' THEN BEGIN
        IF NOT FileRTC.Exists(FileName) THEN
          ERROR(Text003,FileName);
      END ELSE
        EXIT('');

      //Copy file to RTC Temp folder and upload it to server in a stream

      //mh
      //ClientTempFileName := ThreeTierMgt.ClientTempFileName('',PathRTC.GetExtension(FileName));
      ClientTempFileName := ThreeTierMgt.ClientTempFileName(PathRTC.GetExtension(FileName));


      FileRTC.Copy(FileName, ClientTempFileName);
      UPLOADINTOSTREAM('',ThreeTierMgt.Magicpath,'',ClientTempFileName,NVInStream);
      MemoryStream := NVInStream;

      XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook(MemoryStream);
      i := 1;
      EndOfLoop := XlWrkBkDotNet.Worksheets.Count;
      WHILE i <= EndOfLoop DO BEGIN
        XlWrkShtDotNet := XlWrkBkDotNet.Worksheet(i);
        SheetName := XlWrkShtDotNet.Name;
        IF (SheetName <> '') AND (STRLEN(SheetsList) + STRLEN(SheetName) < 250) THEN
          SheetsList := SheetsList + SheetName + ','
        ELSE
          i := EndOfLoop;
        i := i + 1;
      END;
      CLEAR(XlWrkBkDotNet);
      OptionNo := STRMENU(SheetsList,1);
      IF OptionNo <> 0 THEN
        EXIT(SELECTSTR(OptionNo,SheetsList))
      ELSE
        EXIT('');
      // .Net <<
    END;

    PROCEDURE FilterToFormula@7(Filter@1001 : Text[250];Ref1@1002 : Text[250];Ref2@1003 : Text[250]) Formula@1000 : Text[250];
    VAR
      FormulaUnit@1004 : ARRAY [250] OF Code[20];
      IsValue@1005 : ARRAY [250] OF Boolean;
      i@1006 : Integer;
      j@1007 : Integer;
      CountComparison@1008 : Integer;
    BEGIN
      i := 1;
      j := 2;
      REPEAT
        IF STRPOS('|&>.<()*/+-?@',COPYSTR(Filter,i,1)) > 0 THEN BEGIN
          IF FormulaUnit[j] <> '' THEN
            j := j + 1;
          FormulaUnit[j] := FormulaUnit[j] + COPYSTR(Filter,i,1);
          IF COPYSTR(Filter,i,2) = '..' THEN BEGIN //Case: X..Y
            FormulaUnit[j] :=  FormulaUnit[j] + '.';
            i := i + 1;
          END;
          IF (STRPOS('><',COPYSTR(Filter,i,1)) > 0) THEN BEGIN
            IF (STRPOS('>=',COPYSTR(Filter,i + 1,1)) > 0) THEN BEGIN //Case: <>X, <=X, >=X
              FormulaUnit[j] := FormulaUnit[j] + COPYSTR(Filter,i + 1,1);
              i := i + 1;
            END;
          END;
          j := j + 1;
        END ELSE BEGIN
          FormulaUnit[j] := FormulaUnit[j] + COPYSTR(Filter,i,1);
          IsValue[j] := TRUE;
        END;
        i := i + 1;
      UNTIL COPYSTR(Filter,i,1) = '';

      j := 1;
      Formula := '=';
      WHILE NextValue(FormulaUnit,IsValue,j) DO BEGIN
        CASE FormulaUnit[j] OF
          '..':
            BEGIN
              CASE TRUE OF
                IsValue[j - 1] AND IsValue[j + 1]:
                  BEGIN
                    IF FormulaUnit[j - 1] > FormulaUnit[j + 1] THEN BEGIN
                      Formula := '';
                      FormulaUnitErr := Filter + Text008 + FormulaUnit[j - 1] + '..' + FormulaUnit[j + 1] +
                        Text009;
                      EXIT;
                    END ELSE BEGIN
                      Formula := Formula + SumIf(Ref1,'>=',FormulaUnit[j - 1],Ref2) + '-' +
                        SumIf(Ref1,'>=',FormulaUnit[j + 1],Ref2);
                      FormulaUnit[j - 1] := '';
                      FormulaUnit[j + 1] := '';
                    END;
                  END;
                IsValue[j - 1] AND NOT IsValue[j + 1] :
                  BEGIN
                    Formula := Formula + SumIf(Ref1,'>=',FormulaUnit[j - 1],Ref2);
                    FormulaUnit[j - 1] := '';
                  END;
                NOT IsValue[j - 1] AND IsValue[j + 1]:
                  BEGIN
                    Formula := Formula + SumIf(Ref1,'<=',FormulaUnit[j + 1],Ref2);
                    FormulaUnit[j + 1] := '';
                  END;
              END;
            END;
          '>':
            BEGIN
              Formula := Formula + SumIf(Ref1,'>',FormulaUnit[j + 1],Ref2);
              CountComparison := CountComparison + 1;
              FormulaUnit[j + 1] := '';
            END;
          '<':
            BEGIN
              Formula := Formula + SumIf(Ref1,'<',FormulaUnit[j + 1],Ref2);
              CountComparison := CountComparison + 1;
              FormulaUnit[j + 1] := '';
            END;
          '>=':
            BEGIN
              Formula := Formula + SumIf(Ref1,'>=',FormulaUnit[j + 1],Ref2);
              CountComparison := CountComparison + 1;
              FormulaUnit[j + 1] := '';
            END;
          '<=':
            BEGIN
              Formula := Formula + SumIf(Ref1,'<=',FormulaUnit[j + 1],Ref2);
              CountComparison := CountComparison + 1;
              FormulaUnit[j + 1] := '';
            END;
          '<>','&','+','-','/','*','?','@':
            BEGIN
              Formula := '';
              FormulaUnitErr := Filter + Text010 + FormulaUnit[j] + Text009;
              EXIT;
            END;
          '|':
            BEGIN
              IF IsValue[j - 1]  AND (FormulaUnit[j - 1] <> '') THEN BEGIN
                Formula := Formula + SumIf(Ref1,'=',FormulaUnit[j - 1],Ref2);
                FormulaUnit[j - 1] := '';
              END;
              Formula := Formula + '+';
            END;
          '(',')':
            BEGIN
              Formula := Formula + FormulaUnit[j];
            END;
          ELSE BEGIN
            Formula := '';
            FormulaUnitErr := Filter + Text010 + FormulaUnit[j] + Text011;
            EXIT;
          END;
        END;
        IF CountComparison > 1 THEN BEGIN
          Formula := '';
          FormulaUnitErr := Filter + Text012;
          EXIT;
        END;
        FormulaUnit[j] := '';
      END;
      IF IsValue[j - 1] AND (FormulaUnit[j - 1] <> '') THEN
        Formula := Formula + SumIf(Ref1,'=',FormulaUnit[j - 1],Ref2);
    END;

    LOCAL PROCEDURE NextValue@8(FormulaUnit@1000 : ARRAY [250] OF Code[20];IsValue@1001 : ARRAY [250] OF Boolean;VAR j@1002 : Integer) : Boolean;
    BEGIN
      REPEAT
        j := j + 1;
      UNTIL NOT IsValue[j];
      IF FormulaUnit[j] <> '' THEN
        EXIT(TRUE)
      ELSE
        EXIT(FALSE);
    END;

    LOCAL PROCEDURE SumIf@15(Ref1@1000 : Text[250];Operator@1001 : Text[250];Value@1002 : Code[20];Ref2@1003 : Text[250]) : Text[250];
    VAR
      Symbol@1004 : Char;
    BEGIN
      Symbol := 34;
      EXIT(
        GetExcelReference(6) + '(' + Ref1 + ';' +
        FORMAT(Symbol) + Operator + '''' + Value + '''' + FORMAT(Symbol) + ';' +
        Ref2 + ')');
    END;

    PROCEDURE GetFormulaUnitErr@21() : Text[250];
    BEGIN
      EXIT(FormulaUnitErr);
    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.
      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.FIND('-') THEN
        REPEAT
          ExcelBufFormula := ExcelBuf;
          ExcelBufFormula.INSERT;
        UNTIL ExcelBuf.NEXT = 0;
      ExcelBuf.RESET;

      WITH ExcelBufFormula DO
        IF FIND('-') 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.FIND('-') 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.FIND('-') 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 BEGIN
                      SetFormula(ExcelBuf.GetExcelReference(7));
                      Comment := ''  //ExcelBuf.GetFormulaUnitErr; mh14
                    END;
                  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 BEGIN
                SetFormula(ExcelBuf.GetExcelReference(7));
                Comment := ''; // ExcelBuf.GetFormulaUnitErr; mh14
              END;
            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]);
    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;
      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[50]) : Boolean;
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN
        XlWrkShtDotNet.NamedRanges.Add(
        RangeName,
        '=' + GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +
        ':' + GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow)
      ELSE
      // .Net <<
        XlWrkSht.Names.Add(
          RangeName,
          '=' + GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +
          ':' + GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow);
    END;

    PROCEDURE AutoFit@20(RangeName@1000 : Text[50]);
    VAR
      XlRange1@1001 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020846-0000-0000-C000-000000000046}:Unknown Automation Server.Range";
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN BEGIN
        XlWrkShtDotNet.Columns.AdjustToContents();
      END ELSE BEGIN
      // .Net <<
        XlRange1 := XlWrkSht.Range(RangeName);
        XlRange1.Columns.AutoFit;
      END;
    END;

    PROCEDURE BorderAround@39(RangeName@1000 : Text[50]);
    VAR
      XlRange1@1001 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020846-0000-0000-C000-000000000046}:Unknown Automation Server.Range";
      XlBorderStyle@150002024 : DotNet "'ClosedXML, Version=0.69.1.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLBorderStyleValues";
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN BEGIN
        XlWrkShtDotNet.NamedRange(RangeName).Ranges.Style.Border.OutsideBorder :=
          GetEnumValue(XlWrkShtDotNet.NamedRange(RangeName).Ranges.Style.Border.BottomBorder,'None');
      END;
      {mh

        ELSE BEGIN
      // .Net <<
        XlRange1 := XlWrkSht.Range(RangeName);
        XlRange1._BorderAround(1);
      END;
      }
    END;

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

    PROCEDURE SetUseInfoSheed@25();
    BEGIN
      UseInfoSheed := 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]);
    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.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 := XlWrkSht;
        ELSE
          ERROR('Global variable %1 is not included for test.',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]);
    VAR
      XLValidation@1001 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{0002442F-0000-0000-C000-000000000046}:Unknown Automation Server.Validation";
      XLRange@1002 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020846-0000-0000-C000-000000000046}:Unknown Automation Server.Range";
    BEGIN
      // .Net >>
      IF ISSERVICETIER THEN BEGIN
        CreateValidationRuleDotNet(Range);
        EXIT;
      END;
      // .Net <<
      XlApp.Goto(Range);
      XLRange := XlApp.Selection;
      XLValidation := XLRange.Validation;
      XLValidation.Add(3,1,1,
        '=' + GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +
        ':' + GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow);
      XlApp.Goto(XlApp.PreviousSelections(1));
    END;

    PROCEDURE CreateValidationRuleDotNet@150002057(Range@1000 : Code[20]);
    VAR
      XLValidationDotNet@1001 : DotNet "'ClosedXML, Version=0.69.1.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLDataValidation";
      XLNamedRangeDotNet@1002 : DotNet "'ClosedXML, Version=0.69.1.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLNamedRange";
    BEGIN
      {mh
      // .Net >>
      XLNamedRangeDotNet := XlWrkShtDotNet.NamedRange(Range);
      XLValidationDotNet := XLNamedRangeDotNet.Ranges.DataValidation;
      XLValidationDotNet.AllowedValues := GetEnumValue(XLValidationDotNet.AllowedValues,'List');
      XLValidationDotNet.ErrorStyle := GetEnumValue(XLValidationDotNet.ErrorStyle,'Stop');
      XLValidationDotNet.Operator := GetEnumValue(XLValidationDotNet.Operator,'EqualTo');
      XLValidationDotNet.List(XlWrkShtDotNet.Range(
        '=' + GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow +
        ':' + GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow));
      // .Net <<
      }
    END;

    PROCEDURE IsDecimal@28(ValueAsText@1000 : Text[250]) : Boolean;
    VAR
      i@1001 : Integer;
      c@1004 : Text[1];
      c0@1005 : Text[1];
      ThousandSeparator@1002 : Text[1];
      DecimalSeparator@1003 : Text[1];
      DecimalSeparatorExists@1006 : Boolean;
    BEGIN
      IF ValueAsText = '' THEN
        EXIT(FALSE);
      IF STRPOS(ValueAsText,'..') > 0 THEN
        EXIT(FALSE);

      ThousandSeparator := COPYSTR(FORMAT(1000.01,0,0),2,1);
      DecimalSeparator := COPYSTR(FORMAT(1000.01,0,0),6,1);

      FOR i := 1 TO STRLEN(ValueAsText) DO BEGIN
        c := COPYSTR(ValueAsText,i,1);
        CASE TRUE OF
          c IN ['+','-']:
            BEGIN
              IF c0 <> '' THEN
                EXIT(FALSE);
            END;
          c = DecimalSeparator:
            BEGIN
              IF NOT DecimalSeparatorExists THEN
                DecimalSeparatorExists := TRUE
              ELSE
                EXIT(FALSE);
            END;
          c = ThousandSeparator:
            BEGIN
              IF NOT (c0 IN ['0','1','2','3','4','5','6','7','8','9']) THEN
                EXIT(FALSE);
            END;
          c IN ['0','1','2','3','4','5','6','7','8','9']:
          ;
          ELSE
            EXIT(FALSE);
        END;
        c0 := c;
      END;
      IF NOT (c IN ['0','1','2','3','4','5','6','7','8','9']) THEN
        EXIT(FALSE);
      EXIT(TRUE);
    END;

    PROCEDURE GetEnumValue@150002034(Enum@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Enum";Value@150002026 : Text[30]) ReturnValue : Integer;
    VAR
      Convert@150002025 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
    BEGIN
      // .Net >>
      ReturnValue := Convert.ToInt32(Enum.Parse(Enum.GetType(),Value));
      // .Net <<
    END;

    PROCEDURE SaveAs@1000000003(Path@1000000000 : Text[500]);
    BEGIN
      XlWrkBkDotNet.SaveAs(Path);
    END;

    BEGIN
    END.
  }
}

Comments

  • Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    Any ideas?
  • jaylefermjayleferm Member Posts: 13
    I remember that we had a similar problem with ClosedXML and I searched for a solution to get it to work due to language settings of different countries.
    We're using Office 2010 (and in this case NAV2009R2), but the function GiveUserControlDotNet() looks little a bit different than yours:
    PROCEDURE GiveUserControlDotNet@150002040();
    VAR
      MemoryStream@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      ExcelApp@150002031 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Application" RUNONCLIENT;
      ExcelAppClass@150002030 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
      Workbook@150002037 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Workbook" RUNONCLIENT;
      Workbooks@150002036 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Workbooks" RUNONCLIENT;
      MsoAppLanguageID@150002035 : DotNet "'office, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Core.MsoAppLanguageID" RUNONCLIENT;
      ReflectionBindingFlags@150002034 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Reflection.BindingFlags" RUNONCLIENT;
      CultureInfo@150002033 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Globalization.CultureInfo" RUNONCLIENT;
      OldCultureInfo@1100108001 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Globalization.CultureInfo" RUNONCLIENT;
      TYPE@150002032 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Type" RUNONCLIENT;
      RuntimeTypeHandle@150002029 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.RuntimeTypeHandle" RUNONCLIENT;
      Args@150002038 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array" RUNONCLIENT;
      DummyArray@150002026 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array" RUNONCLIENT;
      NVInStream@150002025 : InStream;
      ThreeTierMgt@150002027 : Codeunit 419;
      FileNameRTC@150002028 : Text[1024];
      CultureID@150002039 : Integer;
    BEGIN
      MemoryStream := MemoryStream.MemoryStream();
      XlWrkBkDotNet.SaveAs(MemoryStream);
      NVInStream := MemoryStream;
      DOWNLOADFROMSTREAM(NVInStream,'',ThreeTierMgt.Magicpath,'',FileNameRTC);

      ExcelApp := ExcelAppClass.ApplicationClass();
      Workbooks := ExcelApp.Workbooks;

      RuntimeTypeHandle := TYPE.GetTypeHandle(FileNameRTC);
      TYPE := TYPE.GetTypeFromHandle(RuntimeTypeHandle);
      Args := Args.CreateInstance(TYPE,1);
      Args.SetValue(FileNameRTC,0);

      ReflectionBindingFlags := 0;
      ReflectionBindingFlags := ReflectionBindingFlags.Parse(ReflectionBindingFlags.GetType(), 'InvokeMethod');

      MsoAppLanguageID := 0;
      MsoAppLanguageID := MsoAppLanguageID.Parse(MsoAppLanguageID.GetType(), 'msoLanguageIDUI');

      // New adapted code from KB Article
      OldCultureInfo := CultureInfo.CurrentCulture;
      CultureInfo := CultureInfo.CultureInfo('en-US');
      Workbooks.Add(FileNameRTC);
      CultureInfo := OldCultureInfo;

      // Original code from blog post
      {
      CultureID := ExcelApp.LanguageSettings.LanguageID(MsoAppLanguageID);
      CultureInfo := CultureInfo.CultureInfo(CultureID);
      RuntimeTypeHandle := TYPE.GetTypeHandle(Workbooks);
      TYPE := TYPE.GetTypeFromHandle(RuntimeTypeHandle);

      Workbook := TYPE.InvokeMember('Add', ReflectionBindingFlags, DummyArray, Workbooks, Args, CultureInfo);
      }

      ExcelApp.Visible(TRUE);
      ExcelApp.UserControl(TRUE);
    END;

    Perhaps you could give it a try with this.
  • Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    What is this cultureinfo stuff? Currently Danish locale is working well for my project.

    I finally ended up copying the standard. Duh. I realized after ClosedXML did the job of making the file, opening it we can do the standard way. So I took the 3-4 functions the standard T370 uses for this and copied into one, just of the sake of simplicity.
    Name	DataType	Subtype	Length
XlApp	DotNet	Microsoft.Office.Interop.Excel.ApplicationClass.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'	
XlWrkBk	DotNet	Microsoft.Office.Interop.Excel.Workbook.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'	
XlWrkSht	DotNet	Microsoft.Office.Interop.Excel.Worksheet.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'	
Name	DataType	Subtype	Length
XlHelper	DotNet	Microsoft.Dynamics.Nav.Integration.Office.Excel.ExcelHelper.'Microsoft.Dynamics.Nav.Integration.Office, Version=7.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'	

GiveUserControlDotNet()
// .Net >>
MemoryStream := MemoryStream.MemoryStream();
XlWrkBkDotNet.SaveAs(MemoryStream);
NVInStream := MemoryStream;
DOWNLOADFROMSTREAM(NVInStream,'',ThreeTierMgt.Magicpath,'',FileNameRTC);

//ExcelApp := ExcelAppClass.ApplicationClass();
//ExcelApp.Workbooks.Add(FileNameRTC);
//ExcelApp.Visible(TRUE);
//ExcelApp.UserControl(TRUE);

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

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

  XlApp.Visible := TRUE;
  XlApp.UserControl := TRUE;


// .Net <<
Sign In or Register to comment.