This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Jdbc with MS-excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Jdbc with MS-excel" Watch "Jdbc with MS-excel" New topic
Author

Jdbc with MS-excel

Rakesh Sharma
Ranch Hand

Joined: Dec 29, 2000
Posts: 37
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

Joined: Mar 15, 2001
Posts: 55
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

Joined: Sep 04, 2001
Posts: 35
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

 
Don't get me started about those stupid light bulbs.
 
subject: Jdbc with MS-excel
 
Similar Threads
How to open other windows application through java??
Saving a JTable
how to export data to Excel
Java MySQL MS Excel
Need to develop a Quality checking software Urgent!!!