/ 
CGI-Application-Plugin-Output-XSV-0.9
River stage zero No dependents
/ CGI::Application::Plugin::Output::XSV

NAME

CGI::Application::Plugin::Output::XSV - generate csv output from a CGI::Application runmode

SYNOPSIS

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);

PURPOSE

On many websites, I had code to retrieve a list of data items for use in an HTML::Template TMPL_LOOP. Usually this code would use the DBI 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 a fair amount of 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, 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  => 'Berry', },
  ...
);

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 a csv 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 subclass object.

It takes a reference to a hash of named parameters. All except for values are optional:

csv_opts
csv_opts   => { sep_char => "\t" },

A reference to a hash of options passed to the constructor of Text::CSV_XS. The default is an empty hash.

fields
fields => [ qw(member_id first_name last_name) ],

A reference to a list 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
filename => 'members.csv',

The name of the file which will be sent in the HTTP content-disposition header. The default is "download.csv".

headers
headers => [ "Member ID", "First Name", "Last Name" ],

A reference to a list 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
# replace underscores with spaces
headers_cb => sub {
  my $fields= shift;

  # using temp var to avoid modifying $fields
  my @fields_copy= @$fields;

  return [
    map { tr/_/ /; $_ } @fields_copy
  ];
},

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 a list of column headers.

include_headers
include_headers => 1,

A true or false value indicating whether to include headers (or automatically generated headers) as the first row of output.

The default is true.

line_ending
line_ending     => "\n",

The value appended to each line of csv output. The default is "\n".

values
values => [
  { member_id  => 1,
    first_name => 'Chuck',
    last_name  => 'Berry', },
],

# or a list of lists
values => [
  [ 1, 'Chuck', 'Berry', ],
],

A reference to a list of hash references (such as that returned by the DBI fetchall_arrayref( {} ) routine, or a reference to a list of list references.

This argument is required.

get_row_cb
# uppercase all values -- assumes values are hash references
get_row_cb => sub {
  my( $row, $fields )= @_;

  return [ map { uc } @$row{@$fields} ];
},

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 a list of hashes or lists)

  • the field list (fields - reference to a list of hash keys or array indices)

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 object.

It takes three parameters:

  • A Text::CSV_XS object

  • A reference to a list of values

  • The line ending

On an error from Text::CSV_XS, the function raises an exception.

On receiving an empty list of values, the function returns the line ending only.

Should this return a formatted list of empty fields? Let me know if you think that would be better.

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 documentation for details about what type of input causes a failure.

AUTHOR

Evan A. Zacks <zackse@cpan.org>

BUGS

Please report any bugs or feature requests to bug-cgi-application-plugin-output-xsv@rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=CGI-Application-Plugin-Output-XSV. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SEE ALSO

Text::CSV_XS, CGI::Application

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: XSV.pm 36 2006-10-03 03:29:55Z zackse $