Welcome!

Java IoT Authors: Pat Romanski, Liz McMillan, Elizabeth White, Jamie Maidson, Yakov Fain

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
    The essence of data analysis involves setting up data pipelines that consist of several operations that are chained together – starting from data collection, data quality checks, data integration, data analysis and data visualization (including the setting up of interaction paths in that visualization). In our opinion, the challenges stem from the technology diversity at each stage of the data pipeline as well as the lack of process around the analysis.
    Designing IoT applications is complex, but deploying them in a scalable fashion is even more complex. A scalable, API first IaaS cloud is a good start, but in order to understand the various components specific to deploying IoT applications, one needs to understand the architecture of these applications and figure out how to scale these components independently. In his session at @ThingsExpo, Nara Rajagopalan is CEO of Accelerite, will discuss the fundamental architecture of IoT applications, ...
    A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, wh...
    What a difference a year makes. Organizations aren’t just talking about IoT possibilities, it is now baked into their core business strategy. With IoT, billions of devices generating data from different companies on different networks around the globe need to interact. From efficiency to better customer insights to completely new business models, IoT will turn traditional business models upside down. In the new customer-centric age, the key to success is delivering critical services and apps wit...
    As cloud and storage projections continue to rise, the number of organizations moving to the cloud is escalating and it is clear cloud storage is here to stay. However, is it secure? Data is the lifeblood for government entities, countries, cloud service providers and enterprises alike and losing or exposing that data can have disastrous results. There are new concepts for data storage on the horizon that will deliver secure solutions for storing and moving sensitive data around the world. ...
    SYS-CON Events announced today that ContentMX, the marketing technology and services company with a singular mission to increase engagement and drive more conversations for enterprise, channel and SMB technology marketers, has been named “Sponsor & Exhibitor Lounge Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York. “CloudExpo is a great opportunity to start a conversation with new prospects, but what happens after the...
    SYS-CON Events announced today that MangoApps 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. MangoApps provides modern company intranets and team collaboration software, allowing workers to stay connected and productive from anywhere in the world and from any device. For more information, please visit https://www.mangoapps.com/.
    SYS-CON Events announced today that 24Notion has been named “Bronze Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. 24Notion is full-service global creative digital marketing, technology and lifestyle agency that combines strategic ideas with customized tactical execution. With a broad understand of the art of traditional marketing, new media, communications and social influence, 24Notion uniquely understands how to con...
    WebRTC is bringing significant change to the communications landscape that will bridge the worlds of web and telephony, making the Internet the new standard for communications. Cloud9 took the road less traveled and used WebRTC to create a downloadable enterprise-grade communications platform that is changing the communication dynamic in the financial sector. In his session at @ThingsExpo, Leo Papadopoulos, CTO of Cloud9, will discuss the importance of WebRTC and how it enables companies to fo...
    SYS-CON Events announced today TechTarget has been named “Media 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. TechTarget is the Web’s leading destination for serious technology buyers researching and making enterprise technology decisions. Its extensive global networ...
    Korean Broadcasting System (KBS) will feature the upcoming 18th Cloud Expo | @ThingsExpo in a New York news documentary about the "New IT for the Future." The documentary will cover how big companies are transmitting or adopting the new IT for the future and will be filmed on the expo floor between June 7-June 9, 2016, at the Javits Center in New York City, New York. KBS has long been a leader in the development of the broadcasting culture of Korea. As the key public service broadcaster of Korea...
    In his session at 18th Cloud Expo, Bruce Swann, Senior Product Marketing Manager at Adobe, will discuss how the Adobe Marketing Cloud can help marketers embrace opportunities for personalized, relevant and real-time customer engagement across offline (direct mail, point of sale, call center) and digital (email, website, SMS, mobile apps, social networks, connected objects). Bruce Swann has more than 15 years of experience working with digital marketing disciplines like web analytics, social med...
    With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York and Silicon Valley. 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 Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo and will feature technical sessions from a rock star conference faculty ...
    The 19th International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Containers, Microservices and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportunity. Submit y...
    Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with the 19th International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world and ThingsExpo New York Call for Papers is now open.
    There are several IoTs: the Industrial Internet, Consumer Wearables, Wearables and Healthcare, Supply Chains, and the movement toward Smart Grids, Cities, Regions, and Nations. There are competing communications standards every step of the way, a bewildering array of sensors and devices, and an entire world of competing data analytics platforms. To some this appears to be chaos. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists will discuss the vast to...
    SYS-CON Events announced today that Enzu, a leading provider of cloud hosting 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. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive advantage. By offering a suite of proven hosting and management services, Enzu wants companies to foc...
    SYS-CON Events announced today the How to Create Angular 2 Clients for the Cloud Workshop, being held June 7, 2016, in conjunction with 18th Cloud Expo | @ThingsExpo, at the Javits Center in New York, NY. Angular 2 is a complete re-write of the popular framework AngularJS. Programming in Angular 2 is greatly simplified. Now it’s a component-based well-performing framework. The immersive one-day workshop led by Yakov Fain, a Java Champion and a co-founder of the IT consultancy Farata Systems and...
    Customer experience has become a competitive differentiator for companies, and it’s imperative that brands seamlessly connect the customer journey across all platforms. With the continued explosion of IoT, join us for a look at how to build a winning digital foundation in the connected era – today and in the future. In his session at @ThingsExpo, Chris Nguyen, Group Product Marketing Manager at Adobe, will discuss how to successfully leverage mobile, rapidly deploy content, capture real-time d...
    IoT generates lots of temporal data. But how do you unlock its value? How do you coordinate the diverse moving parts that must come together when developing your IoT product? What are the key challenges addressed by Data as a Service? How does cloud computing underlie and connect the notions of Digital and DevOps What is the impact of the API economy? What is the business imperative for Cognitive Computing? Get all these questions and hundreds more like them answered at the 18th Cloud Expo...