Originally Posted by
chandpuri
Dear all,
I am very new to java and am writing a program to put the resultset from an SQL query into an excel sheet. I already have downloaded the library jexcelapi library.
Please let me know how could I proceed further on this. It would be great if someone could give me a sample code to see.
Thanks a ton.
Cheers
Chandpuri
There is a tutorial page for jexcelapi linked from their sourceforge page.
I'm not familiar with jexcelapi, but the basics of writing a ResultSet to any other tabular medium are pretty straightforward. To access the ResultSet, use boolean ResultSet.next(), and the appropriate getter method for the column information. To write to the worksheet, use whatever setter is most appropriate to the data type. Really it's just a couple of nested for loops.
To determine the data types of the ResultSet's columns, use ResultSet.getMetaData().getColumnType(int) (the return value is an int whose interpretation is defined in java.sql.Types)
Recently I wrote some code to write Excel 2007 (OpenXML) workbooks from a database, and after researching the available software I decided to write it myself from scratch. There are a couple of good powerpoint presentations on Microsoft's website explaining how to write a workbook file in a form that is acceptable to Excel 2007. The format is very simple and also comforms to a published international standard, so unless you need to write Excel files that can be interpreted by earlier versions of Excel I recommend this approach.
After writing the peripheral code to produce the workbook the strategy I chose was to write an interface for a worksheet and implement it in the most basic manner, in an abstract class BasicWorksheet, that knows how to write the XML using the "nested for loops" logic, from any implementation of the interface.
The upshot was that I was then able to write a subclass, ResultSetWorksheet, that implemented the interface using the methods provided by ResultSet and ResultSetMetaData. Immediately it was able to write a worksheet from a ResultSet.
One thing I was careful about in implementing the abstract class was to avoid relying on the code that writes the worksheet file knowing the number of rows in the worksheet. One facet of a ResultSet is that one does not always know how many rows it contains until after one has scanned every row of data. By writing carefully avoiding references to the number of rows, I was able to produce worksheet files from a single SQL select statement. This kind of "single pass" design can be important for performance. The logic tests to see if the number of rows is known, and if not it omits the optional "dimension" element (which must precede the sheetData element if it is present) from the XML. An alternative approach would be to assemble the worksheet as a two-dimensional array in memory prior to writing it out.
Still another approach would be to model the spreadsheet as an updateable ResultSet, and use a jdbc driver for Excel files. One example of this, which I haven't investigated, is apparently xlsql, also hosted on sourceforge.
Linear Mode
