It's Excel-lent

Read MS Excel files with Java

June 29, 2001

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

A: 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.

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.

Learn more about this topic

Join the discussion
Be the first to comment on this article. Our Commenting Policies
See more