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
    ]
Improve this question
$\endgroup$
3
  • $\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$ Commented Nov 18, 2018 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$ Commented Nov 18, 2018 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$ Commented Nov 20, 2018 at 20:32

2 Answers 2

Reset to default
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.

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.