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 41787 Niheel 2,456
Recognized Expert Moderator Top Contributor
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;
-
}
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Luis Ferrao |
last post by:
Hi,
My Windows Forms application uses the WebBrowser control to
open an Excel file since OLE objects are gone in .Net. The application
is actualy a clone of the one found in the MS Knowledge Base Article
"How to Use the WebBrowser Control to Open Office Documents with
Visual C# .NET" which can be found here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;304662
The application does what it's supposed to (ie Loads embeded Excel
|
by: JBAdamsJr |
last post by:
I want to be able to create an Excel file with a VB.NET program on a server that does not have Microsoft Excel loaded on it. I am using the Jet OLE DB to read other data files. Can this be used to save an array in an Excel .XLS format?
|
by: shaurya.rastogi |
last post by:
I need to open an Excel file that has been stored in the Access
Database using the insert Object functionality of MSAccess manually.
What i am aware of is that i cant just read the field containing the
Excel File into a Byte Array and pass it to the Excel object in C#,as
the file is wrapped in the OLE Wrapper used by Access while inserting
the file in database.
I have tried locating the Header of Excel file from the byte array and...
|
by: shaurya |
last post by:
I need to open an Excel file that has been stored in the Access Database using the insert Object functionality of MSAccess manually.
What i am aware of is that i cant just read the field containing the Excel File into a Byte Array and pass it to the Excel object in C#,as the file is wrapped in the OLE Wrapper used by Access while inserting the file in database .
I have tried locating the Header of Excel file from the byte array and read...
|
by: marle |
last post by:
Hi all,
Can anyone briefly explain how to Open an excel file using OLE?
marle..
| |
by: shintu |
last post by:
Hallo,
I am trying to write french accented characters é è ê in Excel worksheet using my perl script , But I am stuck here as I couldnt find a way of writing it !:
My code:
use strict;
use warnings;
|
by: mohanprasadgutta |
last post by:
Hi,
I need help to open a password protected excel file in perl using Win32:OLE.
when I tried to open file in normal way at the time of program execution it is prompting me to enter password.
I am giving the script i used for opening excel file..
So i want to know how to provide password parameter while opening the file.
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant qw(:DEFAULT nothing);
|
by: poolboi |
last post by:
hi all,
i've got the following program that needs yr help:
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) {
|
by: Prasanna CRN |
last post by:
I am a Perl Programmer & i am using WIN32::OLE for Excel operations.
I wanted to use conditional formulas like 'if' in excel via Perl.
Thanks in Advance.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |