Welcome!

Java IoT Authors: Elizabeth White, Carmen Gonzalez, Pat Romanski, Liz McMillan, 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
    SYS-CON Events announced today that Systena America 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. Systena Group has been in business for various software development and verification in Japan, US, ASEAN, and China by utilizing the knowledge we gained from all types of device development for various industries including smartphones (Android/iOS), wireless communication, security technology and IoT serv...
    SYS-CON Events announced today that Outscale 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. Outscale's technology makes an automated and adaptable Cloud available to businesses, supporting them in the most complex IT projects while controlling their operational aspects. You boost your IT infrastructure's reactivity, with request responses that only take a few seconds.
    DevOps at Cloud Expo – being held October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA – announces that its Call for Papers is open. Born out of proven success in agile development, cloud computing, and process automation, DevOps is a macro trend you cannot afford to miss. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the world's largest enterprises – and delivering real r...
    SYS-CON Events announced today that delaPlex will exhibit at SYS-CON's @CloudExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. delaPlex pioneered Software Development as a Service (SDaaS), which provides scalable resources to build, test, and deploy software. It’s a fast and more reliable way to develop a new product or expand your in-house team.
    Five years ago development was seen as a dead-end career, now it’s anything but – with an explosion in mobile and IoT initiatives increasing the demand for skilled engineers. But apart from having a ready supply of great coders, what constitutes true ‘DevOps Royalty’? It’ll be the ability to craft resilient architectures, supportability, security everywhere across the software lifecycle. In his keynote at @DevOpsSummit at 20th Cloud Expo, Jeffrey Scheaffer, GM and SVP, Continuous Delivery Busine...
    SYS-CON Events announced today that Outscale, a global pure play Infrastructure as a Service provider and strategic partner of Dassault Systèmes, 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. Founded in 2010, Outscale simplifies infrastructure complexities and boosts the business agility of its customers. Outscale delivers a secure, reliable and industrial strength solution for its customers, which in...
    SYS-CON Events announced today that CollabNet, a global leader in enterprise software development, release automation and DevOps solutions, will be a Bronze Sponsor of SYS-CON's 20th International Cloud Expo®, taking place from June 6-8, 2017, at the Javits Center in New York City, NY. CollabNet offers a broad range of solutions with the mission of helping modern organizations deliver quality software at speed. The company’s latest innovation, the DevOps Lifecycle Manager (DLM), supports Value S...
    In order to meet the rapidly changing demands of today’s customers, companies are continually forced to redefine their business strategies in order to meet these needs, stay relevant and continue to see profitable growth. IoT deployment and development is integral in this transformation, and today businesses are increasingly seeing the value of investing their resources into IoT deployments. These technologies are able increase ROI through projects such as connecting supply chains or enabling sm...
    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, whic...
    Everywhere we turn in our industry we can find strong opinions about the direction, type and nature of cloud’s impact on computing and business. Another word that is used in every context in our industry is “hybrid.” In his session at 20th Cloud Expo, Alvaro Gonzalez, Director of Technical, Partner and Field Marketing at Peak 10, will use a combination of a few conceptual props and some research recently commissioned by Peak 10 to offer a real-world consideration of how the various categories of...
    SYS-CON Events announced today that Peak 10, Inc., a national IT infrastructure and cloud services provider, 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. Peak 10 provides reliable, tailored data center and network services, cloud and managed services. Its solutions are designed to scale and adapt to customers’ changing business needs, enabling them to lower costs, improve performance and focus intern...
    SYS-CON Events announced today that Super Micro Computer, Inc., a global leader in compute, storage and networking technologies, 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. Supermicro (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology, is a premier provider of advanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/...
    Multiple data types are pouring into IoT deployments. Data is coming in small packages as well as enormous files and data streams of many sizes. Widespread use of mobile devices adds to the total. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists will look at the tools and environments that are being put to use in IoT deployments, as well as the team skills a modern enterprise IT shop needs to keep things running, get a handle on all this data, and deli...
    In his opening keynote at 20th Cloud Expo, Michael Maximilien, Research Scientist, Architect, and Engineer at IBM, will motivate why realizing the full potential of the cloud and social data requires artificial intelligence. By mixing Cloud Foundry and the rich set of Watson services, IBM's Bluemix is the best cloud operating system for enterprises today, providing rapid development and deployment of applications that can take advantage of the rich catalog of Watson services to help drive insigh...
    SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold 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. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
    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.
    SYS-CON Events announced today that EARP Integration 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. EARP Integration is a passionate software house. Since its inception in 2009 the company successfully delivers smart solutions for cities and factories that start their digital transformation. EARP provides bespoke solutions like, for example, advanced enterprise portals, business intelligence systems an...
    SYS-CON Events announced today that Progress, a global leader in application development, has been named “Bronze 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. Enterprises today are rapidly adopting the cloud, while continuing to retain business-critical/sensitive data inside the firewall. This is creating two separate data silos – one inside the firewall and the other outside the firewall. Cloud ISVs oft...
    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.
    The 21st International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Digital Transformation, Machine Learning 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 busin...