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
