Creating excel file in java.

[Nitin Jawarkar]

hello,

I have created excel file using java but when i m getting data from database using resultset then it get data fine and create excel file also but now when i want to add heading to excel file also at top only then if i add that heading using resultset then it repeat for each row which must be only on top of file, my code look like this-

Class.forName(DRIVER);
Connection con = DriverManager.getConnection("jdbc dbc:temp;use;pass");
Statement stmt = con.createStatement();
String sql = "select top 20 text1,text2,text3 from text";
ResultSet rs = stmt.executeQuery(sql);
String fname = rs.getString(1);
String hname = fname.substring(0,3);
String head = "text1 text2 text3";
LogFile.writeFile(head,hname);
while(rs.next())
{
String s1 = rs.getString(1);
String s2 = rs.getString(2);
String s3 = rs.getString(3);
String rec = s1+" "+s2+" "+s3;
LogFile.writeFile(rec,s1);
}


where LogFile is the another class which writes the error file and excel file, i m also use the first col's first three char and current date for excel filename like "bom_20_01_2005" in the above code i m getting heading name outside the while loop but still it gives me invalid cursor state error. so can any body suggest me where should i write code for heading.

Thanx

[Sathvathsan Sampath]

Not very sure if this would be useful http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp

[sandip mense]

Nitin,

jakarta supplies API's for creating, writing and mainiting excel sheets. You can check out the details at http://jakarta.apache.org/poi/hssf/

[Mark Vedder]

There is also a Java Excel API available under the GNU Lesser General Public License. It provides the ability to read, write and modify Excel spreadsheets.

You can alos take a look at this thread, which discussed this topic, for some other suggestions.

[Carl Trusiak]

The error you are getting has nothing to do with Excel.

ResultSet rs = stmt.executeQuery(sql);
String fname = rs.getString(1);
String hname = fname.substring(0,3);
String head = "text1 text2 text3";

You MUST call next on a ResultSet before using it. This causes it to navigate to the first record the first time called and then each record in order afterward.

If you need the first record for this then do something like:

ResultSet rs = stmt.executeQuery(sql);
String fname = null;
String hname = null;
String head = "text1 text2 text3";
if(rs.next()){
    fname = rs.getString(1);
    hname = fname.substring(0,3);
}
....