ClosedXML ExcelApplicationClass error, NAV2013R2
Miklos_Hollender
Member Posts: 1,598
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:
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.
}
}
0
Comments
-
Any ideas?0
-
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.0 -
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 <<
0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 323 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions