Welcome!

Java IoT Authors: Elizabeth White, Carmen Gonzalez, Pat Romanski, AppNeta Blog, Yeshim Deniz

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
    With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
    Have you ever noticed how some IT people seem to lead successful, rewarding, and satisfying lives and careers, while others struggle? IT author and speaker Don Crawley uncovered the five principles that successful IT people use to build satisfying lives and careers and he shares them in this fast-paced, thought-provoking webinar. You'll learn the importance of striking a balance with technical skills and people skills, challenge your pre-existing ideas about IT customer service, and gain new in...
    SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 20th International Cloud Expo, which will take place on June 6–8, 2017, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buyers...
    With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, Cloud Expo and @ThingsExpo are two of the most important technology events of the year. Since its launch over eight years ago, Cloud Expo and @ThingsExpo have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, I provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading the...
    While not quite mainstream yet, WebRTC is starting to gain ground with Carriers, Enterprises and Independent Software Vendors (ISV’s) alike. WebRTC makes it easy for developers to add audio and video communications into their applications by using Web browsers as their platform. But like any market, every customer engagement has unique requirements, as well as constraints. And of course, one size does not fit all. In her session at WebRTC Summit, Dr. Natasha Tamaskar, Vice President, Head of C...
    In the enterprise today, connected IoT devices are everywhere – both inside and outside corporate environments. The need to identify, manage, control and secure a quickly growing web of connections and outside devices is making the already challenging task of security even more important, and onerous. In his session at @ThingsExpo, Rich Boyer, CISO and Chief Architect for Security at NTT i3, will discuss new ways of thinking and the approaches needed to address the emerging challenges of securit...
    TechTarget storage websites are the best online information resource for news, tips and expert advice for the storage, backup and disaster recovery markets. By creating abundant, high-quality editorial content across more than 140 highly targeted technology-specific websites, TechTarget attracts and nurtures communities of technology buyers researching their companies' information technology needs. By understanding these buyers' content consumption behaviors, TechTarget creates the purchase inte...
    As cloud adoption continues to transform business, today's global enterprises are challenged with managing a growing amount of information living outside of the data center. The rapid adoption of IoT and increasingly mobile workforce are exacerbating the problem. Ensuring secure data sharing and efficient backup poses capacity and bandwidth considerations as well as policy and regulatory compliance issues.
    Almost two-thirds of companies either have or soon will have IoT as the backbone of their business. Though, IoT is far more complex than most firms expected with a majority of IoT projects having failed. How can you not get trapped in the pitfalls? In his session at @ThingsExpo, Tony Shan, Chief IoTologist at Wipro, will introduce a holistic method of IoTification, which is the process of IoTifying the existing technology portfolios and business models to adopt and leverage IoT. He will delve in...
    SYS-CON Events announced today that Conference Guru has been named “Media Sponsor” of SYS-CON's 20th International Cloud Expo, which will take place on June 6–8, 2017, at the Javits Center in New York City, NY. A valuable conference experience generates new contacts, sales leads, potential strategic partners and potential investors; helps gather competitive intelligence and even provides inspiration for new products and services. Conference Guru works with conference organizers to pass great dea...
    SYS-CON Events announced today that LeaseWeb USA, a cloud Infrastructure-as-a-Service (IaaS) provider, will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. LeaseWeb is one of the world's largest hosting brands. The company helps customers define, develop and deploy IT infrastructure tailored to their exact business needs, by combining various kinds cloud solutions.
    Data is the fuel that drives the machine learning algorithmic engines and ultimately provides the business value. In his session at Cloud Expo, Ed Featherston, a director and senior enterprise architect at Collaborative Consulting, discussed the key considerations around quality, volume, timeliness, and pedigree that must be dealt with in order to properly fuel that engine.
    WebRTC defines no default signaling protocol, causing fragmentation between WebRTC silos. SIP and XMPP provide possibilities, but come with considerable complexity and are not designed for use in a web environment. In his session at @ThingsExpo, Matthew Hodgson, technical co-founder of the Matrix.org, discussed how Matrix is a new non-profit Open Source Project that defines both a new HTTP-based standard for VoIP & IM signaling and provides reference implementations.
    The Internet of Things is clearly many things: data collection and analytics, wearables, Smart Grids and Smart Cities, the Industrial Internet, and more. Cool platforms like Arduino, Raspberry Pi, Intel's Galileo and Edison, and a diverse world of sensors are making the IoT a great toy box for developers in all these areas. In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists discussed what things are the most important, which will have the most profound e...
    We all know that data growth is exploding and storage budgets are shrinking. Instead of showing you charts on about how much data there is, in his General Session at 17th Cloud Expo, Scott Cleland, Senior Director of Product Marketing at HGST, showed how to capture all of your data in one place. After you have your data under control, you can then analyze it in one place, saving time and resources.
    910Telecom exhibited at the 19th International Cloud Expo, which took place at the Santa Clara Convention Center in Santa Clara, CA, in November 2016. Housed in the classic Denver Gas & Electric Building, 910 15th St., 910Telecom is a carrier-neutral telecom hotel located in the heart of Denver. Adjacent to CenturyLink, AT&T, and Denver Main, 910Telecom offers connectivity to all major carriers, Internet service providers, Internet backbones and exchanges.
    SYS-CON Events announced today that Linux Academy, the foremost online Linux and cloud training platform and community, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Linux Academy was founded on the belief that providing high-quality, in-depth training should be available at an affordable price. Industry leaders in quality training, provided services, and student certification passes, its goal is to c...
    Web Real-Time Communication APIs have quickly revolutionized what browsers are capable of. In addition to video and audio streams, we can now bi-directionally send arbitrary data over WebRTC's PeerConnection Data Channels. With the advent of Progressive Web Apps and new hardware APIs such as WebBluetooh and WebUSB, we can finally enable users to stitch together the Internet of Things directly from their browsers while communicating privately and securely in a decentralized way.
    The IoT industry is now at a crossroads, between the fast-paced innovation of technologies and the pending mass adoption by global enterprises. The complexity of combining rapidly evolving technologies and the need to establish practices for market acceleration pose a strong challenge to global enterprises as well as IoT vendors. In his session at @ThingsExpo, Clark Smith, senior product manager for Numerex, discussed how Numerex, as an experienced, established IoT provider, has embraced a new m...
    With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place June 6-8, 2017, at the Javits Center in New York City, New York, is co-located with 20th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry p...