How to Use JDBC to Connect to MS Excel

Java Programming Language Logo

JDBC, or Java Database Connectivity, is a programming interface that is part of the Java programming language. It allows a software client to access databases or database-like files, such as spreadsheets, to conduct data retrieval and storage in the database. The ability for JDBC to access formatted data such as spreadsheets comes from the ODBC (Open Database Connectivity) bridge connection resident in the current version of the Java platform. By using the ODCB bridge, a programmer can access data stored in Microsoft Excel spreadsheets and conduct advanced manipulations of the data.

Things You'll Need

• Java development environment Microsoft Excel spreadsheet

Instructions

1. Create a basic Excel spreadsheet to access with JDBC. Call the file "example.xls." In the first row, create three column names read left to right: Dogs, Cats, Horses. In the Dog column, enter three names: Dog1, Dog2 and Dog3. Do the same data entry under both Cats and Horses. When accessing Excel through JDBC, the worksheet name is equivalent to the table name. The header names (in our example, Dogs, Cats and Horses) act as the table field names.

2. Create an ODBC data source through Microsoft Excel by selecting the "Menu Select" option. Identify the example.xls file as the source file for the data source.

3. Create a Java source code file in your text editor called "myJDBC.java" and save.

4. Start the Java file by importing the SQL package libraries and opening the class definition.

   import java.sql.*

   public class myJDBC
   {

5. Obtain a handle on the OdbcDriver in a static method call.

   static
   {
   try
   {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   }
   Catch (Exception JDBE)
   {
   System.println(JDBE);
   }// end catch
   }//end static

6. In the static Main method, obtain a handle to the information through the worksheet connected to the ODBC driver.

   public static void main (String args[])
   {
   Connection myConnection = null;
   Statement myStatement = null;
   String mySql = "";
   ResultSet myResultSet = null;

7. Get a direct connection to the Excel spreadsheet and print the information to the command console.

   try {
   myConnection=DriverManager.getConnection("jdbc:odbc:excel","","");
   myStatement=myConnection.createStatement();
   mySql="select * from [Sheet1$]";
   myResultSet=myStatement.executeQuery(sql);

   while(myResultSet.next()){
   System.out.println(resultSet.getString("Dogs")+
   " "+ resultSet.getString("Cats")+" "+
   resultSet.getString("Horses"));
   }
   }
   catch (Exception myException){
   System.err.println(myException);
   }
   finally {
   try{
   myResultSet.close();
   myStatement.close();
   myConnection.close();
   myResultSet =null;
   myStatement=null;
   myConnection=null;
   }
   catch(Exception e){}
   }
   }
   }

Resources

• Read this Article in Spanish
• An advanced JDBC tutorial from Java2s.com
• JDBC API Guide from Sun Microsystems