Search

Domino Upgrade

VersionSupport end
5.0
6.0
6.5
7.0
8.0
8.5
Upgrade to 9.x now!
(see the full Lotus lifcyle) To make your upgrade a success use the Upgrade Cheat Sheet.
Contemplating to replace Notes? You have to read this! (also available on Slideshare)

Languages

Other languages on request.

Twitter

Useful Tools

Get Firefox
Use OpenDNS
The support for Windows XP has come to an end . Time to consider an alternative to move on.

About Me

I am the "IBM Collaboration & Productivity Advisor" for IBM Asia Pacific. I'm based in Singapore.
Reach out to me via:
Follow notessensei on Twitter
(posts)
Skype
Sametime
IBM
Facebook
LinkedIn
XING
Amazon Store
Amazon Kindle
NotesSensei's Spreadshirt shop
profile for stwissel on Stack Exchange, a network of free, community-driven Q&A sites

« Static APEX code analysis with PMD | Main| Blog on the run »

Excel in Continuous Integration

Business Users like Excel. Besides its original use case of calculating, lists are a favourite use case. They also serve as poor man's requirement and bug tracker, so they siep into software development too.

While Excel sheets are great for interaction, they are a beast for anything automation. The irony of it: Modern Excel files (the xslx flavour) are nothing less than zip files with XML content. However that format is, let's say, [insert expletives here].

From an XML representation I would expect something like <cell row="23" col="44">Some value</cell>. However that's not what Excel does. Rename an xlsx to zip and see for yourself. Also (which makes sense for Excel itself) empty cells are not represented in XML.

Cutting a long story short, an Excel file or its XML representation poses some challenges:

  • XML format is not very suitable for automation, like generating reports using XSLT
  • Excel automation only runs on Windows (and when you run headless, you trade the head for a headache). That makes it a no-go for most automation server environments
  • Empty cells are absent from the XML (a variation of "not suitable")
  • Cross reports with other files (e.g. logs in XML format) is hard

To overcome these limitations I wrote Excel2XML. It is a little Java command line utility that converts Excel into a more digestible XML format. I used Microsoft's contribution to the Apache POI project to read the file. It has the following functions:

  • Extract workbooks in one or separate files per worksheet
  • Ignore all formatting
  • Computed cells return their last result values, unless it is a formula error, then the formula is returned
  • The first line of each sheet is treated as column headers, which are extracted as columns/column elements
  • Each cell has a column, a row and a title attribute. The title reflects the value from the first row. This allows in XSLT to query the title instead of relying on the column number. Reordering, adding or removing columns won't kill your XSLT stylesheet that way
  • Optional empty cells can be generated with an attribute of empty="true"
  • Runs on Java8 completely from command line
  • Calling it without parameters outputs the exact syntax of options

The full syntax: java -jar excel2xml.jar -i somefile.xslt [-o somefile.xml [ ]-e] [-s] [-w3,4]

  • -i the input file in xslx format
  • -o the output file. If missing same name as input, but extension xml
  • -e generate empty cells. If missing: cells without data are skipped
  • -s generate a single file for the whole workbook. If missing: creates one file per sheet
  • -w comma separated list of sheets to export. Starts at 0. If missing: exports all sheets

Head over to the git repository and grab a release. Let me know what use you found. As usual YMMV

Comments

Disclaimer

This site is in no way affiliated, endorsed, sanctioned, supported, nor enlightened by Lotus Software nor IBM Corporation. I may be an employee, but the opinions, theories, facts, etc. presented here are my own and are in now way given in any official capacity. In short, these are my words and this is my site, not IBM's - and don't even begin to think otherwise. (Disclaimer shamelessly plugged from Rocky Oliver)
© 2003 - 2017 Stephan H. Wissel - some rights reserved as listed here: Creative Commons License
Unless otherwise labeled by its originating author, the content found on this site is made available under the terms of an Attribution/NonCommercial/ShareAlike Creative Commons License, with the exception that no rights are granted -- since they are not mine to grant -- in any logo, graphic design, trademarks or trade names of any type. Code samples and code downloads on this site are, unless otherwise labeled, made available under an Apache 2.0 license. Other license models are available on written request and written confirmation.