I have to run few SQL queries and put the results into a spreadsheet. Since I am on a Spring/Java environment, I was about to run the queries using JDBC, iterate through the ResultSet, and use Jakarta POI to create a simple XLS.
This looks like a very common requirement, so I was wondering if there is something already available - a package which given some SQL queries and a DataSource, can execute the queries and "export" their ResultSets into a spreadsheet. Does anyone know of such a package?
You can skip Java altogether. Microsoft SQL Server Management Studio can connect to your Excel workbook and dump data into it directly.
If you use something other than MS SQL Server 2005, you can still create a query within Excel to any ODBC data source, and write a SQL query to fetch data into a worksheet. Then you can re-run the query any time to refresh the data. I recommend anything else you need to do with that data, you do on separate worksheets.
SSMS has builtin functionality which can be used. You could also use SSIS. I've also liked the ease of use of SQLAnswers Query which is pretty neat. You could also use command line BCP to go out to CSV.
Not a direct answer to your question but, for this kind of task, I would rather take a look at dbunit which, although normally used for testing DAOs, has all the required features to do this. It can also perform the opposite task (ie reading data from XLS file and feed it into a database).
I think in less than one hour, you can have it do what you need. For this particular use, you can of course remove dbunit dependency on JUnit (none of the dbunit classes you would use for this depend on JUnit).
You could have a look at Jasper Reports. It can certainly do export to XLS (or CSV, or PDF...), and I believe it should be relatively simple to feed it an SQL result set.