9

I'm using the NETLink package to build a Excel file and trying to fill it with some data as this:

Needs["NETLink`"];
InstallNET[];

(*xls = NETNew["Microsoft.Office.Interop.Excel.ApplicationClass"];*)
xls = CreateCOMObject["Excel.Application"];
xls@Visible = True;

wb = xls@Workbooks@Add[]
ws = wb@Worksheets@Item[1]

it works with code :

ws@Cells[1, 1]@Value = 100;
ws@Cells[2, 2]@Value = 200;
ws@Cells[3, 3]@Value = 300;

but gives no result with code: 

For[i=1,i<10,i++,
   ws@Cells[i, i]@Value = i*100
]

so,what's wrong with the loop case ? Thanks!

(*Solution*)
(*define func.*)
wsGetCell[i_,j_]:=ws@Cell[i,j]
(*loop*)
For[i=1,i<10,i++,
       wsGetCell[i, i]@Value = i*100
    ]
Jerry
  • 2,459
  • 9
  • 15
  • NETLink object has to have some upvalues associated to how you can access methods etc. Without this 'environment' Cells[1,1] would just return $Failed. Anyway, since Cells[i,i] is held during the evaluation those notes apply: Function in Table. – Kuba Nov 18 '18 at 22:57
  • Is there any way to release the cells' held evaluations, I've tested the Activate function but it doesnot work. – Jerry Nov 18 '18 at 23:55
  • 1
    This doesn't answer the NetLink question, but ExcelLink makes interaction with Excel files easy. I use it literally everyday. Perhaps it has some deficiencies that I haven't encountered yet. – George Wolfe Nov 20 '18 at 20:32

2 Answers2

8

In the Excel object model, the worksheet symbol Cells represents a property, not a method. In Visual Basic and other languages, many properties have a default method called Item. NETLink does not fully implement this notion of a default method:

ws@Cells[3, 3]@Value

(* 3 *)

but:

$i = 3;
ws@Cells[$i, $i]@Value

(* (NETLink`Objects`NETObject$1412516038$971395236364289)[$i, $i][Value] *)

Notice how the expression remains unevaluated. But it will work if we use the Item method explicitly:

ws@Cells@Item[$i, $i]@Value

(* 300 *)

Item will allow us to perform a successful assignment as well. So, we can adjust the For loop to get the expected behaviour as follows:

For[i = 1, i < 10, i++,
   ws@Cells@Item[i, i]@Value = i*100
]

Internal Details

Part of the problem here is that assignment statements appear to work but fail silently:

ws@Cells[$i, $i]@Value = 3

(* 3 *)

This is due to an NETLink implementation problem. The code expects the invalid property reference to return $Failed but gets confused when, as we have seen, it remains unevaluated. Working from the top-level expression inward, the relevant evaluation sequence for ws@Cells[$i, $i]@Value = 3 looks like this (at least as of version 11.3):

evaluation sequence

For technical reasons, this behaviour might be difficult to correct in the general case.

WReach
  • 68,832
  • 4
  • 164
  • 269
  • Hm, it's strange that NETLink`Package`nCall doesn't return $Failed when its first argument is $Failed, which means that the type has not been inferred correctly... It looks like it would solve the problem of the seemingly successful evaluation... – Anton.Sakovich Nov 21 '18 at 07:56
5

It works with code.

Scan[(ws@Cells[#, #]@Value = #*100) &, Range@10]

It seems that vars i in ws@Cells[i, i]@Value be not evaluated immediately when the loop goes. I wonder is it a bug in NETLink package?

Update v1:

(ws@Cells[#, #]@Value = #*100) & /@ (Range@10);

Update v2 using Range to fill values faster

content = Table[i*j,{i,1,100},{j,1,100}];
lstVals = {{First@Dimensions@content}}~
   Join~{{Last@Dimensions@content}}~Join~{{content}};
(* to avoid vars evaluating in Excel Cells objects *)
MapThread[(ws@Range[ws@Cells[1, 1], ws@Cells[#1, #2]]@Value = #3) &, 
  lstVals];
Jerry
  • 2,459
  • 9
  • 15