Wednesday, March 19, 2008

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.

15 comments:

Scott Davis said...

Josh,

Great tip on ingesting an Excel spreadsheet. If you want to return an Excel spreadsheet, the quickest way I've found is to send out a regular old HTML table with the appropriate MIME type. Normally HTML comes back as "text/html" -- if you change it to response.setContentType("application/vnd.ms-excel"), Excel should slurp it in without an issue.

Josh Reed said...

Thanks for the tip, Scott. Do you know if it works on non-Windows machines? I've got a lot of users using Macs and even some on Linux. I wonder how the various Office clones react to this? I've used this trick in the past in an all-MS shop and it worked great. It was really slick.

Thanks for stopping by.

Cheers,
Josh

Scott Davis said...

Works like a champ on non-M$ platforms. I'm using FireFox on OS X. Obviously IE will render the spreadsheet inline. FF, Safari, et al need to have the MIME type registered with the proper "viewer" (i.e. MS Excel) and they'll pop up the external viewer.

kd1973 said...

Today we got into a issue of a binary stream (content type : application/msword) not opening on a vista machine. found out that MS Office was not installed. But there was openoffice. Changed the content type to open office and worked
(temp fix).
Is there a way to dynamically assign the mime type based on client's installed word processing program.
-kalyan

Josh Reed said...

Kalyan,

I haven't seen of a way to query the available word processor (software) from a webapp. You might be able to check the User-Agent header to make an educated guess, especially if there is a standard setup (e.g. all Vista machines in the enterprise are running OO). But that's brittle at best.

Cheers,
Josh

Scott Davis said...

WRT: client-side viewer configuration -- it's a bit of a pickle, isn't it? Unfortunately, there's no way that I know of for a web browser to report which spreadsheet applications are installed locally, any more than Firefox would report which directory on the local file system is being used for the local cache or IE would report back how much RAM is installed. It's an encapsulation issue. If this is really an issue, you might look into creating a Google Spreadsheet app, an Ajax table (YUI, Ext), or a Flash app (or Java FX, or Silverlight, etc.). Each of these solutions put you squarely back into the driver seat, rather than depending on a client-side configuration issue that you literally have no control over with HTTP.

Josh Reed said...

The other option I had thought of was trying to use some mime type chicanery and sending it as multipart/alternative and then duplicating the word document in the message twice, once with the Word content type and once with the OOo content type.

I know most mail programs can handle the multipart/alternative mime type but I don't know how well it is supported by browsers. Plus, I think you would have to push around twice as many bits for every request to prevent a problem that may or may not be very widespread.

I think Scott is right. If this really is a requirement, then you may need to look at other solutions.

Cheers,
Josh

fabs said...

Scott, unfortunately Apple Numbers'08 shows the HTML markup instead of a proper sheet (I checked it also using the code from chapter 12.3 'returning an excel spreadsheet' of your 'groovy recipes' book).

Emmanuel said...

Thanks for posting Josh

Vyas said...

Excellent!!

Saoirse said...

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.


Sara

http://pianonotes.info

kompoz said...

Do you know of an easy way to read in a CSV file, parse it line by line storing its elements in a database using GRAILS?

fellaws said...

Hello every body,
Im using the same code to upload an excel file , but i have this exception :

Caused by: org.codehaus.groovy.runtime.InvokerInvocationException: java.io.IOException: Unable to read entire header; 45 bytes read; expected 512 bytes
... 1 more
Caused by: java.io.IOException: Unable to read entire header; 45 bytes read; expected 512 bytes
at org.apache.poi.poifs.storage.HeaderBlockReader.alertShortRead(HeaderBlockReader.java:149)
at org.apache.poi.poifs.storage.HeaderBlockReader.(HeaderBlockReader.java:98)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.(POIFSFileSystem.java:151)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:317)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:298)

Alex said...

I have many excel files on my PC. And one day I lost everything on unknown cause. For luck I promptly solved out this issue with the aid of a program. It has quite good capacities for resolving other composite troubles on my view - repair recovered Excel file.

Exequiel Molina said...

Hola, el ejemplo funciona muy bien.
debo agregar que para poder utilizar las clases MultipartHttpServletRequest y CommonsMultipartFile debo importarlas(para la primera org.springframework.web.multipart.MultipartHttpServletRequest y para la segunda org.springframework.web.multipart.commons.CommonsMultipartFile) ya que estaba utilizando el grail v. 1.3.7 y estas no se reconocian.

ademas debo agregar (for (int r = 3; r < sheet.rows; r++) ) el valor de r debe de ser 1 para empezar en la fila 2.

para ayudar mas con este ejemplo me gustaria plantear el sgte. desafio el cual seria: al subir un excel a la base de datos, deberian existir funciones que validen si los datos ya existen o no. y tomar diferentes acciones.

estoy trabajando en eso...

Saludos
Atte.