|
Newbie
Join Date: Jun 2005
Posts: 22
Rep Power: 0 
|
entering data into excel from a form
I have a webpage with a form for entering data. When the user submits the data it calls my perl program. The program takes all of the data and puts it in the next empty row in an excel spreadsheet on the webserver. It works fine and I can get the data I need. But for some reason, if the spreadsheet is empty the first row to be filled is row 415. I was hoping someone could clue me into the reason why this might be happening? Thanks in advance.
There is some code in the program which is useless because I grabbed a similar program that was being used in the past and adapted it to fit my needs but never took the garbage code out in the event that I might need it at some point. Here is the code:
$mailprog = '/usr/lib/sendmail';
$sender = 'customerservice@baseballamerica.com';
$recipient = 'customerservice@baseballamerica.com';
$title = 'Thank You For Submitting Your Request';
$return_html_main_heading ='Thank You For Submitting Your Request';
$return_link = 'http://www.baseballamerica.com';
$return_link_title = 'Back to the Baseball America Home Page';
@required = ("address");
# Done
#############################################################################
# Retrieve Date
&get_date;
# Parse Form Contents
&parse_form;
# Check Required Fields
&check_required;
# Send E-Mail
&send_mail;
# Return HTML Page
&return_html;
sub get_date {
$refdate = localtime
}
sub parse_form {
if ($ENV{'REQUEST_METHOD'} eq 'GET') {
# Split the name-value pairs
@pairs = split(/&/, $ENV{'QUERY_STRING'});
}
elsif ($ENV{'REQUEST_METHOD'} eq 'POST') {
# Get the input
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
# Split the name-value pairs
@pairs = split(/&/, $buffer);
}
else {
&error('request_method');
}
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ s/<!--(.|\n)*-->//g;
if ($name eq 'email') {
$CONFIG{$name} = $value;
}
elsif ($name eq 'env_report') {
@env_report = split(/,/,$value);
}
else {
if ($FORM{$name} && ($value)) {
$FORM{$name} = "$FORM{$name}, $value";
}
elsif ($value) {
$FORM{$name} = $value;
}
}
}
}
sub check_required {
foreach $require (@required) {
if ($require eq 'email') {
if (!($CONFIG{$require}) || $CONFIG{$require} eq ' ') {
push(@ERROR,$require);
}
}
elsif (!($FORM{$require}) || $FORM{$require} eq ' ') {
push(@ERROR,$require);
}
}
if (@ERROR) {
&error('missing_fields', @ERROR);
}
}
sub return_html {
print "Location: http://www.baseballamerica.com/today/mvp06ncaabaseball/thanks.html\n\n";
}
sub send_mail {
# Open The Mail Program
# open(MAIL,"|$mailprog -f $sender -t");
# ^^^^^^^^ DISABLED 2/8/2005 by Mike Warwick at Lee Folger's request
# Open the excel tab delimited file and write variables.
# Added Wed. August 2, 2000. This assumes information
# to be mailed is appropriate for the excel file. No
# additional checking has been performed.
# Section Author: Jay Smith
# Section requirements:
# File must have web user write permissions. May not have read.
if ( -e "/home/httpd/htdocs/cgi-bin/mvp06ncaabaseball.xls") {
open(EXCEL,">>/home/httpd/htdocs/cgi-bin/mvp06ncaabaseball.xls");
} else {
open(EXCEL,">/home/httpd/htdocs/cgi-bin/mvp06ncaabaseball.xls");
}
@t = localtime();
$date = substr("0".($t[4]+1),-2) ."/". substr("0$t[3]",-2) ."/". (1900+$t[5]);
$time = substr("0$t[2]",-2) .":". substr("0$t[1]",-2) .":". substr("0$t[0]",-2);
$datetime = "$date $time";
print EXCEL qq~$datetime\t~;
print EXCEL qq~$FORM{'firstname'}\t$FORM{'lastname'}\t$FORM{'em'}\t~;
print EXCEL qq~$FORM{'address'}\t$FORM{'address1'}\t~;
print EXCEL qq~$FORM{'address2'}\t$FORM{'city'}\t~;
print EXCEL qq~$FORM{'state'}\t$FORM{'zip'}\t~;
print EXCEL qq~$FORM{'phone'}\t~;
print EXCEL qq~\n~;
# Notice --- I have not included the environment report
# here. I'm assuming it is not required.
close (EXCEL);
}
sub error {
($error,@error_fields) = @_;
print "Content-type: text/html\n\n";
if ($error eq 'bad_referer') {
print "<html>\n <head>\n <title>Bad Referrer - Access Denied</title>\n </head>\n";
print " <body>\n <center>\n <h1>Bad Referrer - Access Denied</h1>\n </center>\n";
print "The form that is trying to use this Program</a>\n";
print "resides at: $ENV{'HTTP_REFERER'}, which is not allowed to access this cgi script.<p>\n";
print "Sorry!\n";
print "</body></html>\n";
}
elsif ($error eq 'request_method') {
print "<html>\n <head>\n <title>Error: Request Method</title>\n </head>\n";
print "</head>\n <body>\n <center>\n\n";
print " <h1>Error: Request Method</h1>\n </center>\n\n";
print "The Request Method of the Form you submitted did not match\n";
print "either GET or POST. Please check the form, and make sure the\n";
print "method= statement is in upper case and matches GET or POST.\n";
print "<p><hr size=7 width=75%><p>\n";
print "<ul>\n";
print "<li><a href=\"$ENV{'HTTP_REFERER'}\">Back to the Submission Form</a>\n";
print "</ul>\n";
print "</body></html>\n";
}
elsif ($error eq 'missing_fields') {
print "<html>\n <head>\n <title>Error: Blank Fields</title>\n </head>\n";
print " </head>\n <body BGCOLOR=\"White\">";
print "<IMG SRC=\"/images/balogo.gif\" WIDTH=569 HEIGHT=63 BORDER=0><P>\n";
print "<FONT FACE=\"Arial\">You must include your address.<p>\n";
# Provide Explanation for Error and Offer Link Back to Form.
print "Please return to the <a href=\"$ENV{'HTTP_REFERER'}\">Fill Out Form</a> and try again.\n";
print "</font></body></html>\n";
}
exit;
} |