1) Create MS Excel sheet and enter the data into the worksheet.
Eg. Consider an Excel sheet of Employee containing records of Employee. ie ID,NAME,DEPARTMENT and SALARY. As shown below.
2) Save the file with name "EMPLOYEE.xls".
3) Then go to the Contro Panel --> Administrative tools -->Data Sources (ODBC)
A Data Source Administrator window appears on the Screen.
4) Go to User DSN --> Add
A New Data Source Window appears on the screen.
5) Select "Microsoft Excel Driver(*.xls)" driver. Then click on finish button.
6) A ODBC setup window appears on the screen. Then write Data Source Name as "excelsheet". By default version is "Excel 97-2000" ,keep it as same.
7) If u r using newer versions of XP or MS Office ,the ODBC drivers supports to system.
8) Click on "Select WorkBook" button. Select proper Excel worksheet on the proper address.Then click on "OK" button.
9) Then click on the " Options " button, and deselect the "Read only options".Keep the
rows as same.
10) Your DSN is created.Then click on OK buuton.
11) Now for accessing the values from the MS Excel sheet,run the following PGM.
/* PGM FOR MINING THE DATA FROM MS EXCEL SHEET USING SIMPLE JDBC PGM */
import java.sql.*;
import java.util.*;
import java.io.*;
class READFROMEXCEL
{
public void Connect()
public void Connect()
{
Connection con;
Statement stmt;
ResultSet rs;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:empexcelsheet");
st=con.createStatement();
rs=stmt.executeQuery("select *from [sheet1$]");
System.out.println("ID"+"\t"+"NAME"+"\t"+"DEPARTMENT"+"\t"+" SALARY");
while(rs.next())
{
int id=rs.getInt(1);
String name=rs.getString(2);
String dept=rs.getString(3);
int sal=rs.getInt(4);
System.out.println(id+"\t "+name+" \t"+dept+" \t"+sal);
}
rs.close();
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println("ERROR OCCURED IS="+e);
}
}
public static void main(String args[])
{
READFROMEXCEL rfxl=new READFROMEXCEL();
rfxl.Connect();
}
}
