Cannot cast object 'jxl.biff.EmptyCell@47821b4b' with class 'jxl.biff.EmptyCell' to class 'jxl.DateCell'

Question

My Domain Model

package sample

class Person {

String lastName
String firstName
Date dateOfBirth
int numberOfChildren
static constraints = {
}
} 

My Controller

package sample

/*imported libraries.*/

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')
}


} 

I want to load data from an .xls file in my grails database the problem is that the xls file loading data are not saved and I get the following error. Cannot cast object 'jxl.biff.EmptyCell@47821b4b' with class 'jxl.biff.EmptyCell' to class 'jxl.DateCell'.

Answer 1

It looks like that, in some cases, dateOfBirth does not have any value. In those cases empty value can not be converted to date. This is reason why can not cast class 'jxl.biff.EmptyCell' to class 'jxl.DateCell' is thrown.

Make sure you set dateOfBirth to null, when dataOfBirth cell is empty. If you have constraint on dateOfBirth field it will automatically throw an exception.

Empty constraints means that all the attributes in Person class are required. Obviously there are empty fields in the Excel sheet which cause the entire process to stop. If you still like to ignore those rows with empty field and still process the good ones, you can do one or both of the following:

Check if each field is empty and call continue to process the next row. This would be my recommendation.

AND/OR

Wrap inside the loop with try/catch:

    for (int row = 1; row < sheet.getRows(); row++) {
        try{
                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(failOnError: true)
        }catch(e){
          // log the error and the row number so that you know which row failed
        }

    }

To have a better understanding on which field failed validation, write proper field validation error message.