Dolphin and Excel

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Dolphin and Excel

Peter Kenny-2
Hi All

I have been having a fight trying to automate the entry of results from my
Dolphin app into an Excel spreadsheet (my customer regards Smalltalk as
something alien and wants the results in a familiar form!). So far I have
tried three approaches, with partial success for each:

First, I have tried using full automation with the AXTypeLibraryAnalyser,
following the procedure used in the class method #example3. I have been
generating the libraries as I need them for each object (I know I shall not
use the entire Excel object hierarchy, and the whole library would be
huge!). I find the class and method comments not very enlightening, and I
don't really understand COM anyway, so it's all a bit of a struggle, mostly
trial and error. One thing that bugs me is that so many of the generated
methods have a parameter 'lcid', and I can't find a definition anywhere.
Putting it as zero seems to work usually, but I don't altogether trust it.

Second, I have tried the simple automation illustrated in IDispatch
class>>example 3 and example4. This can be persuaded to work for most
things, but I can't find a way of assigning an Excel range as the Values
property of a series in a chart (every time I try I get the error message
'HRESULT Error: Type Mismatch. (FACILITY_DISPATCH)' ).

Third, I tried using an instance of IScriptControl, sending the instructions
to Excel in JavaScript. This works well for most things (including setting
the Values for a chart!), but seems messy and is awkward when I have to
translate some complex Smalltalk value into the JavaScript equivalent (some
of the results to be entered in the spreadsheet are strings extending over
several lines, and using the literal value in JavaScript causes an error).

At present it looks as though I may need to do the job in parts using more
than one approach, but obviously I would like to do it all with one. The
'proper' Smalltalk approach is obviously the first. Could anyone tell me
what the significance of the lcid parameter is? Or any other ideas?

Thanks in advance

Peter Kenny


Reply | Threaded
Open this post in threaded view
|

Re: Dolphin and Excel

Peter Kenny-2
"Peter Kenny" <[hidden email]> wrote in message
news:[hidden email]...

I have now done what I should have done before posting, which is to Google
on 'lcid'. So I know now that lcid := 0 means language neutral. Sorry for
wasting people's time. Still, any comments gratefully received.

Peter


Reply | Threaded
Open this post in threaded view
|

Re: Dolphin and Excel

Don Rylander-3
Peter,

The lcid argument that shows up everywhere strikes me as just the most
obvious aspect of Office's hideously malfactored object model; I feel your
pain.

On a more helpful note, one thing I've done is to use tab- and
crlf-delimited strings through the clipboard.  Obviously, it isn't ideal,
but it's a pretty common way that 3rd-party apps can interact with Excel.
I've found it handy for prototyping output, and especially so if you can
provide an Excel template to avoid having to do too much ugly interaction.
I have one template with a bunch of pivot tables fed by a named range, which
I resize (from within Smalltalk) after I stuff in the data.  If you haven't
already, it's also probably worthwhile to create a lightweight class for an
application-specific Excel implementation; I almost always need just a tiny
subset of what's available in its object model.

If you want to assign values directly to ranges, you have to build a
SAFEARRAY of VARIANTS, making sure that the dimensions of the SAFEARRAY
match the dimensions of the Excel Range.  IIRC, it always seemed that the
SAFEARRAY was rotated 90 degrees from what I would expect (i.e., you have an
array of columns, not an array of rows), but that may just be a personal
problem ;^).  It's a bit clunky and results in ugly Smalltalk code, but it's
pretty fast and the ugliness can usually be isolated to a method or two.

HTH,

Don


Reply | Threaded
Open this post in threaded view
|

Re: Dolphin and Excel

Peter Kenny-2
"Don Rylander" <[hidden email]> wrote in
message news:[hidden email]...
[snip]
> If you haven't
> already, it's also probably worthwhile to create a lightweight class for
an
> application-specific Excel implementation; I almost always need just a
tiny
> subset of what's available in its object model.

 Don

Thanks for this thought. I find I still don't have the right 'strategic'
approach to many Smalltalk problems; this is an example of something which
will make my task easier.

Peter