Welcome!

Java IoT Authors: Liz McMillan, Elizabeth White, William Schmarzo, SmartBear Blog, Charlotte Spencer-Smith

Related Topics: Java IoT

Java IoT: 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?

    @ThingsExpo Stories
    SYS-CON Events announced today that Avere Systems, a leading provider of enterprise storage for the hybrid cloud, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Avere delivers a more modern architectural approach to storage that doesn’t require the overprovisioning of storage capacity to achieve performance, overspending on expensive storage media for inactive data or the overbuilding of data centers ...
    SYS-CON Events announced today that Alert Logic, Inc., the leading provider of Security-as-a-Service solutions for the cloud, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Alert Logic, Inc., provides Security-as-a-Service for on-premises, cloud, and hybrid infrastructures, delivering deep security insight and continuous protection for customers at a lower cost than traditional security solutions. Ful...
    SYS-CON Events announced today that Interoute, owner-operator of one of Europe's largest networks and a global cloud services platform, has been named “Bronze Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. Interoute is the owner-operator of one of Europe's largest networks and a global cloud services platform which encompasses 12 data centers, 14 virtual data centers and 31 colocation centers, with connections to 195 ad...
    Companies can harness IoT and predictive analytics to sustain business continuity; predict and manage site performance during emergencies; minimize expensive reactive maintenance; and forecast equipment and maintenance budgets and expenditures. Providing cost-effective, uninterrupted service is challenging, particularly for organizations with geographically dispersed operations.
    SYS-CON Events announced today that Commvault, a global leader in enterprise data protection and information management, has been named “Bronze Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Commvault is a leading provider of data protection and information management...
    SYS-CON Events announced today that VAI, a leading ERP software provider, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. VAI (Vormittag Associates, Inc.) is a leading independent mid-market ERP software developer renowned for its flexible solutions and ability to automate critical business functions for the distribution, manufacturing, specialty retail and service sectors. An IBM Premier Business Part...
    With an estimated 50 billion devices connected to the Internet by 2020, several industries will begin to expand their capabilities for retaining end point data at the edge to better utilize the range of data types and sheer volume of M2M data generated by the Internet of Things. In his session at @ThingsExpo, Don DeLoach, CEO and President of Infobright, will discuss the infrastructures businesses will need to implement to handle this explosion of data by providing specific use cases for filte...
    Cognitive Computing is becoming the foundation for a new generation of solutions that have the potential to transform business. Unlike traditional approaches to building solutions, a cognitive computing approach allows the data to help determine the way applications are designed. This contrasts with conventional software development that begins with defining logic based on the current way a business operates. In her session at 18th Cloud Expo, Judith S. Hurwitz, President and CEO of Hurwitz & ...
    Fortunately, meaningful and tangible business cases for IoT are plentiful in a broad array of industries and vertical markets. These range from simple warranty cost reduction for capital intensive assets, to minimizing downtime for vital business tools, to creating feedback loops improving product design, to improving and enhancing enterprise customer experiences. All of these business cases, which will be briefly explored in this session, hinge on cost effectively extracting relevant data from ...
    SYS-CON Events announced today that Pythian, a global IT services company specializing in helping companies adopt disruptive technologies to optimize revenue-generating systems, has been named “Bronze Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. Founded in 1997, Pythian is a global IT services company that helps companies compete by adopting disruptive technologies such as cloud, Big Data, advanced analytics, and DevO...
    As enterprises work to take advantage of Big Data technologies, they frequently become distracted by product-level decisions. In most new Big Data builds this approach is completely counter-productive: it presupposes tools that may not be a fit for development teams, forces IT to take on the burden of evaluating and maintaining unfamiliar technology, and represents a major up-front expense. In his session at @BigDataExpo at @ThingsExpo, Andrew Warfield, CTO and Co-Founder of Coho Data, will dis...
    With the Apple Watch making its way onto wrists all over the world, it’s only a matter of time before it becomes a staple in the workplace. In fact, Forrester reported that 68 percent of technology and business decision-makers characterize wearables as a top priority for 2015. Recognizing their business value early on, FinancialForce.com was the first to bring ERP to wearables, helping streamline communication across front and back office functions. In his session at @ThingsExpo, Kevin Roberts...
    SYS-CON Events announced today that iDevices®, the preeminent brand in the connected home industry, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. iDevices, the preeminent brand in the connected home industry, has a growing line of HomeKit-enabled products available at the largest retailers worldwide. Through the “Designed with iDevices” co-development program and its custom-built IoT Cloud Infrastruc...
    Eighty percent of a data scientist’s time is spent gathering and cleaning up data, and 80% of all data is unstructured and almost never analyzed. Cognitive computing, in combination with Big Data, is changing the equation by creating data reservoirs and using natural language processing to enable analysis of unstructured data sources. This is impacting every aspect of the analytics profession from how data is mined (and by whom) to how it is delivered. This is not some futuristic vision: it's ha...
    Silver Spring Networks, Inc. (NYSE: SSNI) extended its Internet of Things technology platform with performance enhancements to Gen5 – its fifth generation critical infrastructure networking platform. Already delivering nearly 23 million devices on five continents as one of the leading networking providers in the market, Silver Spring announced it is doubling the maximum speed of its Gen5 network to up to 2.4 Mbps, increasing computational performance by 10x, supporting simultaneous mesh communic...
    The cloud promises new levels of agility and cost-savings for Big Data, data warehousing and analytics. But it’s challenging to understand all the options – from IaaS and PaaS to newer services like HaaS (Hadoop as a Service) and BDaaS (Big Data as a Service). In her session at @BigDataExpo at @ThingsExpo, Hannah Smalltree, a director at Cazena, will provide an educational overview of emerging “as-a-service” options for Big Data in the cloud. This is critical background for IT and data profes...
    SYS-CON Events announced today that Men & Mice, the leading global provider of DNS, DHCP and IP address management overlay solutions, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. The Men & Mice Suite overlay solution is already known for its powerful application in heterogeneous operating environments, enabling enterprises to scale without fuss. Building on a solid range of diverse platform support,...
    One of the bewildering things about DevOps is integrating the massive toolchain including the dozens of new tools that seem to crop up every year. Part of DevOps is Continuous Delivery and having a complex toolchain can add additional integration and setup to your developer environment. In his session at @DevOpsSummit at 18th Cloud Expo, Miko Matsumura, Chief Marketing Officer of Gradle Inc., will discuss which tools to use in a developer stack, how to provision the toolchain to minimize onboa...
    SYS-CON Events announced today that Fusion, a leading provider of cloud services, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Fusion, a leading provider of integrated cloud solutions to small, medium and large businesses, is the industry's single source for the cloud. Fusion's advanced, proprietary cloud service platform enables the integration of leading edge solutions in the cloud, including clou...
    Most people haven’t heard the word, “gamification,” even though they probably, and perhaps unwittingly, participate in it every day. Gamification is “the process of adding games or game-like elements to something (as a task) so as to encourage participation.” Further, gamification is about bringing game mechanics – rules, constructs, processes, and methods – into the real world in an effort to engage people. In his session at @ThingsExpo, Robert Endo, owner and engagement manager of Intrepid D...