WORKING WITH EXCEL THROUGH JDBC
To work with DB, first we need to make connection with DB and then need to execute our SQL/PL-SQL commands, for connection we have few steps.
Step 1: import java.sql.* package
import java.sql.*;
Step 2: Load Drivers
We use class.forName keyword to load drivers, it takes string of driver class as an argument, return a class
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Class name which is passed here as an argument is for JDBC ODBC driver, we can use any other driver if required.
Step 3 : Create a DSN(Data source name object, if we don’t create DSN, we can pass DSN string directly to connection string.
Step 2: We should have DSN for Excel, we can do it by 2 ways
Way-1
Go to Control Panel
Administrative Tools
Data Source
System DSN
ADD
Select XLS Driver
FINISH
GIVE DSN NAME
SELECT WORKBOOK WHICH WE WANT TO WORK
Work Done…………..
Way-2 Do it by coding
String Db;
Db = “JdbcOdbcDriver={Microsoft Excel Driver (*.xls)};DBQ=C:\abc.xls”;DriverID=22;ReadOnly=False”
Step 3: Create Connection Object
If we used way 1 then connection object will be created like
Connection con = DricverManager.getConnection(“jdbc.odbc.DSNNAME”)
DSNNAME = what we have set when we created DSN from admin tools
OR
If we used way 2 then connection object will be created like that
Connection Con = DriverManager.getConnection(DSNString,””,””)
DSNString = created in step 2
Step 4: Statement Object
Statement s = Con.createstatement();
Step 5: Resultset Object or ResultSetMetaData object
Resultset r = s.ExecuteQuery(“Select * from [Sheet1$]”) ;
ResultsetMetaData rm =r.getmetadara();
Result set object is used to hold data returned by query
Resultsetmetadata object Is used to hold information about data
ResultSetMetaData rm = rs.getMetaData();
x =rm.getColumnCount();
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment