Wizard API updated!
Tim Boudreau has released a new version of the Swing Wizard library (version 0.997) that fixes the WizardException bug reported in JavaWorld's recent Open Source Java Project profile. The article's examples have been reworked to test out the new, improved WizardException. Thanks, Tim, for this helpful fix!
Open Source Java Projects: The Wizard API

Newsletter sign-up

Sign up for our technology specific newsletters.

Enterprise Java
View all newsletters

Email Address:

It's Excel-lent

Read MS Excel files with Java

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.

Author Bio

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