[ic] Questions about product
Mike Heins
mike at perusion.com
Tue Aug 21 10:24:38 UTC 2012
Quoting Derek Tracy (Derek at thesdr.com):
> Hello,
>
> Is there a way to export my customers as well as orders to an excel
> spreadsheet? Is there a way to access the MySQL database? If so, could
> you please provide me with the steps on how to do so?
>
One more way is via a UserTag I wrote a couple of years back,
query2xls, which is attached.
It would allow you to create a page with just this:
[query2xls
query.transactions="select * from transactions order by order_date"
query.orderline="select * from orderline order by order_date"
query.userdb="select * from orderline order by order_date"
file-name="orders.xls"
deliver=1
]
Documentation for the tag follows (it is also in the UserTag file).
QUERY2XLS.TAG(1) User Contributed Perl Documentation QUERY2XLS.TAG(1)
NAME
query2xls -- Create XLS spreadsheet files from a SQL query
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
image.0=picture
image-dir=/var/www/html/images
max-width=NNN
]
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 "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 "deliver" parameter, the file will be delivered as
binary content vi the browser. Mime type can be specified in the
"type", parameter. The default is application/vnd.ms-excel.
OPTIONS
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.
deliver
Set to 1 if the spreadsheet is to be delivered as binary download.
base
The base table to find the table specified in the query. Can match
the array and hash status of the "query" object to mix tables.
hide
Standard ITL parameter to prevent output. Normally the tag outputs
the binary spreadsheet suitable for writing to a file.
file-name
The name of the file to be written. Defaults to
tmp/xls/SID/spreadsheet.xls
where "tmp" is the catalog ScratchDir and "SID" is the session id.
image
The name of a field to load an image into. Uses the image-dir
attribute to determine base directory (if any).
panel_width
AUTHOR
Mike Heins, <mikeh at perusion.com>.
BUGS
The usual number.
perl v5.10.1 2012-08-21 QUERY2XLS.TAG(1)
--
Mike Heins
Perusion -- Expert Interchange Consulting http://www.perusion.com/
phone +1.765.253.4194 <mike at perusion.com>
"I find that a great part of the information I have was acquired
by looking up something and finding something else on the way."
-- Franklin Pierce Adams
-------------- next part --------------
# 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
image.0=picture
image-dir=/var/www/html/images
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 image
The name of a field to load an image into. Uses the image-dir attribute
to determine base directory (if any).
=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} || $opt->{sql};
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++} = $_;
}
}
my $set = $opt->{set} || {};
if(ref $set ne 'HASH') {
$set = get_option_hash($set);
}
my @order;
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;
push @order, $k;
$base{$k} = $base{$k} || $opt->{base} || $pf0;
}
}
elsif(ref $query eq 'ARRAY') {
my $base_sheet;
if(ref $opt->{sheet_name} eq 'ARRAY') {
@order = @{$opt->{sheet_name}};
}
else {
$base_sheet = $opt->{sheet_name} || 'Sheet ';
}
my $i = 0;
for(@{$query}) {
my $sn = $order[$i];
$i++;
if($base_sheet) {
$sn = $base_sheet . $i;
}
$query{$sn} = $_;
$base{$sn} = $base{$sn} || $base{$i - 1} || $opt->{base} || $pf0;
}
}
my %image;
my $image = $opt->{image};
if(ref $image eq 'HASH' ) {
#::logDebug("Think image is a hash, of: " . ::uneval($image));
for (sort keys %$image) {
my $k = $_;
my $v = $image->{$k};
#::logDebug("processing image $k=$v");
$image{$k} = $v;
push @order, $k;
$base{$k} = $base{$k} || $opt->{base} || $pf0;
}
}
elsif(ref $image eq 'ARRAY') {
my $base_sheet;
if(ref $opt->{sheet_name} eq 'ARRAY') {
@order = @{$opt->{sheet_name}};
}
else {
$base_sheet = $opt->{sheet_name} || 'Sheet ';
}
my $i = 0;
for(@{$image}) {
my $sn = $order[$i];
$i++;
if($base_sheet) {
$sn = $base_sheet . $i;
}
$image{$sn} = $_;
$base{$sn} = $base{$sn} || $base{$i - 1} || $opt->{base} || $pf0;
}
}
::logDebug("created image hash: " . ::uneval(\%image));
use vars qw/$Tag/;
my $dir = "$Vend::Cfg->{ScratchDir}/xls/$Vend::Session->{id}";
$name = "$dir/$name";
use File::Path;
use Spreadsheet::WriteExcel;
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 $width = $opt->{width};
$width = get_option_hash($width) unless ref $width eq 'HASH';
my $numeric = $opt->{numeric};
$numeric = get_option_hash($numeric) unless ref $numeric eq 'HASH';
my $filter = $opt->{filter};
$filter = get_option_hash($filter) unless ref $filter eq 'HASH';
my @errors;
my %format;
for my $col (keys %$set) {
my $opts = $set->{$_};
my @sets;
if(ref $opts eq 'ARRAY') {
for my $o (@$opts) {
push @sets, $o;
}
}
else {
my @opts = Text::ParseWords::shellwords($opts);
for(@opts) {
push @sets, [ split /\|/, $_ ];
}
}
my $form = $xls->add_format();
for my $ary (@sets) {
my $method = shift @$ary;
eval {
$form->$method(@$ary);
};
if($@) {
$die->("format method $method failed with args " . uneval($ary));
}
}
$format{$col} = $form;
}
::logDebug("formats are: " . ::uneval(\%format));
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);
}
my $image = $image{$sn} || $opt->{image};
my $iidx;
#::logDebug("creating header line: " . ::uneval(\@$fa));
for(my $j = 0; $j <= @$fa; $j++) {
if(my $w = $width->{$fa->[$j]}) {
::logDebug("Setting column $j ($fa->[$j]) width to $w");
$sheet->set_column($j, $j, $w);
}
if($fa->[$j] eq $image) {
$iidx = $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++) {
if($iidx and $j == $iidx and $f->[$j] and $f->[$j] !~ /^http:/) {
$sheet->set_row(0, undef, 20);
my $fn = $opt->{image_dir} || '';
$fn and $fn =~ s{/*$}{/};
$f->[$j] =~ s{^/*}{};
$fn = "$fn$f->[$j]";
#::logDebug("creating image for row $i, col $j: $fn");
#::logDebug("file size for $fn: " . -s $fn);
my $status = $sheet->insert_image($i, $j, $fn);
#my $status = $sheet->insert_image("L3", $fn);
#::logDebug("Status for insert_image: " . ::uneval($status));
}
else {
if(my $filt = $filter->{$fa->[$j]}) {
$f->[$j] = filter_value($filt, $f->[$j]);
}
if(length $f->[$j]) {
if($numeric->{$fa->[$j]}) {
$sheet->write_number($i, $j, $f->[$j], $format{$fa->[$j]});
}
else {
$sheet->write_string($i, $j, $f->[$j], $format{$fa->[$j]});
}
}
}
}
$i++;
}
$h++;
::logDebug("finished sheet $sn");
::logDebug("file size for $name: " . -s $name);
}
undef $xls;
::logDebug("file size for $name: " . -s $name);
my $out = $Tag->file($name);
unlink $name unless $opt->{save};
if($opt->{deliver}) {
$opt->{type} ||= 'application/vnd.ms-excel';
$Tag->deliver({ type => $opt->{type}, body => $out });
return length($out);
}
return $out;
}
EOR
More information about the interchange-users
mailing list