It's Excel-lent

Read MS Excel files with Java

By Tony Sintes, JavaWorld.com, 06/29/01

June 29, 2001

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

Yes, 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):

• "It's Excel-lent"
• "The Java-Excel Solution Revisited"
• "It's POI-fect"

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