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

Hi,all:

I need to be from an excel object(50000 rows) that contains all of "0000" line,use WIN32::OLE,but error: 

Uncaught exception from user code:
    Win32::OLE(0.1709) error 0x800a03ec
    in METHOD/PROPERTYGET "Cells" at C:\Documents and Settings\Adminis
+trator\My Documents\script.pl line 2.

 at C:\strawberry\perl\vendor\lib/Win32/OLE/Lite.pm line 15.

 at C:\Documents and Settings\Administrator\My Documents\script.pl lin
+e 2.

[download]
##########################################################

The following complete code: 

use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3;                                # die on errors.
+..


# get already active Excel application or open new

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  

# open Excel file

my $Book = $Excel->Workbooks->Open("E:/OLE2.xls"); 

my $Sheet = $Book->Worksheets(1);

foreach my $row (1..300)
{
foreach my $col (1..300)
{
  # skip empty cells

  next unless defined $Sheet->Cells($row,$col)->{'Value'};

  if ($Sheet->Cells($row,$col)->{'Value'}=~/0000/){
    print $Sheet->Cells($row,$col)->{'Value'};
  }

}
}

# clean up after ourselves

$Book->Close;

[download]
WHY? Thank you very much.

Replies are listed 'Best First'.
Re: Help,WIN32::OLE parse EXCEL ,run error
by Just in (Sexton) on Jan 25, 2011 at 05:21 UTC

    I haven't tested your code, but I doubt you really want 1 .. 300 for the columns. This would be more appropriate:

    foreach my $col ( 'A' .. 'XFD' ) # Assuming >= Office 2007
{
    foreach my $row ( 1 .. 300)
    {
        next unless defined $Sheet->Cells("$col$row")->{Value};

        # the rest of your code here
    }
}

    [download]
[reply]
[d/l]
      Thank you,friend. OK.
[reply]
Re: Help,WIN32::OLE parse EXCEL ,run error
by Ratazong (Monsignor) on Jan 25, 2011 at 07:18 UTC

    I have tested your code, and I have added a 

    print "$row $col\n";

    [download]
    before your 
    next unless defined $Sheet->Cells($row,$col)->{'Value'};

    [download]
    The last pair before the error is 1 257, so it seems that cells() only expects an one-byte-value for the column .... Maybe that is a good starting-point for your investigations ...

    HTH, Rata
[reply]
[d/l]
[select]
Re: Help,WIN32::OLE parse EXCEL ,run error
by perllee (Novice) on Jan 25, 2011 at 07:22 UTC
    Thank you,friend.

    foreach 50000 rows:

    use time: 293 wallclock secs (68.59 usr + 45.39 sys = 113.98 CPU)

    So slowly.

[reply]

      That's because you're not effectively using the Win32::OLE interface to Excel. You want to use the Find function. See a handy reference of Excel methods at the Tek-Tips Forums site. Specifically, check out the "#___ FIND LAST ROW/COLUMN WITH DATA" section. I think you can adapt that to your needs.

[reply]

        After add $last_row and $last_col,program is still slow speed.Is there a way to optimize the code to run by speed?

        Thanks,my friend.

[reply]

Back to Seekers of Perl Wisdom