After a couple hours of banging my head on my monitor and
reading everyones suggestions, I figured it out. here is the code I hacked out.
Thanks to all who posted their Ideas.
$rnd = int(rand 10000);
sub data_into_form ($_)
{
$ntuples = $query->rows;
$nfields = $query->{NUM_OF_FIELDS};
###############
#original table
###################
@data_in_form;
@data_in_form = "<table
width=$tablewidth
bgcolor=$editcolor
border=$table_border
cellspacing=$cell_spacing
cellpadding=$cell_padding>\n\t<TR>\n";
#push(@data_in_form, "<TD> </TD>\n") if ($quick_edit);
push(@data_in_form,"<A href=\"http://oracle/$rnd.xls\">View Result
+s In Excel</a>");
for($fc=1;$fc !=$nfields; $fc++)
{
$fname = $query->{NAME}->[$fc];
push(@data_in_form,"\t\t<TD><CENTER><FONT SIZE=$font><B>
<INPUT TYPE=SUBMIT NAME=\"order_by\" VALUE=\"$fname\"></B>
</FONT></CENTER></TD>\n");
}
push(@data_in_form,"\t</TR>\n");
while (@nrow = $query->fetchrow) {
#push(@data_in_form,"\t<TR>\n");
$first = 0;
foreach $tuple (@nrow) {
if ($first == 0) {
#push(@data_in_form,"\t<TD><CENTER>
#<A href=\"edit.cgi?action=edit&db=$db&oid=$tuple\">edit</
+a>") if ($quick_edit);
push (@data_in_form, " |
<A href=\"edit.cgi?action=delete&db=$db&oid=$tuple\">delet
+e</a>") if ($quick_delete);
push(@data_in_form,"</CENTER></TD>\n");
$first = 1;
} else {
$tuple=~s/ *$//g;
push(@data_in_form,"\t\t<TD><FONT
SIZE=$font>$tuple </FONT></TD>\n");
}
}
push(@data_in_form,"\t</TR>\n");
}
push(@data_in_form,"</Table>\n");
push(@data_in_form,"<A href=\"http://oracle/$rnd.xls\">View Results I
+n Excel</a>"); ##add link on the html table
#######
#end original
##########
###############
#create excel file
####################
my $workbook = Spreadsheet::WriteExcel->new("/u/httpd/html/$rnd.xls
+");
$worksheet1 = $workbook->addworksheet(sheet1);
$formatgen = $workbook->addformat();
$formathead = $workbook->addformat();
$formatgen->set_bold(0);
$formatgen->set_color('black');
$formatgen->set_align('center');
$formatgen->set_size('10');
$formathead->set_color('black');
$formathead->set_align('center');
$formathead->set_size('12');
$formathead->set_bold();
#########
#figure out how long each field is
#Yes I know this is horribly inefficient but boss wants columns presiz
+ed
##############
$k=1;
while (@nrow = $query->fetchrow){
$i=0;
foreach $tuple (@nrow) {
if (length($tuple) > @leng[$i]) {
@leng[$i] = length($tuple);
}
$i++;
}
$k++;
}
$p=0;
for($fc=1;$fc !=$nfields; $fc++)
{
$fname = $query->{NAME}->[$fc];
if (@leng[$p] < length($fname)) {
@leng[$p] = length($fname);}
$p++;
}
##########
#and set the column to that width
###########
$s=0;
for($fc=1;$fc !=$nfields; $fc++) {
$fname = $query->{NAME}->[$fc];
if ($fname =~ /description/) {$worksheet1->set_col_width($s,$s
+, 58);
} else {
$worksheet1->set_col_width($s,$s, @leng[$s] +10);
$s++;
}
}
########
#name the fields
############
$l=1;
for($fc=1;$fc !=$nfields; $fc++)
{
$fname = $query->{NAME}->[$fc];
$worksheet1->write(0, $l, $fname, $formathead);
$l++;
}
###########
#write the actual data to the sheet
###############
$k=1;
while (@nrow = $query->fetchrow){
$i=0;
foreach $tuple (@nrow) {
$worksheet1->write($k, $i, $tuple, $formatgen);
}
$i++;
}
$k++;
}
return(@data_in_form);
}
BigGuy
"One World, one Web, one Program" - Microsoft promotional ad
"Ein Volk, ein Reich, ein Fuhrer" - Adolf Hitler