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");
-
------------------------------------------------------------
-
3 41358
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);
-
}
-
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.
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? -
use Win32::OLE;
-
-
# use existing instance if Excel is already running
-
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
-
die "Excel not installed" if $@;
-
unless (defined $ex) {
-
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
-
or die "Oops, cannot start Excel";
-
}
-
-
if (-e "C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls")
-
-
{$book = $ex->Workbooks->Open("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.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_hour - $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(@parameters)
-
{
-
$sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];
-
$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:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls") ;
-
undef $book;
-
undef $ex;
-
}
-
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
1 post
views
Thread by Luis Ferrao |
last post: by
|
2 posts
views
Thread by JBAdamsJr |
last post: by
|
reply
views
Thread by shaurya.rastogi |
last post: by
| | | | | | | | | | | | | | | | |