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'.

Armaiti · Accepted Answer · 2016-02-21T19:28:12.887

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.

ok thanks for helping me and corrected the problem but now I get this Cannot cast object 'jxl.biff.EmptyCell@36c9ee06' with class 'jxl.biff.EmptyCell' to class 'jxl.LabelCell' — D.Sanxhez, Feb 21 '16 at 15:32
Looks like the same issue but different field. You probably have to validate any field for null/empty before assign it to *Cell or variables. — Armaiti, Feb 21 '16 at 15:42
I mark error in this line, but if you save the data. LabelCell lastName = sheet.getCell(COLUMN_LAST_NAME, row) — D.Sanxhez, Feb 21 '16 at 16:29
Again, validate each row of your excel sheet before saving it. If the validation fails, skip that row. My intention was to give you proper answer for your post. It looks like you have empty cells and your program doesn't like them. The final answer is validate the rows. — Armaiti, Feb 21 '16 at 16:45

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

1 Answers1