Thread: How to Connect to an Excel Spreadsheet using JDBC in Java

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.

import java.sql.*;
 
public class ExcelODBC {
 
    public static void main(String[] args) {
 
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:[B]worcester[/B]");
            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());
        }
    }
}

Notice the bold worcester. This part of the code needs to match your ODBC Data Source Name.

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

This part of the code allows you to query the excel spreadsheet with SQL. This is a simple SQL query to select everything from Sheet1 in the spreadsheet.

If you are unfamiliar with SQL commands then there is a quick tutorial here:

A Gentle Introduction to SQL


Thats it! You can now connect to your Excel Spreadsheet and print its content to the console.