Grails Example - Upload Excel Data File To Database

Nov 30, 2014 Snippet comments

This example will show how to handle Excel upload and save each row in the file as a record in the database.

Target Behavior

This is the sample file that we will upload. The file contains a String, Date, and Numeric columns to show later how to parse such contents.
XX00

We will have a record listing page to show all records in the database. Such that uploaded records can be viewed quickly.
XX01

This is the upload form that will accept an excel file:
XX02

After upload, the records can be seen in the listing page.
XX03

Add JExcelApi library

We will use the JExcelApi library for reading/parsing excel files.

In BuildConfig.groovy, add "net.sourceforge.jexcelapi:jxl:2.6.12" to the dependencies section:

dependencies {
    runtime 'net.sourceforge.jexcelapi:jxl:2.6.12'
}

Model

This is the data model that corresponds to the columns in the excel file.

package asia.grails.sample
class Person {
    String lastName
    String firstName
    Date dateOfBirth
    int numberOfChildren
    static constraints = {
    }
}

Show Form

This is the controller and GSP code to show the upload page.

PersonController.groovy

package asia.grails.sample
class PersonController {
    def upload() {
    }
}

upload.gsp

<%@ page import="asia.grails.sample.Person" %>
<!DOCTYPE html>
<html>
<head>
    <meta name="layout" content="main">
    <title>Upload</title>
</head>

<body>
<div class="nav" role="navigation">
    <ul>
        <li><g:link class="list" action="list">Person List</g:link></li>
    </ul>
</div>

<div id="upload-data" class="content scaffold-create" role="main">
    <div class="content scaffold-create" role="main">
        <h1>Upload Data</h1>
        <g:if test="${flash.message}"><div class="message" role="status">${flash.message}</div></g:if>
        <g:uploadForm action="doUpload">
            <fieldset class="form">
                <input type="file" name="file" />
            </fieldset>
            <fieldset class="buttons">
                <g:submitButton name="doUpload" value="Upload" />
            </fieldset>
        </g:uploadForm>
    </div>
</div>
</body>
</html>

Parse Excel

This is the controller code that will receive the file upload and insert each row to the database:

package asia.grails.sample
class PersonController {
    private final static int COLUMN_LAST_NAME = 0
    private final static int COLUMN_FIRST_NAME = 1
    private final static int COLUMN_DATE_OF_BIRTH = 2
    private final static int COLUMN_NUMBER_OF_CHILDREN = 3

    def doUpload() {
        def file = request.getFile('file')
        Workbook workbook = Workbook.getWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheet(0);

        // skip first row (row 0) by starting from 1
        for (int row = 1; row < sheet.getRows(); row++) {
            LabelCell lastName = sheet.getCell(COLUMN_LAST_NAME, row)
            LabelCell firstName = sheet.getCell(COLUMN_FIRST_NAME, row)
            DateCell dateOfBirth = sheet.getCell(COLUMN_DATE_OF_BIRTH, row)
            NumberCell numberOfChildren = sheet.getCell(COLUMN_NUMBER_OF_CHILDREN, row)

            new Person(lastName:lastName.string , firstName:firstName.string ,
                    dateOfBirth:dateOfBirth.date, numberOfChildren:numberOfChildren.value).save()

        }
        redirect (action:'list')
    }
}

Row index starts from 0. It means the first row is at index 0, the second row is at index 1, and so on. Since we dedicate the first line to the column header of the excel file, we will skip it.
Note also the different Cell class to receive different types of data.

LabelCell - for string content
DateCell - for date content
NumberCell - for number content

Notes

Note that this is far from being suitable for production quality. This is just to help developers fit together the components to be able to upload an excel file.
The full source code for this example can be viewed here or can be downloaded here.

Rest of the code

Here is the full code the controller PersonController.groovy

package asia.grails.sample

import jxl.DateCell
import jxl.LabelCell
import jxl.NumberCell
import jxl.Sheet
import jxl.Workbook

class PersonController {

    private final static int COLUMN_LAST_NAME = 0
    private final static int COLUMN_FIRST_NAME = 1
    private final static int COLUMN_DATE_OF_BIRTH = 2
    private final static int COLUMN_NUMBER_OF_CHILDREN = 3

    def index() {
        redirect(action: "list", params: params)
    }

    def list(Integer max) {
        params.max = Math.min(max ?: 10, 100)
        [personInstanceList: Person.list(params), personInstanceTotal: Person.count()]
    }

    def upload() {
    }

    def doUpload() {
        def file = request.getFile('file')
        Workbook workbook = Workbook.getWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheet(0);

        // skip first row (row 0) by starting from 1
        for (int row = 1; row < sheet.getRows(); row++) {
            LabelCell lastName = sheet.getCell(COLUMN_LAST_NAME, row)
            LabelCell firstName = sheet.getCell(COLUMN_FIRST_NAME, row)
            DateCell dateOfBirth = sheet.getCell(COLUMN_DATE_OF_BIRTH, row)
            NumberCell numberOfChildren = sheet.getCell(COLUMN_NUMBER_OF_CHILDREN, row)

            new Person(lastName:lastName.string , firstName:firstName.string ,
                    dateOfBirth:dateOfBirth.date, numberOfChildren:numberOfChildren.value).save()

        }
        redirect (action:'list')
    }

}

Here is list.gsp code:


<%@ page import="asia.grails.sample.Person" %>
<!DOCTYPE html>
<html>
	<head>
		<meta name="layout" content="main">
		<title>Person List</title>
	</head>
	<body>
		<div class="nav" role="navigation">
			<ul>
				<li><g:link class="create" action="upload">Upload Data</g:link></li>
			</ul>
		</div>
		<div id="list-person" class="content scaffold-list" role="main">
			<h1>Person List</h1>
			<g:if test="${flash.message}">
			<div class="message" role="status">${flash.message}</div>
			</g:if>
			<table>
				<thead>
					<tr>
						<g:sortableColumn property="dateOfBirth" title="Date Of Birth" />
						<g:sortableColumn property="firstName" title="First Name" />
						<g:sortableColumn property="lastName" title="Last Name" />
						<g:sortableColumn property="numberOfChildren" title="Number Of Children" />
					</tr>
				</thead>
				<tbody>
				<g:each in="${personInstanceList}" status="i" var="personInstance">
					<tr class="${(i % 2) == 0 ? 'even' : 'odd'}">
						<td><g:link action="show" id="${personInstance.id}">${fieldValue(bean: personInstance, field: "lastName")}</g:link></td>
						<td>${fieldValue(bean: personInstance, field: "firstName")}</td>
						<td><g:formatDate date="${personInstance.dateOfBirth}" type="date" style="LONG"/></td>
						<td>${fieldValue(bean: personInstance, field: "numberOfChildren")}</td>
					</tr>
				</g:each>
				</tbody>
			</table>
			<div class="pagination">
				<g:paginate total="${personInstanceTotal}" />
			</div>
		</div>
	</body>
</html>

Beginners Tutorial

Example Applications

If you like my posts, like my Facebook page please :)

Links