How to append an excel sheet using perl

[RAGHAVENDRAS]

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

[gpraghuram]

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

[manimarank]

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