Selenium :Connect to an Excel Spreadsheet using JDBC in Java

Connect to an Excel Spreadsheet using JDBC in Java

Ref :http://www.javaprogrammingforums.com/jdbc-database-tutorials/356-how-connect-excel-spreadsheet-using-jdbc-java.html

This tutorial will show you how to connect to and read data from an Excel spreadsheet using JDBC.

To start, we need to setup the local ODBC connection.

Navigate to your computers Control Panel and locate the Administrative Tools.



Once in the Administrative Tools section, locate Data Sources (ODBC)



The ODBC Data Source Administor menu will open



Select the System DSN tab and click Add

Find Driver do Microsoft Excel(*.xls) from the list and click Finish

Give the Data Source Name & Description



Next, click Select Workbook and locate the spreadsheet you wish to use



In this case, we are using worcester.xls. Select it and click OK.

Click OK again to exit the setup. The ODBC connection is now complete.

Now that the ODBC connection is setup, its time for the Java code.

Note :1st Row in Excel is considered as column header 

-------------------------------------------------------------------

import java.sql.*;

public class Connect_Xl_using_JDBC

{

public static void main(String[] args)

{

try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection("jdbc:odbc:worcester");

Statement st = con.createStatement();

ResultSet rs = st.executeQuery("Select * from [Sheet1$]");

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

System.out.println("Columns="+numberOfColumns);

while (rs.next())

{

for (int i = 1; i <= numberOfColumns; i++)

{

if (i > 1)

System.out.print(", ");

String columnValue = rs.getString(i);

System.out.print(columnValue);

}

System.out.println("");

}

st.close();

con.close();

}

catch (Exception ex)

{

System.err.print("Exception: ");

System.err.println(ex.getMessage());

}

}

}

------------------------------------------------------