By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,053 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,053 IT Pros & Developers. It's quick & easy.

How to append an excel sheet using perl

P: 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.


Expand|Select|Wrap|Line Numbers
  1. use Spreadsheet::WriteExcel;
  2. use Spreadsheet::ParseExcel;
  3.  
  4.    $datetime=localtime();
  5.  
  6.  # $count variable is used to track the no of in the excel
  7.     $count=0;
  8. #    $loc=$ENV{"TEMP"};
  9.     $loc="D:\\SpaceUsage";
  10.     $vob="";
  11.     $vobsize=0;
  12.     $totalvobsize+=0;
  13.     $vd="";
  14.     $sum=0;
  15.  
  16. #    print " $loc -- ";    
  17.  # Create a new Excel workbook
  18.     my $workbook = Spreadsheet::WriteExcel->new("$loc\\spacesize.xls");
  19.  
  20.     # Add a worksheet
  21.     $worksheet = $workbook->add_worksheet();
  22.  
  23.     #  Add and define a format
  24.     $format = $workbook->add_format(); # Add a format
  25.     $format->set_bold();
  26.     $format->set_color('blue');
  27.     $format->set_align('center');
  28.  
  29. #    $worksheet->write($count,-2, "VOB Space Usage on BLRKCF9A",$format);
  30.     $worksheet->write($count,0,"Updated on $datetime",$format);
  31.     $worksheet->write($count,1,"VOB Name",$format);
  32.     $worksheet->write($count,2,"db",$format);
  33.     $worksheet->write($count,3,"cdft",$format);
  34.     $worksheet->write($count,4,"ddft",$format);
  35.     $worksheet->write($count,5,"sdft",$format);
  36.     $worksheet->write($count,6,"Total Size in (MB)",$format);
  37.     $worksheet->write($count,7,"Total VOB Size in (GB)",$format);
  38.  
  39.  
  40.  
  41.     @vobs =("\\AXIS");
  42.  
  43.     foreach $vob (@vobs)
  44.     {
  45.  
  46.             $vobname=$vob;
  47.             chomp($vobname);
  48. #            chop($vobname);
  49.             print "\n VOB Name :: $vobname \n";
  50.             @vobdiskuse = `cleartool space -vob -update $vob`;
  51.             foreach $vd (@vobdiskuse)
  52.             {
  53.  
  54.                 if ($vd =~ /VOB database/)
  55.                 {
  56.                     if ($vd =~ /(\d+\.\d+)\s+/)
  57.                     {
  58.                         $dbsize = $1;
  59.                         print "\n DB Pool:$dbsize Mb\n";
  60.                     }
  61.                 }
  62.                 if ($vd =~ /cleartext/)    
  63.                 {
  64.                     if ($vd =~ /(\d+\.\d+)\s+/)
  65.                     {
  66.                         $csize = $1;
  67.                         print "\n C Pool:$csize Mb\n";
  68.                     }
  69.  
  70.                 }
  71.                 if ($vd =~ /derived object/)    
  72.                 {
  73.                     if ($vd =~ /(\d+\.\d+)\s+/)
  74.                     {
  75.                         $dosize = $1;
  76.                         print "\n DO Pool:$dosize Mb\n";
  77.                     }
  78.                 }
  79.                 if ($vd =~ /source pool/)    
  80.                 {
  81.                     if ($vd =~ /(\d+\.\d+)\s+/)
  82.                     {
  83.                         $srcsize = $1;
  84.                         print "\n SOURCE Pool:$srcsize Mb\n";
  85.                     }
  86.  
  87.                 }
  88.  
  89.  
  90.                       if ($vd=~ /^Total*/)
  91.                       {
  92.                     chomp($vd);
  93.  
  94.  
  95.                     if ($vd=~ /\s(\d+\.\d+)\s/)
  96.                     {
  97.                         $vobsize=$1;
  98.                         chomp($vobsize);
  99.                         print "VOB Size :: $vobsize \n";
  100.  
  101.                             $count++;
  102.                             $worksheet->write($count,1,$vobname);
  103.                             $worksheet->write($count,2,$dbsize);
  104.                         $worksheet->write($count,3,$csize);
  105.                         $worksheet->write($count,4,$dosize);
  106.                         $worksheet->write($count,5,$srcsize);
  107.                         $worksheet->write($count,6,$vobsize);    
  108.  
  109.  
  110.                     }
  111.                     $totalvobsize+=$vobsize;
  112.                     $totalvobsize = ($totalvobsize / 1024);
  113.                     $sum+=$totalvobsize;
  114.                     print "\nTotal Vobsize: $totalvobsize GB \n";
  115.                     $worksheet->write($count,7,$totalvobsize);    
  116.  
  117.                        }
  118.               }
  119.  
  120.     }
  121.                     $worksheet->write($count,7,$sum);
  122.  
Kindly suggest me.

THT,
Raghavendra
Dec 20 '07 #1
Share this Question
Share on Google+
2 Replies


gpraghuram
Expert 100+
P: 1,275
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
Dec 21 '07 #2

P: 23
Hi,
You can use Spreadsheet::ParseExcel::SaveParser module to change the existing excel sheet.

max rows and columns should be checked using
Expand|Select|Wrap|Line Numbers
  1. $worksheet->{MaxRow}
  2. $worksheet->{MaxCol}
  3.  
Regards,
Manimaran.K
Dec 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.