Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

CGI & Spreadsheet::WriteExcel Module Help needed

by BigGuy (Friar)
on Nov 02, 2000 at 23:57 UTC ( #39732=perlquestion: print w/replies, xml ) Need Help??

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

What I need to do is, I am using db_browser (if you are not familiar it is a freescript over at cgi.resourceindex.com, that lets you search your db via the web) on our intranet, our chief engineer came and asked me if I could take the results of a search and export it to excel for easier manipulation. Any idea's how to do this, ideally there should just be a link on the bottom of the search results page that when clicked will convert the results and then download the file to the users computer. feel free to contact me swSPAMap@swapSPAMboxen.com if I didn't include enough info. Rob remove the SPAM from my email to contact me
  • Comment on CGI & Spreadsheet::WriteExcel Module Help needed

Replies are listed 'Best First'.
RE: CGI & Spreadsheet::WriteExcel Module Help needed
by Adam (Vicar) on Nov 03, 2000 at 01:11 UTC
    You can always muck with Excel using Win32::OLE (aka COMTM) Recently I wrote the following, relatively simple, script to convert an ExcelTM file to TSV. For you I quickly swapped a few things around to convert TSV back to ExcelTM format. It worked for my simple testing. Note that the filename needs to be fully qualified or ExcelTM evilly thinks you mean it's default directory, not your current working directory.
    #!perl -w use strict; use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 2; # Always warn with verbose error messages # Open a new excel app, thus not stepping on anyones toes. my $Excel = Win32::OLE->new('Excel.Application', 'Quit') or die "Unable to start Excel"; # Set Application Visibility # 0 = Not Visible 1 = Visible $Excel->{Visible} = 0; my $file = 'c:\test.txt'; # I tested with a TSV file. die "'$file' not found" unless -e $file; my $new_file = $file; $new_file =~ s/txt$/xls/i; if( -e $new_file ) { my $n = 4; while( --$n ) { print STDERR "Deleting '$new_file' in $n seconds \r"; sleep 1; } warn "Deleting '$new_file' \n"; unlink $new_file; } my $Book = $Excel->Workbooks->Open($file); # Open the book. # And save it in the prefered format. $Book->SaveAs( { FileName => $new_file, FileFormat => xlNormal, # To save as TSV use xlText CreateBackup => 0} ); $Book->Close(0); $Excel->Quit();
      He's running a cgi script from the server. So unless he's running PerlScript enabled in the browser he won't be able to do OLE.
Re: CGI & Spreadsheet::WriteExcel Module Help needed
by Fastolfe (Vicar) on Nov 03, 2000 at 00:02 UTC
    While I'm sure this Spreadsheet::WriteExcel module may be a great way to do this (I don't know), don't underestimate Excel's ability to work with lowest-common-denominator data formats.

    Excel (98 at least) has the ability of reading HTML files consisting of a <table> and reading all of that data into native Excel cells. It can also take a simple ASCII tab-delimited file and read it in as if it were a native file format. Neither of these options requires any explicit "import" commands. You may be able to simply send either of these file types to Excel by changing its MIME type (not sure what to change it to.. application/vnd.ms-excel or something) and likely its document extension (IE likes to ignore MIME types sometimes).

    I know this doesn't completely address your question, but it is an alternative that might be easier to implement.

RE: CGI & Spreadsheet::WriteExcel Module Help needed
by Malkavian (Friar) on Nov 03, 2000 at 01:46 UTC
    Hmmm... Perhaps I'm being entirely redundant here, but:
    When I knew I needed to save stuff in a spreadsheet compatible way, readable by all versions of excel, and maybe some odd ones people used for kicks, I saved into a simple .csv file.
    When you say you need to export to excel, does this just mean something readable by excel, or does it, of necessity, have to be in the actual MS excel format?
    If you're just saving it to a datafile for easy manipulation, as I've read it, I'd say go with the CSV format.
    At least for starters, as you can then go and check the data you've saved, and be sure any bugs that arise are in your saving routine, not the excel formatting.
    CSV format is also presented as an excel icon, I believe as standard, so you shouldn't have a problem with people who need to see icons..
    Anyhow, that's just a thought,

    Malk
    *I lost my .sig. Do you have a spare?*
Re: CGI & Spreadsheet::WriteExcel Module Help needed
by Trimbach (Curate) on Nov 03, 2000 at 01:26 UTC
    There's an excellent article on Spreadsheet::WriteExcel in this month's The Perl Journal... it sounds like pretty much just what you want to do. I don't know how to get you a copy of the article without trampling on TPJ's copyright, but it looks like they're going to put the articles on their website soon.

    Gary Blackburn
    Trained Killer

Re: CGI & Spreadsheet::WriteExcel Module Help needed
by dallen (Initiate) on Nov 04, 2000 at 01:59 UTC
    This works with excel 97:
    print the data as CSV; send it to the browser with Content-type: application/msexcel

    Of course it may show up as plain text in the browser unless the user has the mime-type set up. You could alternately send the content as a MIME-encoded email, with:

    MIME-Version: 1.0 Content-type: application/msexcel; name="yourFile.xls"; boundary=42
Re: CGI & Spreadsheet::WriteExcel Module Help needed
by BigGuy (Friar) on Nov 04, 2000 at 19:24 UTC
    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
markjugg - exporting data from db_browser
by markjugg (Curate) on Nov 03, 2000 at 06:59 UTC
    I think the HTML conversion trick of Excel described above sounds promising. If you need a tab or comma deliminated export from db_browser, consider contacting us at Summersault Web Development. Fellow monk silas, aka Chris Hardie, chris@summersault.com, wrote and maintains db_browser and would be a good choice for a consultant to modify it further. :)

    -mark

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (8)
As of 2021-03-01 07:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?