Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Win32::OLE & Excel help

by imrags (Monk)
on Jan 14, 2009 at 08:58 UTC ( [id://736158]=perlquestion: print w/replies, xml ) Need Help??

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

Hi
I have an excel file report where i need to traverse through each row and check if a certain
column matches a regular expression (MSD) and another match as well....
My code does it...but i'm looking for a better way to do it
Can you guys look at the code and suggest some performance boosters to the code?
my $CB_COUNT = 0; my $NM_COUNT = 0; my $LastRow = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row}; my $LastCol = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByColumns})->{Column}; print $LastRow." and last column ".$LastCol; for $i (1 .. $LastRow) { my $rango5 = "E".$i; my $rango6 = "J".$i; if ($Sheet->Range("$rango5")->{Value} !~ /MSD/) { $CB_COUNT++; } if ($Sheet->Range("$rango5")->{Value} !~ /MSD/ && $Sheet->Range("$ +rango6")->{Value} !~ /No_/i) { $NM_COUNT++; } }
UPDATE:
Change in the code...
my $CB_COUNT = 0; my $NM_COUNT = 0; $LastRow = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row}; my $LastCol = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByColumns})->{Column}; print $LastRow." and last column ".$LastCol; for $i (1 .. $LastRow) { my $rango5 = "E".$i; my $rango6 = "J".$i; # my $rango7 = " if ($Sheet->Range("$rango5")->{Value} !~ /MSD/) { $CB_COUNT++; if($Sheet->Range("$rango6")->{Value} !~ /No_/i) { $NM_COUNT++; } } }
Raghu

Replies are listed 'Best First'.
Re: Win32::OLE & Excel help
by prasadbabu (Prior) on Jan 14, 2009 at 09:17 UTC

    Hi imrags,

    I have not benchmarked, but by adding next we can avoid some extra executions. It is not going to boost more performance, but slight improvement will be there.

    use strict; use warnings; for my $i (1 .. $LastRow) { if ($Sheet->Range("E$i")->{Value} =~ /MSD/){ next; } else{ $CB_COUNT++; if ( $Sheet->Range("J$i")->{Value} !~ /No_/i){ $NM_COUNT++; } }

    OP has updated the code. Posted solution for the previous one.
    update: Removed extra 'if' statement in 'else' part.

    Prasad

      You'll get a little extra benefit if you avoid entering a BLOCK, so:

      for my $i (1 .. $LastRow) { next if ($Sheet->Range("E$i")->{Value} =~ /MSD/) ; $CB_COUNT++; next if ($Sheet->Range("J$i")->{Value} =~ /No_/i) ; $NM_COUNT++; }
      but as noted elsewhere, the major time cost is probably in the OLE calls.

      Is it possible to pull a range into an array ? I'm thinking: @stuff = $Sheet->Range("J1:J$lastrow") ?

Re: Win32::OLE & Excel help
by holli (Abbot) on Jan 14, 2009 at 09:21 UTC
    Well if it works .... This may be "tiny bit" faster, because it avoids one call to the OLE layer but I doubt you notice any difference unless your files are really large. It's also a bit more readable.
    for $i (1 .. $LastRow) { my $rango5 = "E".$i; my $rango6 = "J".$i; my $value5 = $Sheet->Range("$rango5")->{Value}; my $value6 = $Sheet->Range("$rango5")->{Value}; if ( $value5 !~ /MSD/) { $CB_COUNT++; } if ($value5 !~ /MSD/ && $value6 !~ /No_/i) { $NM_COUNT++; } }


    holli, /regexed monk/
Re: Win32::OLE & Excel help
by Corion (Patriarch) on Jan 14, 2009 at 09:31 UTC

    If all else fails and you're really desperate, you can also export the Excel file to a .csv file and then search through the CSV in Perl. You can then in parallel update your Excel file. That way, you save a lot of calls and data transfers through the OLE layer, but you add some complexity to your code. I'd use Text::CSV, as that one should even treat embedded newlines in your Excel data well enough.

      Beware, when exporting (using Excel) .XLS to .CSV, that Excel (in its infinite wisdom) can return multiple individual cells for a single cell containing large amounts of data (AFAIR, the limit appeared to be ~ 1kB) - a situation that breaks both Text::CSV & Text::xSV since an unexpectedly higher number of columns appear to be returned from rows containing such cells.

      A user level that continues to overstate my experience :-))
Re: Win32::OLE & Excel help
by jmcnamara (Monsignor) on Jan 14, 2009 at 11:11 UTC
    I believe that creating ranges can be expensive so it would probably be better to create one range and then iterate through the cell looking for a match.*

    Alternatively, you could try using the Excel worksheet CountIf() function. Here is a small working example:

    #!/usr/bin/perl use strict; use warnings; use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; my $application = Win32::OLE->new("Excel.Application"); my $workbook = $application->Workbooks->Open("c:/temp/test.xls" +); my $worksheet = $workbook->Worksheets(1); my $range = $worksheet->Range("A1:A8"); my $count = $application->WorksheetFunction->CountIf( $range, "2" +); print $count, "\n"; __END__

    As a further alternative you could try using Jon Allen's XLSperl which gives you a commandline application that behaves like perl but works on Excel files. Something like this:

    XLSperl -lne 'print if /pattern/ and $COL eq "A"' file.xls

    XLSperl used Spreadsheet::ParseExcel which may also be an alternative solution.

    * This isn't a general rule. It is usually better if you can call a single method on a Range rather than iterate through the Cells. However, I don't think there is an applicable method that will produce the desired results in this case.

    --
    John.

      Unfortunately the countif() function of excel doesn't do what I want it to
      I have also tried sumproduct for that...
      But sadly it doesn't work as well...that's the reason i'd to resort to loops...
      Raghu

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (5)
As of 2024-03-28 15:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found