Share on Google+Share on Google+

XLS JDBC Example

Advertisement
In this tutorial you will learn how connect java application to excel spreadsheet using JDBC-ODBC bridge driver

XLS JDBC

XlS JDBC driver is used to access xls file from java application. It is read only JDBC driver. You can only do a SELECT * FROM xls file statement. The other SQL command or option is not supported in this driver, even a single WHERE clause.

Example-

At First make an Excel Sheet of name 'student' as given below-

Name Age Course RollNo
Vinay 24 MCA 12345
John 25 B.Tech 21345

Since excel comes with ODBC driver therefore you should use JDBC - ODBC bridge driver to connect your excel spreadsheet . The name of the worksheet is equivalent to the database table name. Now Create a Data Source Name using Microsoft  Excel Driver. Give Data Source Name 'datasource'. 

XLSJDBCExample.java

package roseindia.net;

import java.sql.*;

public class XLSJDBCExample {
	XLSJDBCExample() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (Exception e) {
			System.out.println(e.toString());
		}
	}

	public static void main(String args[]) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection("jdbc:odbc:datasource", "", "");
			stmt = conn.createStatement();
			String query = "select * from [Sheet1$]";
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				System.out.println("Roll No:- " + rs.getInt("RollNo")
						+ ", Name:- " + rs.getString("Name") + ", Course:- "
						+ rs.getString("Course") + ", Age:- "
						+ rs.getInt("Age"));
			}
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			rs.close();
			stmt.close();
			conn.close();
		}
	}
}

When you run this application it will display message as shown below:


Roll No:- 12345, Name:- Vinay, Course:- MCA, Age:- 24
Roll No:- 21345, Name:- John, Course:- B.Tech, Age:- 25

Download this example code

Advertisements

Advertisement

Posted on: December 22, 2004 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Share this Tutorial Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.