The Java-Excel solution revisited

Reader tips refine Tony's method to read MS Excel files in Java

By Tony Sintes, JavaWorld.com, 08/24/01

n the Java Q&A entitled "It's Excel-lent," I presented a method for reading Microsoft Excel documents via JDBC.

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

• "It's Excel-lent" (June 2001)
• "The Java-Excel Solution Revisited" (August 2002)
• "It's POI-fect" (May 2002)

In this Q&A, I revisit my original answer by answering a few of your questions, as well as by offering a few alternatives to the original JDBC solution.

Overview

In the original answer I presented the following Excel spreadsheet (which was included in the source download):

The spreadsheet contained one tab named "qas." qas has four columns: "Title," "URL," "Month," and "Year." In order to pull out all Q&As from March 2000, I presented the following query:

String query = "SELECT URL FROM [qas$] WHERE Month='March' and Year=2000;";

When writing queries against an Excel spreadsheet, each worksheet is treated as its own table. So in the case of the Excel Q&A file, you need to select from the qas worksheet. However, be sure to append "$" to the end of the worksheet name; Excel/ODBC intricacies require it.

Supported statements

Many readers have written requesting more information about what constitute valid SQL statements. According to Microsoft, the Microsoft Excel ODBC driver does have some limitations. Specifically, the driver does not support DELETE, UPDATE, or ALTER TABLE statements. CREATE and INSERT statements are supported; just be sure to set up your data source as write-able. As an example, execute the following two queries to create a new worksheet and insert values into it:

CREATE TABLE test ( Row1 TEXT, Row2 NUMBER )
INSERT INTO [test$] (Row1, Row2) VALUES('a value', 1 )

For a full description of what is and is not supported, be sure to check out the documentation cited above.

JDBC solution deficiencies

Several readers pointed out a major problem with the JDBC Excel solution: it does not work if you run a nonWindows platform since you won't have access to the proper ODBC driver. Unfortunately, there is no easy work around, but you do have a few options.

First, consider exporting the workbook to a plain text file. You can then use JDBC (if you have a driver that knows how to read a deliminated text file) or a custom program to read the plain text file.

Second, you can also consider importing the spreadsheet into Access, MS SQL, or another full-featured database. In my opinion, this represents the more practical solution compared to reading an Excel file directly through JDBC.

As a third option, purchase a third-party library designed specifically for reading Excel files in Java. A quick Google search found:

• extenTech extenXLS .9 beta
• Actuate Formula One for Java 9.0

Fourth, if you're really brave (and running on Windows), you can treat an Excel file as a COM object. Neva Object Technology links to some libraries for using COM objects from Java.