Dolphin and Excel

I'm trying to automate Excel using the Community Edition of Dolphin. Just
dipping my toe in.

Here's what I did:

    1.  Use the ActiveX Component Wizard to generate wrappers around
        the "Microsoft Excel 11.0 Object Library".

    2.  In a workspace, evaluate:

app := Excel_Application new.
app  visible: nil rhs: true.      "(a)"
wb := app workbooks add: nil.     "create a workbook"

ws := wb worksheets item: 1.      "get the first worksheet"
ws := ws asImplType.              "(b) - cast to a Excel_Worksheet"
range := ws range: 'A1:C3'.


As a result, I have some observations and questions:

     1.  The line marked (a) took me a while to figure out. Working in other languages, like
         python (and VB), the process of setting a property is normally more direct.

              app.Visible = True

         which left me wondering why it was a bit messy in Dolphin.  Perhaps I didn't tick a
         necessary option in the generation phase?  Any information appreciated because
         even the line under (a) was similarly puzzling, so this issue came up a few times.

     2.  Similar comment about the line marked (b). Why did I even need to do this line?
         Given some comments in the method #asImplType, I've got a feeling that this is an
         efficiency thing, which is fine, but I wanted to ask the question anyway, just in
         case I'm missing something.

     3.  These simple lines of code took me quite a while to work out, so I was wondering
         if there were any example code fragments of Dolphin automating Excel somewhere
         around. Google didn't uncover anything which surprised me, but perhaps I missed
         a useful resource out there.  If the answer is no, then I guess this post might
         represent a beginning.

     4.  Finally, the next step. Given the code above, I've struggled with my next step.
         I wanted to set multi-dimensional values in worksheet in the way I'm used to
         in other languages.

         I found I could do this:

                range value2: #(1 2 'Hi there').

         (took me a while to figure out that I had to use #value2: rather than the
          more natural #value:) and this call repeated these three values down the
          page, covering A1:C3 as expected.

         But I couldn't seem to send #value2: a multidimensional Array:

               a := Array with: #(0 0 'Hi') with: #(1 1 'There') with: #(2 2 'World').
               range value2: a

         Nothing happened. I can do this in other languages, so I know its possible.
         Any hints?

Thanks,
Mike

Mike,
"Mike Thompson" <I@dontthinkso> wrote in message
news:43f14f97$0$9022$[hidden email]...
>
> I'm trying to automate Excel using the Community Edition of Dolphin. Just
> dipping my toe in.
Unfortunately, you've chosen one of the messier options for learning ;^).
The Office applications can be pretty obnoxious to work with in my
experience (mainly Outlook and Excel).  Their type libraries are really a
mess (check out the Dummy* methods on Excel_Application--what the heck is
that!?!).  Also, as you've probably noticed if you generated everything in
the type library, it's a huge bunch of classes.  The result is not very
well-factored, and requires a fair amount of cleaning-up to be useful.
Furthermore, a large proportion of what's in the type library is of dubious
value for typical automation tasks (like the whole VBA environment, IIRC).
It's easier in VBA, since a lot of what's available is hidden from view, but
in Dolphin it's there for all to see.

For trivial things, I've taken to using the plain IDispatch methods, like
(note that Office "methods" need one of the #invoke: methods, while
"properties" need one of the set* or get* methods:

xl := IDispatch createObject: 'Excel.Application'.
xl setProperty: 'visible' value: true.
xl invoke: 'quit'.
xl := nil.

It's also nice to check the Task Manager's Processes tab to see whether
Excel is really gone.  If you execute the above lines one at a time, you
should see Excel disappear from the process list right after you execute the
last line (xl := nil).

For a slightly more in-depth version of the IDispatch approach, check this
newsgroup thread:
http://groups.google.com/group/comp.lang.smalltalk.dolphin/browse_thread/thread/5b85565aa78b4373/a9876a07496a61bc?lnk=st&q=excel+group%3Acomp.lang.smalltalk.dolphin&rnum=3&hl=en

[..] This is a really obnoxious feature of working with Excel.  IIRC, Excel wants
an array of variants, and their arrangement is not what you would expect
(rotated 90 degrees or some such thing).

The simplest solution is to put it into a string with columns delimited by
tabs and rows delimited by crlfs.  Then you paste it into the worksheet as
plain text:

xl := IDispatch createObject: 'Excel.Application'.
xl setProperty: 'visible' value: true.

wb := (xl getProperty: 'workbooks') invoke: 'add'.
ws := wb getProperty: 'sheets' item: 1.
"IIRC, the range property requires a 2-element array"
range := ws
 getPropertyId: (ws idOfName: 'range')
 withArguments: (Array with: 'b3' asBSTR with: VARIANT unspecified).
range invoke: 'activate'.
"create something to paste"
String writeStream
 nextPutAll: 'asdf';
 tab;
 nextPutAll: 'zxcv';
 contents copyToClipboard.
ws invoke: 'paste'. "should show up in b3:c3"
wb invoke: 'close' with: false. "don't save"
wb := ws := range := nil.
xl invoke: 'quit'.
xl := nil.


>
>         But I couldn't seem to send #value2: a multidimensional Array:
>
>               a := Array with: #(0 0 'Hi') with: #(1 1 'There') with: #(2
> 2 'World').
>               range value2: a
If you really need to do this without the clipboard (admittedly quite
necessary sometimes), I've got the code somewhere.

HTH,

Don

[...]

Don Rylander wrote:
Seconded - in big bold letters.  The problems are not with Dolphin or
the type library analyzer.  The IDL is big and ugly, the component
design is terrible, and that's before the defined constants enter into
it.  Get VBA for Dummies, and follow along with IDispatch.

When you are dealing with well designed COMponents, and especially ones
with high granularity, the type library analyzer is the way to go.  When
you are asking Excel to edit a few cells and then print a range, some
slow calls due to lookup/conversion/etc. are of no consequence.

Back to Excel, you might also want to check out my Word automation
package, which goes a step further by creating some simple wrappers
around stuff like this.  My thinking is that once I struggle with how to
get Word to do something, I don't want to have to have to refresh my
memory on it later.  I have not needed to do the same with Excel, but
you certainly could follow along and make it work.  You will thank
yourself the next time you want to make Excel jump through a given set
of hoops.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Don Rylander wrote:
The Excel object model is large and a bit of a pain, for sure, but for
my applications, it's great to be able to automatically generate Excel
workbooks.  I use the oldest trick in the book when I want to make Excel
do something:  I go into Excel, start recording a macro, and then
perform the job I want done, then stop the macro recording and have a
look at the VB code that's been put into the macro. This trick means
you never need to *really* understand the Excel object model,
I just need to be able to translate this VB code into my
preferred language/COM wrapping.

In the past I've used python as my development language and that's
worked pretty well, but I'm now investigating my options in smalltalk.
I hope to look at the LOOP product for VisualWorks after this.

BUT, and this is the main point of my post, I'm finding it more
difficult to translate from VB (macros) into Dolphin than I did
into python.  A lot of that will be to do with my inexperience and I
have made progress, but I was left wondering if I was missing
something and in particular, if it was something about the
generation step.

>
> For trivial things, I've taken to using the plain IDispatch methods, like
> (note that Office "methods" need one of the #invoke: methods, while
> "properties" need one of the set* or get* methods:
>
> xl := IDispatch createObject: 'Excel.Application'.
> xl setProperty: 'visible' value: true.
> xl invoke: 'quit'.
> xl := nil.

Okay, I can see what's going on here and it is a lot more natural,
but I'm guessing this would be using Dynamic dispatch (rather than
Static Dispatch)?  I ask because obviously Dynamic dispatch can be
quite a bit slower (and that may, perhaps, be an issue in some
situations).

>
> It's also nice to check the Task Manager's Processes tab to see whether
> Excel is really gone.  If you execute the above lines one at a time, you
> should see Excel disappear from the process list right after you execute the
> last line (xl := nil).

Yes, thanks, I had noticed many Excel instances hanging around. I've also
found I have to do more than get rid of the application object, I have
to get rid of all related objects - for example, WorkSheet objects hold
references back to their Application object and can keep it alive.

Can you remember anything more than this?


It does help Don, thanks.

--
Mike

"Mike Thompson" <I@dontthinkso> wrote in message
news:43F28F7D.2000204@dontthinkso...
Cross-process calls (marshaling) will be used in this case, which it is
essential slower than even a calls through IDispatch.
I assume that use early-binding for calls Excel will give very insignificant
increase in speed ( 2-5%).

Pavel

Pavel wrote:
Very true, but I doubt that scripting Excel is such a situation.  Given
a well designed component into which one makes a very large number of
"small calls", generated interfaces will be big winners.

Have a good one,

Bill


--
Wilhelm K. Schwab, Ph.D.
[hidden email]

> In the past I've used python as my development language and that's
> worked pretty well, but I'm now investigating my options in smalltalk.
> I hope to look at the LOOP product for VisualWorks after this.

I have used VisualWorks 7.3 to extract data from complex Excel spreadsheets.
To do this I used the class Smalltalk.Examples.ExcelApplicationController
found in package "Com- Automation Examples". This package is delivered with
the base product. Doing this in VisualWorks was fairly easy. I did not try
to write to the spreadsheets.

Feel free to drop me a note if you have any questions.

Runar Jordahl
http://www.smallwalk.com/

Don, Mike,

> For trivial things, I've taken to using the plain IDispatch methods, like
> (note that Office "methods" need one of the #invoke: methods, while
> "properties" need one of the set* or get* methods:
>
> xl := IDispatch createObject: 'Excel.Application'.
> xl setProperty: 'visible' value: true.

It does seem strange that you have to invoke #setProperty:value: explicitly.
The normal DNU handling allows one to get the 'visble' property with just:

    xl visible.

but you can't similarly use:

    xl visible: true.

(Or rather, you /can/ but it doesn't work ;-)   Looking at the code for
#doesNotUnderstand: it seems that it attempts to make that work, but that the
implementation isn't sufficient when applied to Office apps.  I wonder if it
should be beefed-up to handle the case where there is exactly one argument
specially too, and to try to distinguish properties from methods.  Maybe what
it already does /is/ adequate and it's the Office suite that has broken
behaviour...

    -- chris

Chris,
"Chris Uppal" <[hidden email]> wrote in message
news:43f30a58$0$1171$[hidden email]...
> Don, Mike,
[...] [...]

I suppose I'd argue that what's broken is a conceptual model that forces
callers to know enough about the internal operations of the callee to
distinguish between properties and methods in the first place, but my
training is in music, not computer science, so what do I know ;^).

Of course, that leaves us with the problem: in Dolphin, it *is* more
difficult to do certain things that are easy in other languages.  And MS
Office automation is an issue that comes up frequently.  Noting Runar's post
about the samples available for VW, I wonder whether we (i.e., those of us
who like Dolphin and have some relevant knowledge) should put something
together.  Between Bill's Word stuff and what I've got from working (almost
always ad-hoc) with Excel, we probably have the beginnings of something
useful.

Per your DNU idea, though, I've wondered in the past whether changing that
might deal with most of the problems people run across in light-duty
interaction with MS Office.  Unfortunately, all I thought of was adding some
error handling to try methods and properties separately.  Have you got other
ideas, or do you suppose that might be sufficient for most cases?

Don

Mike,
[...]
>> This is a really obnoxious feature of working with Excel.  IIRC, Excel
>> wants an array of variants, and their arrangement is not what you would
>> expect (rotated 90 degrees or some such thing).
>
> Can you remember anything more than this?
[...]
Fortunately, I don't have to remember it ;^).  Below is the #export: method
from a small, very limited-purpose Excel wrapper (6 methods).  This was
originally written a few years ago, so it probably does some things that
aren't really necessary (I suspect the #finalize messages are, and maybe
always were, superfluous), and a few "Why did I do it like that?" issues
(like that process of figuring out the address for the range).  But it has
the benefit of being actually functional.  By the way, "aRowCollection" just
needs to be an indexable collection of indexable collections; they just have
to respond to the #at: message.

Don


export: aRowCollection
 "First row contains column headings, subsequent rows are data."

 | columnCount rangeStream rangeString range safeArray |
 columnCount := aRowCollection first size.
 (rangeStream := String writeStream)
  display: 'A1:'.
 columnCount > 26
    ifTrue: [rangeStream nextPut: (Character codePoint: (columnCount - 1 //
26 + 64))].
 rangeStream
  nextPut: (Character codePoint: 65 + (columnCount - 1\\ 26));
  display: aRowCollection size.
 range := self dataSheet getProperty: 'range' item: rangeStream contents.
 safeArray := range value.
 "range clearContents"
 1 to: aRowCollection size
  do:
   [:i |
   | row |
   row := aRowCollection at: i.
   1 to: row size
    do: [:j | safeArray elementAt: (Array with: i with: j) put: (row at: j)
asVariant]].
 range
  value: safeArray;
  setProperty: 'wrapText' value: false.
 "range setProperty: 'autofit' value: true."
 xlApp setProperty: 'visible' value: true.
 usingTemplate ifTrue: [self updatePivotCaches].
 safeArray finalize.
 range finalize.
 range := safeArray := nil

Don,

> I suppose I'd argue that what's broken is a conceptual model that forces
> callers to know enough about the internal operations of the callee to
> distinguish between properties and methods in the first place, but my
> training is in music, not computer science, so what do I know ;^).

I assume you know a clam when you hear it :)  In this case, I suspect
the offensive sound is coming from the VB section of the orchestra.


> Of course, that leaves us with the problem: in Dolphin, it *is* more
> difficult to do certain things that are easy in other languages.

Could we make that "easy in other _frameworks_"?  I don't think this is
language related at all.  If anything, Smalltalk and Dolphin in
particular will help, if we let them (which you suggest below).


 > And MS
> Office automation is an issue that comes up frequently.  Noting Runar's post
> about the samples available for VW, I wonder whether we (i.e., those of us
> who like Dolphin and have some relevant knowledge) should put something
> together.  Between Bill's Word stuff and what I've got from working (almost
> always ad-hoc) with Excel, we probably have the beginnings of something
> useful.

Please feel free to adapt my code, and/or build it into something larger.


> Per your DNU idea, though, I've wondered in the past whether changing that
> might deal with most of the problems people run across in light-duty
> interaction with MS Office.  Unfortunately, all I thought of was adding some
> error handling to try methods and properties separately.  Have you got other
> ideas, or do you suppose that might be sufficient for most cases?

That reminds me of my A/D board.  The software for it is mildly screwed
up, and the their registration and/or IDL is _very_ screwed up.  It does
work though.  However, I had a terrible time debugging it and added the
following:

!DAQCOMLibIDaqBase methodsFor!

getProperty:aString

        #wksDangerous.

        [
                ^super getProperty:aString.

        ] on:HRESULTError do:[ :e | | selector |
                selector := aString ensureFirstLowercase asSymbol.
                ^self perform:selector.
        ].
! !
!DAQCOMLibIDaqBase categoriesFor: #getProperty:!public! !

IIRC, I was concerned about inspectors.  I _think_ they were broken
because of problems in the type info, but maybe something similar will
help here.  My gut reaction is to make this Office specific, unless
there is a good argument for it or something similar for more generic use.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Don,

> Of course, that leaves us with the problem: in Dolphin, it *is* more
> difficult to do certain things that are easy in other languages.  And MS
> Office automation is an issue that comes up frequently.  Noting Runar's
> post about the samples available for VW, I wonder whether we (i.e., those
> of us who like Dolphin and have some relevant knowledge) should put
> something together.  Between Bill's Word stuff and what I've got from
> working (almost always ad-hoc) with Excel, we probably have the
> beginnings of something useful.

I'll leave it to the Office gurus (or should that be "victims" ?) to advance
that idea.  It seems good to me, but I don't know enough to contribute.


> Per your DNU idea, though, I've wondered in the past whether changing that
> might deal with most of the problems people run across in light-duty
> interaction with MS Office.  Unfortunately, all I thought of was adding
> some error handling to try methods and properties separately.  Have you
> got other ideas, or do you suppose that might be sufficient for most
> cases?

I've been having a hack around with IDispatch>>doesNotUnderstand: in D6 (pl 1).
Given that
    a) I know almost nothing about ActiveX/IDispatch.
    b) I'm not well acquainted with the Dolphin wrappers.
I feel /very proud/ that my image didn't crash once...  All I was doing was
error trapping, as you describe.  It does seem to work.  (Note the third word
in that sentence very carefully !)  I'll append the code to this message in
case anyone wants to play with it.  There are some obvious issues with it:
    1) The trap to recognise no-such-method and try again as a property
        should be more specific.
    2) I'm not sure whether multi-argument properties should be handled
        specially too.
    3) Should the flags for the N-argument case include DISPATCH_PROPERTYPUT
        or is just DISPATCH_METHOD sufficient ?
There are undoubtedly other issues too that are not apparent to me.

Still with the change, I can do:
    word := IDispatch createObject: 'Word.Application'.
    word visible: true.
    word visible.
    word visible: false.
    word visible.
    word quit.
which may at least indicate that the concept is worth following up.

    -- chris

======= file in ==========
!IDispatch methodsFor!

doesNotUnderstand: aMessage
 "Sent to the receiver by the VM when a message sent to the receiver was
 not implemented by the receiver or its superclasses.
 In this case we assume this is an attempt at invoking a late-bound message,
 so we try looking it up in the receiver's type info and invoking it."

 self isExtensible ifFalse: [^super doesNotUnderstand: aMessage].

 aMessage argumentCount == 0 ifTrue: [^ self doesNotUnderstand0: aMessage].
 aMessage argumentCount == 1 ifTrue: [^ self doesNotUnderstand1: aMessage].
 ^ self doesNotUnderstandN: aMessage!

doesNotUnderstand0: aMessage
 "Private - DNU handling for zero-argument messages."

 | id |

 id := self
  idOfName: aMessage selector
  ifAbsent: [^ self basicDoesNotUnderstand: aMessage].

 "Could be method or property get, try it as a method or revert to get if that
fails"
 ^ [self invokeId: id flags: DISPATCH_METHOD parms: DISPPARAMS zeroArgs]
   on: HRESULTError
   do: [:ex | ex return: (self getPropertyId: id)]!

doesNotUnderstand1: aMessage
 "Private - DNU handling for single-argument messages."

 | id |

 id := self
  idOfName: (aMessage selector upTo: $:)
  ifAbsent: [^ self basicDoesNotUnderstand: aMessage].

 "Could be method or property set, try it as a method or revert to set if that
fails"
 ^ [self invokeId: id flags: DISPATCH_METHOD parms: (DISPPARAMS with: aMessage
arguments first)]
   on: HRESULTError
   do: [:ex | ex return: (self setPropertyId: id value: aMessage arguments
first)]!

doesNotUnderstandN: aMessage
 "Private - DNU handling for multiple-argument messages."

 | id |

 id := self
   idOfName: (aMessage selector upTo: $:)
   ifAbsent: [^ self basicDoesNotUnderstand: aMessage].
 ^ self
  invokeId: id
  flags: DISPATCH_METHOD
  parms: (DISPPARAMS withArguments: aMessage arguments).! !
!IDispatch categoriesFor: #doesNotUnderstand:!exceptions!public! !
!IDispatch categoriesFor: #doesNotUnderstand0:!exceptions!private! !
!IDispatch categoriesFor: #doesNotUnderstand1:!exceptions!private! !
!IDispatch categoriesFor: #doesNotUnderstandN:!exceptions!private! !
=====================

"Chris Uppal" <[hidden email]> wrote in message
news:43f30a58$0$1171$[hidden email]...
This is one of those cases for which I feel in much the same was as I do
about the arguments for introducing a switch statement into Smalltalk.
Frankly I'd rather the DNU handling were not used at all, and so I am not
minded to spend much effort on bandaging it up. That's not to say I don't
think a totally dynamic solution is useful, just that I wish I'd never gone
down the DNU route. A better solution would be to dynamically generate a
lightweight class on demand, at least when a type library is available
(almost always). For those cases where there really is no type information,
I think that using explicit property get/set and method calls is really more
comparable to what you might do in languages which syntactically distinguish
between property assignment and method invocations.

Regards

Blair

"Mike Thompson" <I@dontthinkso> wrote in message
news:43f14f97$0$9022$[hidden email]...
>
> I'm trying to automate Excel using the Community Edition of Dolphin. Just
> dipping my toe in.

Mike

I don't know whether this will help, but I had a fight to automate Excel
from Dolphin last year. After a while I gave up trying to use the generated
Dolphin wrapper, since I could not get it to work. Knowing from past
experience that it is possible to automate Excel from JavaScript, I used
IScriptControl to issue the required JavaScript commands from Dolphin. It
was possible to construct a few Dolphin methods which generated the
appropriate JS code, so the nastiness was hidden away. It seems a really
ugly way to do things, but it did work with very little hassle. I had to get
answers to a deadline, so I swallowed hard and used it. I don't think my
methods would generalise easily, but I could dig out some code samples if
you are interested.

HTH

Peter Kenny

Blair,

I'm in complete sympathy with your aversion to DNU.  <smug>I chose to go the
lightweight classes route (I call 'em "ghost classes") for roughly comparable
purposes in JNIPort.</smug>   It's been rather a lot more work to set up but
I'm happy with the result, and it is fully dynamic without suffering from DNU's
fragility.

But...

Since you /have/ opted for the DNU-based design, shouldn't you either implement
it as well as possible (within reason) or deprecate it altogether and replace
it ?  There doesn't seem to be a lot of point in having a half-baked
implementation, and it can only confuse people when it /sometimes/ works.

    -- chris