> 6. perl 5.8.x/Excel vis Win32::OLE - file creation.seek issue
> ([EMAIL PROTECTED])
> ----------------------------------------------------------------------
> Message: 6
> Date: Thu, 9 Mar 2006 08:23:27 -0500
> From: [EMAIL PROTECTED]
> Subject: perl 5.8.x/Excel vis Win32::OLE - file creation.seek issue
> To: [email protected]
> Message-ID:
> <[EMAIL PROTECTED]>
>
> Content-Type: text/plain; charset="US-ASCII"
>
> I have two issues with the following script:
>
> 1: it wont create a file when there is none
> 2: i don't know how to jump to the final row when the file exists so as
to
> not overwrite information
>
> i spent several hours yesterday scouring google on variations of "perl
ole
> excel" with other modifiers to try to get the answers and have not found
> them.
>
> thank you in advance for your help.
>
> -josh
>
> #! /usr/bin/perl
> use strict;
> use warnings;
> use Getopt::Long; # used in getting unix-style options
> use Win32::OLE;
> use Win32::OLE::Const 'Microsoft Excel'; # use OLE/Excel
> $Win32::OLE::Warn = 3; # die on errors...
>
>
################################################################################
> # Purpose:
> # This script catalogues and reports informationon lab systems
> #
> # This should be kept as both a script and an executable. The executable
> should
> # be made by the last person to revise the script. The Active State Perl
> PDK has
> # been used.
> #
> # Created: 03/02/2006
> # Original Author: Josh Perlmutter
> # Maintainers:
> # Last Revised: 03/07/2006
> # Copyright SeaChange International (tm)
>
################################################################################
>
> my(@comps);
> my($comp, $date);
> # standard options/revision information variables
> # verbose line is 79 chars long w/2 \t
> # base to use to add options: -<option>\t\t<use>
> my($help, $usage, $verb, $qrev, $rev, $revdat, $modules, $log, $err);
> $modules="\tnone as of now\n\n"; $help=0; $qrev=0; $err=0;
> $rev="0.0.3"; $revdat="03/08/2006"; $verb=0; $log='';
> $usage = <<EOD;
> \tFile: $0\tVersion: $rev\tReleased: $revdat\n
> This is the $0 file.\n
> The options for this file are:
> -help This help screen only (overrides all other
> options)
> -log File to log to (appends if the file exists)
> -version List Version and exit (overrides everything but
> -help)
> -verbose Turn on verbose (useful for debugging errors;
call
> twice
> for extra output)
>
> usage ([] denotes optional argument {} denotes default value
> {} denotes option set of which your choice of one is
> required):
> $0 [-help] [-version] [-verbose]
> to debug try: $0 -verb -verb > debug_log.txt\n
> EOD
>
> # short explanation of variables:
>
>
> ######
> # this section deals with options. it looks for unix-style options
passed.
> that
> # is, <script> -option [value]
> # now get the options
> GetOptions ('help'=>\$help, 'verbose+'=>\$verb, 'version'=>\$qrev,
> 'log:s'=>\$log, 'computers:s' => \$comp);
>
>
> ###########################
> # arguments ARE required
> unless($help || $qrev ||
> ($log)){
> my $necessary = <<EOD;
> You must supply at least 1 parameter(s) to run this program. It
> needs:
> -log log file to log actions to (appends if existing)
> Turning on help
> EOD
> print $necessary;
> $help++;
> }
>
> #
> # now put the options to use...
> #
>
> # show usage and exit if asked for
> die $usage if $help;
>
> # report version and exit
> die "\tFile: $0\tVersion: $rev\tReleased: $revdat\n" if $qrev;
>
> ####################################################
> ## this is a standardized reporting subroutine #####
> ## it takes a line and a flag for verbose and #####
> ## reports to a universal file handle (and screen) #
> ####################################################
> sub rep{
> # this assumes two passed values,
> # first a message
> # second a flag for verbose (screen printing)
> my $time=localtime().' | ';
> print LOG "$time $_[0]\n";
> if($_[1]){ print STDOUT "$time $_[0]\n"; }
> }
>
> sub getinfo(){
>
>
#############################################################################
> ## this grabs the system information for reporting at the begining of
> logs ##
>
>
#############################################################################
>
> my $nname=`hostname`; chomp($nname);
> my $nos=`ver`; $nos =~ s/\W*(\w.*\])\s.*/$1/;
> my $nip=`ipconfig`; $nip =~ s/\D+([\d\.]{7,15})[\D\d]*/$1/;
>
> my $info= <<EOD;
> Program Information:
> Script: $0
> Last Updated: $revdat Version $rev
>
> System Information:
> Node Name: $nname
> Node OS: $nos
> Node IP: $nip
>
> EOD
> return $info;
> }
>
> sub dbg{
> # this is a debugging subroutine that prints things to screen if the
> # verbosity level is high enough
> my $item=$_[1];
> my $dbgpt=$_[0];
>
> while(1){
> print "Debug Point: $dbgpt\nItem value:\n$item
> \n\t\tShall I continue? (y or n)\n\n";
> if(<STDIN> =~ m/^n$/i){ die "\t\t\tyou chose to leave at
$dbgpt\n\n";
> }
> elsif(<STDIN> =~ m/^y$/i){ print "Continuing to next point\n\n";
last;
> }
> else{ print "response not understood\n"; }
> }
> }
>
> # start the log
> open LOG, ">>$log" or die "cannot open log file";
> &rep(&getinfo,$verb);
> ############################ Below is non-templeted
> ############################
> # make the computer list an array
> @comps=split ',', $comp;
>
> # get the date
> my @rdate=split ' ', localtime();
> $date=$rdate[4].'-'.$rdate[1].'-'.$rdate[2];
> if($verb>1){ &rep("$date\n",$verb); }
>
> foreach $comp (@comps){
> # for each computer in the array
> # get the following information placed into a file
> # titled by the computer name and date
> # or tell use why there is an error and exit
>
> my $err=0; # error indicator
>
> # this should be 0 on error and 1 on success
> my $cmd="psinfo -h -s \\\\$comp > $comp-$date.txt";
> my $res=system "$cmd";
>
> if($res){
> my $err_rep="\t\tinformation gathering error!";
> $err_rep.=" $res Information may not be complete.
> \t\tcommand: $cmd\n$!\n$^E\n\n";
> &rep($err_rep,$verb);
> }else{
> &rep("Cataloged $comp to $comp-$date.txt\n",$verb);
> }
>
> }
>
> #######
> ## OLE interaction upon the saved files follows
> #######
>
> # get an active Excel or create a new one
> my $Excel = Win32::OLE -> GetActiveObject('Excel.Application')
> || Win32::OLE -> new('Excel.Application', 'Quit');
>
> my $labrep="./Lab_Report_$date.xls";
>
> # does the file exit?
> if(-e "$labrep"){
> #we are just adding to it, so open it
> my $report = $Excel->Workbooks->Open("$labrep");
> my $ws = $report -> Worksheets(1);
>
> my $ldate = localtime();
> &rep("Examining information gathered from lab computers @ $ldate\n",
> $verb);
>
> # start row counter
> my
$row=2;###################<----------------------------bad!!!!!!!!!!!
>
> foreach my $node (@comps){
> # for each node we check the information returned
> # if there is no information we only have two items to place on the
> row,
> # otherwise we have A-V items
>
> # set the first cell since that's always going to be the same
> $ws -> Cells($row, "A") -> ('Value') = "$node";
>
> # try to open the file with the information
> open LNINFO, "<$comp-$date.txt" or $err=1;
>
> if($err){
> # there was an error on the file, so note that in the report
> $ws -> Cells($row, "B") -> ('Value') =
> "$comp-$date.txt could not be opened to be put into the lab
> report. $! $^E\n";
>
> }else{
> # there was no error, so now we check the file,
> # first reading in the file
> my @examine;
> while(<LNINFO>){
> push @examine, $_;
> }
>
> # then noting which couldnt be connected to
> if($examine[6] =~ m/The network path was not found./i ){
> $ws -> Cells($row, "B") -> ('Value') = "The network path was not
> found.";
>
> }else{
> # we need to run through the file for the information to put
into
> the rows
>
> # drop what's before what we need
> while (!($examine[0] =~ m/Uptime.*/i)){ shift(@examine); }
>
> while (!($examine[0] =~ m/OS Hot Fix.*/i)){
> # now go through a series of if/elsif sections for the rest
> # hot fixes and programs will be handled slightly differently
> though
>
> if($examine[0] =~ m/Uptime:\s+(\w.*)/i){
> $ws -> Cells($row, "E") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Kernel version:\s+(\w.*)/i){
> $ws -> Cells($row, "F") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Product type:\s+(\w.*)/i){
> $ws -> Cells($row, "G") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Product version:\s+(\w.*)/i){
> $ws -> Cells($row, "H") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Service pack:\s+(\w.*)/i){
> $ws -> Cells($row, "I") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Kernel build number:\s+(\w.*)/i){
> $ws -> Cells($row, "J") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Registered organization:\s+(\w.*)/i){
> $ws -> Cells($row, "K") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Registered owner:\s+(\w.*)/i){
> $ws -> Cells($row, "L") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Install date:\s+(\w.*)/i){
> $ws -> Cells($row, "M") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Activation status:\s+(\w.*)/i){
> $ws -> Cells($row, "N") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/IE version:\s+(\w.*)/i){
> $ws -> Cells($row, "O") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/System root:\s+(\w.*)/i){
> $ws -> Cells($row, "P") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Processors:\s+(\w.*)/i){
> $ws -> Cells($row, "Q") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Processor speed:\s+(\w.*)/i){
> $ws -> Cells($row, "R") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Processor type:\s+(\w.*)/i){
> $ws -> Cells($row, "S") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Physical memory:\s+(\w.*)/i){
> $ws -> Cells($row, "T") -> ('Value') = "$1";
> }
> }
>
> # remove the line starting "OS Hot Fix..."
> shift(@examine);
> # create a variable for holding the hotfixes
> my $hotfixes="\"";
>
> while (!($examine[0] =~ m/Applications.*/i)){
> # while hot fixes need to be added. skip blank lines
> if($examine[0] =~ m/\w/){
> chomp($examine[0]);
> $hotfixes .="$examine[0]\n";
> }
> }
>
> # add the hotfixes
> $ws -> Cells($row, "U") -> ('Value') = "$hotfixes";
>
> # remove the line starting "Applications..."
> shift(@examine);
>
> my $apps='';
> foreach my $app (@examine){
> # the rest should all be applications to add
> # so we're using a foreach to put them in
> # only add lines that are not blank
> # there are 3 special lines: NAV, Alarms, and SNMP
> if($app =~ m/Symantec AntiVirus (\w+)/i){
> $ws -> Cells($row, "B") -> ('Value') = "ver: $1";
> }elsif($app =~ m/Alarm/i){
> $ws -> Cells($row, "C") -> ('Value') = "Installed";
> }elsif($app =~ m/SNMP Informant Agent \(([^\)]+)\) (\w.*)/i){
> $ws -> Cells($row, "D") -> ('Value') = "$1 : $2";
> }elsif($app =~ m/\s+(\w.*)/i){
> $apps .= "$1\n";
> }
> }
>
> # add the other applications
> $ws -> Cells($row, "V") -> ('Value') = "$apps";
> }
> }
>
> # end the loop increasing the row number
> $row++;
> }
> }else{
> # we have to create it, including make the first row
> $Excel -> {'Visible'} = 1;
> $Excel -> { 'SheetsInNewWorkBook' } = 1;
> my $workbook = $Excel -> Workbooks -> Add();
> my $ws = $workbook -> Worksheets(1);
> $ws -> { 'Name' } = "Lab Report $date";
>
> # set first row titles
> $ws -> Cells(1, "A") -> ('Value') = "Node";
> $ws -> Cells(1, "B") -> ('Value') = "NAV";
> $ws -> Cells(1, "C") -> ('Value') = "Alarms";
> $ws -> Cells(1, "D") -> ('Value') = "SNMP";
> $ws -> Cells(1, "E") -> ('Value') = "Uptime";
> $ws -> Cells(1, "F") -> ('Value') = "Kernel Version";
> $ws -> Cells(1, "G") -> ('Value') = "Product Type";
> $ws -> Cells(1, "H") -> ('Value') = "Product Version";
> $ws -> Cells(1, "I") -> ('Value') = "Service Pack";
> $ws -> Cells(1, "J") -> ('Value') = "Kernel Build Number";
> $ws -> Cells(1, "K") -> ('Value') = "Registered Organization";
> $ws -> Cells(1, "L") -> ('Value') = "Registered Owner";
> $ws -> Cells(1, "M") -> ('Value') = "Install Date";
> $ws -> Cells(1, "N") -> ('Value') = "Activation Status";
> $ws -> Cells(1, "O") -> ('Value') = "IE Version";
> $ws -> Cells(1, "P") -> ('Value') = "System Root";
> $ws -> Cells(1, "Q") -> ('Value') = "Processors";
> $ws -> Cells(1, "R") -> ('Value') = "Processor Speed";
> $ws -> Cells(1, "S") -> ('Value') = "Processor Type";
> $ws -> Cells(1, "T") -> ('Value') = "Physical Memory";
> $ws -> Cells(1, "U") -> ('Value') = "Installed OS Hotfixes";
> $ws -> Cells(1, "V") -> ('Value') = "Other Applications";
>
> my $ldate = localtime();
> &rep("Examining information gathered from lab computers @ $ldate\n",
> $verb);
>
> # start row counter
> my $row=2;
>
> foreach my $node (@comps){
> # for each node we check the information returned
> # if there is no information we only have two items to place on the
> row,
> # otherwise we have A-V items
>
> # set the first cell since that's always going to be the same
> $ws -> Cells($row, "A") -> ('Value') = "$node";
>
> # try to open the file with the information
> open LNINFO, "<$comp-$date.txt" or $err=1;
>
> if($err){
> # there was an error on the file, so note that in the report
> $ws -> Cells($row, "B") -> ('Value') =
> "$comp-$date.txt could not be opened to be put into the lab
> report. $! $^E\n";
>
> }else{
> # there was no error, so now we check the file,
> # first reading in the file
> my @examine;
> while(<LNINFO>){
> push @examine, $_;
> }
>
> # then noting which couldnt be connected to
> if($examine[6] =~ m/The network path was not found./i ){
> $ws -> Cells($row, "B") -> ('Value') = "The network path was not
> found.";
>
> }else{
> # we need to run through the file for the information to put
into
> the rows
>
> # drop what's before what we need
> while (!($examine[0] =~ m/Uptime.*/i)){ shift(@examine); }
>
> while (!($examine[0] =~ m/OS Hot Fix.*/i)){
> # now go through a series of if/elsif sections for the rest
> # hot fixes and programs will be handled slightly differently
> though
>
> if($examine[0] =~ m/Uptime:\s+(\w.*)/i){
> $ws -> Cells($row, "E") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Kernel version:\s+(\w.*)/i){
> $ws -> Cells($row, "F") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Product type:\s+(\w.*)/i){
> $ws -> Cells($row, "G") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Product version:\s+(\w.*)/i){
> $ws -> Cells($row, "H") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Service pack:\s+(\w.*)/i){
> $ws -> Cells($row, "I") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Kernel build number:\s+(\w.*)/i){
> $ws -> Cells($row, "J") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Registered organization:\s+(\w.*)/i){
> $ws -> Cells($row, "K") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Registered owner:\s+(\w.*)/i){
> $ws -> Cells($row, "L") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Install date:\s+(\w.*)/i){
> $ws -> Cells($row, "M") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Activation status:\s+(\w.*)/i){
> $ws -> Cells($row, "N") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/IE version:\s+(\w.*)/i){
> $ws -> Cells($row, "O") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/System root:\s+(\w.*)/i){
> $ws -> Cells($row, "P") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Processors:\s+(\w.*)/i){
> $ws -> Cells($row, "Q") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Processor speed:\s+(\w.*)/i){
> $ws -> Cells($row, "R") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Processor type:\s+(\w.*)/i){
> $ws -> Cells($row, "S") -> ('Value') = "$1";
> }elsif($examine[0] =~ m/Physical memory:\s+(\w.*)/i){
> $ws -> Cells($row, "T") -> ('Value') = "$1";
> }
> }
>
> # remove the line starting "OS Hot Fix..."
> shift(@examine);
> # create a variable for holding the hotfixes
> my $hotfixes="\"";
>
> while (!($examine[0] =~ m/Applications.*/i)){
> # while hot fixes need to be added. skip blank lines
> if($examine[0] =~ m/\w/){
> chomp($examine[0]);
> $hotfixes .="$examine[0]\n";
> }
> }
>
> # add the hotfixes
> $ws -> Cells($row, "U") -> ('Value') = "$hotfixes";
>
> # remove the line starting "Applications..."
> shift(@examine);
>
> my $apps='';
> foreach my $app (@examine){
> # the rest should all be applications to add
> # so we're using a foreach to put them in
> # only add lines that are not blank
> # there are 3 special lines: NAV, Alarms, and SNMP
> if($app =~ m/Symantec AntiVirus (\w+)/i){
> $ws -> Cells($row, "B") -> ('Value') = "ver: $1";
> }elsif($app =~ m/Alarm/i){
> $ws -> Cells($row, "C") -> ('Value') = "Installed";
> }elsif($app =~ m/SNMP Informant Agent \(([^\)]+)\) (\w.*)/i){
> $ws -> Cells($row, "D") -> ('Value') = "$1 : $2";
> }elsif($app =~ m/\s+(\w.*)/i){
> $apps .= "$1\n";
> }
> }
>
> # add the other applications
> $ws -> Cells($row, "V") -> ('Value') = "$apps";
> }
> }
>
> # end the loop increasing the row number
> $row++;
> }
>
> $workbook -> SaveAs($labrep); # save active sheet
> }
>
> # save and exit
> $Excel -> Workbooks -> Save(); # save file
> $Excel -> Workbooks -> Quit(); # leave excel
> my $et=locatime();
> &rep("program completed at $et.",$verb); # wrap up log
> close LOG; # close log
>
################################################################################
> __END__
> # # line used to help keep lines under 80 characters
>
The errors I get are:
E:\lab reports\test18>rem information section is over, turning echo off.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Not a subroutine reference at compinfo.pl line 318.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Can't spawn "cmd.exe": Bad file descriptor at compinfo.pl line 157.
Win32::OLE(0.1702) error 0x8001010a: "The message filter indicated that
the
application is busy"
in PROPERTYPUT "Visible" at compinfo.pl line 311
E:\lab reports\test18>
which are lines
311 $Excel -> {'Visible'} = 1;
318 $ws -> Cells(1, "A") -> ('Value') = "Node";
and are causing he program to die at those spots.
-----------------------------------------
PLEASE NOTE:
SeaChange International headquarters in Maynard, MA is moving!
Effective March 1, 2006, our new headquarters address will be:
SeaChange International
50 Nagog Park
Acton, MA 01720 USA
All telephone numbers remain the same:
Main Corporate Telephone: 978-897-0100
Customer Service Telephone: 978-897-7300
_______________________________________________
ActivePerl mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
