DOTNET EXCEL INTEROP

r2d2ro

Hi all!
I am very beginner with navision. I got as task to transform a report from Ms Dynamics Navision2009 into 2013.
As improvement I should use DOTNET excel interop instead automation...and i got stuck in this.
I declared dotnet variables, marked them to run on client and wrote next code :

ExcelApp := ExcelApp.ApplicationClass;
IF ISNULL(ExcelApp) THEN
ERROR(Text000);
Template := '';

ExcelSheet := ExcelApp.Workbooks.Add(Template);
IF ISNULL(ExcelWbk) THEN
ERROR('Fehler ExcelWbK = NULL');

ExcelSheet := ExcelWbk.Worksheets.Item(1);

IF ISNULL(ExcelSheet) THEN
ERROR('Fehler ExcelSheet = NULL');


where

ExcelApp DotNet Microsoft.Office.Interop.Excel.ApplicationClass.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'
ExcelWbk DotNet Microsoft.Office.Interop.Excel.Workbook.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'
ExcelSheets DotNet Microsoft.Office.Interop.Excel.Worksheets.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'
ExcelSheet DotNet Microsoft.Office.Interop.Excel.Worksheet.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'
ExcelWorkbooks DotNet Microsoft.Office.Interop.Excel.Workbooks.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'
Template Variant

The proogram break into this: ExcelSheet := ExcelApp.Workbooks.Add(Template);

In debugger :
ExcelApp - <Unknown> - "DotNet on Client ""Microsoft.Office.Interop.Excel.ApplicationClass"""
ExcelWbk - <Uninitialized> - "DotNet on Client ""Microsoft.Office.Interop.Excel.Workbook"""


The question: How to initialize excel workbook? Or what I do wrong ... There is somewhere a good example related to this subject?

Thanks in advance to anyone involved!
Victor

thegunzo

Take a look at how this is done in Table 370 Excel Buffer

r2d2ro

It is required to implement this with DOTNET... could not succeed to convince them to let me use the nice Excel buffer, because "it's slow" ... and now it's time consuming because I can't make it work

StLi

May I recomend ClosedXML to you?

http://closedxml.codeplex.com/

It's super fast and way easyer to use than OpenXML. I bulit a wrapper Codeunit for our solutions. Took me about 20 hours and now the whole team is using it. We got that time back within a few months and have more features than ExcelBuffer now. There are some issues with color selection by Color-name and date formating while reading but its still worth it.

Eighter way; you need to call a constructor of your Interop-Class before you can use it any other way. In the C/AL Symbol Menu (F5 in the C/AL Editor) there is a seperate section for the constructors.

TonyDuarte

You have here 2 functions from the Codeunit 8618 Config. Excel Exchange from NAV 2013 R2 that has the Excel variables as DotNet.

Yes I know that NAV 2013 uses automations instead of DotNet.

This is the import one, might be handy

ImportExcel (XLSDataFile :Text ) Imported : Boolean
XlApp := XlApp.ApplicationClass;
IF ISNULL(XlApp) THEN
  ERROR(Text019);

XlHelper.CallOpen(XlApp,XLSDataFile);
XlBook := XlApp.ActiveWorkbook;

XMLDataFile := ''; // To Avoid Precal
XmlMaps := XlBook.XmlMaps;
IF XmlMaps.Count <> 0 THEN BEGIN
  TmpXmlFile.CREATETEMPFILE;
  TmpXmlFile.CREATEINSTREAM(InStream);
  DOWNLOADFROMSTREAM(InStream,'',FileMgt.Magicpath,'',XMLDataFile);
  TmpXmlFile.CLOSE;

  XmlMap := XmlMaps.Item(1);
  XmlMap.Export(XMLDataFile,TRUE);

  XMLDataFileServer := FileMgt.UploadFile(Text017,XMLDataFile);
  ConfigXMLExchange.SetExcelMode(TRUE);
  IF ConfigXMLExchange.ImportPackageXML(XMLDataFileServer) THEN
    Imported := TRUE;
  CloseXlApp;
END ELSE BEGIN
  CloseXlApp;
  ERROR(Text020);
END;

EXIT(Imported);

This one might be more of interest that is the export

ExportExcel(ExcelFile : Text;VAR ConfigPackageTable : Record "Config. Package Table";ExportFromWksht : Boolean) : Boolean
TempSchemaFile.CREATETEMPFILE;
TempSchemaFileName := TempSchemaFile.NAME + '.xsd';
TempSchemaFile.CLOSE;

TempSchemaFile.CREATE(TempSchemaFileName);
TempSchemaFile.CREATEOUTSTREAM(OStream);
ConfigDataSchema.SETDESTINATION(OStream);
ConfigDataSchema.SETTABLEVIEW(ConfigPackageTable);
IF NOT ConfigDataSchema.EXPORT THEN
  ERROR(Text018);
TempSchemaFile.CLOSE;

TempSetupDataFileName := FileMgt.ServerTempFileName('');
CLEAR(ConfigXMLExchange);
ConfigXMLExchange.SetExcelMode(TRUE);
ConfigXMLExchange.SetCalledFromCode(TRUE);
ConfigXMLExchange.SetExportFromWksht(ExportFromWksht);
ConfigXMLExchange.ExportPackageXML(ConfigPackageTable,TempSetupDataFileName);
ConfigXMLExchange.SetExcelMode(FALSE);

TempSetupDataFileNameCL := FileMgt.DownloadTempFile(TempSetupDataFileName);
TempSchemaFileNameCL := FileMgt.DownloadTempFile(TempSchemaFileName);
WrkShtNo := 1;

TempXLSFile := FileMgt.ClientTempFileName('xlsx');
CreateBook(TempXLSFile);

IF ConfigPackageTable.FINDSET THEN
  REPEAT
    ConfigPackageTable.CALCFIELDS("Table Name");
    WrkBkWriter.AddWorksheet(ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Name"));
  UNTIL ConfigPackageTable.NEXT = 0;
WrkBkWriter.Close;

OpenBook(TempXLSFile,FALSE);
XlApp.DisplayAlerts(FALSE);

XlBook.XmlMaps.Add(TempSchemaFileNameCL,'DataList');
XMLMap := XlBook.XmlMaps.Item(1);

XlSheet := XlBook.Worksheets.Item(1);
XlSheet.Delete;

IF NOT HideDialog THEN
  ConfigProgressBar.Init(ConfigPackageTable.COUNT,Text024);
IF ConfigPackageTable.FINDSET THEN
  REPEAT
    ConfigPackageTable.CALCFIELDS("Table Name");
    IF NOT HideDialog THEN
      ConfigProgressBar.Update(ConfigPackageTable."Table Name");
    XlSheet := XlBook.Worksheets.Item(WrkShtNo);
    XlSheet.Activate;
    WrkShtNo += 1;

    Range := XlSheet.Range('A1');
    Range.Value := ConfigPackageTable."Package Code";
    Range.XPath.SetValue(
      XMLMap,
      '/DataList/' +
      (ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Name") + 'List') +
      '/' + ConfigXMLExchange.GetElementName(ConfigPackageTable.FIELDNAME("Package Code")),'',FALSE);

    Range := XlSheet.Range('C1');
    Range.Value := ConfigPackageTable."Table ID";
    Range.XPath.SetValue(
      XMLMap,
      '/DataList/' +
      (ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Name") + 'List') +
      '/' + ConfigXMLExchange.GetElementName(ConfigPackageTable.FIELDNAME("Table ID")),'',FALSE);

    ConfigPackageField.RESET;
    ConfigPackageField.SETCURRENTKEY("Package Code","Table ID","Processing Order");
    ConfigPackageField.SETRANGE("Package Code",ConfigPackageTable."Package Code");
    ConfigPackageField.SETRANGE("Table ID",ConfigPackageTable."Table ID");
    ConfigPackageField.SETRANGE("Include Field",TRUE);
    IF NOT ExportFromWksht THEN
      ConfigPackageField.SETRANGE(Dimension,FALSE);
    Range := XlSheet.Range('A3',FORMAT(GetXLColumnID(ConfigPackageField.COUNT)) + '3');
    Range.Select;
    ListObject := XlSheet.ListObjects.Add(1,Range,TRUE,XlYesNoGuess.xlNo,Range);
    ListColumns := ListObject.ListColumns;

    i := 1;
    RecRef.OPEN(ConfigPackageTable."Table ID");
    IF ConfigPackageField.FINDSET THEN
      REPEAT
        ListColumn := ListColumns.Item(i);
        IF Field.GET(ConfigPackageField."Table ID",ConfigPackageField."Field ID") OR ConfigPackageField.Dimension THEN BEGIN
          IF ConfigPackageField.Dimension THEN
            ListColumn.Name := ConfigPackageField."Field Caption" + ' ' + STRSUBSTNO('(%1)',Dimension.TABLECAPTION)
          ELSE
            ListColumn.Name := ConfigPackageField."Field Caption";
          ListColumn.XPath.SetValue(
            XMLMap,'/DataList/' + (ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Name") + 'List') +
            '/' + ConfigXMLExchange.GetElementName(ConfigPackageTable."Table Name") +
            '/' + ConfigXMLExchange.GetElementName(ConfigPackageField."Field Name"),'',TRUE);
          IF NOT ConfigPackageField.Dimension THEN BEGIN
            FieldRef := RecRef.FIELD(ConfigPackageField."Field ID");
            XlSheet.Range(FORMAT(GetXLColumnID(i)) + '3').AddComment(ConfigValidateMgt.AddComment(FieldRef));
          END;
          i := i + 1;
        END;
      UNTIL ConfigPackageField.NEXT = 0;
    RecRef.CLOSE;

    Range := XlSheet.Range('A1',FORMAT(GetXLColumnID(ConfigPackageField.COUNT)) + '3');
    Range.Columns.AutoFit;
  UNTIL ConfigPackageTable.NEXT = 0;

XMLMap.Import(TempSetupDataFileNameCL,TRUE);

IF FileMgt.GetExtension(ExcelFile) = '' THEN
  ExcelFile := ExcelFile + '.xlsx';
XlBook.SaveCopyAs(ExcelFile);

CloseXlApp;

FILE.ERASE(TempSchemaFileName);
FILE.ERASE(TempSetupDataFileName);

IF NOT HideDialog THEN
  ConfigProgressBar.Close;

EXIT(TRUE);

r2d2ro

Hi again!
I thank you for your answers !

I have made some tiny steps and got a new problem:

the code:


ExcelApp := ExcelApp.ApplicationClass;
IF ISNULL(ExcelApp) THEN
ERROR(' Excel App is null ');

Template := '';
ExcelWbk := ExcelApp.Workbooks.Add(Template);

IF ISNULL(ExcelWbk) THEN
//EXIT;
ERROR(' Excel workbook is null ');
ExcelSheet1 := ExcelWbk.Worksheets.Item(1);
ExcelSheet1.Name := 'page1';
IF ISNULL(ExcelSheet1) THEN
// EXIT;
ERROR(' Excel worksheet1 is null ');

ExcelSheet2 := ExcelWbk.Worksheets.Add(DotNetMissing,ExcelSheet1,DotNetMissing,DotNetMissing);
//ExcelSheet := ExcelWbk.Worksheets.Item(2);
ExcelSheet2.Name := 'page2';
IF ISNULL(ExcelSheet2) THEN
// EXIT;
ERROR(' Excel worksheet is null ');
////////////////////////////////////////
ExcelApp.Visible := TRUE;

fails in method Worksheet.Add() with message : "Add method of Sheets class failed" ....

Name DataType Subtype Length
DotNetMissing DotNet System.Reflection.Missing.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

I have tried to reproduce the same interop usage in C# and worked .... I guess something is not ok with parameter list...

Has anyone have a clue?