The Java-Excel solution revisited

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

In 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):

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

TitleURLMonthYear
A multiline button is possible!http://www.javaworld.com/javaworld/javaqa/2000-03/01-qa-button.htmlMarch2000
Inner Classeshttp://www.javaworld.com/javaworld/javaqa/2000-03/02-qa-innerclass.htmlMarch2000
Interprocess communications in Javahttp://www.javaworld.com/javaworld/javaqa/2000-03/03-qa-0324-ipc.htmlMarch2000
................
qas

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:

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.

Finally, if you have a lot of time on your hands, and you're really serious about manipulating Excel files in Java, build your own custom Java class that knows how to read Excel files. Many open source projects include utilities for reading Excel files. Use one of those as a basis for a Java class.

Tony Sintes is a senior 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