# Problem with Excel operation in NETLink way

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;

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
]

• 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 • 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 Answers 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

(* (NETLinkObjectsNETObject$$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.

• Hm, it's strange that NETLinkPackagenCall 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 7:56

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

• +1 With[{i = 3}, ws@Cells[i, i]@Value]` will work as well. – WReach Nov 20 '18 at 15:25