You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Matthew Monkan <ma...@ieee.org> on 2008/05/14 16:24:30 UTC
Query Database & Produce Excel Document
Hey everyone. I'm somewhat new to XML and Cocoon, so please pardon my
ignorance with the subject. :confused:
I am using Cocoon 2.1.11 on Windows XP. I've been using the Moczar & Aston
book and have been able to create the static page examples in Chapter 7
within the downloaded Cocoon folder.
Ultimately, I need to be able to query info from a database and output an
Excel spreadsheet. Then, I need Cocoon to send out an e-mail with the Excel
spreadsheet attached.
I basically need help understanding what tools in Cocoon I need to use. Can
someone verify these or make suggestions? Here are some of the things I
listed down so far:
For DB to Excel document:
SQLTransformer (queries the database, passing the result back in the SAX
event stream)
HSSF Serializer (turns SAX events into Excel spreadsheet document)
To send out the e-mail:
Cocoon mail block (SendMail Transformer?)
I don't really understand what generator to use. I'm trying to create an
example of this offline using MySQL. So for example, I have a small table
created in MySQL; I want to say, stick some data from it in some shape or
form into Excel, and send out a quick e-mail to a hardcoded e-mail address
with the Excel document attached.
I also need to be able to run this from the offline CLI.
Can someone help me understand the most optimal flow of information I would
use. I want to create a sequence diagram to help me visualize this, so if
someone could help me understand what info is being passed where, that would
be extremely helpful. All the terminology in Cocoon is killing me, and I
need help focusing on what is most important to know for these requirements.
--
View this message in context: http://www.nabble.com/Query-Database---Produce-Excel-Document-tp17232331p17232331.html
Sent from the Cocoon - Users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Query Database & Produce Excel Document
Posted by warrell harries <wa...@googlemail.com>.
I have done exactly this (even under Cocoon CLI) but don't have the code to
hand. It's quite straightforward except that the sendmail transformer
invoked the pipeline to build the s/s which took so long that the sendmail
connection was dropped. To solve this I ran one CLI to build the s/s and
then scheduled the sendmail one an hour later.
I will dig out the code later.
Regards
Re: Query Database & Produce Excel Document
Posted by Derek Hohls <DH...@csir.co.za>.
As per below - except I would not be using ESQL but the SQL transformer
to read data from the database and create the XML...
>>> On 2008/05/14 at 08:44, in message <48...@lampsacos.demon.co.uk>, Ken Starks <ke...@lampsacos.demon.co.uk> wrote:
If you are new to Cocoon, I would put the email requirement, and
produce a (virtual) spreadsheet in the user's browser.
You scheme of work can then follow the most usual Cocoon sitemap design:
i) A generator (Non-XML to XML)
ii) One or more Transformation steps (XML_1 to XML_2 to XML_3 to ..., as
required)
iii) A serialiser (XML to Non-XML)
i. I generally use an ESQL logicsheet for reading my database, which is
a generator
and has to go at the start of your pipeline. But I find it simpler than
an 'Action' which
can go anywhere in the pipeline. It turns your payload of data into an
XML format
which you can design yourself; as well as coding various errors and
empty payloads
in XML. (cf Appendix E of Mozcar and Aston )
ii. You then need to write XSLT to turn this XML into something
acceptable to the
HSSF serialiser. I'm not sure how up to date it is, or how up to date
the HSSF
serialiser, but you can get some documentation from:
www.jfree.org/jworkbook/download/gnumeric-xml.pdf
Data values are pretty much OK to deal with, but formulas are hell
(IMHO). No
wonder its called HSSF (Horrible Spread Sheet Format).
iii. The HSSF serialiser can be used out of the box. Look at the Cocoon
Samples 'Hello World' (Excel pipeline) to see how to set the correct
Mime-type.
In fact, the best approach for most people, is to start with a working
copy of
the Hello World Example, and gradually elaborate it. After a bit of
experience
you will know what kind of XMl at the start of the Transformation stage will
make life easy. If it is hard to produce it using ESQL, you can consider
doing
some preliminary work it in the database, using a database view.
(i.e. Copy the main bits of <Cocoon-Home>/samples/hello-world/ to
<Cocoon-Home>/MyHello-World.
You need:
content/hello.xml
sitemap.xmap with most map:match elements deleted apart from hello.xml,
hello.html, hello.txt
style/xsl/page2html.xsl
style/xsl/page2xsl.xml
Unfortunately, the pipeline for hello.xsl has been carefully hidden and
is no
longer part of samples/hello-world/sitemap.xmap.
It is now in samples/blocks/poi/sitemap.xmap, but you might as well just
copy this
into Myhello-world/sitemap.xmap:
<map:match pattern="hello.xls">
<map:generate src="content/hello.xml"/>
<map:transform src="style/xsl/page2xls.xsl"/>
<map:serialize type="xls"/>
</map:match>
Point your browser to:
http://your/usual/cocoon/url/and/port/Myhello-world/hello.xls
Or if you want differently named source files, use a wildcard:
<map:match pattern="*.xls">
<map:generate src="content/{1}.xml"/>
<map:transform src="style/xsl/page2xls.xsl"/>
<map:serialize type="xls"/>
</map:match>
... and use widldcards in all the other pipelines too.
Edit hello.xml, or write something similar, and see what difference it
makes to the
various styles of output.
You can look at the XML at any stage either by defining a special
pipeline, or
by defining a Cocoon view. The special pipeline is easier, frankly, if
you are a
beginner.
Matthew Monkan wrote:
> Hey everyone. I'm somewhat new to XML and Cocoon, so please pardon my
> ignorance with the subject. :confused:
>
> I am using Cocoon 2.1.11 on Windows XP. I've been using the Moczar & Aston
> book and have been able to create the static page examples in Chapter 7
> within the downloaded Cocoon folder.
>
> Ultimately, I need to be able to query info from a database and output an
> Excel spreadsheet. Then, I need Cocoon to send out an e-mail with the Excel
> spreadsheet attached.
>
> I basically need help understanding what tools in Cocoon I need to use. Can
> someone verify these or make suggestions? Here are some of the things I
> listed down so far:
>
> For DB to Excel document:
>
> SQLTransformer (queries the database, passing the result back in the SAX
> event stream)
> HSSF Serializer (turns SAX events into Excel spreadsheet document)
>
> To send out the e-mail:
> Cocoon mail block (SendMail Transformer?)
>
> I don't really understand what generator to use. I'm trying to create an
> example of this offline using MySQL. So for example, I have a small table
> created in MySQL; I want to say, stick some data from it in some shape or
> form into Excel, and send out a quick e-mail to a hardcoded e-mail address
> with the Excel document attached.
>
> I also need to be able to run this from the offline CLI.
>
> Can someone help me understand the most optimal flow of information I would
> use. I want to create a sequence diagram to help me visualize this, so if
> someone could help me understand what info is being passed where, that would
> be extremely helpful. All the terminology in Cocoon is killing me, and I
> need help focusing on what is most important to know for these requirements.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.
This message has been scanned for viruses and dangerous content by MailScanner,
and is believed to be clean. MailScanner thanks Transtec Computers for their support.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Query Database & Produce Excel Document
Posted by Ken Starks <ke...@lampsacos.demon.co.uk>.
Ken Starks wrote:
>
>
> If not, the XLS block was, perhaps, not activated at compile time.
>
Another check:
go to http://localhost:8080/samples/
In the section headed:
System Tools And Pages
click on
Status Page.
Scroll down to the section headed 'WEB-INF/lib' and check you have the
entry:
'file: cocoon-poi-block.jar'
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Query Database & Produce Excel Document
Posted by Ken Starks <ke...@lampsacos.demon.co.uk>.
Do the other urls work:
http://localhost:8080/excel/hello.xml
http://localhost:8080/excel/hello.html
http://localhost:8080/excel/hello.txt
If not, perhaps you have not got the subdirectories
excel/style/xsl
excel/content
Does the original sample work, as supplied:
http://localhost:8080-/samples/hello-world/
followed by going to the 'Blocks Hello World' section and clicking on 'XLS'
If not, the XLS block was, perhaps, not activated at compile time.
Matthew Monkan wrote:
>
> http://www.nabble.com/file/p17316736/sitemap.xmap sitemap.xmap
> http://www.nabble.com/file/p17316736/page2xls.xsl page2xls.xsl
> http://www.nabble.com/file/p17316736/hello.xml hello.xml :wistle:
>
> Hi Ken, I can't seem to get this to work. I just get the following error
> message:
>
> type Status report
>
> message /excel/hello.xls
>
> description The requested resource (/excel/hello.xls) is not available.
>
> I've attached my page2xls.xsl, sitemap.xmap, and hello.xml files. The
> location paths are fine and I put the files in a folder called "excel".
>
> I'm trying to get http://localhost:8080/excel/hello.xls to work.
>
> I've tried many things, but can't figure out what I'm doing wrong since I'm
> mostly copying and pasting from the samples page. If anyone can help, I'd
> greatly appreciate it; thanks.
>
> Matt
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Query Database & Produce Excel Document
Posted by Matthew Monkan <ma...@ieee.org>.
http://www.nabble.com/file/p17316736/sitemap.xmap sitemap.xmap
http://www.nabble.com/file/p17316736/page2xls.xsl page2xls.xsl
http://www.nabble.com/file/p17316736/hello.xml hello.xml :wistle:
Hi Ken, I can't seem to get this to work. I just get the following error
message:
type Status report
message /excel/hello.xls
description The requested resource (/excel/hello.xls) is not available.
I've attached my page2xls.xsl, sitemap.xmap, and hello.xml files. The
location paths are fine and I put the files in a folder called "excel".
I'm trying to get http://localhost:8080/excel/hello.xls to work.
I've tried many things, but can't figure out what I'm doing wrong since I'm
mostly copying and pasting from the samples page. If anyone can help, I'd
greatly appreciate it; thanks.
Matt
--
View this message in context: http://www.nabble.com/Query-Database---Produce-Excel-Document-tp17232331p17316736.html
Sent from the Cocoon - Users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Query Database & Produce Excel Document
Posted by Ken Starks <ke...@lampsacos.demon.co.uk>.
If you are new to Cocoon, I would put the email requirement, and
produce a (virtual) spreadsheet in the user's browser.
You scheme of work can then follow the most usual Cocoon sitemap design:
i) A generator (Non-XML to XML)
ii) One or more Transformation steps (XML_1 to XML_2 to XML_3 to ..., as
required)
iii) A serialiser (XML to Non-XML)
i. I generally use an ESQL logicsheet for reading my database, which is
a generator
and has to go at the start of your pipeline. But I find it simpler than
an 'Action' which
can go anywhere in the pipeline. It turns your payload of data into an
XML format
which you can design yourself; as well as coding various errors and
empty payloads
in XML. (cf Appendix E of Mozcar and Aston )
ii. You then need to write XSLT to turn this XML into something
acceptable to the
HSSF serialiser. I'm not sure how up to date it is, or how up to date
the HSSF
serialiser, but you can get some documentation from:
www.jfree.org/jworkbook/download/gnumeric-xml.pdf
Data values are pretty much OK to deal with, but formulas are hell
(IMHO). No
wonder its called HSSF (Horrible Spread Sheet Format).
iii. The HSSF serialiser can be used out of the box. Look at the Cocoon
Samples 'Hello World' (Excel pipeline) to see how to set the correct
Mime-type.
In fact, the best approach for most people, is to start with a working
copy of
the Hello World Example, and gradually elaborate it. After a bit of
experience
you will know what kind of XMl at the start of the Transformation stage will
make life easy. If it is hard to produce it using ESQL, you can consider
doing
some preliminary work it in the database, using a database view.
(i.e. Copy the main bits of <Cocoon-Home>/samples/hello-world/ to
<Cocoon-Home>/MyHello-World.
You need:
content/hello.xml
sitemap.xmap with most map:match elements deleted apart from hello.xml,
hello.html, hello.txt
style/xsl/page2html.xsl
style/xsl/page2xsl.xml
Unfortunately, the pipeline for hello.xsl has been carefully hidden and
is no
longer part of samples/hello-world/sitemap.xmap.
It is now in samples/blocks/poi/sitemap.xmap, but you might as well just
copy this
into Myhello-world/sitemap.xmap:
<map:match pattern="hello.xls">
<map:generate src="content/hello.xml"/>
<map:transform src="style/xsl/page2xls.xsl"/>
<map:serialize type="xls"/>
</map:match>
Point your browser to:
http://your/usual/cocoon/url/and/port/Myhello-world/hello.xls
Or if you want differently named source files, use a wildcard:
<map:match pattern="*.xls">
<map:generate src="content/{1}.xml"/>
<map:transform src="style/xsl/page2xls.xsl"/>
<map:serialize type="xls"/>
</map:match>
... and use widldcards in all the other pipelines too.
Edit hello.xml, or write something similar, and see what difference it
makes to the
various styles of output.
You can look at the XML at any stage either by defining a special
pipeline, or
by defining a Cocoon view. The special pipeline is easier, frankly, if
you are a
beginner.
Matthew Monkan wrote:
> Hey everyone. I'm somewhat new to XML and Cocoon, so please pardon my
> ignorance with the subject. :confused:
>
> I am using Cocoon 2.1.11 on Windows XP. I've been using the Moczar & Aston
> book and have been able to create the static page examples in Chapter 7
> within the downloaded Cocoon folder.
>
> Ultimately, I need to be able to query info from a database and output an
> Excel spreadsheet. Then, I need Cocoon to send out an e-mail with the Excel
> spreadsheet attached.
>
> I basically need help understanding what tools in Cocoon I need to use. Can
> someone verify these or make suggestions? Here are some of the things I
> listed down so far:
>
> For DB to Excel document:
>
> SQLTransformer (queries the database, passing the result back in the SAX
> event stream)
> HSSF Serializer (turns SAX events into Excel spreadsheet document)
>
> To send out the e-mail:
> Cocoon mail block (SendMail Transformer?)
>
> I don't really understand what generator to use. I'm trying to create an
> example of this offline using MySQL. So for example, I have a small table
> created in MySQL; I want to say, stick some data from it in some shape or
> form into Excel, and send out a quick e-mail to a hardcoded e-mail address
> with the Excel document attached.
>
> I also need to be able to run this from the offline CLI.
>
> Can someone help me understand the most optimal flow of information I would
> use. I want to create a sequence diagram to help me visualize this, so if
> someone could help me understand what info is being passed where, that would
> be extremely helpful. All the terminology in Cocoon is killing me, and I
> need help focusing on what is most important to know for these requirements.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org