Welcome!

Java Authors: Hovhannes Avoyan, Carmen Gonzalez, Imran Akbar, Sharon Barkai, Adrian Bridgwater

Related Topics: Java

Java: Article

ExtenXLS Java/XLS Toolkit 2.1 by Extentech Inc.

ExtenXLS Java/XLS Toolkit 2.1 by Extentech Inc.

For the business people of the world, Excel is like mother's milk. I'm convinced that my neighbor, a financial planner for an investment bank, does our homeowner's reconciliation for fun: a showcase for his Excel prowess. It's a sickness. Excel is powerful, simple to use, and ubiquitous in virtually every market. The problem is that those of us tasked with Excel integration know that at the binary level, Excel is a gory mess and, as a rule, does not play well with anything but COM.

Extentech offers an intuitive, pure Java API for Excel integration. Under pressure from an anxious project manager, I evaluated it side-by-side with two other Java-based Excel integration tools available on the Web: POI (Apache Software Foundation) and JExcel. The requirements were for a fast, reliable tool that could push data from a Java-based application server to heavily formatted Excel templates in either Windows or Solaris operating systems.

Extentech packages its product thoughtfully, so I was reading and writing cells within a half-hour of the download. The object model is clean, the Javadocs are fully commented, and the concise manual provides ample information about how to work through common problems. My first 30 minutes using ExtenXLS were productive and reassuring. POI, while powerful and easy on the budget, has a significantly steeper learning curve. POI's online documentation, while amusing and voluminous, is comparatively arcane. Extentech got me started much faster - a huge plus when you're strapped for time.

ExtenXLS works by first ingesting the Excel spreadsheet from either a byte array, file path, or InputStream, then parsing the binary spreadsheet and providing an API for accessing Workbook, Spreadsheet, Row, Cell, Formula, and other normal Excel objects. Once changes are written in memory through the API, the spreadsheet can then be stored back in its original form.

//Construct a workbook from a path string
String str_fileNameIn = "simple.xls";
WorkBookHandle book = new WorkBookHandle(str_fileNameIn);
WorkSheetHandle sheet = wbh_bookIn.getWorkSheet("Sheet1");
CellHandle cell = sheet.getCell("A1");

//Reading the value of an existing cell by ID
String s = (String) cell.getStringVal();
System.out.println("Cell G8: " + s);

//Writing the value of a cell
cell.setVal("Hello Darlin' ...");

//writing back to file
byte foo[] = book.getBytes();
File file_Out = new File(str_fileNameIn);
FileOutputStream fileOS_fileoutputstream = new FileOutputStream(file_Out);
fileOS_fileoutputstream.write(foo);
fileOS_fileoutputstream.close();

Code Sample 1: A very simple example of opening, reading, and writing to/from a file on disk

The key differentiator that sold us on ExtenXLS was its ability to write to spreadsheets that contained macros. All other Excel integration products that I've seen truncate macros and VBA code, no matter how simple, and write only data back to the spreadsheet, rendering it useless and/or corrupt! With POI, I found that files with macros would decrease in size after write operations by about the same number of bytes as I had macro code. Subsequent attempts to open the file would generally fail. ExtenXLS hiccupped on only the most Byzantine spreadsheets I tried, and was polite enough to throw a comprehensible exception.

When I first evaluated ExtenXLS in Q4 2002, I had two complaints: no InputStream constructor (only files and byte arrays) and no support for named ranges. The InputStream constructor was provided as a patch release within days of our enhancement request, and named range support was recently announced as a new feature.

For our purposes, these two improvements have been huge. The InputStream allows us to take spreadsheets directly from the application server document store, manipulate them without any disk I/O, and stream them back to the document store. Named range support abstracts spreadsheet data from its location within the spreadsheet - our customers are free to change their spreadsheet layout without impacting the application server integration. If the customer wants to put the task percentage complete field in D8 rather than D9, the application integration is not impacted.

Performance improvements have been noticeable as well. ExtenXLS version 1.4 took up to 30 seconds to ingest our larger spreadsheets, whereas version 2.0 does the same job in under three. Virtually all of the overhead now comes from our own business logic.

The chief criticisms I have now are bugs, not feature deficiencies. Occasionally I find that template formatting, such as boxes around certain regions, colored regions, etc., is destroyed by writes to adjacent cells. We surmounted these problems by laying out the templates more strategically, and by educating our users on some of the fussy details.

Customer licensing is simple to understand - being based on the number of CPUs in the deployment at $1,145 per CPU. Deployment licenses come with installation support (not that you would need it), and one developer seat per CPU. Developer licenses can be purchased independently, and are also reasonably priced at $150.

In my view, ExtenXLS faces two challenges going forward. First, the Apache Software Foundation produces excellent products that are widely adopted in the Java community. Luckily for Extentech, customers are still willing to pay a premium for dedicated support, and the ExtenXLS product is easily as good as POI, and in my view, even better.

More important, however, Extentech, like any software vendor, needs to look carefully at its Microsoft strategy. Following Sun's lead with an all XML-based office suite in StarOffice 6, Microsoft has used XML under the covers in Office 2003, making the novelty of a Java Excel parser much less novel. Nevertheless, the release of Office 2003 and the adoption of it in the enterprise are two very different things. Extentech has the interim to formulate new, fast, reliable, feature-rich, and well-packaged ways of bridging the .NET and Java worlds.

SIDEBAR

Extentech Inc.
1032 Irving Street #910
San Francisco, CA 94122-2200
Phone: 415.759.5292
Fax: 800.787.6849
Web: www.extentech.com

Test Environment

  • Sun 420R, Quad 450MHz, 4GB RAM, 500GB Mounted SAN, Solaris 8
  • Dell Latitude C610, Pentium 3, 833MHz, 20GB Disk, 320 MB RAM, W2K Pro SP2
  • JDK 1.3.1 as well as Jython 2.1 in both cases
  • *Excel 2000 (9.0.4402 SR1)

    SIDEBAR

    Snapshot
    Target Audience:
    Developers, architects, and analysts
    Level: Beginner to intermediate

    Pros:

  • Intuitive, flexible, well-documented API
  • Can read/write spreadsheets that contain macros and VBA
  • Timely, thorough support
  • Fast, reliable

    Cons:

  • Some difficulty with extremely complicated spreadsheets
  • Occasional formatting problems
  • More Stories By Peter Curran

    Peter Curran, a Software Architect for Intraspect Software of Brisbane, California, builds collaborative applications for high-tech vendors, investment banks, and systems integrators. The views expressed herein are those of the author and not necessarily endorsed by employer.

    Comments (3) View Comments

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


    Most Recent Comments
    Andy 07/08/03 11:45:00 AM EDT

    Interesting that we are comparing an old version of POI against a new version of extentech.. POI now preserves macros. Its also interesting that it isn't mentioned that POI is in fact commercially supported. You just don't have to pay license fees. You can find this support through SuperLink Software, Inc. (http://superlinksoftware.com). Furthermore, the Javadoc for POI is very complete and I find it suspect that someone would have difficulty understanding the quick guide (http://jakarta.apache.org/poi/hssf/quick-guide.html). I seriously hope this article is a paid advertisement, as it lacks any symptoms of a serious attempt of unbiased technical reporting. POI has beaten these guys and their advertising is getting more aggressive as a result. With the number of global banking and financial institutions whom are using POI and getting commercial support from SuperLink, I bet these guys are starving.

    POI 07/08/03 04:21:00 AM EDT

    POI has active mailing lists.

    Try and subscribe to the poi-dev list on jakarta.apache.org

    You can ask for help there - that's POIs support contract (provided you spend some time to search the archive and try the latest version) ;)

    But beware that POI is under development, far from being finished. However, a lot of the problems mentioned in the article are already fixed. This is only possible because people give it a try and fix the problems (being an open source tool you have the source code).

    Rider 07/08/03 03:38:00 AM EDT

    I'm using POI to open/modify Excel files and I have the following problem:
    Sometimes, a piece of data is lost without any explanation. For example, I fill the cell with '123456789', but it shows '123' only.
    Any idea?