You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Jon Svede <js...@yahoo.com> on 2011/07/11 00:43:50 UTC
Proposed Enhancements to ExcelAnt
Hi all,
I am thinking about extending the ExcelAnt framework to be able to create
workbooks from various data sources. Some of the people I have worked who use
Excel heavily often times have to go through several steps to extract data from
databases and CSV files before then can build their workbooks. Usually it is
aggregating data from many sources into a single workbook. This can be a time
consuming task simply due to the number files or databases with which they have
to interact.
Also, in many cases, they will have some boiler plate sheets they want to
create. I believe ExcelAnt can make this less painful by allowing them to
create workbooks from Ant in a highly repeatable fashion that is also platform
neutral.
I want to extend ExcelAnt to cover these cases. I am seeking feedback from you
on the following proposed additions to ExcelAnt:
Feature Requests for POI ExcelAnt
---------------------------------
The following are upgrades being requested for Apache POI ExcelAnt.
I. Add the ability to create a workbook and persist it to the filesystem.
Example:
<excelant>
<createWorkbook file="/absolute/path/to/new.xls" overwrite="true">
<addSheet name="sheet1">
<setCell address="A1" type="Cell.Types" value="someValue"/><!--
where Cell.Types are the types supported by POI-->
...
</addSheet>
</createWorkbook>
</excelant>
II. Add the ability to update an existing workbook.
Example:
<excelant>
<updateWorkbook file="/absolute/path/to/new.xls" create="true"> <!--
will create the WB if it doesn't exist-->
<updateSheet name="sheet1">
<setCell address="A1" type="Cell.TypeConstants"
value="someValue"/>
...
</addSheet>
</createWorkbook>
</excelant>
III. Add the ability to create/update sheets from the ResultSet of a SQL
statement run through JDBC.
Example:
<excelant>
<createWorkbook file="/absolute/path/to/new.xls" overwrite="true">
<addSheet name="sheet1" startAt="b5" maxRows="100" maxColumns="50">
<sql statement="select a, b, c from some.table where
a='something'">
<connection driver="driver.class.name"
url="jdbc:url"
username="username"
password="password"/>
</sql>
</addSheet>
</createWorkbook>
</excelant>
IV. Add the ability to create/update sheets from CSV files.
Example:
<excelant>
<createWorkbook file="/absolute/path/to/new.xls" overwrite="true">
<addSheet name="sheet1" startAt="b5" maxRows="100" maxColumns="50">
<csv file="/absolute/path/to/file.cvs"/>
</addSheet>
</createWorkbook>
</excelant>
Not only can I envision that this would help people who want to create workbooks
for various analysis, I also think it would help in testing POI itself.
Currently with ExcelAnt you have to have existing workbooks. In some cases it
might also be nice for a test to create workbook on the fly (though this is a
slippery slope in some ways, since you are testing against a workbook that POI
will have created so why wouldn't it test out as expected?).
I'd be curious to know what people think about this idea, if you have any
opinion at all on this please feel free to share it. If there are tools that do
something like this already, feel free to share that as well.
Sincerely,
Jon
Re: Proposed Enhancements to ExcelAnt
Posted by Jon Svede <js...@yahoo.com>.
> In terms of the CSV and SQL bits, it's possible that we'd be better off
>looking at somethingl like Apache Cocooon to do the transformations for these.
>It's quite likely that we'd need to do a few tweaks to the POI Excel output
>types (not sure how much love they've had lately), but that could deliver a
>wide range of inputs very easily. The excel ant step could then just be
>configuring up a cocoon pipeline based on the options given in ant, rather than
>having to do loads of other coding too!
I still don't see it. Reading the tutorials for Cocoon, it seems like this for
XML transformation, how would that apply to CSV and SQL results? Are the Cocoon
bindings for these types of things to create XML?
Thanks in advance for clarifying.
Jon
________________________________
From: Nick Burch <ni...@alfresco.com>
To: POI Developers List <de...@poi.apache.org>
Sent: Tue, July 12, 2011 4:29:16 PM
Subject: Re: Proposed Enhancements to ExcelAnt
On Sun, 10 Jul 2011, Jon Svede wrote:
> I am thinking about extending the ExcelAnt framework to be able to create
>workbooks from various data sources. Some of the people I have worked who use
>Excel heavily often times have to go through several steps to extract data from
>databases and CSV files before then can build their workbooks. Usually it is
>aggregating data from many sources into a single workbook. This can be a time
>consuming task simply due to the number files or databases with which they have
>to interact.
A lot of these aren't problems I personally face, but I suspect quite a lot of
users (and potential users!) do, so if you're happy to work on it then I don't
see why we shouldn't have it!
In terms of the CSV and SQL bits, it's possible that we'd be better off looking
at somethingl like Apache Cocooon to do the transformations for these. It's
quite likely that we'd need to do a few tweaks to the POI Excel output types
(not sure how much love they've had lately), but that could deliver a wide range
of inputs very easily. The excel ant step could then just be configuring up a
cocoon pipeline based on the options given in ant, rather than having to do
loads of other coding too!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Proposed Enhancements to ExcelAnt
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 12 Jul 2011, Jon Svede wrote:
> I am not experienced with Cocoon, so I would need to spend some time
> learning that, but it seems promising based on your description. Do you
> have any suggestions on good resources for learning Cocoon?
The POI powered serialization is documented at:
http://cocoon.apache.org/2.1/userdocs/xls-serializer.html
In terms of getting started with Cocoon, they seem to have a pretty good
getting started guide on their website, try
http://cocoon.apache.org/2.2/1159_1_1.html
then
http://cocoon.apache.org/2.2/1290_1_1.html
If anyone's good at writing prose, and has a few spare hours, it might be
good to do something similar for POI too :)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Proposed Enhancements to ExcelAnt
Posted by Jon Svede <js...@yahoo.com>.
I do not mind at all, I don't think this is a huge effort.
I am not experienced with Cocoon, so I would need to spend some time learning
that, but it seems promising based on your description. Do you have any
suggestions on good resources for learning Cocoon?
Thanks!
Jon
________________________________
From: Nick Burch <ni...@alfresco.com>
To: POI Developers List <de...@poi.apache.org>
Sent: Tue, July 12, 2011 4:29:16 PM
Subject: Re: Proposed Enhancements to ExcelAnt
On Sun, 10 Jul 2011, Jon Svede wrote:
> I am thinking about extending the ExcelAnt framework to be able to create
>workbooks from various data sources. Some of the people I have worked who use
>Excel heavily often times have to go through several steps to extract data from
>databases and CSV files before then can build their workbooks. Usually it is
>aggregating data from many sources into a single workbook. This can be a time
>consuming task simply due to the number files or databases with which they have
>to interact.
A lot of these aren't problems I personally face, but I suspect quite a lot of
users (and potential users!) do, so if you're happy to work on it then I don't
see why we shouldn't have it!
In terms of the CSV and SQL bits, it's possible that we'd be better off looking
at somethingl like Apache Cocooon to do the transformations for these. It's
quite likely that we'd need to do a few tweaks to the POI Excel output types
(not sure how much love they've had lately), but that could deliver a wide range
of inputs very easily. The excel ant step could then just be configuring up a
cocoon pipeline based on the options given in ant, rather than having to do
loads of other coding too!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Proposed Enhancements to ExcelAnt
Posted by Nick Burch <ni...@alfresco.com>.
On Sun, 10 Jul 2011, Jon Svede wrote:
> I am thinking about extending the ExcelAnt framework to be able to
> create workbooks from various data sources. Some of the people I have
> worked who use Excel heavily often times have to go through several
> steps to extract data from databases and CSV files before then can build
> their workbooks. Usually it is aggregating data from many sources into
> a single workbook. This can be a time consuming task simply due to the
> number files or databases with which they have to interact.
A lot of these aren't problems I personally face, but I suspect quite a
lot of users (and potential users!) do, so if you're happy to work on it
then I don't see why we shouldn't have it!
In terms of the CSV and SQL bits, it's possible that we'd be better off
looking at somethingl like Apache Cocooon to do the transformations for
these. It's quite likely that we'd need to do a few tweaks to the POI
Excel output types (not sure how much love they've had lately), but that
could deliver a wide range of inputs very easily. The excel ant step could
then just be configuring up a cocoon pipeline based on the options given
in ant, rather than having to do loads of other coding too!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org