• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Jdbc with MS-excel

 
Rakesh Sharma
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please tell me how to use jadc with MS-excel worksheet. Say I have a file "Emp" in excel and I want to retrieve the data from it. How to do it. It will be of great help if steps and code are given.
Thanks
 
hemanth kumar
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi rakesh
Steps are :
1)Create System DSN to ur Excel Workbook i.e. excel file using Microsoft Excel Driver.
2)In ur java source code connect to that workbook i.e. excel file
3)then open recordset with the sheet name becoming ur table name
4)the columns become ur field names
thats abt it
Here's the working source code
<code>
import java.sql.*;
public class ConnectToExcel
{
public static void main(String a[])
{

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Connect to Excel DSN
String url = "jdbc dbc:test";
Connection connection =
DriverManager.getConnection(url, "", "");
Statement st=connection.createStatement();
// here sheet name equals to table name [sheet1]
String query = "SELECT * from [sheet1$]";
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
String s = rs.getString("EName");
System.out.println(" field values for EName are " + s);
}

}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
</code>
Here test is the DSN name.Sheet1 is the table name and this1 is the column name
Hope the above helps.
So how was ur space flight .just joking
Hemanth
 
Suji N
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Hemanth
Your Code Helped me a lot
Thankyou Verymuch
Yours
Suji
Originally posted by hemanth kumar:
Hi rakesh
Steps are :
1)Create System DSN to ur Excel Workbook i.e. excel file using Microsoft Excel Driver.
2)In ur java source code connect to that workbook i.e. excel file
3)then open recordset with the sheet name becoming ur table name
4)the columns become ur field names
thats abt it
Here's the working source code
<code>
import java.sql.*;
public class ConnectToExcel
{
public static void main(String a[])
{

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Connect to Excel DSN
String url = "jdbc dbc:test";
Connection connection =
DriverManager.getConnection(url, "", "");
Statement st=connection.createStatement();
// here sheet name equals to table name [sheet1]
String query = "SELECT * from [sheet1$]";
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
String s = rs.getString("EName");
System.out.println(" field values for EName are " + s);
}

}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
</code>
Here test is the DSN name.Sheet1 is the table name and this1 is the column name
Hope the above helps.
So how was ur space flight .just joking
Hemanth

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic