On 8/8/2011 4:29, Orchid XP v8 wrote:
> I can see how an application might expose a COM object such that invoking a
> certain method is like clicking on a particular button. What I can't figure
> out is how COM lets you do stuff like link a chart to a database such that
> every time the database changes, the chart updates. I'm not sure how you
> invoke methods to make that happen.

Assuming you're talking about (say) a SQL server and excel, you write a 
macro in excel that runs whenever you open the spreadsheet (probably in 
VBA). The macro in VBA opens a connection to the SQL COM object, tells it to 
read a database, and then loops over the results, using the Excel COM object 
to stick the rows into the spreadsheet. When it gets to the end of the 
result set, it closes the SQL COM object and tells Excel to draw a chart 
using the COM interface for that.

> Would Excel be a single COM object? Or would you have like seperate objects
> for each workbook, worksheet, cell, etc?

Excel would be a single COM Server. You instantiate an "application" object, 
and that object will have methods for instantiating workbooks, worksheets, etc.

So looking at the Tcl example of talking to Excel again:

 >> set application [::tcom::ref createobject "Excel.Application"]

This makes the "application" variable refer to a COM object of type 
"Excel.Application", which fires up Excel if it isn't already running. If 
you look in the registry for "Excel.Application", it shows you all the data 
structure links telling you which executable it is, what command line 
arguments it uses to do that, and what GUID Excel will store in the 
appropriate data structures to make it work. It also tells you the threading 
model, permissions needed, etc etc etc.

You can also pass the GUID directly instead of the name to be looked up, at 
which point you get that and only that implementation.

In a scripting language like Tcl, things like "Visible" and "Workbooks" are 
passed to a function called IDispatch. So "$application Visible 1" is really 
closer to "$application IDispatch ("Visible", 1)". However, there is also 
statically typed interfaces, and the description of those interfaces (like C 
"struct" headers or C++ class declarations) are described in the Interface 
Description Language, or IDL.

 >> $application Visible 1

This is a method invocation. It's passing "1" to the argument of the 
"Visible" method on the Excel Application object.

 >> set workbooks [$application Workbooks]

The Workbooks method returns the list of workbooks in the currently focussed 
spreadsheet.

 >> set workbook [$workbooks Add]

This adds a new workbook and returns it.

 >> set worksheets [$workbook Worksheets]

This gives you the list of worksheets in the given workbook.

 >> set worksheet [$worksheets Item [expr 1]]

"Item" is the standard method name for indexing elements, like [] in C. You 
have to run "1" thru expr to make sure it's an integer, or you might wind up 
looking for the worksheet whose name is the single-character string "1". 
Because Tcl doesn't have types.

set data [list \
     [list "North" "South" "East" "West"] \
     [list 5.2 10.0 8.0 20.0] \
]
set sourceRange [$worksheet Range "A1" "D2"]
$sourceRange Value $data

So you create some data, pick out the range A1 through D2 in your worksheet, 
and set the values into that range. Again, "Value" is a pretty standard 
method that means "assign the value to the content", basically the inverse 
operation of "Item".

 >> set charts [$workbook Charts]
 >> set chart [$charts Add]

Get the list of charts and add a new one.

 >> $chart ChartWizard $sourceRange 5 [::tcom::na] 1 1 0 0 "Sales Percentages"

Start the chart wizard and answer these values. You'd have to look at the 
docs to figure out what the chart wizard arguments mean here, but a couple 
are obvious.

And that's how you drive Excel. You want to drive it from SQL? Replace the 
"set data" line with one that opens Sql.Application, creates a query, 
submits it, and retrieves the data.

But that page of code is all you need (given Tcl and TCom and of course 
Excel) to actually create a chart in Excel from your script.

Word has similar stuff, where you can insert text, set it bold or change the 
font, etc etc etc.  A lot like javascript's DOM model.

> See, I've always assumed that the glitering world of native Windows GUIs,
> IPC, DLLs, COM, etc. are all behind closed doors, accessible only to people
> who know how to write really low-level Windows code. And I've always assumed
> that the really /good/ stuff (like embedding one application inside another)
> is accessible only to Microsoft themselves. (Or anybody who pays really vast
> sums of money. People like Symantec.)

Nope. You really can do it in Tcl.  Getting it to actually display in your 
code is a little messier, but not much.

>>> I've yet to see a language that can invoke DLLs either...
>>
>> Maybe you should learn a normal language, then. :-)
>
> So... you mean basically C or C++ then?

Something not experimental. Java, C, C++, C#, Ruby, Python, VBA, Tcl, etc 
etc etc.

>>> I'd be perfectly happy doing COM from, say, JavaScript. It's not that
>>> Haskell is the problem, it's that I can't see *anything* that speaks COM.
>>
>> Tcl, VBScript, WScript, VBA, and C# all have trivial interfaces to COM.
>
> Oh dears.

Looks like Python has COM access:

http://www.devshed.com/c/a/Python/Windows-Programming-in-Python/2/

The best way to search seems to be something like
     python "com interface"
because otherwise it finds python.com and stuff like that.

This may be broken, but maybe it'll be easier for you to understand how COM 
works:
http://research.microsoft.com/en-us/um/people/emeijer/Papers/HaskellCom.pdf
I mean, that looks like a pretty good description of how it all works.

>> Didn't you tell me you had written a VBA macro or two?
>
> Yeah. It took me several hours to finally construct a 25-line VBA macro
> which performs the utterly trivial task of setting the value of a certain
> cell to today's date. This was an exercise in extreme frustration, as the
> help files utterly failed to be remotely helpful in any way. I've been
> bitter ever since. :-P

Heh. Interestingly enough, that has been my experience too. Not uncommonly, 
the documentation for something will be just a little off, or it won't tell 
you exactly what you need installed to make it work, so the docs will tell 
you what the method is and the arguments, but it won't tell you what class 
it applies to, or which libraries have to be installed to find the 
implementation, or etc.

> Yeah, maybe that's what it comes down to. I tend to avoid all the
> MS-designed languages, which is maybe why I can't do COM.

But COM is really common in pretty much anything that's not so UNIX-centric 
that nobody even writes a COM library interface. Even PHP interfaces to COM:

http://php.net/manual/en/ref.com.php

Pretty much any language powerful enough to override the function call 
syntax is going to have something that looks like COM pretty easily. Or 
anything statically typed for which you have the IDL in advance, so you can 
compile interface stubs that take the arguments to calls and package them up 
for COM, which is how C++ gets to COM for example. (You just wind up with 
different syntax to get to dynamically-linked COM vs statically-linked COM 
interfaces.)

Funny enough, it looks like nobody ever wrote a COM interface for elisp. :-)

-- 
Darren New, San Diego CA, USA (PST)
   How come I never get only one kudo?

Post a reply to this message