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.
 
img01.jpg 
 
Step 2: To start, we need to setup the local ODBC connection. So select Administrative Tools in Control Panel.
 
img02.jpg 
 
Step 3: Select Data Source (ODBC) option.
 
img3.jpg 
 
Step 5: Select System DSN and click on the Add tab.
 
img4.jpg 
 
Step 6: Select the driver named as Driver do Microsoft Excel(*.xls) from the list.
 
img5.jpg 
 
Step 7: Give the data source name and select your workbook.
 
img6.jpg 
 
Step 8: Click on the select workbook tab and then click ok.
 
img7.jpg 
 
So now your DSN is created.
 
Java code
  1. import java.sql.*;  
  2. public class ExcelConnection  
  3. {  
  4.     public static void main(String[] args)  
  5.     {  
  6.         try  
  7.         {  
  8.             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
  9.             Connection con = DriverManager.getConnection("jdbc:odbc:abhishek");  
  10.             Statement st = con.createStatement();  
  11.             ResultSet rs = st.executeQuery("Select * from [Sheet1$]");  
  12.             ResultSetMetaData rsmd = rs.getMetaData();  
  13.             int numberOfColumns = rsmd.getColumnCount();  
  14.             while (rs.next())  
  15.             {  
  16.                 for (int i = 1; i <= numberOfColumns; i++)  
  17.                 {  
  18.                     if (i > 1)  
  19.                         System.out.print(", ");  
  20.                     String columnValue = rs.getString(i);  
  21.                     System.out.print(columnValue);  
  22.                 }  
  23.                 System.out.println("");  
  24.             }  
  25.             st.close();  
  26.             con.close();  
  27.         } catch (Exception ex) {  
  28.             System.err.print("Exception: ");  
  29.             System.err.println(ex.getMessage());  
  30.         }  
  31.     }  
  32. }  
OUTPUT
 
img8.jpg 
 
Resources