Problem with Excel operation in NETLink way

Question

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
    ]

Answer 1

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):

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

Answer 2

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];