Loading ...
Sorry, an error occurred while loading the content.

578Re: [soaplite] OLE (Win32::OLE) and SOAP::Lite [solution and question]

Expand Messages
  • Jörg Ziefle
    Jul 5, 2001
    • 0 Attachment
      On Wed, Jul 04, 2001 at 09:15:14PM -0700, Paul Kulchenko wrote:

      > > I want to use MS Excel (on the server side) (via Win32::OLE) over
      > > SOAP::Lite. Unfortunately, I encountered some problems. Does
      > > anybody have an idea what could be the wrong (please see below)?
      > Nothing is wrong there, but Win32::OLE is one of the objects that
      > can't be properly serialized. If you create an object of this type
      > and try to make 'x' in debugger on this object, you'll see why. It
      > has some internal structure that will create new references on demand
      > when you try to get a value of this property. I don't know many
      > details about it, but it doesn't seem to be serializible with
      > Data::Dumper also and SOAP::Lite's serializer goes into endless loop
      > trying to serialize it. I would appreciate any information on how to
      > handle it properly.
      > What you can do is use object-by-reference interface and don't return
      > Win32::OLE object back on client side. As slightly better option (you
      > have more control in this case), you can implement simple wrapper
      > around Win32::OLE object that keeps it on server side and return some
      > information on client side that helps you identify object stored on
      > server side. Let me know if you find any other options or way to
      > serialize Win32::OLE object.

      The following email contains a solution to my problem with a little
      problem at the very bottom (see PROBLEM).


      Thanks to your hint, I have come up with a little wrapper class
      (WrapOLE) around the Win32::OLE module. The class generates a global
      (to the class) Win32::OLE object with the name $OLE of the kind
      specified in the constructor call (see example) The class then provides
      a method OLE_eval_sub that uses string eval to evaluate the string
      passed form the client side and pass back the result. The string passed
      from the client can use (of course, otherwise the whole thing would be
      pointless) the global $OLE object stored on the server side. This
      solution works because the method stays on the server side and doesn't
      have to be serialized (what obviously doesn't work). I think the
      solution is simple, has only little overhead and is powerful. Of
      course, fancier things could be thought of like AUTOLOAD()ing and
      dispatching of WrapOLE method calls to the corresponding Win32::OLE $OLE
      object. Anyway, it works.

      Here's the WrapOLE class. Simple, isn't it? :)

      package WrapOLE;

      use strict;
      use Win32::OLE;

      our $OLE;

      sub new {

      my ($proto, $arg) = @_;

      my $class = ref $proto || $proto;
      $OLE = Win32::OLE->new($arg) or die Win32::OLE->LastError;
      my $self = {};
      bless $self, $class;
      return $self;


      sub OLEeval_sub {

      return eval $_[1];



      Of course, error checking of the eval() would be a good thing, but
      that's left as an exercise to the reader. :/

      Here's a sample client program that uses the WrapOLE module to access
      and perform a calculation in Excel (with Excel's builtin correl()

      use strict;
      use SOAP::Lite
      +trace => ['fault'],
      +autodispatch =>
      uri => '',
      proxy => '',
      on_fault => sub { print $_[1]->faultstring },

      my $ole = WrapOLE->new('Excel.Application') or die "$!\n";

      my $sub = <<'EOSUB';
      $OLE->{Visible} = 0;
      $OLE->{DisplayAlerts} = 0;
      my $book = $OLE->Workbooks->Add or die Win32::OLE->LastError;
      my $sheet = $book->Worksheets(1) or die Win32::OLE->LastError;
      my @valueary = (
      [1, 34],
      [2, 45],
      [32, 34],
      [5, 7],
      [34, 45],
      [5, 7],
      [32, 32],
      [46, 8],
      [57, 34],
      [34, 84],
      [5, 12],
      [45, 34],
      [65, 72],
      [34, 82],
      [74, 98],
      [23, 2],
      [6, 43],
      [35, 65],
      [98, 74],
      [4, 45],
      [57, 34],
      [23, 3],
      [87, 74],
      [23, 25],
      [6, 37],
      [77, 2],
      $sheet->Range('A1:B'.scalar @valueary)->{Value} = \@valueary;
      my $range1 = $sheet->Range('A1:A'.scalar @valueary);
      my $range2 = $sheet->Range('B1:B'.scalar @valueary);
      my $correl = $OLE->Correl($range1, $range2) or die Win32::OLE->LastError;
      return $correl;

      print $ole->OLEeval_sub($sub);

      If you set

      $OLE->{Visible} = 1;

      then you can even see the magic happening on the desktop of the Windows


      Only one little problem is staying: Even though callling $OLE->Quit, of
      Excel doesn't seem to shut down. Alternatively, I provided a WrapOLE
      method quit that just called


      but that didn't make a difference. However, Excel shut down fine when
      testing the program locally (without the wrapper module and without
      SOAP,- just the commands inside the eval).

      Does anybody have an idea why Excel keeps on running and doesn't
      shutdown properly?



      P.s.: To complete the set of programs, here is the daemon I used:

      use SOAP::Transport::HTTP;

      $daemon = SOAP::Transport::HTTP::Daemon
      -> new(
      LocalPort => 5000,
      Reuse => 1,
      -> dispatch_to('C:\\perl\\site\\lib', '[\w:]+');

      print "Contact to SOAP server at ", $daemon->url, "\n";

    • Show all 4 messages in this topic