Grails Excel File Upload

Just like most things in Grails, parsing data from an uploaded Excel file is relatively easy. The first thing I did was go out and grab JExcelAPI. JExcelAPI will let you read and write Excel spreadsheets from Java. It doesn't support all of the advanced features (like charts) of Excel, but for my needs it works well. You can also look at Apache POI for reading and writing more MS Office formats, and there's numerous other options if you're on Windows or want to plop down some money for a commercial library.

After dropping JExcelAPI into my lib directory, I went ahead and created a view to upload the spreadsheet. The form can be as simple or as fancy as you want, just as long as you have a file input field:

<g:form action="upload" method="post" enctype="multipart/form-data">
<label for="file">File:</label>
<input type="file" name="file" id="file"/>
<input class="save" type="submit" value="Upload"/>
</g:form>

Now we need to create the upload method in our controller to parse the Excel file:

def upload = {
 // get our multipart
 MultipartHttpServletRequest mpr = (MultipartHttpServletRequest)request;
 CommonsMultipartFile file = (CommonsMultipartFile) mpr.getFile("file");
  
 // create our workbook
 Workbook workbook = Workbook.getWorkbook(file.inputStream)
 Sheet sheet = workbook.getSheet(0)

 def added = 0;
 def skipped = [];
 for (int r = 3; r < sheet.rows; r++) {
  // get our fields
  def top = sheet.getCell(0, r).contents
 
  def bottom = sheet.getCell(1, r).contents
  if (bottom == "") bottom = null
 
  def number = sheet.getCell(2, r).contents
  if (number == "") number = 1

  def spacing = sheet.getCell(3, r).contents
  if (spacing == "") spacing = 0.0
 
  def type = sheet.getCell(4, r).contents
  def notes = sheet.getCell(5, r).contents
 
  // check that we got a top and a type
  if (top == null || top == "") {
   // do nothing
  } else if ((new SampleRequest(
    "investigator":user,
    "hole":hole,
    "sampleGroup":group,
    "top":top,
    "bottom":bottom,
    "sampleType":type,
    "samplesRequested":number,
    "sampleSpacing":spacing,
    "notes":notes)).save()) {
   added++
  } else {
   skipped += (r + 1)
  }
 }
 workbook.close()

 // generate our flash message
 flash.message = "${added} sample request(s) added."
 if (skipped.size() > 0) {
  flash.message += "  Rows ${skipped.join(', ')} were skipped because they were incomplete or malformed"
 }
 redirect(controller:"home", action:"index")
}

My spreadsheet has a fairly simple format with fixed columns, some being optional and some required, so I hard coded the column indices.

In a future blog post, I'll show how to output an Excel spreadsheet with Grails and JExcelAPI.

Edit: Blogger seems to have eaten some of the code when I updated tags so I just updated it.