NAME CGI::Application::Plugin::Output::XSV Generate csv output from a CGI::Application runmode SYNOPSIS # in a CGI::Application subclass use CGI::Application::Plugin::Output::XSV; # in some runmode... # $sth is a prepared DBI statement handle my $members= $sth->fetchall_arrayref( {} ); my @headers= qw( member_id first_name last_name ... ); return $self->xsv_report_web({ fields => \@headers, values => $members, csv_opts => { sep_char => "\t" }, filename => 'members.csv', }); DESCRIPTION "CGI::Application::Plugin::Output::XSV" provides csv-related routines useful for web applications (via Text::CSV_XS). A method, "xsv_report_web" is exported by default. Three other functions, "xsv_report", "clean_field_names", and "add_to_xsv" are available for optional export. You may export all four routines by specifying the export tag ":all": use CGI::Application::Plugin::Output::XSV qw(:all); INSTALLATION To install this module, run the following commands: perl Build.PL ./Build ./Build test ./Build install Alternatively, if you do not have Module::Build but you do have 'make', you can used the included Makefile.PL and run the following commands: perl Makefile.PL make make test make install DEPENDENCIES This module requires these other modules and libraries: TEXT::CSV_XS CGI::Application PURPOSE On many websites, I had code to retrieve a list of data items for use in an HTML::Template(3) TMPL_LOOP. Usually this code would use the DBI(3) routine "fetchall_arrayref( {} )" to get a list of hash references, one for each data item. my $users= $sth->fetchall_arrayref( {} ); my $template= $self->load_tmpl( ... ); $template->param( users => $users ); return $template->output; Inevitably, the client would ask for a data format they could load in Excel, so I'd add another runmode for a csv export. This runmode almost always looked the same: my @fields= qw(keys to each data item); my $csv= Text::CSV_XS->new(); foreach( @$users ) { $csv->combine( [ @$_{ @fields } ] ); $output .= $csv->string() . "\n"; } $self->header_props( -type => 'application/x-csv', '-content-disposition' => "attachment; filename=export.csv", ); return $output; The purpose of this module is to provide a simple method, "xsv_report_web", that wraps the above code while offering enough programmer flexibility. For example, the programmer may control the naming of header columns, filter each line of output before it is passed to Text::CSV_XS(3), and set the filename that is supplied to the user's browser. Please see the documentation below for "xsv_report_web" for a list of available options. METHODS xsv_report_web # in a runmode my @members= ( { member_id => 1, first_name => 'Chuck', last_name => 'Barry', }, ... ); my @headers= ("Member ID", "First Name", "Last Name"); my @fields = qw(member_id first_name last_name); return $self->xsv_report_web({ fields => \@fields, headers => \@headers, values => \@members, csv_opts => { sep_char => "\t" }, filename => 'members.csv', }); This method generates an XSV file that is sent directly to the user's web browser. It sets the content-type header to 'application/x-csv' and sets the content-disposition header to 'attachment'. It should be invoked through a CGI::Application(3) subclass object. It takes a reference to a hash of named parameters. All except for "values" are optional: fields A reference to an array of field names or array indices. This parameter specifies the order of fields in each row of output. If "fields" is not supplied, a list will be generated using the first entry in the "values" list. Note, however, that in this case, if the "values" parameter is a list of hashes, the field order will be random because the field names are extracted from a hash. If the "values" parameter is a list of lists, the field order will be the same as the data provided. filename The name of the file which will be sent in the HTTP content-disposition header. The default is "download.csv". headers A reference to an array of column headers to be used as the first row of the csv report. If "headers" is not supplied (and "include_headers" is not set to a false value), "headers_cb" will be called with "fields" as a parameter to generate column headers. headers_cb A reference to a subroutine used to generate column headers from the field names. A default routine is provided in "clean_field_names". This function is passed the list of fields ("fields") as a parameter and should return a reference to an array of column headers. include_headers A true or false value indicating whether to include "headers" (or automatically generated headers) as the first row of output. The default is true. values A reference to an array of hash references (such as that returned by the DBI(3) "fetchall_arrayref( {} )" routine, or a reference to an array of list references. This argument is required. get_row_cb A reference to a subroutine used to generate each row of output (other than the header row). Default routines are provided that return each row of "values" in the order specified by "headers". This subroutine is passed two parameters for each row: * the current row (reference to an array) * the field list ("fields" - reference to an array) FUNCTIONS add_to_xsv # $sth is a prepared DBI statement handle my $values= $sth->fetchall_arrayref( {} ); my @headers= qw/foo bar baz/; my $output; # $csv is a Text::CSV_XS object foreach( @$values ) { $output .= add_to_xsv( $csv, [ @$_{@headers} ], "\r\n" ); } This function, used internally by "xsv_report"/"xsv_report_web", formats a list of values for inclusion a csv file. The return value is from "$csv->string()", where $csv is a Text::CSV_XS(3) object. It takes three parameters: * A Text::CSV_XS(3) object * A reference to a list of values * The line ending On an error from Text::CSV_XS(3), the function raises an exception. On receiving an empty list of values, the function returns the line ending only. XXX should this return a formatted list of empty fields? clean_field_names my $fields= [ qw/first_name foo bar baz/ ]; my $headers= clean_field_names( $fields ); # $headers is now [ 'First Name', 'Foo', 'Bar', 'Baz' ] This function takes a reference to a list of strings and returns a reference to a new list in which the strings are reformatted as such: 1. Underscores ('_') are changed to spaces 2. The first letter of each word is capitalized This function is used by "xsv_report" and "xsv_report_web" if the "headers_cb" parameter is not supplied. xsv_report # $sth is a prepared DBI statement handle my $members= $sth->fetchall_arrayref( {} ); my @headers= qw( member_id first_name last_name ... ); my $output= $self->xsv_report({ fields => \@headers, values => $members, csv_opts => { sep_char => "\t" }, }); # do something with $output This function generates a string containing csv data and returns it. This may be useful when you want to do some manipulation of the data before sending it to the user's browser or elsewhere. It takes the same named parameters (via a reference to a hash) as "xsv_report_web" except for "filename", which is not applicable to this function. EXAMPLES Specify (almost) everything return $self->xsv_report_web({ values => [ { first_name => 'Jack', last_name => 'Tors', phone => '555-1212' }, { first_name => 'Frank', last_name => 'Rizzo', phone => '555-1515' }, ], headers => [ "First Name", "Last Name", "Phone" ], fields => [ qw(first_name last_name phone) ], include_headers => 1, line_ending => "\n", csv_opts => { sep_char => "\t" }, filename => 'download.csv', }); __END__ "First Name" "Last Name" Phone Jack Tors 555-1212 Frank Rizzo 555-1515 Use defaults # ends up with same options and output as above return $self->xsv_report_web({ values => [ { first_name => 'Jack', last_name => 'Tors', phone => '555-1212' }, { first_name => 'Frank', last_name => 'Rizzo', phone => '555-1515' }, ], headers => [ "First Name", "Last Name", "Phone" ], fields => [ qw(first_name last_name phone) ], }); Use header generation provided by module # headers generated will be [ "First Name", "Last Name", "Phone" ] # same output as above return $self->xsv_report_web({ values => [ { first_name => 'Jack', last_name => 'Tors', phone => '555-1212' }, { first_name => 'Frank', last_name => 'Rizzo', phone => '555-1515' }, ], fields => [ qw(first_name last_name phone) ], }); Use custom header generation # headers generated will be [ "first", "last", "phone" ] return $self->xsv_report_web({ values => [ { first_name => 'Jack', last_name => 'Tors', phone => '555-1212' }, { first_name => 'Frank', last_name => 'Rizzo', phone => '555-1515' }, ], fields => [ qw(first_name last_name phone) ], headers_cb => sub { my @h= @{ +shift }; s/_name$// foreach @h; return \@h; }, }); __END__ first,last,phone Jack,Tors,555-1212 Frank,Rizzo,555-1515 If order of fields doesn't matter # headers and fields will be in random order (but consistent # throughout data processing) due to extraction from hash # (headers will be generated automatically) return $self->xsv_report_web({ values => [ { first_name => 'Jack', last_name => 'Tors', phone => '555-1212' }, { first_name => 'Frank', last_name => 'Rizzo', phone => '555-1515' }, ], }); __END__ Phone,"Last Name","First Name" 555-1212,Tors,Jack 555-1515,Rizzo,Frank No header row return $self->xsv_report_web({ values => [ { first_name => 'Jack', last_name => 'Tors', phone => '555-1212' }, { first_name => 'Frank', last_name => 'Rizzo', phone => '555-1515' }, ], fields => [ qw(first_name last_name phone) ], include_headers => 0, }); __END__ Jack,Tors,555-1212 Frank,Rizzo,555-1515 Filter data as it is processed sub plus_one { my( $row, $fields )= @_; return [ map { $_ + 1 } @$row{@$fields} ]; } # each row (other than header row) will be # passed through plus_one() return $self->xsv_report_web({ fields => [ qw(foo bar baz) ], values => [ { foo => 1, bar => 2, baz => 3 }, ], get_row_cb => \&plus_one, }); __END__ Foo,Bar,Baz 2,3,4 Pass list of lists (instead of hashes) # each row will be processed in order # since fields parameter is omitted $self->xsv_report_web({ include_headers => 0, values => [ [ 1, 2, 3 ], [ 4, 5, 6 ], ], }); __END__ 1,2,3 4,5,6 ERROR HANDLING The function "add_to_xsv" will raise an exception when "Text::CSV_XS->combine" fails. Please see the Text::CSV_XS(3) documentation for details about what type of input causes a failure. AUTHOR Evan A. Zacks SEE ALSO Text::CSV_XS(3), CGI::Application(3) COPYRIGHT AND LICENSE Copyright (c) 2005 CommonMind, LLC. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. REVISION $Id: README 26 2005-09-22 15:21:37Z zackse $