Re: [Templates] tt and SpreadSheet::WriteExcel
[prev]
[thread]
[next]
[Date index for 2005/01/08]
This is a multi-part message in MIME format.
--------------090905030203000703030506
Content-Type: multipart/alternative;
boundary="------------040902080309090100090508"
--------------040902080309090100090508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Todd Freeman wrote:
>On Fri, Jan 07, 2005 at 10:45:38AM -0500, Sean T Allen wrote:
>
>
>>Todd Freeman wrote:
>>
>>
>>
>>>Can't speak for the others... but personally I would love to be able to do
>>>just that... being able to take one data set and do
>>>
>>>$template->process('pdf');
>>>$template->process('html');
>>>$template->process('excel');
>>>
>>>
>>>
>>>
>>for any type of file or just a spreadsheet?
>>
>>
>>
>
>Well.. I am not thinking about "files" I am talking about taking a chunk of data and formatting it appropriately for each output type. For example:
>
>my $q = new CGI;
>
>my $format = { blah => { heading => 'Blah $$!', align => 'right'},
> de => { heading => 'Delicious!', align => 'center'},
> foo => { heading => '# of Foo!', align => 'left'}
> };
>my $sth = $dbh->prepare($q->param('sql_query');
>$sth->execute();
>
>while( my $data = $sth->fetchrow_hashref())
> { push @rows, {%{$hash}}; }
>
>my $vars = { format => $format, rows => \@rows, q => $q };
>
>$tt->process('blah_' . $q->param('output_format'), $vars)
>
>So then we have 3 templates:
>
>--- blah_html.tt ---
>[% q.start_html();
> q.start_table();
> q.start_Tr();
> FOREACH column = rows.0.keys;
> q.th(format.$column.heading);
> END;
> CGI.end_Tr();
>
> FOREACH row = rows;
> q.start_Tr();
> FOREACH column = row.keys;
> CGI.td({align=>format.$column.align}, row.$column);
> END
> q.end_Tr();
> END;
> q.end_table();
> q.end_html();
>%]
>--------------------
>
>--- blah_pdf.tt ---
>[%- FILTER latex("pdf") -%]
>\documentclass[12pt,letterpaper,landscape,nohead,nofoot]{report}
>\usepackage{supertabular}
>\begin{document}
> \tablehead{ [%- FOREACH heading = rows.0.keys;
> format.$heading.heading;
> IF (! loop.last );
> '&';
> END;
> END -%] }
> \begin{supertabular}{[% FOREACH alignment = rows.0.keys; format.$alignment.align.chunk(1).0; END %]@{\extracolsep{\fill}}}
> [%- FOREACH row = rows;
> FOREACH column = row.keys;
> row.$column;
> IF (! loop.last );
> '&';
> ELSE;
> '\\';
> END;
> END;
> END; -%]
> \end{supertabular}
>\end{document}
>[%- END -%]
>-------------------
>
>--- blah_csv.tt ---
>[%- FOREACH row = rows;
> FOREACH column = row.keys;
> row.$column;
> IF (! loop.last );
> "\t";
> ELSE;
> "\n";
> END;
> END;
> END; -%]
>-------------------
>
>Not sure on the syntax for SpreadSheet::WriteExcel off the top of my head... but I bet you can see how this is going...
>
>Is a dead simple way to drop an SQL query into a program and get a report in whatever format you want... especially if you let the user define the headings, alignment and style on the page that they request the report from.
>
>Anyways... yes the syntax is probably not correct... I just dropped this outta my head on the fly so... don't think a copy paste is gonna work the first time :P
>
>
>
>
Not the way I was thinking of it but it could work. Couple things see
for that would be creating a template that includes a template
based on a run decision that you pass and it selects the appropriate one
of yours above. One thing I would consider adding is the ability
to add in arbitrary templates via PROCESS at different point something
like say when doing a row foreach or a column for each... I moved the
functionality you have in the template further down into plugin
classes... which makes figuring out how to do formatting a bit more of
an issue.. but either solution would require a formatting abstraction so
you could pass the same formatting info in the data and have it properly
translated into the end formats requirements.
I didn't think of doing it the way you have it because the data I don't
like push everything into a row/cell format in the code calling the
template but again that could be pushed into a higher level template...
You might have a problem with WriteExcel based on:
its output has to be tied to a file handle
if you aren't send the process results to someplace other than STDOUT
it isnt an issue, however if you are sending it to a file... well...
template
doesn't pass where the output is going down to your plugin...
mine as it stands ( with some application specific SET code taken out
looks like:
[% USE sheet = Spreadsheet( type => "$type" ) -%]
[% sheet.start() -%]
[% sheet.add_row( [ 'First Name', 'Last Name', 'Address', 'Address2',
'City', 'State', 'Zip', 'Country', 'Phone number', 'Email', 'Media
Code', 'JF Reference #(FEI ORDER)', 'Offer SKU', 'Upsell', 'Tender (AX
VI MC DC)', 'Credit Card Number', 'Expiration Date', 'Total Order Cost'
] ) -%]
[% FOREACH order IN orders -%]
*** code to determine sku and upsell cut ***
[% cells = [ order.fname, order.lname, order.address1, order.address2,
order.city, order.state.abbrev, order.zipcode, order.country.abbrev,
order.phone, order.email, order.mediacode, "JF$order.id", sku, upsell,
order.customer.paymethod.code, order.customer.ccnum,
order.customer.ccexp, order.ordertotal ] -%]
[% sheet.add_row( cells ) -%]
[% END -%]
[% sheet.stop() -%]
But it could also work if you just did....
[% USE sheet = Spreadsheet( type => "$type" ) -%]
[% sheet.start() -%]
[% sheet.add_row( headers ) -%]
[% FOREACH order IN orders -%]
[% sheet.add_row( row.cells ) -%]
[% END -%]
[% sheet.stop() -%]
or something similar... the only formatting it does right now is for
excel files it sets all cells to 'text'
but when i need it, i'll work out that issue...
currently i have tsv, csv, html, and xls output defined...
as well as a basic text type that can take any user supplied cell and
row seps.
its basic but it serves my current purposes...
1. template, multiple runtime determined output formats...
In order to solve the aforementioned WriteExcel issue, the plugin
classes below Spreadhseet that I wrote
buffer all output until stop is called at which point its returned. Only
the 'xls' one needs this but I made
the others operate the same way to keep the return results the same
across the board.
--------------040902080309090100090508
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Todd Freeman wrote:
<blockquote cite="mid20050108001423.GA3399@londo" type="cite">
<pre wrap="">On Fri, Jan 07, 2005 at 10:45:38AM -0500, Sean T Allen wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Todd Freeman wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Can't speak for the others... but personally I would love to be able to do
just that... being able to take one data set and do
$template->process('pdf');
$template->process('html');
$template->process('excel');
</pre>
</blockquote>
<pre wrap="">for any type of file or just a spreadsheet?
</pre>
</blockquote>
<pre wrap=""><!---->
Well.. I am not thinking about "files" I am talking about taking a chunk of data and formatting it appropriately for each output type. For example:
my $q = new CGI;
my $format = { blah => { heading => 'Blah $$!', align => 'right'},
de => { heading => 'Delicious!', align => 'center'},
foo => { heading => '# of Foo!', align => 'left'}
};
my $sth = $dbh->prepare($q->param('sql_query');
$sth->execute();
while( my $data = $sth->fetchrow_hashref())
{ push @rows, {%{$hash}}; }
my $vars = { format => $format, rows => \@rows, q => $q };
$tt->process('blah_' . $q->param('output_format'), $vars)
So then we have 3 templates:
--- blah_html.tt ---
[% q.start_html();
q.start_table();
q.start_Tr();
FOREACH column = rows.0.keys;
q.th(format.$column.heading);
END;
CGI.end_Tr();
FOREACH row = rows;
q.start_Tr();
FOREACH column = row.keys;
CGI.td({align=>format.$column.align}, row.$column);
END
q.end_Tr();
END;
q.end_table();
q.end_html();
%]
--------------------
--- blah_pdf.tt ---
[%- FILTER latex("pdf") -%]
\documentclass[12pt,letterpaper,landscape,nohead,nofoot]{report}
\usepackage{supertabular}
\begin{document}
\tablehead{ [%- FOREACH heading = rows.0.keys;
format.$heading.heading;
IF (! loop.last );
'&';
END;
END -%] }
\begin{supertabular}{[% FOREACH alignment = rows.0.keys; format.$alignment.align.chunk(1).0; END %]@{\extracolsep{\fill}}}
[%- FOREACH row = rows;
FOREACH column = row.keys;
row.$column;
IF (! loop.last );
'&';
ELSE;
'\\';
END;
END;
END; -%]
\end{supertabular}
\end{document}
[%- END -%]
-------------------
--- blah_csv.tt ---
[%- FOREACH row = rows;
FOREACH column = row.keys;
row.$column;
IF (! loop.last );
"\t";
ELSE;
"\n";
END;
END;
END; -%]
-------------------
Not sure on the syntax for SpreadSheet::WriteExcel off the top of my head... but I bet you can see how this is going...
Is a dead simple way to drop an SQL query into a program and get a report in whatever format you want... especially if you let the user define the headings, alignment and style on the page that they request the report from.
Anyways... yes the syntax is probably not correct... I just dropped this outta my head on the fly so... don't think a copy paste is gonna work the first time :P
</pre>
</blockquote>
Not the way I was thinking of it but it could work. Couple things see
for that would be creating a template that includes a template<br>
based on a run decision that you pass and it selects the appropriate
one of yours above. One thing I would consider adding is the ability<br>
to add in arbitrary templates via PROCESS at different point something
like say when doing a row foreach or a column for each... I moved the
functionality you have in the template further down into plugin
classes... which makes figuring out how to do formatting a bit more of
an issue.. but either solution would require a formatting abstraction
so you could pass the same formatting info in the data and have it
properly translated into the end formats requirements.<br>
I didn't think of doing it the way you have it because the data I don't
like push everything into a row/cell format in the code calling the
template but again that could be pushed into a higher level template...<br>
<br>
You might have a problem with WriteExcel based on:<br>
<br>
its output has to be tied to a file handle<br>
if you aren't send the process results to someplace other than STDOUT <br>
it isnt an issue, however if you are sending it to a file... well...
template<br>
doesn't pass where the output is going down to your plugin...<br>
<br>
<br>
mine as it stands ( with some application specific SET code taken out
looks like:<br>
<br>
[% USE sheet = Spreadsheet( type => "$type" ) -%]<br>
[% sheet.start() -%]<br>
[% sheet.add_row( [ 'First Name', 'Last Name', 'Address', 'Address2',
'City', 'State', 'Zip', 'Country', 'Phone number', 'Email', 'Media
Code', 'JF Reference #(FEI ORDER)', 'Offer SKU', 'Upsell', 'Tender (AX
VI MC DC)', 'Credit Card Number', 'Expiration Date', 'Total Order Cost'
] ) -%]<br>
[% FOREACH order IN orders -%]<br>
*** code to determine sku and upsell cut ***<br>
[% cells = [ order.fname, order.lname, order.address1, order.address2,
order.city, order.state.abbrev, order.zipcode, order.country.abbrev,
order.phone, order.email, order.mediacode, "JF$order.id", sku, upsell,
order.customer.paymethod.code, order.customer.ccnum,
order.customer.ccexp, order.ordertotal ] -%]<br>
[% sheet.add_row( cells ) -%]<br>
[% END -%]<br>
[% sheet.stop() -%]<br>
<br>
But it could also work if you just did....<br>
<br>
[% USE sheet = Spreadsheet( type => "$type" ) -%]<br>
[% sheet.start() -%]<br>
[% sheet.add_row( headers ) -%]<br>
[% FOREACH order IN orders -%]<br>
[% sheet.add_row( row.cells ) -%]<br>
[% END -%]<br>
[% sheet.stop() -%]<br>
<br>
or something similar... the only formatting it does right now is for
excel files it sets all cells to 'text' <br>
but when i need it, i'll work out that issue...<br>
<br>
currently i have tsv, csv, html, and xls output defined...<br>
as well as a basic text type that can take any user supplied cell and
row seps.<br>
<br>
its basic but it serves my current purposes...<br>
<br>
1. template, multiple runtime determined output formats...<br>
<br>
In order to solve the aforementioned WriteExcel issue, the plugin
classes below Spreadhseet that I wrote <br>
buffer all output until stop is called at which point its returned.
Only the 'xls' one needs this but I made<br>
the others operate the same way to keep the return results the same
across the board.<br>
</body>
</html>
--------------040902080309090100090508--
--------------090905030203000703030506
Content-Type: text/x-vcard; charset=utf-8;
name="sean.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="sean.vcf"
begin:vcard
fn:Sean T. Allen
n:Allen;Sean T.
org:USA Herbals, LLC
email;internet:sean@xxxxxxxxxx.xxx
title:Tech Guru
tel;work:718-388-5424
x-mozilla-html:FALSE
version:2.1
end:vcard
--------------090905030203000703030506--
_______________________________________________
templates mailing list
templates@xxxxxxxxxxxxxxxx.xxx
http://lists.template-toolkit.org/mailman/listinfo/templates
