http://www.public-knowledge.com/cgi-bin/dbman/mods/exportXL.txt

#########################################################
##             E X P O R T    S E A R C H     R E S U L T S
##
##                       Written by:  D Mitchinson
##                       Modified:    04 Oct 1999
##              Mod Template borrowed from JPDeni (thanks)
##
## What it does--
##
## Provides button for user to export search results directly to Excel
## spreadsheet or at least allow Excel to open a comma-seperated-values
## (CSV) file. It does some checking for browser type and version
## in order to make a best-guess decision as to what will work for the client.
##
## New subroutines in html.pl
## --------------------------
##  export_to_Excel
##    does the majority of work: decides fields and export format

##    if needed creates a temporary file and a page that links to it otherwise sends data directly to Excel
##  csv_header
##    CSV format:  sends first row of column headers describing data
##  csv_record
##    CSV format:  sends one row of data
##  xl_header
##    HTML format: sends first row of column headers describing data
##  xl_record
##    HTML format: sends one row of data
##
## New subroutines in db.cgi
## --------------------------
##  export_records
##    initiates query and sends data to export_to_Excel
##
## ** CAUTIONS **
## As suggested by joebagodonuts (JR) on the Gossamer Threads Forum,
## I am cautioning against exporting the ID field. He suggests renaming
## it to something else as Excel won't import properly (comes up with a SYLK error.
##############################################################
#                            script:  html.pl
#                             add new line
#
#     <INPUT TYPE="SUBMIT" NAME="export_records" VALUE="Export Results">
#
# Where to put it--
#      subroutines: html_view_search
#                   html_view_failure
# Location: in between ...
# <p><center> **** <INPUT TYPE="SUBMIT" NAME="view_records" VALUE="View Records">
#
#############################################################
#                            script:  html.pl
#                           add new subroutines
#
# Where to put it--
#       at bottom of file,
# Location:
#       before 1;
#
##############################
# START OF html.pl EXPORT SUBROUTINES
##############################

sub export_to_Excel {
# --------------------------------------------------------
# Exports the results of a successful search.  Exports all non-admin fields
# What we send depends on the Browser

        my ($browser) = "";
        my ($match) = ($ENV{'HTTP_USER_AGENT'} =~ m# MSIE #);
        if ($match) {   # Internet Explorer
                if ($ENV{'HTTP_USER_AGENT'} =~ m# MSIE 3#) {
                        # this is recognized by PCs with Internet Explorer and Excel
                        # Check to see that is a registered type just in case
                        my ($match) = ($ENV{'HTTP_ACCEPT'} =~ m#vnd.ms-excel#);
                        if ($match) {
                                # Internet Explorer 3 with registered type
                                $browser = "IE3";
                                print qq| Content-type: application/vnd.ms-excel\n\n|;
                        }
                }
                if ($browser eq "") {
                        # must create a CSV file, otherwise IE4 et. al. gets mixed up with spaces in default Internet Temporary Files location
                        # it's easier then asking user to manually change his Internet options
                        $browser = "IE4";
                        &html_print_headers; # for page with link to CSV file
                }
        }
        else {
                # Ideally we could use javascript to check navigator.mimeType array
# Old code....
#               print qq| Content-type: application/msexcel\n\n|;
# Start new code ...
                $match = ($ENV{'HTTP_USER_AGENT'} =~ m#Mozilla/4.#);
                if ($match) {
                        print qq| Content-type: application/vnd.ms-excel\n\n|;
                }
                else {
                        print qq| Content-type: application/msexcel\n\n|;
                }
# End new code ............
                # Netscape needs html header info, MSIE gets mixed up.
                print qq|<html><head><title>$html_title: Search Results.</title></head>
                        <body> |;
        }

        my (@hits) = @_;
        my ($numhits) = ($#hits+1) / ($#db_cols+1);
        my ($maxhits); $in{'mh'} ? ($maxhits = $in{'mh'}) : ($maxhits = $db_max_hits);

        if ($browser ne "IE4") {
                &xl_header();

# Go through each hit and convert the array to hash and send to html_record for printing.

                for (0 .. $numhits - 1) {
                        &xl_record (&array_to_hash($_, @hits));
                }
                print "</TABLE>";
        }
        else {

# Open temporary csv file
                my ($csvfilename) = "$db_uid" . ".csv";
                my ($csvfileloc) = ">$db_csv_path$csvfilename";
                open(CSVFILE, $csvfileloc) || die "Unable to open CSV file location ($csvfileloc).";

                &csv_header();

# Go through each hit and convert the array to hash and send to csv_record for printing.

                for (0 .. $numhits - 1) {
                        &csv_record (&array_to_hash($_, @hits));
                }
                close CSVFILE || die "Unable to close CSV file ($csvfilename).";

# Create link to CSV file for user to open delete file after use.

                print "<html><head><title>$html_title: Search Results.</title></head><body>";
                print qq| <h3>Your Export File has been created!</h3><p>
                        <a href="$db_csv_url/$csvfilename" target="_export" name="Export" title="Export Results">Click here to retrieve it.</a> |;
                &html_footer;
        }
        print "</body></html>";
}

sub csv_header {
# --------------------------------------------------------
# print field name headers for each column of data

        my $num = 0;
        for (my $i =0; $i <= $#db_cols; $i++) {
# Export all non-admin fields (admin form length = -2)
                if ($db_form_len{$db_cols[$i]} >= -1) {
                        if ($num > 0) {print CSVFILE ",";}
                        print CSVFILE qq|$db_cols[$i]|;
                        $num += 1;
                }
        }
        print CSVFILE "\n";
}

sub csv_record {
# --------------------------------------------------------
# writes record in CSV format

        my (%rec) = @_;         # Load any defaults to put in the VALUE field.
        ($db_auto_generate and print &build_html_record(%rec) and return);

        my $num = 0;
        for (my $i =0; $i <= $#db_cols; $i++) {
                if ($db_form_len{$db_cols[$i]} >= -1) {
# Export all non-admin fields (admin form length = -2)
                        if ($num > 0) {print CSVFILE ",";}
                        print CSVFILE qq|"$rec{$db_cols[$i]}"|;
                        $num += 1;
                }
        }
        print CSVFILE "\n";
}

sub xl_header {
# --------------------------------------------------------
# print field name headers for each column of data

print qq| <TABLE CELLPADDING=0 CELLSPACING=0 BORDER=1>
<tr>    |;
        for (my $i =0; $i <= $#db_cols; $i++) {
                if ($db_form_len{$db_cols[$i]} >= -1) {
# Export all non-admin fields (admin form length = -2)
                        print qq|<TH width="$db_lengths{$db_cols[$i]}">&nbsp;<$font>$db_cols[$i]</font></TH>|;
                }
        }
        print "</tr>";
}

sub xl_record {
# --------------------------------------------------------
# writes record in table format for Excel export

        my (%rec) = @_;         # Load any defaults to put in the VALUE field.
        my $num = 0;

        print "<tr>";
        for (my $i =0; $i <= $#db_cols; $i++) {

                if ($db_form_len{$db_cols[$i]} >= -1) {
# Export all non-admin fields (admin form length = -2)
                        print qq|<td width="$db_lengths{$db_cols[$i]}">&nbsp;<$font>$rec{$db_cols[$i]}</font></td>|;
                }
        }
        print "</tr>";
}

##############################
# END OF html.pl EXPORT SUBROUTINES
##############################
#                            script:  db.cgi
##
##                              add new lines
##
## 1)  Add:
##           # Actual path to directory holding temporary csv files
##           # NOTE: add trailing "/"
##           (Make sure that this directory is chmod 755 on Unix servers)
##           $db_csv_path    = "/mywebdir/csv/";
##
##           # Actual url to directory holding temporary csv files
##           # NOTE: no trailing "/"
##           $db_csv_url     = "http://www.mydomain.com/csv";
##
##     Where to put it--
##           At top of file
##     Location:
##           After ...
##           $db_script_path = "/web/dmitchinson/cgi-bin/dbman";
##
## 2)  Add:
##              elsif ($in{'export_records'})                    { if ($per_view) { &export_records; }             else { &html_unauth; } }
#     Where to put it--
##           in main
##     Location:
##           After ...
##               elsif ($in{'view_records'})                     { if ($per_view) { &view_records; }               else { &html_unauth; } }
#           Before ...
##               elsif ($in{'delete_search'})            { if ($per_del) { &html_delete_search; }          else { &html_unauth; } }
#
#############################################################
#                            script:  db.cgi
##                           add new subroutine
##
## Where to put it--
##       at bottom of file,
## Location:
##       after last }
##
###############################
# START OF db.cgi EXPORT SUBROUTINES
##############################

sub export_records {
# --------------------------------------------------------
# This is called when a user wants to export a search to Excel All the work is done in query() and the routines just checks to see if the search was successful or not and returns the user to the appropriate page.

        my ($status, @hits) = &query("view");
        if ($status eq "ok") {
                &export_to_Excel(@hits);
        }
        else {
                &html_view_failure($status);
        }
}

############################
# END OF db.cgi EXPORT SUBROUTINES
############################