Java/Database SQL JDBC/Excel

Read data from Excel

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestAccessExcel {

 public static Connection getConnection() throws Exception {
   String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
   String url = "jdbc:odbc:excelDB";
   String username = "username";
   String password = "pass";
   Class.forName(driver);
   return DriverManager.getConnection(url, username, password);
 }
 public static void main(String args[]) {
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   try {
     conn = getConnection();
     stmt = conn.createStatement();
     String excelQuery = "select * from [Sheet1$]";
     rs = stmt.executeQuery(excelQuery);
     while (rs.next()) {
       System.out.println(rs.getString("BadgeNumber") + " " + rs.getString("FirstName") + " "
           + rs.getString("LastName"));
     }
   } catch (Exception e) {
     System.err.println(e.getMessage());
   } finally {
     try {
       rs.close();
       stmt.close();
       conn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }

}

 </source>
   
  
 
  

Read data from Excel worksheet

   <source lang="java">
 

import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Statement stmt = DriverManager.getConnection("jdbc:odbc:employee").createStatement();
   ResultSet rs = stmt
       .executeQuery("select lastname, firstname, id from [Sheet1$]");
   while (rs.next()) {
     String lname = rs.getString(1);
     String fname = rs.getString(2);
     int id = rs.getInt(3);
     System.out.println(fname + " " + lname + "  id : " + id);
   }
   rs.close();
   stmt.close();
 }

}

 </source>
   
  
 
  

Use JDBC ODBC bridge to read from Excel

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; public class Main {

 public static void main(String[] argv) throws Exception {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   String myDB = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=c:/data.xls;"
       + "DriverID=22;READONLY=false";
   Connection con = DriverManager.getConnection(myDB, "", "");
 }

}

 </source>