Orbeon Forms community mailing list

to-xls-converter

Classic

List

Threaded

7 messages Options

jeff.diademi

to-xls-converter

Hi,

I played with the export to excel employee example and I realised that there is a limitation within excel.

<employees>
<xsl:apply-templates select="/employees/*[23 > position()]"/>
</employees>

I did modify the number of employee returned and when opening the generated excel file a error message say: "No more custom number formats can be added".

Where do this limitation come from? It ther's a way to generate bigger excel file?

Thanks!

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Alessandro Vernet

Re: to-xls-converter

Administrator

Hi Jeff,

You have hit there a limitation of the current implementation for the
Excel processor. In the generated Excel file, as a cell is populated
with the value of node, the Excel processor stores information about
the node in the cell format. Unfortunately, Excel has a pretty drastic
limitation on the number of different formats you can have in one
file. This limits the amount of data you can export to Excel.

In the future, we need to figure out a better way to store this
information in the Excel file and modify the Excel processor
accordingly.

Alex

On 10/14/05, [hidden email] <[hidden email]> wrote:

> Hi,
>
> I played with the export to excel employee example and I realised that there is a limitation within excel.
>
> <employees>
> <xsl:apply-templates select="/employees/*[23 > position()]"/>
> </employees>
>
> I did modify the number of employee returned and when opening the generated excel file a error message say: "No more custom number formats can be added".
>
> Where do this limitation come from? It ther's a way to generate bigger excel file?
>
> Thanks!
>
>
>
>
>
> --
> You receive this message as a subscriber of the [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
>
>
>

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet

LUX Diademi Jeff

RE: to-xls-converter

In reply to this post by jeff.diademi

Hi Alex,
Thanks for your reply.
I had a look at the source XLSSerializer and noticed that a cell format
is created for each cell, i.e:

employees/employee[1]/employee-id|employees/employee-id
employees/employee[2]/employee-id|employees/employee-id
.
.

I found in MS article that the limitation comes from the custom format
in excel that is limited to 4K.
But If I just want to display data in excel, I do not understand why
it's necessary to have a format for each cell?

Regards,
Jeff.

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of
Alessandro Vernet
Sent: Friday, October 14, 2005 9:59 PM
To: [hidden email]
Subject: Re: [ops-users] to-xls-converter

Hi Jeff,

You have hit there a limitation of the current implementation for the
Excel processor. In the generated Excel file, as a cell is populated
with the value of node, the Excel processor stores information about
the node in the cell format. Unfortunately, Excel has a pretty drastic
limitation on the number of different formats you can have in one
file. This limits the amount of data you can export to Excel.

In the future, we need to figure out a better way to store this
information in the Excel file and modify the Excel processor
accordingly.

Alex

On 10/14/05, [hidden email] <[hidden email]> wrote:
> Hi,
>
> I played with the export to excel employee example and I realised that
there is a limitation within excel.
>
> <employees>
> <xsl:apply-templates select="/employees/*[23 > position()]"/>
> </employees>
>
> I did modify the number of employee returned and when opening the
generated excel file a error message say: "No more custom number formats
can be added".
>
> Where do this limitation come from? It ther's a way to generate bigger
excel file?
>
> Thanks!
>
>
>
>
>
> --
> You receive this message as a subscriber of the
[hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page:
http://www.objectweb.org/wws
>
>
>

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Alessandro Vernet

Re: to-xls-converter

Administrator

Hi Jeff,

What you are suggesting makes sense. Right now one would generate an
Excel file with something like (example from the documentation):

<p:processor name="oxf:xls-serializer">
<p:input name="config">
<config template="oxf:/excel/template.xls"/>
</p:input>
<p:input name="data">
<currency>
<value1>10</value1>
<value2>20</value2>
<value3>30</value3>
</currency>
</p:input>
</p:processor>

You could add an attribute on the "config" element to tell the
transformer not to generate those special formats in the Excel file,
for instance: <config readonly="true" template="..."/>. Then you won't
be able to read the generated file with the XLS Generator, but you
only care about export, that will do the trick!

If you or anybody else is interested in modifying the code to do this,
please go ahead, send us your modification, and we'll be happy to
consider it for integration into the code.

Alex

On 10/17/05, LUX Diademi Jeff <[hidden email]> wrote:

>
> Hi Alex,
> Thanks for your reply.
> I had a look at the source XLSSerializer and noticed that a cell format
> is created for each cell, i.e:
>
> employees/employee[1]/employee-id|employees/employee-id
> employees/employee[2]/employee-id|employees/employee-id
> .
> .
>
> I found in MS article that the limitation comes from the custom format
> in excel that is limited to 4K.
> But If I just want to display data in excel, I do not understand why
> it's necessary to have a format for each cell?
>
>
> Regards,
> Jeff.
>
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On Behalf Of
> Alessandro Vernet
> Sent: Friday, October 14, 2005 9:59 PM
> To: [hidden email]
> Subject: Re: [ops-users] to-xls-converter
>
> Hi Jeff,
>
> You have hit there a limitation of the current implementation for the
> Excel processor. In the generated Excel file, as a cell is populated
> with the value of node, the Excel processor stores information about
> the node in the cell format. Unfortunately, Excel has a pretty drastic
> limitation on the number of different formats you can have in one
> file. This limits the amount of data you can export to Excel.
>
> In the future, we need to figure out a better way to store this
> information in the Excel file and modify the Excel processor
> accordingly.
>
> Alex
>
> On 10/14/05, [hidden email] <[hidden email]> wrote:
> > Hi,
> >
> > I played with the export to excel employee example and I realised that
> there is a limitation within excel.
> >
> > <employees>
> > <xsl:apply-templates select="/employees/*[23 > position()]"/>
> > </employees>
> >
> > I did modify the number of employee returned and when opening the
> generated excel file a error message say: "No more custom number formats
> can be added".
> >
> > Where do this limitation come from? It ther's a way to generate bigger
> excel file?
> >
> > Thanks!
> >
> >
> >
> >
> >
> > --
> > You receive this message as a subscriber of the
> [hidden email] mailing list.
> > To unsubscribe: mailto:[hidden email]
> > For general help: mailto:[hidden email]?subject=help
> > ObjectWeb mailing lists service home page:
> http://www.objectweb.org/wws
> >
> >
> >
>
>
>
>
>
> --
> You receive this message as a subscriber of the [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
>
>
>

--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet

LUX Diademi Jeff

RE: to-xls-converter

In reply to this post by jeff.diademi

ok,
I have some small change to include a readonly attribute
(to-xls-converter-config.rng) and to handle it in XLSSerializer.java
It seems to work.

XLSSerializar.java changes:

boolean readOnly =
configDocument.getRootElement().attributeValue("readonly").contentEquals
("true");

if(!readOnly)

cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat));
else {
// Set values in cells
}

if(!readOnly) {
XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() {
....
}

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of
Alessandro Vernet
Sent: Tuesday, October 18, 2005 12:21 AM
To: [hidden email]
Subject: Re: [ops-users] to-xls-converter

Hi Jeff,

What you are suggesting makes sense. Right now one would generate an
Excel file with something like (example from the documentation):

<p:processor name="oxf:xls-serializer">
<p:input name="config">
<config template="oxf:/excel/template.xls"/>
</p:input>
<p:input name="data">
<currency>
<value1>10</value1>
<value2>20</value2>
<value3>30</value3>
</currency>
</p:input>
</p:processor>

You could add an attribute on the "config" element to tell the
transformer not to generate those special formats in the Excel file,
for instance: <config readonly="true" template="..."/>. Then you won't
be able to read the generated file with the XLS Generator, but you
only care about export, that will do the trick!

If you or anybody else is interested in modifying the code to do this,
please go ahead, send us your modification, and we'll be happy to
consider it for integration into the code.

Alex

On 10/17/05, LUX Diademi Jeff <[hidden email]> wrote:
>
> Hi Alex,
> Thanks for your reply.
> I had a look at the source XLSSerializer and noticed that a cell
format

> is created for each cell, i.e:
>
> employees/employee[1]/employee-id|employees/employee-id
> employees/employee[2]/employee-id|employees/employee-id
> .
> .
>
> I found in MS article that the limitation comes from the custom format
> in excel that is limited to 4K.
> But If I just want to display data in excel, I do not understand why
> it's necessary to have a format for each cell?
>
>
> Regards,
> Jeff.
>
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On Behalf Of
> Alessandro Vernet
> Sent: Friday, October 14, 2005 9:59 PM
> To: [hidden email]
> Subject: Re: [ops-users] to-xls-converter
>
> Hi Jeff,
>
> You have hit there a limitation of the current implementation for the
> Excel processor. In the generated Excel file, as a cell is populated
> with the value of node, the Excel processor stores information about
> the node in the cell format. Unfortunately, Excel has a pretty drastic
> limitation on the number of different formats you can have in one
> file. This limits the amount of data you can export to Excel.
>
> In the future, we need to figure out a better way to store this
> information in the Excel file and modify the Excel processor
> accordingly.
>
> Alex
>
> On 10/14/05, [hidden email] <[hidden email]> wrote:
> > Hi,
> >
> > I played with the export to excel employee example and I realised

that
> there is a limitation within excel.
> >
> > <employees>
> > <xsl:apply-templates select="/employees/*[23 > position()]"/>
> > </employees>
> >
> > I did modify the number of employee returned and when opening the
> generated excel file a error message say: "No more custom number

formats
> can be added".
> >
> > Where do this limitation come from? It ther's a way to generate
bigger

> excel file?
> >
> > Thanks!
> >
> >
> >
> >
> >
> > --
> > You receive this message as a subscriber of the
> [hidden email] mailing list.
> > To unsubscribe: mailto:[hidden email]
> > For general help: mailto:[hidden email]?subject=help
> > ObjectWeb mailing lists service home page:
> http://www.objectweb.org/wws
> >
> >
> >
>
>
>
>
>
> --
> You receive this message as a subscriber of the

[hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page:

http://www.objectweb.org/wws
>
>
>

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Alessandro Vernet

Re: to-xls-converter

Administrator

Hi David,

Thank you for the contribution! While the solution I suggested and
that you implemented works, I think we need something better. See
below a description of what I think is a better solution, and let me
know what you think. Implementing this is a little bit more involved,
but if you want to give it a shot we'll be here to help if needed.

1) How it works now: The Excel template contains in the cell format an
XPath expression to the node to be used to populate this cell. This
expression can be relative, as in:
General;"price"

This is used in conjunction with a <repeat-row> in the configuration, as in:
<repeat-row row-num="2" for-each="/products/product"/>

In the generated Excel file, the format will combine the XPath
expression in the for-each with the expression in the template cell.
E.g.:
General;"/products/product[1]/price"

2) The main problem: Potentially a large number of different formats
will be generated. Excel imposes a limit on the number of different
number formats. If that number is exceeded, the generated Excel file
won't be readable. Also, cell formats are not convenient to edit in
Excel. Furthermore, the place in the format used for the XPath
expression is interpreted by Excel as the format for negative numbers.
So if the cell is populated with a negative number, the XPath
expression will be displayed instead of the number (!). For all those
reasons, using cell formats is not a good idea.

3) The solution: Instead of using cell formats we use comments. A
comment can contain one or more instructions. Those are parsed and
recognized when the template is read:

a) xpath: /a/b is equivalent to the current General;"/a/b" cell format.
b) repeat-row: /products/product on the first cell of a row is
equivalent to the current <repeat-row for-each="/products/product"> in
the config. Then some cells on the same row are expected to contain
relative XPath expressions (xpath: price).

See the attached screenshot for an example.

In the generated Excel file, the comments with commands are removed.
If there is an allow-import="true" attribute on the <config> element,
then for each sheet in the Excel file, a copy is created. The copy is
hidden and the comments are kept in the copy. Furthermore, when lines
are duplicated in the original sheet because of a "repeat-row", they
are also duplicated in the hidden copy. The sheet containing the data
combined with the hidden sheet containing the comments allows the XML
file to be recreated based on the Excel file.

I have created a bug to track this (link below). Unless this is
sponsored or someone contributes an implementation we are not planning
to implement this for OPS 3.0.

http://forge.objectweb.org/tracker/index.php?func=detail&aid=304178&group_id=168&atid=350207

Alex

On 10/18/05, LUX Diademi Jeff <[hidden email]> wrote:

>
>
> ok,
> I have some small change to include a readonly attribute
> (to-xls-converter-config.rng) and to handle it in XLSSerializer.java
> It seems to work.
>
> XLSSerializar.java changes:
>
> boolean readOnly =
> configDocument.getRootElement().attributeValue("readonly").contentEquals
> ("true");
>
> if(!readOnly)
>
> cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat));
> else {
> // Set values in cells
> }
>
>
> if(!readOnly) {
> XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() {
> ....
> }
>
>
>
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On Behalf Of
> Alessandro Vernet
> Sent: Tuesday, October 18, 2005 12:21 AM
> To: [hidden email]
> Subject: Re: [ops-users] to-xls-converter
>
> Hi Jeff,
>
> What you are suggesting makes sense. Right now one would generate an
> Excel file with something like (example from the documentation):
>
> <p:processor name="oxf:xls-serializer">
> <p:input name="config">
> <config template="oxf:/excel/template.xls"/>
> </p:input>
> <p:input name="data">
> <currency>
> <value1>10</value1>
> <value2>20</value2>
> <value3>30</value3>
> </currency>
> </p:input>
> </p:processor>
>
> You could add an attribute on the "config" element to tell the
> transformer not to generate those special formats in the Excel file,
> for instance: <config readonly="true" template="..."/>. Then you won't
> be able to read the generated file with the XLS Generator, but you
> only care about export, that will do the trick!
>
> If you or anybody else is interested in modifying the code to do this,
> please go ahead, send us your modification, and we'll be happy to
> consider it for integration into the code.
>
> Alex
>
> On 10/17/05, LUX Diademi Jeff <[hidden email]> wrote:
> >
> > Hi Alex,
> > Thanks for your reply.
> > I had a look at the source XLSSerializer and noticed that a cell
> format
> > is created for each cell, i.e:
> >
> > employees/employee[1]/employee-id|employees/employee-id
> > employees/employee[2]/employee-id|employees/employee-id
> > .
> > .
> >
> > I found in MS article that the limitation comes from the custom format
> > in excel that is limited to 4K.
> > But If I just want to display data in excel, I do not understand why
> > it's necessary to have a format for each cell?
> >
> >
> > Regards,
> > Jeff.
> >
> >
> > -----Original Message-----
> > From: [hidden email] [mailto:[hidden email]] On Behalf Of
> > Alessandro Vernet
> > Sent: Friday, October 14, 2005 9:59 PM
> > To: [hidden email]
> > Subject: Re: [ops-users] to-xls-converter
> >
> > Hi Jeff,
> >
> > You have hit there a limitation of the current implementation for the
> > Excel processor. In the generated Excel file, as a cell is populated
> > with the value of node, the Excel processor stores information about
> > the node in the cell format. Unfortunately, Excel has a pretty drastic
> > limitation on the number of different formats you can have in one
> > file. This limits the amount of data you can export to Excel.
> >
> > In the future, we need to figure out a better way to store this
> > information in the Excel file and modify the Excel processor
> > accordingly.
> >
> > Alex
> >
> > On 10/14/05, [hidden email] <[hidden email]> wrote:
> > > Hi,
> > >
> > > I played with the export to excel employee example and I realised
> that
> > there is a limitation within excel.
> > >
> > > <employees>
> > > <xsl:apply-templates select="/employees/*[23 > position()]"/>
> > > </employees>
> > >
> > > I did modify the number of employee returned and when opening the
> > generated excel file a error message say: "No more custom number
> formats
> > can be added".
> > >
> > > Where do this limitation come from? It ther's a way to generate
> bigger
> > excel file?
> > >
> > > Thanks!
> > >
> > >
> > >
> > >
> > >
> > > --
> > > You receive this message as a subscriber of the
> > [hidden email] mailing list.
> > > To unsubscribe: mailto:[hidden email]
> > > For general help: mailto:[hidden email]?subject=help
> > > ObjectWeb mailing lists service home page:
> > http://www.objectweb.org/wws
> > >
> > >
> > >
> >
> >
> >
> >
> >
> > --
> > You receive this message as a subscriber of the
> [hidden email] mailing list.
> > To unsubscribe: mailto:[hidden email]
> > For general help: mailto:[hidden email]?subject=help
> > ObjectWeb mailing lists service home page:
> http://www.objectweb.org/wws
> >
> >
> >
>
>
>
>
>
> --
> You receive this message as a subscriber of the [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
>
>
>

--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet

Alessandro Vernet

Re: to-xls-converter

Administrator

See attached the screenshot mentioned in my previous email.

Alex

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

excel.png (12K) Download Attachment

--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet