You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Jasha Joachimsthal <j....@onehippo.com> on 2008/06/02 09:38:59 UTC
RE: Database to Excel Spreadsheet
Hi Matthew,
It looks like you're on the right track.
I assume
<map:match pattern="*.xls">
Will be followed with something like
<map:generate type="file" src="content/{1}.xml" />
So the wildcard is being used.
I don't know what your xml file looks like, but if it's only replacing
one lookup value for your SQL query, try using the JX Generator [1].
Your generator may look like:
<map:generate type="jx" src="content/query.xml">
<map:parameter name="record" value="{1}" />
</map:generate>
[1] http://cocoon.apache.org/2.1/userdocs/jx-generator.html
Regards,
Jasha Joachimsthal
www.onehippo.com
Amsterdam - Hippo B.V. Oosteinde 11 1017 WT Amsterdam +31(0)20-5224466
San Francisco - Hippo USA Inc. 101 H Street, suite Q Petaluma CA
94952-3329 +1 (707) 773-4646
> -----Original Message-----
> From: Matthew Monkan [mailto:matthew@ieee.org]
> Sent: vrijdag 30 mei 2008 18:02
> To: users@cocoon.apache.org
> Subject: Database to Excel Spreadsheet
>
>
> Windows XP / Cocoon 2.1.11 / running inside Cocoon's
> build\webapp folder
>
> As an elementary user of Cocoon, I am still have trouble
> dealing with the sitemap and being confident with it. I'm
> always scared that I've messed up some syntax. My ultimate
> goal is to do some simple selects to an Oracle DB and put it
> on an Excel spreadsheet, which I feel should be really simple.
> But before I get into the XML and XSLT, I simply want to
> design the most optimal sitemap, and at the same time get a
> better concept of what's going on with it. From what I
> understand, I need the file generator, SQLTransformer, XSLT
> transformer, and HSSF Serializer. (If there is anything
> easier that I do not know, feel free to let me know.)
>
> Today I tried writing one from scratch:
>
> <?xml version="1.0"?>
> <map:sitemap xmlns:map="http://apache.org/cocoon/sitemap/1.0">
>
> <map:components>
>
> <map:generators default="file"/>
>
> <map:transformers default="xslt">
> <map:transform type="sql">
> <map:parameter name="use-connection" value="acuworld_test">
> <map:parameter name="show-nr-of-rows" value="true">
> <map:parameter name="clob-encoding" value="UTF-8">
> </map:transform>
> </map:transformers>
>
> <map:readers default="resource" />
>
> <map:serializers default="html">
> <map:serializer
> name="xls"
> src="org.apache.cocoon.serialization.HSSFSerializer"
> locale="us" />
> </map:serializers>
>
> <map:selectors default="browser" />
>
> <map:matchers default="wildcard" />
>
> </map:components>
>
> <map:pipelines>
> <map:pipeline>
>
> <map:match pattern="*.xls">
> <map:generate type="file" src="content/MYCONTENT.xml" />
> <map:transform type="sql">
> <map:parameter name="use-connection"
> value="CONNECTIONNAME" />
> </map:transform>
> <map:transform type="xslt" src="style/MYSTYLE.xsl" />
> <map:serialize type="xls" />
> </map:match>
>
> </map:pipeline>
> </map:pipelines>
>
> </map:sitemap>
>
> Am I on the right track? I just want to have peace of mind
> with the sitemap so I'm not going back and forth between
> different files later trying to debug my little application.
>
> Any help and recommendations are appreciated. :-)
> --
> View this message in context:
> http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564
p17562564.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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
bump
Posted by Matthew Monkan <ma...@ieee.org>.
--
View this message in context: http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564p17656567.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: Database to Excel Spreadsheet
Posted by Matthew Monkan <ma...@ieee.org>.
Thanks for the reply. Actually, I did make a copy-paste mistake. There should
be only one Cell element outputted, which is empty:
<gmr:Cell ValueType="60" Row="1" Col="1"/>
But, I still thought this was unusual. BTW, I edited my original post with
the correct output.
> <rowset>
> <row>
> <test_data>Puffball</test_data>
> </row>
> </rowset>
> Are you sure this is the exact XML output of the SQLTransformer stage?
I tested with an XML serializer after the SQLTransformer, and this is indeed
the output. But maybe the ns is not there because in the initially-read XML
file I just have
<execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
without xmlns:sql
I'll keep tinkering around some more.
- Matt
Jason Johnston-4 wrote:
>
> Matthew Monkan wrote:
>> Alright, I'm really close to getting this application to work but am
>> stumped
>> over one thing.
>>
>> Here is my initial XML file:
>>
>> <?xml version="1.0"?>
>> <execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
>> <query>SELECT test_data from test_table;</query>
>> </execute-query>
>>
>> It gets passed through the SQL transformer and produces this XML:
>>
>> <rowset>
>> <row>
>> <test_data>Puffball</test_data>
>> </row>
>> </rowset>
>
> Are you sure this is the exact XML output of the SQLTransformer stage?
> I'm surprised there is no namespace declaration. The docs indicate this
> output should be in the http://apache.org/cocoon/SQL/2.0 namespace, in
> which case you'll need to adjust your XSLT to target the namespaced nodes.
>
> If you're viewing the XML in Firefox, make sure you do a View Source on
> it; FF's pretty XML tree view does not show namespaces if I recall.
>
>>
>> Okay, so now I just need to select Puffball from test_data and wrap it
>> with
>> XML that the HSSFSerializer likes. This is the XSL I use:
>>
>> <?xml version="1.0"?>
>> <xsl:stylesheet version="1.0"
>> xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
>> <xsl:template match="/">
>>
>> <gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
>> <gmr:SheetNameIndex>
>> <gmr:SheetName>Sheet1</gmr:SheetName>
>> </gmr:SheetNameIndex>
>> <gmr:Sheets>
>> <gmr:Sheet>
>> <gmr:Name>Sheet1</gmr:Name>
>> <gmr:MaxCol>-1</gmr:MaxCol>
>> <gmr:MaxRow>-1</gmr:MaxRow>
>> <gmr:Cells>
>> <gmr:Cell Col="1" Row="1" ValueType="60">
>> <xsl:value-of select="/rowset/row/test_data[1]" />
>> </gmr:Cell>
>> </gmr:Cells>
>> </gmr:Sheet>
>> </gmr:Sheets>
>> </gmr:Workbook>
>>
>> </xsl:template>
>> </xsl:stylesheet>
>>
>> However, after the transformation I get this XML:
>>
>> <?xml version="1.0"?>
>> <xsl:stylesheet version="1.0"
>> xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
>> <xsl:template match="/">
>>
>> <gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
>> <gmr:SheetNameIndex>
>> <gmr:SheetName>Sheet1</gmr:SheetName>
>> </gmr:SheetNameIndex>
>> <gmr:Sheets>
>> <gmr:Sheet>
>> <gmr:Name>Sheet1</gmr:Name>
>> <gmr:MaxCol>-1</gmr:MaxCol>
>> <gmr:MaxRow>-1</gmr:MaxRow>
>> <gmr:Cells>
>> <gmr:Cell Col="1" Row="1" ValueType="60">
>> <gmr:Cell ValueType="60" Row="1" Col="1"/>
>> </gmr:Cell>
>> </gmr:Cells>
>> </gmr:Sheet>
>> </gmr:Sheets>
>> </gmr:Workbook>
>>
>> </xsl:template>
>> </xsl:stylesheet>
>>
>> Notice that the <xsl:value-of select="/rowset/row/test_data[1]" />
>> statement
>> retrieved <gmr:Cell ValueType="60" Row="1" Col="1"/>
>
> That output makes no sense, I can't imagine any reason it would be
> duplicating the element. Is this a direct copy-paste of the output, or
> is it possible there's just one empty gmr:Cell element and the
> duplication was a copy-paste mistake?
>
>>
>> ?!?!
>
> My thoughts exactly. ;)
>
>>
>> Why did it return a copy of the Cell element and not Puffball? :,(
>>
>>
>> - Matt
>>
>>
>> ------------------------------------------
>>
>> Robin Wyles wrote:
>>> I think there is an error in your xsl here:
>>>
>>> <xsl:value-of select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/
>>> test_data/[1]" />
>>>
>>> Should be...
>>>
>>> <xsl:value-of select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/
>>> test_data[1]" />
>>>
>>> You don't use a slash before declaring a predicate.
>>>
>>> Cheers,
>>>
>>> Robin
>>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564p17672302.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: Database to Excel Spreadsheet
Posted by Jason Johnston <jj...@apache.org>.
Matthew Monkan wrote:
> Alright, I'm really close to getting this application to work but am stumped
> over one thing.
>
> Here is my initial XML file:
>
> <?xml version="1.0"?>
> <execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
> <query>SELECT test_data from test_table;</query>
> </execute-query>
>
> It gets passed through the SQL transformer and produces this XML:
>
> <rowset>
> <row>
> <test_data>Puffball</test_data>
> </row>
> </rowset>
Are you sure this is the exact XML output of the SQLTransformer stage?
I'm surprised there is no namespace declaration. The docs indicate this
output should be in the http://apache.org/cocoon/SQL/2.0 namespace, in
which case you'll need to adjust your XSLT to target the namespaced nodes.
If you're viewing the XML in Firefox, make sure you do a View Source on
it; FF's pretty XML tree view does not show namespaces if I recall.
>
> Okay, so now I just need to select Puffball from test_data and wrap it with
> XML that the HSSFSerializer likes. This is the XSL I use:
>
> <?xml version="1.0"?>
> <xsl:stylesheet version="1.0"
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
> <xsl:template match="/">
>
> <gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
> <gmr:SheetNameIndex>
> <gmr:SheetName>Sheet1</gmr:SheetName>
> </gmr:SheetNameIndex>
> <gmr:Sheets>
> <gmr:Sheet>
> <gmr:Name>Sheet1</gmr:Name>
> <gmr:MaxCol>-1</gmr:MaxCol>
> <gmr:MaxRow>-1</gmr:MaxRow>
> <gmr:Cells>
> <gmr:Cell Col="1" Row="1" ValueType="60">
> <xsl:value-of select="/rowset/row/test_data[1]" />
> </gmr:Cell>
> </gmr:Cells>
> </gmr:Sheet>
> </gmr:Sheets>
> </gmr:Workbook>
>
> </xsl:template>
> </xsl:stylesheet>
>
> However, after the transformation I get this XML:
>
> <?xml version="1.0"?>
> <xsl:stylesheet version="1.0"
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
> <xsl:template match="/">
>
> <gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
> <gmr:SheetNameIndex>
> <gmr:SheetName>Sheet1</gmr:SheetName>
> </gmr:SheetNameIndex>
> <gmr:Sheets>
> <gmr:Sheet>
> <gmr:Name>Sheet1</gmr:Name>
> <gmr:MaxCol>-1</gmr:MaxCol>
> <gmr:MaxRow>-1</gmr:MaxRow>
> <gmr:Cells>
> <gmr:Cell Col="1" Row="1" ValueType="60">
> <gmr:Cell ValueType="60" Row="1" Col="1"/>
> </gmr:Cell>
> </gmr:Cells>
> </gmr:Sheet>
> </gmr:Sheets>
> </gmr:Workbook>
>
> </xsl:template>
> </xsl:stylesheet>
>
> Notice that the <xsl:value-of select="/rowset/row/test_data[1]" /> statement
> retrieved <gmr:Cell ValueType="60" Row="1" Col="1"/>
That output makes no sense, I can't imagine any reason it would be
duplicating the element. Is this a direct copy-paste of the output, or
is it possible there's just one empty gmr:Cell element and the
duplication was a copy-paste mistake?
>
> ?!?!
My thoughts exactly. ;)
>
> Why did it return a copy of the Cell element and not Puffball? :,(
>
>
> - Matt
>
>
> ------------------------------------------
>
> Robin Wyles wrote:
>> I think there is an error in your xsl here:
>>
>> <xsl:value-of select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/
>> test_data/[1]" />
>>
>> Should be...
>>
>> <xsl:value-of select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/
>> test_data[1]" />
>>
>> You don't use a slash before declaring a predicate.
>>
>> Cheers,
>>
>> Robin
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Database to Excel Spreadsheet
Posted by Matthew Monkan <ma...@ieee.org>.
Thanks Robin, I did indeed use the predicate wrong.
Alright, I'm really close to getting this application to work but am stumped
over one thing.
Here is my initial XML file:
<?xml version="1.0"?>
<execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
<query>SELECT test_data from test_table;</query>
</execute-query>
It gets passed through the SQL transformer and produces this XML:
<rowset>
<row>
<test_data>Puffball</test_data>
</row>
</rowset>
Okay, so now I just need to select Puffball from test_data and wrap it with
XML that the HSSFSerializer likes. This is the XSL I use:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
<gmr:SheetNameIndex>
<gmr:SheetName>Sheet1</gmr:SheetName>
</gmr:SheetNameIndex>
<gmr:Sheets>
<gmr:Sheet>
<gmr:Name>Sheet1</gmr:Name>
<gmr:MaxCol>-1</gmr:MaxCol>
<gmr:MaxRow>-1</gmr:MaxRow>
<gmr:Cells>
<gmr:Cell Col="1" Row="1" ValueType="60">
<xsl:value-of select="/rowset/row/test_data[1]" />
</gmr:Cell>
</gmr:Cells>
</gmr:Sheet>
</gmr:Sheets>
</gmr:Workbook>
</xsl:template>
</xsl:stylesheet>
However, after the transformation I get this XML:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
<gmr:SheetNameIndex>
<gmr:SheetName>Sheet1</gmr:SheetName>
</gmr:SheetNameIndex>
<gmr:Sheets>
<gmr:Sheet>
<gmr:Name>Sheet1</gmr:Name>
<gmr:MaxCol>-1</gmr:MaxCol>
<gmr:MaxRow>-1</gmr:MaxRow>
<gmr:Cells>
<gmr:Cell Col="1" Row="1" ValueType="60">
<gmr:Cell ValueType="60" Row="1" Col="1"/>
</gmr:Cell>
</gmr:Cells>
</gmr:Sheet>
</gmr:Sheets>
</gmr:Workbook>
</xsl:template>
</xsl:stylesheet>
Notice that the <xsl:value-of select="/rowset/row/test_data[1]" /> statement
retrieved <gmr:Cell ValueType="60" Row="1" Col="1"/>
?!?!
Why did it return a copy of the Cell element and not Puffball? :,(
- Matt
------------------------------------------
Robin Wyles wrote:
>
> I think there is an error in your xsl here:
>
> <xsl:value-of select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/
> test_data/[1]" />
>
> Should be...
>
> <xsl:value-of select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/
> test_data[1]" />
>
> You don't use a slash before declaring a predicate.
>
> Cheers,
>
> Robin
>
--
View this message in context: http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564p17626874.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: Database to Excel Spreadsheet
Posted by Robin Wyles <ro...@robinwyles.com>.
I think there is an error in your xsl here:
<xsl:value-of select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/
test_data/[1]" />
Should be...
<xsl:value-of select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/
test_data[1]" />
You don't use a slash before declaring a predicate.
Cheers,
Robin
On 3 Jun 2008, at 15:34, Matthew Monkan wrote:
>
> I'm trying to select the text Puffball, but I keep getting this error
> message:
>
> javax.xml.transform.TransformerException: A location step was expected
> following the '/' or '//' token.
> Unable to get transformer handler for
> file:///C:/cocoon/build/webapp/eta/style/main.xsl
>
> I don't understand the error message and I couldn't find any help
> browsing
> through the forum.
>
> Here is the xsl document:
>
> <?xml version="1.0"?>
> <xsl:stylesheet version="1.0"
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
> <xsl:template match="/">
>
> <gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
> <gmr:SheetNameIndex>
> <gmr:SheetName>Sheet1</gmr:SheetName>
> </gmr:SheetNameIndex>
> <gmr:Sheets>
> <gmr:Sheet>
> <gmr:Name>Sheet1</gmr:Name>
> <gmr:MaxCol>-1</gmr:MaxCol>
> <gmr:MaxRow>-1</gmr:MaxRow>
> <gmr:Cells>
> <gmr:Cell Col="1" Row="1" ValueType="60">
> <xsl:value-of
> select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/test_data/[1]" />
> </gmr:Cell>
> </gmr:Cells>
> </gmr:Sheet>
> </gmr:Sheets>
> </gmr:Workbook>
>
> The xml document that is supposed to be processed is in my last
> post. Is
> there something wrong with the syntax? I'm thinking there is
> something wrong
> with the xsl: and gmr: groupings, but after trying various
> manipulations to
> the tags I still haven't gotten the application to work.
>
> Any help or insight is appreciated, thanks.
>
>
>
> Hi Matthew,
>
> Good that you found out how to "debug" your pipeline. You'll need
> an extra
> XSL transformation for that. Be aware of the amespace prefix of the
> SQL
> elements.
>
> Regards,
>
> Jasha
>
>
> -----Oorspronkelijk bericht-----
> Van: Matthew Monkan [mailto:matthew@ieee.org]
> Verzonden: ma 2-6-2008 22:04
> Aan: users@cocoon.apache.org
> Onderwerp: RE: Database to Excel Spreadsheet
>
>
> I'm so close! I switched to the XML serializer to see what XML was
> going
> into
> the HSSFserializer. (My produced excel document would crash.)
>
> And here is what returns:
>
> <Workbook>
> <SheetNameIndex>
> <SheetName>Sheet1</SheetName>
> </SheetNameIndex>
> <Sheets>
> <Sheet>
> <Name>Sheet1</Name>
> <MaxCol>-1</MaxCol>
> <MaxRow>-1</MaxRow>
> <Cells>
> <Cell Col="1" Row="1" ValueType="60">
> <rowset nrofrows="1">
> <row>
> <test_data>Puffball</test_data>
> </row>
> </rowset>
> </Cell>
> </Cells>
> </Sheet>
> </Sheets>
> </Workbook>
>
> Obviously the problem is the
>
> <rowset>
> <row>
> <test_data>Puffball</test_data>
> </row>
> </rowset>
>
> which cannot be read by the HSSF serializer. I thought that the SQL
> transformer would just return the text Puffball which would open
> fine. Is
> this easy to fix? Or do I need to transform this XML again?
>
> -Matt
> --
> View this message in context: http://www.nabble.com/Database-to-
> Excel-Spreadsheet-tp17562564p17625045.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: Database to Excel Spreadsheet
Posted by Matthew Monkan <ma...@ieee.org>.
I'm trying to select the text Puffball, but I keep getting this error
message:
javax.xml.transform.TransformerException: A location step was expected
following the '/' or '//' token.
Unable to get transformer handler for
file:///C:/cocoon/build/webapp/eta/style/main.xsl
I don't understand the error message and I couldn't find any help browsing
through the forum.
Here is the xsl document:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
<gmr:SheetNameIndex>
<gmr:SheetName>Sheet1</gmr:SheetName>
</gmr:SheetNameIndex>
<gmr:Sheets>
<gmr:Sheet>
<gmr:Name>Sheet1</gmr:Name>
<gmr:MaxCol>-1</gmr:MaxCol>
<gmr:MaxRow>-1</gmr:MaxRow>
<gmr:Cells>
<gmr:Cell Col="1" Row="1" ValueType="60">
<xsl:value-of
select="/Workbook/Sheets/Sheet/Cells/Cell/rowset/row/test_data/[1]" />
</gmr:Cell>
</gmr:Cells>
</gmr:Sheet>
</gmr:Sheets>
</gmr:Workbook>
The xml document that is supposed to be processed is in my last post. Is
there something wrong with the syntax? I'm thinking there is something wrong
with the xsl: and gmr: groupings, but after trying various manipulations to
the tags I still haven't gotten the application to work.
Any help or insight is appreciated, thanks.
Hi Matthew,
Good that you found out how to "debug" your pipeline. You'll need an extra
XSL transformation for that. Be aware of the amespace prefix of the SQL
elements.
Regards,
Jasha
-----Oorspronkelijk bericht-----
Van: Matthew Monkan [mailto:matthew@ieee.org]
Verzonden: ma 2-6-2008 22:04
Aan: users@cocoon.apache.org
Onderwerp: RE: Database to Excel Spreadsheet
I'm so close! I switched to the XML serializer to see what XML was going
into
the HSSFserializer. (My produced excel document would crash.)
And here is what returns:
<Workbook>
<SheetNameIndex>
<SheetName>Sheet1</SheetName>
</SheetNameIndex>
<Sheets>
<Sheet>
<Name>Sheet1</Name>
<MaxCol>-1</MaxCol>
<MaxRow>-1</MaxRow>
<Cells>
<Cell Col="1" Row="1" ValueType="60">
<rowset nrofrows="1">
<row>
<test_data>Puffball</test_data>
</row>
</rowset>
</Cell>
</Cells>
</Sheet>
</Sheets>
</Workbook>
Obviously the problem is the
<rowset>
<row>
<test_data>Puffball</test_data>
</row>
</rowset>
which cannot be read by the HSSF serializer. I thought that the SQL
transformer would just return the text Puffball which would open fine. Is
this easy to fix? Or do I need to transform this XML again?
-Matt
--
View this message in context: http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564p17625045.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: Database to Excel Spreadsheet
Posted by Jasha Joachimsthal <j....@onehippo.com>.
Hi Matthew,
Good that you found out how to "debug" your pipeline. You'll need an extra XSL transformation for that. Be aware of the amespace prefix of the SQL elements.
Regards,
Jasha
-----Oorspronkelijk bericht-----
Van: Matthew Monkan [mailto:matthew@ieee.org]
Verzonden: ma 2-6-2008 22:04
Aan: users@cocoon.apache.org
Onderwerp: RE: Database to Excel Spreadsheet
I'm so close! I switched to the XML serializer to see what XML was going into
the HSSFserializer. (My produced excel document would crash.)
And here is what returns:
<Workbook>
<SheetNameIndex>
<SheetName>Sheet1</SheetName>
</SheetNameIndex>
<Sheets>
<Sheet>
<Name>Sheet1</Name>
<MaxCol>-1</MaxCol>
<MaxRow>-1</MaxRow>
<Cells>
<Cell Col="1" Row="1" ValueType="60">
<rowset nrofrows="1">
<row>
<test_data>Puffball</test_data>
</row>
</rowset>
</Cell>
</Cells>
</Sheet>
</Sheets>
</Workbook>
Obviously the problem is the
<rowset>
<row>
<test_data>Puffball</test_data>
</row>
</rowset>
which cannot be read by the HSSF serializer. I thought that the SQL
transformer would just return the text Puffball which would open fine. Is
this easy to fix? Or do I need to transform this XML again?
-Matt
Matthew Monkan wrote:
>
> I am having a difficult time understanding what's going on inside my
> sitemap. I made this XML file which is what I assume I would want passed
> through the HSSF serializer:
>
> main.xml:
>
> <?xml version="1.0"?>
>
> <Workbook xmlns="http://www.gnome.org/gnumeric/v7">
> <SheetNameIndex>
> <SheetName>Sheet1</SheetName>
> </SheetNameIndex>
> <Sheets>
> <Sheet>
> <Name>Sheet1</Name>
> <MaxCol>-1</MaxCol>
> <MaxRow>-1</MaxRow>
> <Cells>
> <Cell Col="1" Row="1" ValueType="60">
> <execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
> <query>select * from test_table</query>
> </execute-query>
> </Cell>
> </Cells>
> </Sheet>
> </Sheets>
> </Workbook>
>
> Should that work? I simply want to make a sample excel document that has
> one cell of data queried from my database. Here is my sitemap:
>
> <map:match pattern="*.xls">
> <map:generate type="file" src="content/main.xml" />
> <map:transform type="sql">
> <map:parameter name="use-connection" value="eta" />
> </map:transform>
> <map:serialize type="xls" />
> </map:match>
>
> Here is what I thought happens: When I ask for a .xls file, the file
> generator reads the main.xml file, the sqltransformer executes the queries
> in the xml and the HSSFserializer creates an excel file out of the altered
> xml document. But it's not working; I think I'm really confused. Is that
> how the pipeline works? Or is it nonsensical? (I wouldn't know at this
> point.) :-(
>
> - Matt
>
>
>
> Jasha Joachimsthal-2 wrote:
>>
>> Hi Matthew,
>>
>>
>> It looks like you're on the right track.
>> I assume
>> <map:match pattern="*.xls">
>> Will be followed with something like
>> <map:generate type="file" src="content/{1}.xml" />
>>
>> So the wildcard is being used.
>> I don't know what your xml file looks like, but if it's only replacing
>> one lookup value for your SQL query, try using the JX Generator [1].
>>
>> Your generator may look like:
>> <map:generate type="jx" src="content/query.xml">
>> <map:parameter name="record" value="{1}" />
>> </map:generate>
>>
>> [1] http://cocoon.apache.org/2.1/userdocs/jx-generator.html
>>
>> Regards,
>>
>> Jasha Joachimsthal
>>
>> www.onehippo.com
>> Amsterdam - Hippo B.V. Oosteinde 11 1017 WT Amsterdam +31(0)20-5224466
>> San Francisco - Hippo USA Inc. 101 H Street, suite Q Petaluma CA
>> 94952-3329 +1 (707) 773-4646
>>
>>
>>
>>> -----Original Message-----
>>> From: Matthew Monkan [mailto:matthew@ieee.org]
>>> Sent: vrijdag 30 mei 2008 18:02
>>> To: users@cocoon.apache.org
>>> Subject: Database to Excel Spreadsheet
>>>
>>>
>>> Windows XP / Cocoon 2.1.11 / running inside Cocoon's
>>> build\webapp folder
>>>
>>> As an elementary user of Cocoon, I am still have trouble
>>> dealing with the sitemap and being confident with it. I'm
>>> always scared that I've messed up some syntax. My ultimate
>>> goal is to do some simple selects to an Oracle DB and put it
>>> on an Excel spreadsheet, which I feel should be really simple.
>>> But before I get into the XML and XSLT, I simply want to
>>> design the most optimal sitemap, and at the same time get a
>>> better concept of what's going on with it. From what I
>>> understand, I need the file generator, SQLTransformer, XSLT
>>> transformer, and HSSF Serializer. (If there is anything
>>> easier that I do not know, feel free to let me know.)
>>>
>>> Today I tried writing one from scratch:
>>>
>>> <?xml version="1.0"?>
>>> <map:sitemap xmlns:map="http://apache.org/cocoon/sitemap/1.0">
>>>
>>> <map:components>
>>>
>>> <map:generators default="file"/>
>>>
>>> <map:transformers default="xslt">
>>> <map:transform type="sql">
>>> <map:parameter name="use-connection" value="acuworld_test">
>>> <map:parameter name="show-nr-of-rows" value="true">
>>> <map:parameter name="clob-encoding" value="UTF-8">
>>> </map:transform>
>>> </map:transformers>
>>>
>>> <map:readers default="resource" />
>>>
>>> <map:serializers default="html">
>>> <map:serializer
>>> name="xls"
>>> src="org.apache.cocoon.serialization.HSSFSerializer"
>>> locale="us" />
>>> </map:serializers>
>>>
>>> <map:selectors default="browser" />
>>>
>>> <map:matchers default="wildcard" />
>>>
>>> </map:components>
>>>
>>> <map:pipelines>
>>> <map:pipeline>
>>>
>>> <map:match pattern="*.xls">
>>> <map:generate type="file" src="content/MYCONTENT.xml" />
>>> <map:transform type="sql">
>>> <map:parameter name="use-connection"
>>> value="CONNECTIONNAME" />
>>> </map:transform>
>>> <map:transform type="xslt" src="style/MYSTYLE.xsl" />
>>> <map:serialize type="xls" />
>>> </map:match>
>>>
>>> </map:pipeline>
>>> </map:pipelines>
>>>
>>> </map:sitemap>
>>>
>>> Am I on the right track? I just want to have peace of mind
>>> with the sitemap so I'm not going back and forth between
>>> different files later trying to debug my little application.
>>>
>>> Any help and recommendations are appreciated. :-)
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564
>> p17562564.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
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
>> For additional commands, e-mail: users-help@cocoon.apache.org
>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564p17609638.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: Database to Excel Spreadsheet
Posted by Matthew Monkan <ma...@ieee.org>.
I'm so close! I switched to the XML serializer to see what XML was going into
the HSSFserializer. (My produced excel document would crash.)
And here is what returns:
<Workbook>
<SheetNameIndex>
<SheetName>Sheet1</SheetName>
</SheetNameIndex>
<Sheets>
<Sheet>
<Name>Sheet1</Name>
<MaxCol>-1</MaxCol>
<MaxRow>-1</MaxRow>
<Cells>
<Cell Col="1" Row="1" ValueType="60">
<rowset nrofrows="1">
<row>
<test_data>Puffball</test_data>
</row>
</rowset>
</Cell>
</Cells>
</Sheet>
</Sheets>
</Workbook>
Obviously the problem is the
<rowset>
<row>
<test_data>Puffball</test_data>
</row>
</rowset>
which cannot be read by the HSSF serializer. I thought that the SQL
transformer would just return the text Puffball which would open fine. Is
this easy to fix? Or do I need to transform this XML again?
-Matt
Matthew Monkan wrote:
>
> I am having a difficult time understanding what's going on inside my
> sitemap. I made this XML file which is what I assume I would want passed
> through the HSSF serializer:
>
> main.xml:
>
> <?xml version="1.0"?>
>
> <Workbook xmlns="http://www.gnome.org/gnumeric/v7">
> <SheetNameIndex>
> <SheetName>Sheet1</SheetName>
> </SheetNameIndex>
> <Sheets>
> <Sheet>
> <Name>Sheet1</Name>
> <MaxCol>-1</MaxCol>
> <MaxRow>-1</MaxRow>
> <Cells>
> <Cell Col="1" Row="1" ValueType="60">
> <execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
> <query>select * from test_table</query>
> </execute-query>
> </Cell>
> </Cells>
> </Sheet>
> </Sheets>
> </Workbook>
>
> Should that work? I simply want to make a sample excel document that has
> one cell of data queried from my database. Here is my sitemap:
>
> <map:match pattern="*.xls">
> <map:generate type="file" src="content/main.xml" />
> <map:transform type="sql">
> <map:parameter name="use-connection" value="eta" />
> </map:transform>
> <map:serialize type="xls" />
> </map:match>
>
> Here is what I thought happens: When I ask for a .xls file, the file
> generator reads the main.xml file, the sqltransformer executes the queries
> in the xml and the HSSFserializer creates an excel file out of the altered
> xml document. But it's not working; I think I'm really confused. Is that
> how the pipeline works? Or is it nonsensical? (I wouldn't know at this
> point.) :-(
>
> - Matt
>
>
>
> Jasha Joachimsthal-2 wrote:
>>
>> Hi Matthew,
>>
>>
>> It looks like you're on the right track.
>> I assume
>> <map:match pattern="*.xls">
>> Will be followed with something like
>> <map:generate type="file" src="content/{1}.xml" />
>>
>> So the wildcard is being used.
>> I don't know what your xml file looks like, but if it's only replacing
>> one lookup value for your SQL query, try using the JX Generator [1].
>>
>> Your generator may look like:
>> <map:generate type="jx" src="content/query.xml">
>> <map:parameter name="record" value="{1}" />
>> </map:generate>
>>
>> [1] http://cocoon.apache.org/2.1/userdocs/jx-generator.html
>>
>> Regards,
>>
>> Jasha Joachimsthal
>>
>> www.onehippo.com
>> Amsterdam - Hippo B.V. Oosteinde 11 1017 WT Amsterdam +31(0)20-5224466
>> San Francisco - Hippo USA Inc. 101 H Street, suite Q Petaluma CA
>> 94952-3329 +1 (707) 773-4646
>>
>>
>>
>>> -----Original Message-----
>>> From: Matthew Monkan [mailto:matthew@ieee.org]
>>> Sent: vrijdag 30 mei 2008 18:02
>>> To: users@cocoon.apache.org
>>> Subject: Database to Excel Spreadsheet
>>>
>>>
>>> Windows XP / Cocoon 2.1.11 / running inside Cocoon's
>>> build\webapp folder
>>>
>>> As an elementary user of Cocoon, I am still have trouble
>>> dealing with the sitemap and being confident with it. I'm
>>> always scared that I've messed up some syntax. My ultimate
>>> goal is to do some simple selects to an Oracle DB and put it
>>> on an Excel spreadsheet, which I feel should be really simple.
>>> But before I get into the XML and XSLT, I simply want to
>>> design the most optimal sitemap, and at the same time get a
>>> better concept of what's going on with it. From what I
>>> understand, I need the file generator, SQLTransformer, XSLT
>>> transformer, and HSSF Serializer. (If there is anything
>>> easier that I do not know, feel free to let me know.)
>>>
>>> Today I tried writing one from scratch:
>>>
>>> <?xml version="1.0"?>
>>> <map:sitemap xmlns:map="http://apache.org/cocoon/sitemap/1.0">
>>>
>>> <map:components>
>>>
>>> <map:generators default="file"/>
>>>
>>> <map:transformers default="xslt">
>>> <map:transform type="sql">
>>> <map:parameter name="use-connection" value="acuworld_test">
>>> <map:parameter name="show-nr-of-rows" value="true">
>>> <map:parameter name="clob-encoding" value="UTF-8">
>>> </map:transform>
>>> </map:transformers>
>>>
>>> <map:readers default="resource" />
>>>
>>> <map:serializers default="html">
>>> <map:serializer
>>> name="xls"
>>> src="org.apache.cocoon.serialization.HSSFSerializer"
>>> locale="us" />
>>> </map:serializers>
>>>
>>> <map:selectors default="browser" />
>>>
>>> <map:matchers default="wildcard" />
>>>
>>> </map:components>
>>>
>>> <map:pipelines>
>>> <map:pipeline>
>>>
>>> <map:match pattern="*.xls">
>>> <map:generate type="file" src="content/MYCONTENT.xml" />
>>> <map:transform type="sql">
>>> <map:parameter name="use-connection"
>>> value="CONNECTIONNAME" />
>>> </map:transform>
>>> <map:transform type="xslt" src="style/MYSTYLE.xsl" />
>>> <map:serialize type="xls" />
>>> </map:match>
>>>
>>> </map:pipeline>
>>> </map:pipelines>
>>>
>>> </map:sitemap>
>>>
>>> Am I on the right track? I just want to have peace of mind
>>> with the sitemap so I'm not going back and forth between
>>> different files later trying to debug my little application.
>>>
>>> Any help and recommendations are appreciated. :-)
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564
>> p17562564.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
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
>> For additional commands, e-mail: users-help@cocoon.apache.org
>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564p17609638.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: Database to Excel Spreadsheet
Posted by Matthew Monkan <ma...@ieee.org>.
I am having a difficult time understanding what's going on inside my sitemap.
I made this XML file which is what I assume I would want passed through the
HSSF serializer:
main.xml:
<?xml version="1.0"?>
<Workbook xmlns="http://www.gnome.org/gnumeric/v7">
<SheetNameIndex>
<SheetName>Sheet1</SheetName>
</SheetNameIndex>
<Sheets>
<Sheet>
<Name>Sheet1</Name>
<MaxCol>-1</MaxCol>
<MaxRow>-1</MaxRow>
<Cells>
<Cell Col="1" Row="1" ValueType="60">
<execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
<query>select * from test_table</query>
</execute-query>
</Cell>
</Cells>
</Sheet>
</Sheets>
</Workbook>
Should that work? I simply want to make a sample excel document that has one
cell of data queried from my database. Here is my sitemap:
<map:match pattern="*.xls">
<map:generate type="file" src="content/main.xml" />
<map:transform type="sql">
<map:parameter name="use-connection" value="eta" />
</map:transform>
<map:serialize type="xls" />
</map:match>
Here is what I thought happens: When I ask for a .xls file, the file
generator reads the main.xml file, the sqltransformer executes the queries
in the xml and the HSSFserializer creates an excel file out of the altered
xml document. But it's not working; I think I'm really confused. Is that how
the pipeline works? Or is it nonsensical? (I wouldn't know at this point.)
:-(
- Matt
Jasha Joachimsthal-2 wrote:
>
> Hi Matthew,
>
>
> It looks like you're on the right track.
> I assume
> <map:match pattern="*.xls">
> Will be followed with something like
> <map:generate type="file" src="content/{1}.xml" />
>
> So the wildcard is being used.
> I don't know what your xml file looks like, but if it's only replacing
> one lookup value for your SQL query, try using the JX Generator [1].
>
> Your generator may look like:
> <map:generate type="jx" src="content/query.xml">
> <map:parameter name="record" value="{1}" />
> </map:generate>
>
> [1] http://cocoon.apache.org/2.1/userdocs/jx-generator.html
>
> Regards,
>
> Jasha Joachimsthal
>
> www.onehippo.com
> Amsterdam - Hippo B.V. Oosteinde 11 1017 WT Amsterdam +31(0)20-5224466
> San Francisco - Hippo USA Inc. 101 H Street, suite Q Petaluma CA
> 94952-3329 +1 (707) 773-4646
>
>
>
>> -----Original Message-----
>> From: Matthew Monkan [mailto:matthew@ieee.org]
>> Sent: vrijdag 30 mei 2008 18:02
>> To: users@cocoon.apache.org
>> Subject: Database to Excel Spreadsheet
>>
>>
>> Windows XP / Cocoon 2.1.11 / running inside Cocoon's
>> build\webapp folder
>>
>> As an elementary user of Cocoon, I am still have trouble
>> dealing with the sitemap and being confident with it. I'm
>> always scared that I've messed up some syntax. My ultimate
>> goal is to do some simple selects to an Oracle DB and put it
>> on an Excel spreadsheet, which I feel should be really simple.
>> But before I get into the XML and XSLT, I simply want to
>> design the most optimal sitemap, and at the same time get a
>> better concept of what's going on with it. From what I
>> understand, I need the file generator, SQLTransformer, XSLT
>> transformer, and HSSF Serializer. (If there is anything
>> easier that I do not know, feel free to let me know.)
>>
>> Today I tried writing one from scratch:
>>
>> <?xml version="1.0"?>
>> <map:sitemap xmlns:map="http://apache.org/cocoon/sitemap/1.0">
>>
>> <map:components>
>>
>> <map:generators default="file"/>
>>
>> <map:transformers default="xslt">
>> <map:transform type="sql">
>> <map:parameter name="use-connection" value="acuworld_test">
>> <map:parameter name="show-nr-of-rows" value="true">
>> <map:parameter name="clob-encoding" value="UTF-8">
>> </map:transform>
>> </map:transformers>
>>
>> <map:readers default="resource" />
>>
>> <map:serializers default="html">
>> <map:serializer
>> name="xls"
>> src="org.apache.cocoon.serialization.HSSFSerializer"
>> locale="us" />
>> </map:serializers>
>>
>> <map:selectors default="browser" />
>>
>> <map:matchers default="wildcard" />
>>
>> </map:components>
>>
>> <map:pipelines>
>> <map:pipeline>
>>
>> <map:match pattern="*.xls">
>> <map:generate type="file" src="content/MYCONTENT.xml" />
>> <map:transform type="sql">
>> <map:parameter name="use-connection"
>> value="CONNECTIONNAME" />
>> </map:transform>
>> <map:transform type="xslt" src="style/MYSTYLE.xsl" />
>> <map:serialize type="xls" />
>> </map:match>
>>
>> </map:pipeline>
>> </map:pipelines>
>>
>> </map:sitemap>
>>
>> Am I on the right track? I just want to have peace of mind
>> with the sitemap so I'm not going back and forth between
>> different files later trying to debug my little application.
>>
>> Any help and recommendations are appreciated. :-)
>> --
>> View this message in context:
>> http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564
> p17562564.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
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Database-to-Excel-Spreadsheet-tp17562564p17608055.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