Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 15 of 15

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

  1. #1
    mmm.. coffee JavaPF's Avatar
    Join Date
    May 2008
    Location
    United Kingdom
    Posts
    3,336
    My Mood
    Mellow
    Thanks
    258
    Thanked 294 Times in 227 Posts
    Blog Entries
    4

    Post 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 use [highlight=Java] code [/highlight] tags when posting your code.
    Forum Tip: Add to peoples reputation by clicking the button on their useful posts.


  2. #2
    mmm.. coffee JavaPF's Avatar
    Join Date
    May 2008
    Location
    United Kingdom
    Posts
    3,336
    My Mood
    Mellow
    Thanks
    258
    Thanked 294 Times in 227 Posts
    Blog Entries
    4

    Default Re: How to Connect to an Excel Spreadsheet using ODBC in Java

    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("");
    Please use [highlight=Java] code [/highlight] tags when posting your code.
    Forum Tip: Add to peoples reputation by clicking the button on their useful posts.

  3. #3
    Junior Member
    Join Date
    Apr 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    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.

    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.

  4. #4
    mmm.. coffee JavaPF's Avatar
    Join Date
    May 2008
    Location
    United Kingdom
    Posts
    3,336
    My Mood
    Mellow
    Thanks
    258
    Thanked 294 Times in 227 Posts
    Blog Entries
    4

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    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.
    Please use [highlight=Java] code [/highlight] tags when posting your code.
    Forum Tip: Add to peoples reputation by clicking the button on their useful posts.

  5. #5
    Junior Member
    Join Date
    Apr 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    Thank you.

    Now i am able to insert data into the excel.

    Dev.

  6. #6
    Junior Member
    Join Date
    Mar 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    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 ?

  7. #7
    mmm.. coffee JavaPF's Avatar
    Join Date
    May 2008
    Location
    United Kingdom
    Posts
    3,336
    My Mood
    Mellow
    Thanks
    258
    Thanked 294 Times in 227 Posts
    Blog Entries
    4

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    Quote Originally Posted by Mehul View Post
    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.
    Please use [highlight=Java] code [/highlight] tags when posting your code.
    Forum Tip: Add to peoples reputation by clicking the button on their useful posts.

  8. #8
    Junior Member
    Join Date
    Jun 2011
    Posts
    1
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    Quote Originally Posted by Devaraj View Post
    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.

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

  9. The Following User Says Thank You to zeddarn For This Useful Post:

    JavaPF (June 21st, 2011)

  10. #9
    Junior Member
    Join Date
    Dec 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    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

  11. #10
    Junior Member
    Join Date
    Aug 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    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

  12. #11
    mmm.. coffee JavaPF's Avatar
    Join Date
    May 2008
    Location
    United Kingdom
    Posts
    3,336
    My Mood
    Mellow
    Thanks
    258
    Thanked 294 Times in 227 Posts
    Blog Entries
    4

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    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.
    Please use [highlight=Java] code [/highlight] tags when posting your code.
    Forum Tip: Add to peoples reputation by clicking the button on their useful posts.

  13. #12
    Junior Member
    Join Date
    Sep 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    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

  14. #13
    mmm.. coffee JavaPF's Avatar
    Join Date
    May 2008
    Location
    United Kingdom
    Posts
    3,336
    My Mood
    Mellow
    Thanks
    258
    Thanked 294 Times in 227 Posts
    Blog Entries
    4

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    Hello veluruanil.

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

    Exception: [Microsoft][ODBC Driver Manager]
    Please use [highlight=Java] code [/highlight] tags when posting your code.
    Forum Tip: Add to peoples reputation by clicking the button on their useful posts.

  15. #14
    Junior Member
    Join Date
    Mar 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Re: How to Connect to an Excel Spreadsheet using ODBC in Java

    Quote Originally Posted by JavaPF View Post
    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 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

  16. #15
    Junior Member
    Join Date
    Aug 2013
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default Re: How to Connect to an Excel Spreadsheet using JDBC in Java

    hi cn you explain the line
    Connection con = DriverManager.getConnection("jdbcdbc:worcester");

    i don't understand what is the purpose of

Similar Threads

  1. Export to excel
    By ebosysindia in forum File I/O & Other I/O Streams
    Replies: 7
    Last Post: May 14th, 2009, 06:25 AM

Tags for this Thread