spurperl has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

I'm creating Excel charts from my Perl script using Win32 OLE and what annoys me the most is its slow loading time.

What takes most time is the use Win32... clause, it seems. After that, the chart creation itself is relatively simple. Any ideas why this module takes 2 minutes to load ? (My machine is pretty fast, Excel XP loads in about a second).

Thanks in advance

Update: the huge amount of time is spent in:

use Win32::OLE::Const 'Microsoft Excel';

Even without the 'Microsoft Excel' it takes very long...

Replies are listed 'Best First'.
Re: Win32 OLE painfully slow
by tachyon (Chancellor) on Apr 13, 2004 at 11:22 UTC

    Win32::OLE is no speed demon, but this code: 

    use Win32::OLE;

    [download]

    Takes 2 seconds to run on Win2K, AS Perl 5.6.1 Are you sure about where the time is being spent? To use Excel you then have to instantiate an Excel object which also takes a couple of seconds on this box. Nothing takes minutes.

    Do you have enough RAM? If you only have 256MB and XP you don't have enough RAM to have many windows open at once before you will start swapping. That might cause that sort of slow down.

    As an aside why use Excel for graphing? It has always been rather mediocre. Can I recommend GD::Graph to you. It is fast and has a much nicer interface than (dare I say) anything involving Win32::OLE.

    cheers

    tachyon

[reply]
[d/l]
      use Win32::OLE;

      Takes a second on my PC too (P4 1.7Ghz, 512MB memory). What takes most time is

      use Win32::OLE::Const 'Microsoft Excel';

      I want to use Excel because I want to save these charts so other people will be able to open them and analyze them, using no special purpose tools (only Excel).

[reply]

        Dunno what your problem is. This code: 

        use Win32::OLE;
use Win32::OLE::Const;
my $xl = Win32::OLE::Const->Load("Microsoft Excel");
foreach my $const (sort keys %$xl) {
    printf "$const = %s\n", $xl->{$const};
    #printf "sub $const { %d }\n", $xl->{$const};
}

        [download]

        loads and prints all the constants in a couple of seconds. An ugly hack would be to hard code the constants. You could do this simply by using the code above to dump 

        sub constname { NNN }

        [download]

        Cut and paste the output into your code or your own constants module like Win32::OLE::Const::Excel. Leave the generator code there but comment it out. Net effect - you get the constants you need fast. If the constants change just rerun the generator and it will get updated. Not a perfect solution but quite a practical kludge.

        cheers

        tachyon

[reply]
[d/l]
[select]
Re: Win32 OLE painfully slow
by guha (Priest) on Apr 13, 2004 at 13:08 UTC

    I've been in a similar situation, which was resolved by reinstalling Office. The problem had something to do with the anti-virus software

    Try to disable AWS temporarily to see if that makes any difference.

    Just my 25-öring

[reply]

      I would recommend specifying you're excel object version, just for the sake of being very explicit. You can check if its 9 or 10 (probably 10 for XP) in the OLE object browser installed w/ ActivePerl

      That aside I use Win32::OLE for the exact things you're talking about, and it runs with zero problem. p4-2.4Ghz 1GB RAM.

      Anti-virus software could make a difference, because I know NAV will scan a document as you open it, maybe it's (if you're using NAV) having some issue with you creating that document, but it doesn't sound likely

      Grygonos
[reply]
Re: Win32 OLE painfully slow
by maa (Pilgrim) on Apr 13, 2004 at 11:53 UTC

    Hi

    Both statements take a couple of seconds on AS 6.6.1 & NT4... is it perhaps the nomenclature for your constants that's causing the problem? Howabout 'Microsoft Excel XP' or 'Microsoft Excel 10.0' ?

    Just an untested thought...

    HTH - Mark

[reply]

Back to Seekers of Perl Wisdom