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.

http://javaprogrammingforums.com/images/excelodbc1.jpg

Once in the Administrative Tools section, locate Data Sources (ODBC)

http://javaprogrammingforums.com/images/excelodbc2.jpg

The ODBC Data Source Administor menu will open

http://javaprogrammingforums.com/images/excelodbc3.jpg

Select the System DSN tab and click Add

http://javaprogrammingforums.com/images/excelodbc4.jpg

Find Driver do Microsoft Excel(*.xls) from the list and click Finish

Give the Data Source Name & Description

http://javaprogrammingforums.com/images/excelodbc5.jpg

Next, click Select Workbook and locate the spreadsheet you wish to use

http://javaprogrammingforums.com/images/excelodbc6.jpg

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.

Code Java:

---

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.

Code Java:

---

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).

Code Java:

---

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

---

I used the above given code to test and it works fine. But when i tried to use INSERT query , i get "java.sql.SQLException:[Microsoft][ODBC excel Driver] Operation must use an updateable query" exception message. Please help me to insert data. Please find the code below.

Code :

---

try {
conn=DriverManager.getConnection("jdbcdbc:Bookin g");
stmt=conn.createStatement();
sql="insert into [Sheet1$]([USERID],[FIRST_NAME],[LAST_NAME]) values('123456','Murugan','Saravanan')";
pS=conn.prepareStatement(sql);
pS.execute();
 
}

---

Thanks,
Dev.

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.

Thank you.

Now i am able to insert data into the excel.

Dev.

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 ?

Quote:

---

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.

Quote:

---

Originally Posted by Devaraj

I used the above given code to test and it works fine. But when i tried to use INSERT query , i get "java.sql.SQLException:[Microsoft][ODBC excel Driver] Operation must use an updateable query" exception message. Please help me to insert data. Please find the code below.

Code :

---

try {
conn=DriverManager.getConnection("jdbcdbc:Bookin g");
stmt=conn.createStatement();
sql="insert into [Sheet1$]([USERID],[FIRST_NAME],[LAST_NAME]) values('123456','Murugan','Saravanan')";
pS=conn.prepareStatement(sql);
pS.execute();
 
}

---

Thanks,
Dev.

---

Try using executeUpdate(); Insert, update, delete use executeUpdate(). On select use executeQuery() .

Hello ,
My name is Kinjan Ajudiya.
I am new to this forum.
I have question about reading excel file in java.
I have implemented application which read excel file and store it in database.
But this application is limited to single change only.
Means ,if i want to read that file continuously then i cant do in my application.
I want to do such thing, one excel file is currently running.
First my application will read excel file .then change is made to excel file .Say some cell is modified.
Now in next request of file reading i should get that modified cell value .
Is this possible ?
If yes then please let me know ...

Thanks
Kinjan

I found this information very useful, and thank you for sharing. I do have a question I am trying to pull data from Access but the information is stored on a web based application and to get the data I need I have to create a template using word, I can get some of the fields to work but not all of them. This is a project for work and I am trying to get this done and having little luck. Would you be able to give some advice? Or any help would be greatly appreciated.
Thank you

Hello phatdogg,

Is this work project written in Java? If so, I suggest you start a new thread about it.
You will get a better response.

Hi,

i have tried this but i'm facing an issue with it, got an error
Exception: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

can some one please help me to resolve this

Hello veluruanil.

Please try here. There is lots of information on this error:

Exception: [Microsoft][ODBC Driver Manager]

Quote:

---

Originally Posted by JavaPF

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).

Code Java:

---

                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 sir.

The above mentioned way of selecting the excel sheet is unavailable in vista. I tries configuring it, but no use... Is there a way out to do it directly from netbeans inbuilt server... could you plz help me with that