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.

Please Note: This code will not print the first row in the spreadsheet as the jdbc-bridge assumes the first row to be akin to column names in the database.

You can use the following code to print the column names (which is the first row of the spread sheet).

                for (int i = 1; i <= numberOfColumns; i++) {
                    if (i > 1) System.out.print(", ");
                    String columnName = rsmd.getColumnName(i);
                    System.out.print(columnName);
                }
                    System.out.println("");

Hello Devaraj,

Welcome to the forums

Try this:

Follow the tutorial through to the part where you select the driver. Click Configure then click on Options and then uncheck the Read Only option.

Originally Posted by Mehul

I work on Excel all day long and query 100s of sheets everyday . Is it possible to reuse the same connection somehow ? Or for each workbook I will need to have hundreds of excel connections ?

I believe you will need to setup an ODBC connection for each spread sheet.