JDBC Connection to Microsoft Excel

Introduction

In this article we will describe how a Java program connects to a Microsoft Excel sheet and retrieves the data from the sheet. And this connection is made with the JdbcOdbc bridge driver API provided by the SQL package in Java. You have to perform some steps to make the connection with an Excel sheet.

Step 1: First create the workbook (Excel sheet); if your Excel sheet is already created then there is no need to create it again.



Step 2: To start, we need to setup the local ODBC connection. So select Administrative Tools in Control Pannel .

Step 3: Select Data Source (ODBC) option.

Step 5: Select System DSN and click on the Add tab.

Step 6: Select the driver named as Driver do Microsoft Excel(*.xls) from the list.

Step 7: Give the data source name and select your work book.



Step 8: Click on the selectworkbook tab and then click ok.

So now your DSN is created.

Java code

import java.sql.*;

public class ExcelConnection

{

public static void main(String[] args)

{

try

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

Connection con = DriverManager.getConnection("jdbc:odbc:abhishek");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("Select * from [Sheet1$]");

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

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());

}

}

}

OUTPUT

Resourcesa

JDBC Connection to MS-Access in Windows 7

Creating table and accessing data from MS-Access in JDBC
How to Create Backup File Dynamically for MS Excel
Creating Excel/Word/HTML document using C#
Display Data from Database through JSP And JDBC