[ic] [query2xls] tag interpolation -- SOLVED

Rick Bragg lists at gmnet.net
Mon Dec 28 15:22:09 UTC 2009


On Sun, 2009-12-27 at 16:01 +0100, Stefan Hornburg (Racke) wrote:
> Rick Bragg wrote:
> > Hi,
> > 
> > Does anybody know how I could get the query2xls tag to interpolate?
> > For example, I would like to do something like the following:
> > 
> > [tmp sql]SELECT * FROM tablename WHERE code = '1'[/tmp]
> > 
> >   [query2xls interpolate=1
> >     query.sheetname1="[scratch sql]"
> >     file-name="file-to-create.xls" 
> >     base.tablename
> >     width=25
> >     deliver=1
> >   ]
> > 
> > Actually, I would like to loop through the entire query.## area like
> > this:
> > 
> >   [query2xls interpolate=1
> >     [loop prefix=q list="1..[scratch q_count]" ranges=1]
> >         query.[calc] $Tag->scratch({ name => sheet_name_[q-code], });[/calc]="[calc] $Tag->scratch({ name => sql_[q-code], });[/calc]"
> >     [/loop]
> >     file-name="file-to-create.xls" 
> >     base.tablename
> >     width=25
> >     deliver=1
> >   ]
> > 
> > Anybody know the best way to do this?
> 
> It looks like that your objective is to loop through a number of SQL queries and produce a XLS file with one
> sheet for each SQL query. My suggestion is to collect the queries inside a [calc] or [perl] block and pass
> them to your [query2xls] tag with the backtick notation.
> 
> [calc]
> $Scratch->{xlsqueries} = ['select foo FROM bar', 'select bar FROM foo', 'select * from foobar'];
> return;
> [/calc]
> 
> [query2xls
> 	queries=`$Scratch->{xlsqueries}`
> /]
> 
> Regards
>          Racke
> 

That's great,

The way I ended up doing it was to build the tag in a tmp variable then
just scratch it out in the end. 

[tmpn downloadxls][query2xls[/tmpn]

[tmp downloadxls][scratch downloadxls]
     [loop prefix=q list="1..[scratch q_count]" ranges=1]
         query.[calc] $Tag->scratch({ name => sheet_name_[q-code], });[/calc]="[calc] $Tag->scratch({ name => sql_[q-code], });[/calc]"
     [/loop]
[/tmp]

[tmpn downloadxls][scratch downloadxls]
     file-name="file-to-create.xls" 
     base.tablename
     width=25
     deliver=1
   ]
[/tmpn]

[scratch name="downloadxls" interpolate="1"]


Thanks!
Rick






More information about the interchange-users mailing list