Newsletter sign-up
View all newsletters

Sign up for our Enterprise Java Newsletter

Enterprise Java

It's Excel-lent

Read MS Excel files with Java

  • Digg
  • Reddit
  • SlashDot
  • Stumble
  • del.icio.us
  • Technorati
  • dzone

June 29, 2001

QCan I read a Microsoft Excel file from Java? If so, how?

AYes, you can read Microsoft Excel files from Java. Microsoft provides an ODBC driver for Excel, thus allowing you to use JDBC and the Sun JDBC-ODBC driver to read Excel files.

Read Tony Sintes's entire Java/Microsoft Excel series (JavaWorld):



Assume you have an Excel file named c:\qa.xls (to download, see Resources). Also suppose that the data is in a worksheet named qas and takes the following format: Microsoft's ODBC driver treats the first row in a spreadsheet as the column names and the worksheet name as the database name.

To access a spreadsheet with JDBC, you need to create a new ODBC data source. To create one in Windows 2000:

  1. Go to "Control Panel"
  2. Go to "Administrative Tools"
  3. Go to "Data Sources"
  4. Select "Add"
  5. Choose "Microsoft Excel Driver" and "Finish," as seen in Figure 1


Figure 1. Create new data source



Then give the "Data Source Name" qa-list and select the workbook, shown in Figure 2.

Figure 2. ODBC Microsoft Excel Setup



When you are done you should see your new qa-list data source name:

Figure 3. New listing of user data sources



Now the spreadsheet is available as an ODBC source.

Say that you would like to pull out all March 2000 entries. You will need to hit the data source with the following SQL query:

select URL from [qas$] where Month='March' and Year=2000;


Note that the table name is the name of the worksheet with a $ appended to the end. You have to append the $ in order for the query to work. Why? Because. The brackets are there because $ is a reserved character in SQL. Life is never easy.

Now try ExcelReader on for size:

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
public class ExcelReader 
{
    public static void main( String [] args )
    {
        Connection c = null;
        Statement stmnt = null;
        try
        {
            Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
            c = DriverManager.getConnection( "jdbc:odbc:qa-list", "", "" );
            stmnt = c.createStatement();
            String query = "select URL from [qas$] where Month='March' and Year=2000;";
            ResultSet rs = stmnt.executeQuery( query );
            
            System.out.println( "Found the following URLs for March 2000:" );
            while( rs.next() )
            {
                System.out.println( rs.getString( "URL" ) );
            }
        }
        catch( Exception e )
        {
            System.err.println( e );
        }
        finally
        {
            try
            {
                stmnt.close();
                c.close();
            }
            catch( Exception e )
            {
                System.err.println( e );
            }
        }
    }
}


In ExcelReader, the main() gets a connection to the spreadsheet, then pulls out all of the entries for March 2000.

About the author

Tony Sintes is a principal consultant at BroadVision. Tony, a Sun-certified Java 1.1 programmer and Java 2 developer, has worked with Java since 1997.
  • Digg
  • Reddit
  • SlashDot
  • Stumble
  • del.icio.us
  • Technorati
  • dzone
Comments (9)
Login
Forgot your account info?

can we do the above in linux, mac env?By Anonymous on January 24, 2010, 1:45 pmHi, Is it possible to mimic the same stuff in Linux and MAC environments? If so, please mail me to ram_rahu_srr@yahoo.com

Reply | Read entire comment

Open source Java spreadsheet SDKBy Anonymous on January 21, 2010, 7:54 pmI have heard good things about OpenXLS, which I believe is at version 6, all Java open source spreadsheet SDK from Extentech. i think they are on 8.0 now for their...

Reply | Read entire comment

Type of field different in different records?By Anonymous on September 1, 2009, 2:08 pmMy excel sheet contains a column which is supposed to be dates and all data LOOKS like dates. However some of them are (probably) text, because I can not calculate...

Reply | Read entire comment

Thanks a lotBy Anonymous on August 14, 2009, 2:49 amThank You

Reply | Read entire comment

how to read numbers By Anonymous on August 4, 2009, 12:13 amHi, Can someone tell me how to read numbers from excel using odbc connection. I am getting null for all the number columns. Thanks.

Reply | Read entire comment

View all comments

Add comment
Anonymous comments subject to approval. Register here for member benefits.
Have a JavaWorld account? Log in here. Register now for a free account.
Resources