Groovyness with Excel and XML

Today in class one of the students mentioned that they need to read data from an Excel spreadsheet supplied by one of their clients and transform the data into XML adhering to their own schema.

I’ve thought about similar problems for some time and looked at the various Java APIs for accessing Excel. I spent a fair amount of time working with the POI project at Apache, which is a poor substitute but at least worked.

On the XML side, the Java libraries have gotten better, but working with XML in Java is rarely fun. I know the Apache group has built a few helper projects to make it easier, but I haven’t used them that much. In class, students don’t really want to talk about other projects; they want to know what’s in the standard libraries.

In short, I know I could write the necessary code to take data out of Excel and write it out to XML, but it would be long and awkward. It certainly wouldn’t be much fun.

Now, though, I’m spending a lot of time with Groovy. I’m working my way through the book Groovy in Action (Manning), which has jumped to the top of my favorite technical books list. I’m still learning, but I knew there was a Groovy library for accessing Excel, and I knew Groovy had a “builder” for outputting XML. I just needed to see how to write the actual code. I set up a sample Excel spreadsheet with a few rows of data and went to work.

Here’s the result. It’s about 25 lines of code all told. In other words, it’s almost trivial. I’m amazed.

package com.kousenit;


import org.codehaus.groovy.scriptom.ActiveXProxy


def addresses = new File('addresses.xls').canonicalPath
def xls = new ActiveXProxy('Excel.Application')

// get the workbooks object
def workbooks = xls.Workbooks
def workbook = workbooks.Open(addresses)

// select the active sheet
def sheet = workbook.ActiveSheet

// get the XML builder ready
def builder = new groovy.xml.MarkupBuilder()
builder.people {

for (row in 2..1000) {
def ID = sheet.Range("A${row}").Value.value
if (!ID) break

// use the builder to write out each person
person (id: ID) {
name {
firstName sheet.Range("B${row}").Value.value
lastName sheet.Range("C${row}").Value.value
}

address {
street sheet.Range("D${row}").Value.value
city sheet.Range("E${row}").Value.value
state sheet.Range("F${row}").Value.value
zip sheet.Range("G${row}").Value.value
}
}
}
}

// close the workbook without asking for saving the file
workbook.Close(false, null, false)
// quits excel
xls.Quit()
xls.release()

I’d call that a successful experiment. It certainly was a happy one. I know I’ll do more in the future. I’d bet that somebody with more experience could show me how to condense that even further.

Groovy is just plain fun, and I haven’t felt that way about Java for a long, long time.

About Ken Kousen
I am a Java Champion and the author of the books "Modern Java Recipes" (O'Reilly Media), "Gradle Recipes for Android" (O'Reilly Media), and "Making Java Groovy" (Manning), as well as over a dozen video courses at Safari Books Online. I'm a regular member of the No Fluff, Just Stuff conference tour and have given talks all over the world. Through my company, Kousen IT, Inc, I've taught training courses to and worked with thousands of developers.

4 Responses to Groovyness with Excel and XML

  1. David says:

    Thanks for the tip!
    I’m happy to discover all these useful libraries for Groovy, they’re helping a lot trying to leave PHP.
    Hope I can contribute some of my own at some point (I’m too still working through the GIA book, which is relly great as you said).

    Thanks again!

  2. fantastic, this is what i’m looking for.

  3. I almost never comment, but I read a few of the comments on this page Groovyness with Excel and XML | Stuff I’ve learned recently…. I actually do have 2 questions for you if it’s okay.
    Could it be simply me or does it seem like a
    few of the comments come across like they are left by brain dead individuals?
    😛 And, if you are posting on other online sites, I
    would like to keep up with anything fresh you have to post.
    Could you make a list of every one of all your social sites like your linkedin profile, Facebook
    page or twitter feed?

  4. Payal says:

    “for (row in 2..1000) {
    def ID = sheet.Range(“A${row}”).Value.value
    if (!ID) break”

    Is there no other way to determine if we have reached the end of used excel rows?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: