#!/usr/bin/perl
######################################################################
+###
# This guy automates the process of entering daily traffic        #
# into an Excel file.                            #
# It expects the statistics to be found in /stats in the below format.
+    #
#                                    #
# for AOL, in file /stats/aol: YYYY-MM-DD-aol 123,456            #
# for MSN, in file /stats/msn: YYYY-MM-DD-msn 123,456            #
# for WWW, in file /stats/www: YYYY-MM-DD-www 123,456            #
#                                    #
# Val Polyakov vpolyakov@katrillion.com August 22, 2002            #
######################################################################
+###

use Spreadsheet::ParseExcel::SaveParser;


my $weekday = (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturda
+y)[(localtime)[6]-1] ;
my $dayofmonth = (localtime)[3] - 1;
my $month = (January, February, March, April, May, June, July, August,
+ September, October, November, December)[(localtime)[4]];
my $date = (localtime)[3] - 1; 
my $column = (localtime)[6] + 1;
my $row =  2 + $dayofmonth;
my $excelrow = $row + 1;
my $aolstats;
my $msnstats;
my $wwwstats;
my $aolfile = "/stats/aol";
my $msnfile = "/stats/msn";
my $wwwfile = "/stats/www";

#########################
# Get the stats
#########################
open(AOLFILE, $aolfile) or die "Couldn't open AOL stats: $!\n";
$aolstats = <AOLFILE>;
if ($aolstats =~ /\d{4}-\d{2}-\d{2}-aol\s+(.*)/) {
  $aolstats = $1; 
}
open(MSNFILE, $msnfile) or die "Couldn't open MSN stats: $!\n";
$msnstats = <MSNFILE>;
if ($msnstats =~ /\d{4}-\d{2}-\d{2}-msn\s+(.*)/) {
 $msnstats = $1;
}
open(WWWFILE, $wwwfile) or die "Couldn't open WWW stats: $!\n";
$wwwstats = <WWWFILE>;
if ($wwwstats =~ /\d{4}-\d{2}-\d{2}-www\s+(.*)/) {
 $wwwstats = $1;
}

################################
# Get rid of the comma in stats
################################
if ($aolstats =~ /(\d+),(\d+)/) {
 $aolstats =  $1 . $2;
}
if ($msnstats =~ /(\d+),(\d+)/) {
 $msnstats = $1 . $2;
}
if ($wwwstats =~ /(\d+),(\d+)/) {
  $wwwstats = $1 . $2;
}

#######################
# open the excel file
#######################

my $Excel = new Spreadsheet::ParseExcel::SaveParser;
my $workbook = $Excel->Parse("test.xls");
#########################################
# Workbook name         Workbook number #
#########################################
# Daily Traffic 2nd Quarter   0        #
# Daily Traffic 3rd Quarter   1        #
# Daily Traffic 4th Quarter   2        #
# 1st Quarter 2002          3        #
# 2nd Quarter 2002          4        #
# 3rd Quarter 2002          5        #
# 4th Quarter 2002          6        #
#########################################

if ($month =~ /January|February|March/) {
  if ($month =~ /January/) { $row = 2 + $dayofmonth; }
  if ($month =~ /February/) { $row = 35 + $dayofmonth; }
  if ($month =~ /March/) { $row = 66 + $dayofmonth; }
  $workbook->AddCell(3, $row, 0, "$month $date");
  $workbook->AddCell(3, $row, 1, $weekday);
  $workbook->AddCell(3, $row, 2, $aolstats);
  $workbook->AddCell(3, $row, 3, $msnstats);
  $workbook->AddCell(3, $row, 4, $wwwstats);
  $workbook->AddCell(3, $row, 6, "=SUM(C$excelrow:F$excelrow)"   );
  $Excel->SaveAs($workbook, 'test.xls');
}
elsif ($month =~ /April|May|June/) {
 if ($month =~ /April/) { $row = 2 + $dayofmonth; }
 if ($month =~ /May/) { $row = 35 + $dayofmonth; }
 if ($month =~ /June/) { $row = 67 + $dayofmonth; }
 $workbook->AddCell(4, $row, 0, "$month $date");
 $workbook->AddCell(4, $row, 1, $weekday);
 $workbook->AddCell(4, $row, 2, $aolstats);
 $workbook->AddCell(4, $row, 3, $msnstats);
 $workbook->AddCell(4, $row, 4, $wwwstats);
 $workbook->AddCell(4, $row, 6, "=SUM(C$excelrow:F$excelrow)"   );
 $Excel->SaveAs($workbook, 'test.xls');
}
elsif ($month =~ /July|August|September/) {
if ($month =~ /July/) { $row = 2 + $dayofmonth; }
if ($month =~ /August/) { $row = 35 + $dayofmonth; }
if ($month =~ /September/) { $row = 69 + $dayofmonth; } 
 $workbook->AddCell(5, $row, 0, "$month $date");
 $workbook->AddCell(5, $row, 1, $weekday);
 $workbook->AddCell(5, $row, 2, $aolstats);
 $workbook->AddCell(5, $row, 3, $msnstats);
 $workbook->AddCell(5, $row, 4, $wwwstats);
 $workbook->AddCell(5, $row, 6, "=SUM(C$excelrow:F$excelrow)"   );
 $Excel->SaveAs($workbook, 'test.xls');
}
elsif ($month =~ /October|November|December/) {
 if ($month =~ /October/) { $row = 2 + $dayofmonth; }
 if ($month =~ /November/) { $row = 35 + $dayofmonth; }
 if ($month =~ /December/) { $row = 67 + $dayofmonth; }
 $workbook->AddCell(6, $row, 0, "$month $date");
 $workbook->AddCell(6, $row, 1, $weekday);
 $workbook->AddCell(6, $row, 2, $aolstats);
 $workbook->AddCell(6, $row, 3, $msnstats);
 $workbook->AddCell(6, $row, 4, $wwwstats);
 $workbook->AddCell(6, $row, 6, "=SUM(C$excelrow:F$excelrow)"   );
 $Excel->SaveAs($workbook, 'test.xls');
}

[download]

In reply to Auto-dailies by vxp

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.