DOTNET EXCEL INTEROP

r2d2ro · 2014-07-30

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 · 2014-07-30

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

r2d2ro · 2014-07-30

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 · 2014-08-06

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 · 2014-08-06

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 · 2014-08-21

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?

Howdy, Stranger!

Quick Links

Categories

DOTNET EXCEL INTEROP

Comments