• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Liutauras Vilda
Sheriffs:
  • Paul Clapham
  • Jeanne Boyarsky
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
Bartenders:
  • Jesse Duncan
  • Frits Walraven
  • Mikalai Zaikin

Jdbc with MS-excel

 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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


 
pie. tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic