Java Tutorial/Database/Excel

A JDBC Program to Access/Read Microsoft Excel

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static Connection getConnection() throws Exception {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String url = "jdbc:odbc:excelDB";
    String username = "yourName";
    String password = "yourPass";
    Class.forName(driver);
    return DriverManager.getConnection(url, username, password);
  }
  public static void main(String args[]) throws Exception {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    conn = getConnection();
    stmt = conn.createStatement();
    String excelQuery = "select * from [Sheet1$]";
    rs = stmt.executeQuery(excelQuery);
    while (rs.next()) {
      System.out.println(rs.getString("FirstName") + " " + rs.getString("LastName"));
    }
    
    rs.close();
    stmt.close();
    conn.close();
  }
}

Read data from Excel

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    ResultSet rs = DriverManager.getConnection("jdbc:odbc:employee_xls")
        .createStatement().executeQuery("Select * from [Sheet1$]");
    ResultSetMetaData rsmd = rs.getMetaData();
    int numberOfColumns = rsmd.getColumnCount();
    System.out.println("No of cols " + numberOfColumns);
    while (rs.next()) {
      for (int i = 1; i <= numberOfColumns; i++) {
        String columnValue = rs.getString(i);
        System.out.println(columnValue);
      }
    }
    rs.close();
  }
}

Read data from Excel worksheet

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();
  }
}

Use JDBC ODBC bridge to read from Excel

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, "", "");
  }
}

Write Data to a Microsoft Excel Spreadsheet File

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static Connection getConnection() throws Exception {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String url = "jdbc:odbc:excelDB";
    String username = "";
    String password = "";
    Class.forName(driver); // load JDBC-ODBC driver
    return DriverManager.getConnection(url, username, password);
  }
  public static void main(String args[]) throws Exception {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    conn = getConnection();
    stmt = conn.createStatement();
    String excelQuery = "insert into [Sheet1$](FirstName, LastName) values("A", "K")";
    stmt.executeUpdate(excelQuery);
    rs.close();
    stmt.close();
    conn.close();
  }
}