[ic] query2xls UserTag

[Carl Bailey]

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.com>
> #
> # 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.com>.
>
> =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.org
> 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
. . . . . . . . . . . . . . . . . .