Retrieving data from excel

In real world applications, there is a possibility of retrieving the data from conventional data bases like ms-excel.

Steps for retrieving data from ms-excel:

1. Create an excel sheet, enter the column names along with data, rename the sheet1 as user defined name which is treated as table name.

2. Save the excel sheet in a current working directory.

For example:

D:\advanced\jdbc\stbook.xls

3. Create DSN for excel

4. Use xldsn while obtaining a connection from excel.

For example:

Connection con=DriverManager.getConnetion ("jdbc : odbc : xldsn");
                        

Note: In order to refer excel sheet name as a database sheet name we should use the format [<sheet name> $]

Write a jdbc program to retrieve the data from excel?

Answer:

import java.sql.*;

class XSelect {

    public static void main(String[] args) {
        try {
            DriverManager.registerDriver(new Sun.jdbc.odbc.JdbcOdbcDriver());
            System.out.println("DRIVERS LOADED...");
            Connection con = DriverManager.getConnection("jdbc:odbc:xldsn");
            System.out.println("CONNECTION  ESTABLISHED...");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from [student$]");
            while (rs.next()) {
                System.out.println(rs.getString(1) + " " + rs.getString(2) + "	" + rs.getString(3));
            }
            con.close();
        } catch (SQLException sqle) {

            sqle.printStackTrace();
        }
    }// main
};// XSelect