Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re: CGI & Spreadsheet::WriteExcel Module Help needed

by BigGuy (Friar)
on Nov 04, 2000 at 19:24 UTC ( #39992=note: print w/replies, xml ) Need Help??


in reply to CGI & Spreadsheet::WriteExcel Module Help needed

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>&nbsp;</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&nbsp;</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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://39992]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (2)
As of 2021-03-08 00:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favorite kind of desktop background is:











    Results (123 votes). Check out past polls.

    Notices?