| CGI::Application::Plugin::Output::XSV - generate csv output from a CGI::Application runmode |
CGI::Application::Plugin::Output::XSV - generate csv output from a CGI::Application runmode
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',
});
# or, generate the list on the fly:
sub get_members { $sth->fetchrow_arrayref() }
return $self->xsv_report_web({
iterator => \&get_members,
csv_opts => { sep_char => "\t" },
filename => 'members.csv',
});
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);
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;
At some point, it would become apparent that an ``export to spreadsheet'' feature would be useful, so I'd add another runmode, which almost always looked the same:
my @fields = qw(keys to each data item);
my $csv = Text::CSV_XS->new();
foreach my $user ( @{$users} ) {
$csv->combine( [ @{$user}{@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 row of data 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.
## METHOD 1. Pre-generated list of values for csv
# 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',
});
## METHOD 2. Generate list on the fly
# in a runmode
sub get_members { $sth->fetchrow_arrayref() }
my @headers = ("Member ID", "First Name", "Last Name");
return $self->xsv_report_web({
headers => \@headers,
iterator => \&get_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 or iterator are optional:
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 => [ qw(member_id first_name last_name) ],
# or array indices fields => [ 1, 2, 0 ],
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.
If fields is not supplied and iterator is used instead of values,
the field list will be empty.
filename => 'members.csv',
The name of the file which will be sent in the HTTP content-disposition header. The default is ``download.csv''.
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.
# 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 a reference to the list of fields (fields)
as a parameter and should return a reference to a list of column 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 => "\n",
The value appended to each line of csv output. The default is ``\n''.
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.
Either this argument or iterator must be provided.
iterator => sub { $sth->fetchrow_arrayref() },
A reference to a subroutine that is used to generate each row
of data. It is passed a reference to the list of fields (fields)
as a parameter and should return a reference to a list (which
will be passed to add_to_xsv()).
It will be called repeatedly to generate each row of data until it returns a false value.
This may be preferred to values when the data set is large
or expensive to generate up-front. Thanks to Mark Stosberg for
suggesting this option.
Either this argument or values must be provided.
maximum_iters => 1_000_000,
This is the maximum number of times the iterator will be called
before an exception is raised. This is a basic stopgap to
prevent a runaway iterator that never returns false.
The default is one million.
# uppercase all values
# (each row is a list of hash references)
row_filter => sub {
my ($row, $fields) = @_;
return [ map { uc } @{$row}{@$fields} ];
},
A reference to a subroutine used to filter each row of data
(other than the header row) before it is passed to add_to_xsv.
When the values parameter is
supplied, a default filter is provided that produces each row
in the order specified by headers. For example, the default
filter for a values list of hash references is shown below.
row_filter => sub {
my ($row, $fields) = @_;
return [ @{$row}{@$fields} ];
},
This subroutine is passed two parameters for each row:
fields - reference to a list of hash keys or array indices)
Note: This parameter used to be named get_row_cb. That name is
deprecated and a warning will be issued if it is used instead of
row_filter.
# $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 my $href ( @{$values} ) {
$output .= add_to_xsv( $csv, [ @{$href}{@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:
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.
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.
# $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.
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
# 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) ],
});
# 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) ],
});
# 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
# 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
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
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 }, ],
row_filter => \&plus_one,
});
__END__ Foo,Bar,Baz 2,3,4
# 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
my @vals = qw(one two three four five six);
sub get_vals {
while ( @vals ) {
return [ splice @vals, 0, 3 ]
}
};
$self->xsv_report_web({
include_headers => 0,
iterator => \&get_vals,
});
__END__ one,two,three four,five,six
my $get_vals = sub { $sth->fetchrow_arrayref() };
$self->xsv_report_web({
include_headers => 0,
iterator => $get_vals,
});
$self->xsv_report_web({
include_headers => 0,
iterator => sub { $sth->fetchrow_arrayref() };
row_filter => sub {
my $row = shift;
return [ map { $_ + 1 } @{$row} ];
},
});
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.
Evan A. Zacks <zackse@cpan.org>
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.
I will be notified, and then you'll automatically be notified of progress on
your bug as I make changes.
the Text::CSV_XS manpage, the CGI::Application manpage
Copyright (c) 2006 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.
$Id: XSV.pm 43 2006-10-13 15:45:07Z zackse $
| CGI::Application::Plugin::Output::XSV - generate csv output from a CGI::Application runmode |