carl at endpoint
Oct 17, 2009, 12:26 PM
Post #2 of 4
(680 views)
Permalink
|
On Oct 17, 2009, at 2:28 PM, Mike Heins wrote:
> Haven't sent a new usertag skyward in a while....will work both
> with nvend and Interchange. Attached as well as inline.
>
> # Copyright 2009 Perusion <mikeh [at] perusion>
> #
> # This program is free software; you can redistribute it and/or modify
> # it under the terms of the GNU General Public License as published by
> # the Free Software Foundation; either version 2 of the License, or
> # (at your option) any later version. See the LICENSE file for
> details.
> #
> # $Id: query2xls.tag,v 1.0 2009-10-12 22:02:57 mheins Exp $
>
> UserTag query2xls AddAttr
> UserTag query2xls Version $Revision: 1.00 $
> UserTag query2xls Documentation <<EOD
> =head1 NAME
>
> query2xls -- Create XLS spreadsheet files from a SQL query
>
> =head1 SYNOPSIS
>
> [.query2xls
> query="select field1,field2,field3 from table1"
> sheet-name="Sheet 1 of 1"
> file-name="file-to-create.xls"
> base="tablename"
> deliver=1
> width=NN
> max-width=NNN
> ]
>
> or
>
> [.query2xls
> query.sheetname1="select * from table1"
> query.sheetname2="select * from table2"
> query.sheetname3="select * from table3"
> file-name="file-to-create.xls"
> base.sheetname2=table2
> width=NN
> deliver=1
> max-width=NNN
> ]
>
> or
>
> [.query2xls
> query.0="select * from table1"
> query.1="select * from table2"
> query.1="select * from table3"
> file-name="file-to-create.xls"
> base.1=table2
> width=NN
> deliver=1
> max-width=NNN
> ]
>
> =head1 DESCRIPTION
>
> The [query2xls] tag accepts one or more SQL queries and outputs an
> XLS spreadsheet
> using the perl Spreadsheet::WriteExcel.
>
> Output is the contents of the file created unless the C<hide>
> parameter is set.
> If there is an error during creation, undef will be returned and the
> error will
> be logged and set in the error array.
>
> You can set the display width of the columns, and also (to some
> extent) the max string size
> allowed.
>
> If you set the C<deliver> parameter, the file will be delivered as
> binary content vi the browser. Mime type can be specified in the
> C<type>, parameter. The default is I<application/vnd.ms-excel>.
>
> =head2 OPTIONS
>
> =over 4
>
> =item query
>
> Contains the query or queries. Uses standard Interchange array and
> hash
> setting if desired. The sheet name will be the name of the hash
> member --
> if you want capitalization and spaces in the sheet title you should
> format and pass a hash like:
>
> [.query2xls query=`{
> "Basic sheet" => "select sku,description as
> title,price,image from products",
> "Full sheet" => "select * from inventory",
> "Partial Sheet" => "select * from products where
> price > 10",
> }`
> deliver=1 width=20]
>
> Will honor "as" if header columns are to be set.
>
> descending-brightness colors. The default value will cause
> the selected tab to have a color of #eeeeee, the first unselected
> tab will have #dddddd, the next #cccccc, etc. To create a yellow
> series, use #ffffxx.
>
> =item deliver
>
> Set to 1 if the spreadsheet is to be delivered as binary download.
>
> =item base
>
> The base table to find the table specified in the query. Can match the
> array and hash status of the C<query> object to mix tables.
>
> =item hide
>
> Standard ITL parameter to prevent output. Normally the tag outputs the
> binary spreadsheet suitable for writing to a file.
>
> =item file-name
>
> The name of the file to be written. Defaults to
>
> tmp/xls/SID/spreadsheet.xls
>
> where C<tmp> is the catalog ScratchDir and C<SID> is the session id.
>
> =item panel_width
>
> =back
>
> =head1 AUTHOR
>
> Mike Heins, <mikeh [at] perusion>.
>
> =head1 BUGS
>
> The usual number.
>
> =cut
> EOD
>
> UserTag query2xls Routine <<EOR
> sub {
> my $opt = shift;
> my $query = $opt->{query};
> my $name = $opt->{file_name} || 'spreadsheet.xls';
>
> use vars qw/$Tag/;
> my $pf0 = $Vend::Cfg->{ProductFiles}[0];
>
> my %query;
> my %base;
> if(! ref $query) {
> #::logDebug("Think query is a scalar");
> my $q = $query;
> undef $query;
> $q =~ s/\s+$//;
> $q =~ s/^\s+//;
> $opt->{sheet_name} ||= 'Sheet 1';
> $query{$opt->{sheet_name}} = $q;
> $base{$opt->{sheet_name}} = $opt->{base} || $pf0;
> }
>
> if(ref $opt->{base} eq 'HASH') {
> %base = %{$opt->{base}};
> }
> elsif(ref $opt->{base} eq 'ARRAY') {
> my $i = 0;
> for(@{$opt->{base}}) {
> $base{$i++} = $_;
> }
> }
>
> if(ref $query eq 'HASH' ) {
> #::logDebug("Think query is a hash, of: " . ::uneval($query));
> for (sort keys %$query) {
> my $k = $_;
> my $v = $query->{$k};
> #::logDebug("processing query $k=$v");
> $query{$k} = $v;
> $base{$k} = $base{$k} || $opt->{base} || $pf0;
> }
> }
> elsif(ref $query eq 'ARRAY') {
> my $base_sheet = $opt->{sheet_name} || 'Sheet ';
> my $i = 1;
> for(@{$query}) {
> my $sn = $base_sheet . $i++;
> $query{$sn} = $_;
> $base{$sn} = $base{$sn} || $base{$i} || $opt->{base} || $pf0;
> }
> }
>
> #::logDebug("created query hash: " . ::uneval(\%query));
>
> use vars qw/$Tag/;
> my $dir = "$Vend::Cfg->{ScratchDir}/xls/$Vend::Session->{id}";
> $name = "$dir/$name";
> use File::Path;
> use Spreadsheet::WriteExcel;
May I suggest using Spreadsheet::WriteExcel::Big if available. (See
usage in backup_database.coretag.)
This will support more rows per sheet for larger tables, bigger
queries and newer versions of Excel.
Also consider supporting a maxrows setting in $opt.
>
> File::Path::mkpath($dir) unless -d $dir;
>
> my $Max_xls_string = 255;
>
> my $die = sub {
> my $msg = errmsg(@_);
> $Tag->error({ name => 'tab2xls', set => $msg });
> ::logError("tab2xls: $msg");
> return undef;
> };
>
> my $xls = Spreadsheet::WriteExcel->new($name)
> or return $die->("Unable to create spreadsheet %s", $name);
>
> if($opt->{max_xls_string}) {
> $Max_xls_string = int($opt->{max_xls_string}) || 255;
> $xls->{_xls_strmax} = $Max_xls_string;
> }
>
> my @errors;
>
> my $h = 0;
> for(sort keys %query) {
> my $sn = $_;
> my $q = $query{$_};
> #::logDebug("creating sheet: " . $sn);
> my $sheet = $xls->addworksheet($sn)
> or return $die->("Unable to create sheet '%s'", $sn);
> #::logDebug("created sheet object: " . $sheet);
> my $tab = $base{$sn} || $opt->{base} || $pf0;
> #::logDebug("referencing table: " . $tab);
> my $db = dbref($tab);
> $sheet->{_xls_strmax} = $Max_xls_string
> if defined $opt->{max_xls_string};
>
> my ($ary, $fn, $fa) = $db->query($q);
>
> if(! $ary) {
> my $err = $db->errstr;
> return $die->("%s query failed: %s\nerror: %s", 'query2xls', $q,
> $err);
> }
>
> #::logDebug("creating header line: " . ::uneval(\@$fa));
>
> for(my $j = 0; $j <= @$fa; $j++) {
> $sheet->write_string(0, $j, $fa->[$j])
> if length $fa->[$j];
> }
>
> my $i = 1;
> for my $f (@$ary) {
> chomp;
> #::logDebug("writing row $i: " . ::uneval(\@f));
> for(my $j = 0; $j < @$f; $j++) {
> $sheet->write_string($i, $j, $f->[$j])
> if length $f->[$j];
> }
> $i++;
> }
>
> if($opt->{width}) {
> $sheet->set_column(0, $#$fa, $opt->{width});
> }
> $h++;
> }
>
> undef $xls;
> my $out = $Tag->file($name);
> unlink $name;
> if($opt->{deliver}) {
> $opt->{type} ||= 'application/vnd.ms-excel';
> $Tag->deliver({ type => $opt->{type}, body => $out });
> return length($out);
> }
> return $out;
> }
> EOR
> <query2xls.tag>_______________________________________________
> interchange-users mailing list
> interchange-users [at] icdevgroup
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
Very handy tag indeed! Thanks for this.
Regards,
Carl
. . . . . . . . . . . . . . . . . .
Carl Bailey
End Point Corp.
t: 919-323-8025
. . . . . . . . . . . . . . . . . .
_______________________________________________
interchange-users mailing list
interchange-users [at] icdevgroup
http://www.icdevgroup.org/mailman/listinfo/interchange-users
|
query2xls.tag
