Re: [Templates] tt and SpreadSheet::WriteExcel

[Sean T Allen]

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 = "" { push @rows, {%{$hash}}; } my $vars = { format => $format, rows => [EMAIL PROTECTED], 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 [EMAIL PROTECTED] [%- 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.

begin:vcard
fn:Sean T. Allen
n:Allen;Sean T.
org:USA Herbals, LLC
email;internet:[EMAIL PROTECTED]
title:Tech Guru
tel;work:718-388-5424
x-mozilla-html:FALSE
version:2.1
end:vcard