9
$\begingroup$

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
    ]
$\endgroup$
  • $\begingroup$ 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. $\endgroup$ – Kuba Nov 18 '18 at 22:57
  • $\begingroup$ Is there any way to release the cells' held evaluations, I've tested the Activate function but it doesnot work. $\endgroup$ – Jerry Nov 18 '18 at 23:55
  • 1
    $\begingroup$ 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. $\endgroup$ – George Wolfe Nov 20 '18 at 20:32
8
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ 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... $\endgroup$ – Anton.Sakovich Nov 21 '18 at 7:56
5
$\begingroup$

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];
$\endgroup$
  • $\begingroup$ +1 With[{i = 3}, ws@Cells[i, i]@Value] will work as well. $\endgroup$ – WReach Nov 20 '18 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.