Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Over a minute to do: use Win32::OLE::Const 'Microsoft Excel';

by spikey_wan (Scribe)
on Sep 09, 2004 at 09:54 UTC ( [id://389608]=perlquestion: print w/replies, xml ) Need Help??

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

Hello World!

I've been having trouble with using Excel, in that it's extremely slow. I think I've isolated the problem, but don't know whether it's to do with perl, my pc, or what.

This script:...

use Win32::OLE::Const 'Microsoft Excel'; print xlExtractData."\n";
Takes 75 seconds to execute!

I did this:
perl -D:Prof test1.pl
dprofpp -r tmon.out
and got this result:

Exporter::export has -1 unstacked calls in outer Exporter::Heavy::heavy_export has 1 unstacked calls in outer Total Elapsed Time = 74.89209 Seconds Real Time = 74.89209 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 99.4 74.45 74.454 538 0.1384 0.1384 Win32::OLE::Const::_Typel +ib 0.36 0.268 74.722 1 0.2678 74.721 Win32::OLE::Const::_Typel +ibs 0.07 0.051 0.051 1 0.0510 0.0510 Win32::OLE::Const::_LoadR +egTypeLib 0.05 0.040 0.040 1 0.0400 0.0400 Win32::OLE::Const::_Const +ants 0.04 0.030 74.892 1 0.0300 74.892 main::BEGIN 0.03 0.020 0.050 3 0.0066 0.0165 Win32::OLE::Const::BEGIN 0.01 0.010 0.010 2 0.0050 0.0050 Config::fetch_string 0.01 0.010 0.010 2 0.0050 0.0050 DynaLoader::BEGIN 0.01 0.010 0.010 3 0.0033 0.0033 vars::BEGIN 0.00 0.000 0.000 1 0.0000 0.0000 Exporter::Heavy::heavy_ex +port 0.00 - -0.000 1 - - Config::TIEHASH 0.00 - -0.000 1 - - DynaLoader::dl_load_file 0.00 - -0.000 1 - - DynaLoader::dl_install_xs +ub 0.00 - -0.000 1 - - warnings::BEGIN 0.00 - -0.000 1 - - Config::import
My system:
XPpro SP1 with 512 Mb memory, an Intel(R) Pentium(R) M processor 1600MHz with a bus speed of 797 MHz, activeperl v5.8.4 build 810.

I'm in well over my head, now. can anyone help me, please?

Thanks,
Spike.

Replies are listed 'Best First'.
Re: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel';
by guha (Priest) on Sep 09, 2004 at 10:53 UTC

    This thread discusses the same / a similar problem.

    HIH
Re: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel';
by iburrell (Chaplain) on Sep 09, 2004 at 23:08 UTC
    How long does it take to start Excel on the machine? Because that is what the use Win32::OLE::Const  'Microsoft Excel'; line is doing. It loads the 'Microsoft Excel' COM component, which is effectively the Excel application. I am guessing it loads some constants out of the library, but the application startup is where the time goes.
      I'm not doing anything fancy at all in Excel, just running the 2 line script at the top of this thread!

      As for opening Excel normally through windows, well it opens almost instantaneously.

Re: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel';
by terra incognita (Pilgrim) on Sep 09, 2004 at 14:28 UTC
    What is in your Excel workbook? How many sheets, columns, and rows? Any special formula's or macros? I do not see an issue with the same version of perl against Office 2003.
Re: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel';
by BrowserUk (Patriarch) on Sep 10, 2004 at 12:59 UTC

    Looking at your profile output it shows that Win32::OLE::Const::_Typelib is being called 538 times. Presumably, once each for 538 imported constants. The pod shows an alternative syntax that gives you back a hash of the constants rather than exporting each as a constant sub. It's possible, though I cannot test it here, that using the alternate syntax mught take less time?

    From Win32::OLE::Const pod:

    The first example imports all Excel constants names into the main name +space and prints the value of xlMarkerStyleDot (-4118). use Win32::OLE::Const ('Microsoft Excel 8.0 Object Library'); print "xlMarkerStyleDot = %d\n", xlMarkerStyleDot; The second example returns all Word constants in a hash ref. use Win32::OLE::Const; my $wd = Win32::OLE::Const->Load("Microsoft Word 8.0 Object Librar +y"); foreach my $key (keys %$wd) { printf "$key = %s\n", $wd->{$key}; } printf "wdGreen = %s\n", $wd->{wdGreen}; The last example uses an OLE object to specify the type library: use Win32::OLE; use Win32::OLE::Const; my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); my $xl = Win32::OLE::Const->Load($Excel);

    It might even be, but again I cannot test this guess, that using the third form above would only import a subset of available constants applicable to excel?


    Examine what is said, not who speaks.
    "Efficiency is intelligent laziness." -David Dunham
    "Think for yourself!" - Abigail
    "Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon
      After a lot of fiddling about, I have found 2 things that make a difference.

      1. Remove the network cable from my PC. Then the 2 line script runs in ~5 seconds.

      2. Modify C:\Perl\site\lib\Win32\OLE\Const.pm, and change this line:
      return unless -f $filename || $filename !~ /^\w:\\.*\.(exe|dll)$/;
      to this:
      return unless $filename !~ /^\w:\\.*\.(exe|dll)$/;
      and the 2 liner runs almost instantaneously.

      Should I just do the second fix, and not worry about it, or is there a 'proper' way to sort this out?

      Thanks,
      Spike.

        ... or is there a 'proper' way to sort this out?

        I'd certainly apply the second method, and then report the problem to ActiveState.


        Examine what is said, not who speaks.
        "Efficiency is intelligent laziness." -David Dunham
        "Think for yourself!" - Abigail
        "Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://389608]
Approved by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2024-04-18 19:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found