Open and create new Excel file with Perl and win32::OLE

[sandycat05]

Hello all, I am a new Perl programmer. Below is the beginnings of a code that I am using to manipulate an Excel spreadsheet via Perl using win32::OLE. However, what I'd like to do is the following: instead of either opening a file or creating a new one, I'd like to do BOTH. I was thinking of creating a loop where I could basically say something like:

if $excelfile exists, then open $excelfile, otherwise, create a new workbook named $excelfile

However, being new to Perl, I haven't quite been able to find the correct syntax. Can you provide any clues as to how to do this, or is it even possible?

Thanks! ;)

 ------------------------------------------------------------ 
#!/usr/bin/perl -w
 
use strict;
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'); 
 
my $excel_file = 'C:/Perl/myfolder/testbook';
 
# open Excel file
my $Book = $Excel->Workbooks->Open("$excel_file"); 
------------------------------------------------------------

[Niheel]

Hey,

You are almost there, all you need is the code for the if file exists / else:

#!/usr/bin/perl -w
 
use strict;
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'); 
 
my $excel_file = 'C:/Perl/myfolder/testbook';
 
// -e checks to see if the file exists
if (-e "$excel_file")
{ 
# open Excel file
my $Book = $Excel->Workbooks->Open("$excel_file"); 
# run any other excel code you want here
# ...
}else{
// paste code here to create a new file 
$excel->{Visible} = 1;
$excel->{SheetsInNewWorkBook} = 1;
$workbook = $excel->Workbooks->Add();
$worksheet = $workbook->Worksheets(1);
$worksheet->{Name} = "New WorkSheet";
 
# run any other excel code you want here
# ...
 
$workbook->SaveAs($excel_file);
}

[m3rajk]

thank you Kub

I had the same question. this was about link #40 hat i opened looking for help and the first one to go beyond giving mee how to open an excel file.

I have two more questions though:

1: how do i find the last row number with information so that I may jump to there?

2: how do i get this to actually fill in and save? when i run my script now it has 2 books with blank sheets.

my wrapper file (batch), with computer names removed for safety/brevity:

@echo on
rem this batch is for cataloguing computers using two other "helpers"
rem "helpers" are psinfo and compinfo
rem information section is over, turning echo off.
@echo off

compinfo -l l1.txt -c [a bunch of computer names] >> errlog.txt 
compinfo -l l2.txt -c [a bunch of computer names] >> errlog.txt

excerpt from my perl file that has the interesting parts of excel interaction

#! /usr/bin/perl
use strict;
use warnings;
use Getopt::Long; # used in getting unix-style options
use Win32::OLE::Const 'Microsoft Excel'; # use OLE/Excel
$Win32::OLE::Warn = 3; # die on errors...

# some stuff that's probably not interesting nor relevant

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"; }
}

# stuff creating files of information used to create the excel spreadsheet

#######
## 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! needs to jump to end!!!!!

###
#some stuff like below only without the first row and should be after what's there
###

}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

the script "finishes" but i never get the completed line in the log file, nor does the excel have any information, nor is there anything in the errlog.txt file.

the reg log only has the print out i would expect showing one how far the script is.

[poolboi]

hi,
just came across this thread thought u might help me out
i tried to do it yr way..
but when i created a file already
it should input a hi at the position i stated
but it didn't...
is there anything wrong with my code?

[CODE=perl]
use Win32::OLE;

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObjec t('Excel.Applic ation')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.App lication', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}

if (-e "C:\\Docume nts and Settings\\clong \\Desktop\\perl \\$save_file_na me.xls")

{$book = $ex->Workbooks->Open("C:\\Docu ments and Settings\\clong \\Desktop\\perl \\$save_file_na me.xls");
$sheet->Cells(3,1)->{Value} = ['hi'];

}else{

# get a new workbook
$book = $ex->Workbooks->Add;
$sheet = $book->Worksheets(1 );

# write a 2 rows by 3 columns range

$sheet->Range("A1:J2 ")->{Value} = [['Date','Total (IN)','Succ (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ (OUT)','MO(IN)' ,'MO(OUT)'],
[$date, $total_in, $succ_in,$pk_in ,$pk_out,"$pk_h our - $pk_hour_dur hr",$total_out, $succ_out ]];


$sheet->Range("K1:L2 ")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'],
[$max_pk_msg,$pk _msg]];


foreach(@parame ters)
{
$sheet->Cells(2,9)->{Value} = [$parameter_in_a rray{$_}];
$sheet->Cells(2,10)->{Value} = [$parameter_out_ array{$_}];
}


# print "XyzzyPerl"
$array = $sheet->Range("A3:I1 ")->{Value};
for (@$array) {
for (@$_) {
print defined($_) ? "$_|" : "<undef>|";
}
print "\n";
}

# save and exit

$book->SaveAs ("C:\\Docume nts and Settings\\clong \\Desktop\\perl \\$save_file_na me.xls") ;
undef $book;
undef $ex;
}
[/CODE]