How to append an excel sheet using perl | Newbie | | Join Date: Sep 2006
Posts: 9
| |
Hi,
I am writing a script which should update an excel sheet daily. So how do I determine which is the last row which contains data and then write the latest data into the sheet. -
use Spreadsheet::WriteExcel;
-
use Spreadsheet::ParseExcel;
-
-
$datetime=localtime();
-
-
# $count variable is used to track the no of in the excel
-
$count=0;
-
# $loc=$ENV{"TEMP"};
-
$loc="D:\\SpaceUsage";
-
$vob="";
-
$vobsize=0;
-
$totalvobsize+=0;
-
$vd="";
-
$sum=0;
-
-
# print " $loc -- ";
-
# Create a new Excel workbook
-
my $workbook = Spreadsheet::WriteExcel->new("$loc\\spacesize.xls");
-
-
# Add a worksheet
-
$worksheet = $workbook->add_worksheet();
-
-
# Add and define a format
-
$format = $workbook->add_format(); # Add a format
-
$format->set_bold();
-
$format->set_color('blue');
-
$format->set_align('center');
-
-
# $worksheet->write($count,-2, "VOB Space Usage on BLRKCF9A",$format);
-
$worksheet->write($count,0,"Updated on $datetime",$format);
-
$worksheet->write($count,1,"VOB Name",$format);
-
$worksheet->write($count,2,"db",$format);
-
$worksheet->write($count,3,"cdft",$format);
-
$worksheet->write($count,4,"ddft",$format);
-
$worksheet->write($count,5,"sdft",$format);
-
$worksheet->write($count,6,"Total Size in (MB)",$format);
-
$worksheet->write($count,7,"Total VOB Size in (GB)",$format);
-
-
-
-
@vobs =("\\AXIS");
-
-
foreach $vob (@vobs)
-
{
-
-
$vobname=$vob;
-
chomp($vobname);
-
# chop($vobname);
-
print "\n VOB Name :: $vobname \n";
-
@vobdiskuse = `cleartool space -vob -update $vob`;
-
foreach $vd (@vobdiskuse)
-
{
-
-
if ($vd =~ /VOB database/)
-
{
-
if ($vd =~ /(\d+\.\d+)\s+/)
-
{
-
$dbsize = $1;
-
print "\n DB Pool:$dbsize Mb\n";
-
}
-
}
-
if ($vd =~ /cleartext/)
-
{
-
if ($vd =~ /(\d+\.\d+)\s+/)
-
{
-
$csize = $1;
-
print "\n C Pool:$csize Mb\n";
-
}
-
-
}
-
if ($vd =~ /derived object/)
-
{
-
if ($vd =~ /(\d+\.\d+)\s+/)
-
{
-
$dosize = $1;
-
print "\n DO Pool:$dosize Mb\n";
-
}
-
}
-
if ($vd =~ /source pool/)
-
{
-
if ($vd =~ /(\d+\.\d+)\s+/)
-
{
-
$srcsize = $1;
-
print "\n SOURCE Pool:$srcsize Mb\n";
-
}
-
-
}
-
-
-
if ($vd=~ /^Total*/)
-
{
-
chomp($vd);
-
-
-
if ($vd=~ /\s(\d+\.\d+)\s/)
-
{
-
$vobsize=$1;
-
chomp($vobsize);
-
print "VOB Size :: $vobsize \n";
-
-
$count++;
-
$worksheet->write($count,1,$vobname);
-
$worksheet->write($count,2,$dbsize);
-
$worksheet->write($count,3,$csize);
-
$worksheet->write($count,4,$dosize);
-
$worksheet->write($count,5,$srcsize);
-
$worksheet->write($count,6,$vobsize);
-
-
-
}
-
$totalvobsize+=$vobsize;
-
$totalvobsize = ($totalvobsize / 1024);
-
$sum+=$totalvobsize;
-
print "\nTotal Vobsize: $totalvobsize GB \n";
-
$worksheet->write($count,7,$totalvobsize);
-
-
}
-
}
-
-
}
-
$worksheet->write($count,7,$sum);
-
Kindly suggest me.
THT,
Raghavendra
|  | Expert | | Join Date: Mar 2007 Location: Chennai
Posts: 1,256
| | | re: How to append an excel sheet using perl
I havent used this utility.
But my idea is if you want to write to the previously written excel file then you have to open the file in append mode.
Check whether this utility provides you that option.
Raghuram
| | Newbie | | Join Date: Jul 2007 Location: chennai
Posts: 22
| | | re: How to append an excel sheet using perl
Hi,
You can use Spreadsheet::ParseExcel::SaveParser module to change the existing excel sheet.
max rows and columns should be checked using -
$worksheet->{MaxRow}
-
$worksheet->{MaxCol}
-
Regards,
Manimaran.K
|
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,295 network members.
|
