Q : How to read a Microsoft Excel ( .xls ) file from Java?

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.

Assume you have an Excel file named c:\qa.xls . Also suppose that the data is in a worksheet named qas and takes the following excel/04-qa-0629-excel-table.html 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.