Importing data from db2 to excel sheet

You want to use JDBC to connect to DB2.

Find the drivers for it and find a good book for JDBC programming and study that.

As far as connecting to Excel goes, I've got something that works on Windows systems. It uses the ODBC data source that is setup by default. So it uses a JDBC-ODBC bridge connection.

Connection con = null;

Statement st = null;

ResultSet rs = null;

String xls_address= "c:\\nogoodworkbook.xls";

String xls_sheet = "nogoodsheet$";

try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)}; DBQ="+xls_address+";DriverID=22;READONLY=false","","");

st = con.createStatement();

String query = "select * from ["+xls_sheet+"]";

rs = st.executeQuery(query);

while ( rs.next() )

{

System.out.println(rs.getString(1);

}

}

catch ( Exception e )

{

System.out.println("Exception: " + e );

}

finally

{

if ( rs != null)

rs.close();

if ( st != null )

st.close();

if ( con != null )

con.close();

}

The important things to note are the connection string and the name of the sheet. The sheet name must end with a '$'.

Also, you'll have to keep in mind that not all SQL queries are supported and there are some idiosyncracies that you'll have to discover and deal with on your own.

To handle Excel sheets in a better way and to be able to produce formatted workbooks, try the Apache POI Project at http://jakarta.apache.org/poi. You want to use the HSSF component.

nogoodatcodinga at 2007-7-12 8:51:12 >