Monday, September 27, 2010

Read data from Excel worksheet In Java

Let's assume we have a worksheet like this
LASTNAME       FIRSTNAME   ID
waran          Vignesh     102
sekaran        Rethna      111
mathi          kalai       200
Vishnu         Ananada     300
To access this data, we can use the JDBC-ODBC bridge. Microsoft provides an ODBC driver to Excel worksheet.
Define an ODBC datasource (system DSN) named "employee_xls" that points to that worksheet.
example 1
import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.*;

public class EmployeeReader{
   public static final String DRIVER_NAME =
          "sun.jdbc.odbc.JdbcOdbcDriver";
   public static final String DATABASE_URL = "jdbc:odbc:employee_xls";

   public static void main(String[] args)
      throws ClassNotFoundException, SQLException{
      Class.forName(DRIVER_NAME);
      Connection con = null;
      try {
         con = DriverManager.getConnection(DATABASE_URL);
         Statement stmt = con.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();
      }
      finally {
         if (con != null)
            con.close();
      }
   }
}
example 2
import java.io.*;
import java.sql.*;

public class EmployeeReader{
  public static void main(String[] args){
    Connection connection = null;
    try{
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Connection con = DriverManager.getConnection( "jdbc:odbc:employee_xls" );
      Statement st = con.createStatement();
      ResultSet rs = st.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++) {
            if (i > 1) System.out.print(", ");
            String columnValue = rs.getString(i);
            System.out.print(columnValue);
            }
            System.out.println(""); 
          }
      rs.close();  
      st.close();
      } 
    catch(Exception ex) {
      System.err.print("Exception: ");
      System.err.println(ex.getMessage());
      }
    finally {
      con.close();
      }
    }
 }

No comments:

Post a Comment