Wednesday, October 14, 2009

Reading Excel Files from JDBC

Reading Excel Files from JDBC


I followed this example from JavaWorld and it worked. The instructions were good, except for the explanation of why qas was used as the name of the worksheet. The file was named qa.xls, the connection was qa-list; it took me a few minutes to realize that the name on the worksheet tab was qas.

Next step is to read my own file and do a query. It worked.

Next step is to perform an outer join between two sheets in the same file.

I have created the outer join with the following syntax (from ibm):

query = "SELECT * FROM {oj [09032009$] LEFT OUTER JOIN [09172009$] ON ([09032009$].MAC_EMPLID=[09172009$].MAC_EMPLID)}";

The names of my sheets in the file are 09032009 and 09172009.

I have determined the number of columns in the result set (from devdaily):

    //------------------------------------------------------//
   //  Here's the code to determine the number of columns  //
   //  in the ResultSet.                                   //
   //------------------------------------------------------//
   Statement st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * from Customer");
   ResultSetMetaData rsmd = rs.getMetaData();

   int numCols = rsmd.getColumnCount();
This can also be used to retrieve a column name.

No comments:

Post a Comment

Followers