vxp has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to automate my company's statistic gatherings. I am using Spreadsheet::Excel for this purpose. my code (i just started this a few mins ago) looks like this: 
#!/usr/bin/perl
use Spreadsheet::WriteExcel;

my $day = (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)[(
+localtime)[6]];
my $month = (January, February, March, April, May, June, July, August,
+ September, October, November, December)[(localtime)[4]];
my $date = (localtime)[3];

my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->addworksheet('3rd Quarter 2002');
my $format = $workbook->addformat();
$format->set_bold();

$worksheet->write(2, 0, 'Date', $format);
$worksheet->write(2, 1, 'Day', $format);
$worksheet->write(2, 2, 'AOL', $format);
$worksheet->write(2, 3, 'MSN', $format);
$worksheet->write(2, 4, 'Katrillion', $format);
$worksheet->write(2, 5, 'CS', $format);
$worksheet->write(2, 6, 'Daily Total', $format);
$worksheet->write(2, 7, 'AOL Weekly', $format);
$worksheet->write(2, 8, 'Mo. Run Total', $format);
$worksheet->write(2, 9, '% change', $format);

$worksheet->write(3, 0, "$month $date");
$worksheet->write(3, 1, $day);

[download]
my problem is those last 2 lines right there. i cant hardcode those values, this script is supposed to write in a different cell each day.. 3, 0 and 3, 1 are today, for example. 4, 0 and 4, 1 are tomorrow. etc help, please.

Replies are listed 'Best First'.
Re: Need help with a loop (i think)
by reyjrar (Hermit) on Aug 20, 2002 at 15:11 UTC
    If this is going to be run as a weekly sheet, if the number resets to 2,0 every monday, then you can do something simple like this: 
    my $cell = (localtime)[6] + 1;

$worksheet->write($cell, 0, "$month $date");
$worksheet->write($cell, 0, $day);

    [download]
    If its monthly, you can use the month day return value from localtime() to play with. You could also use a temp file to store the month/day that the file was first created and index everything from that, creating a new file when the month value changes and reseting the index to 0.

    just a few ideas..


    -brad..
[reply]
[d/l]
      its a daily script. each day it'll write to a new cell, with current day's data
[reply]
        Here is some code to show what you could do, I have split it out into a lot of lines so it will be easier for you to read...
        open (CELLNUM, "/usr/local/share/dailystats/.cellnum") or die "Cant op
+en Cell num file: $!\n";
open (CELLNUM2, ">/usr/local/share/dailystats/.cellnum") or die "Cant 
+open Cell num file: $!\n";
$cellnum = <CELLNUM>;
$cellnum++
print CELNUM2 "$cellnum";
close CELNUM;
close CELNUM2;

        [download]
        Then use $celnum in place of "3" in your orig script.. You will want to refine this approach but you get the idea.

        -Waswas
[reply]
[d/l]
        You do realize that Spreadsheet::WriteExcel creates a new spreadsheet. It cannot append data to an already existing spreadsheet. You have to add that functionality yourself.
[reply]
        Is there some limit to the number or is the cell num going to go on forever ? Even if this is just a personal project that's not going to really break anything if it breaks, you might want to use it as an opportunity to think about "what if its still runnning a year from now? Are the 365 cells overkill?" That kinda stuff and try to come up with a nifty idea to manage that amount of data.

        -brad..
[reply]
Re: Need help with a loop (i think)
by splitOnce (Acolyte) on Aug 20, 2002 at 15:38 UTC
    since it is a daily script , I advice you of using Date::Manip it is powerfull enough to deal with date and kron scripts .
[reply]
Re: Need help with a loop (i think)
by vxp (Pilgrim) on Aug 20, 2002 at 16:25 UTC
    i figured it out. i did my $dayofmonth = (localtime)3; my $row = $dayofmonth; so august 20th would be in 21st row. august 21st would be in 22nd row. as of now script looks like this: 
    #!/usr/bin/perl
use Spreadsheet::WriteExcel;

my $weekday = (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturda
+y)[(localtime)[6]];
my $dayofmonth = (localtime)[3];
my $month = (January, February, March, April, May, June, July, August,
+ September, October, November, December)[(localtime)[4]];
my $date = (localtime)[3];

my $column = (localtime)[6] + 1;
my $row = $dayofmonth;
print $tmpday;

my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->addworksheet('3rd Quarter 2002');
my $format = $workbook->addformat();
$format->set_bold();

$worksheet->write(0, 0, 'Date', $format);
$worksheet->write(0, 1, 'Day', $format);
$worksheet->write(0, 2, 'AOL', $format);
$worksheet->write(0, 3, 'MSN', $format);
$worksheet->write(0, 4, 'Katrillion', $format);
$worksheet->write(0, 5, 'CS', $format);
$worksheet->write(0, 6, 'Daily Total', $format);
$worksheet->write(0, 7, 'AOL Weekly', $format);
$worksheet->write(0, 8, 'Mo. Run Total', $format);
$worksheet->write(0, 9, '% change', $format);

$worksheet->write($row, 0, "$month $date");
$worksheet->write($row, 1, $weekday);

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom