Working with Excel Import Plugin

In a use case, I needed to import excel spreadsheet and save the data in the database, that was when I came across grails’ excel import plugin. All I had to do was install the plugin and start using it. No Fuss. I created a class in my src/groovy folder
[groovy]
import org.grails.plugins.excelimport.*
class StudentImportXLS extends AbstractExcelImporter {

static Map CONFIG_BOOK_COLUMN_MAP = [sheet:’Sheet1′, startRow: 1, columnMap:[ ‘A’ : ‘firstName’,’B’:’lastName’, ‘D’:’gender’ , ‘E’ :’birthday’, ‘F’: ‘inStudentDirectory’]]

static Map propertyConfigurationMap = [
firstName:([expectedType: ExcelImportUtils.PROPERTY_TYPE_STRING, defaultValue:null]),
lastName:([expectedType: ExcelImportUtils.PROPERTY_TYPE_STRING, defaultValue:0]),
(‘birthday’):([expectedType: ExcelImportUtils.PROPERTY_TYPE_STRING, defaultValue:null])]

public StudentImportXLS(fileName){
super(fileName)
}

List<Map> getStudents(){
List studentList = ExcelImportUtils.convertColumnMapConfigManyRows(workbook, CONFIG_BOOK_COLUMN_MAP, null, propertyConfigurationMap)
return studentList
}
}
[/groovy]

In this class I am defining a Map (CONFIG_BOOK_COLUMN_MAP) which tells you which column of excel file is mapped to which field. It also maps from which row to start extracting the data and which sheet Number.

In the second Map (propertyConfigurationMap) we are providing what type of value we are expecting and a default value for it if the value is missing.

Now what getStudents() method does is it gives a list of maps for the whole spreadsheet and it will return the data you wanted as a list of maps.

All You need to do now is, in your service/controller write
[groovy]
StudentImportXLS importer = new StudentImportXLS(fileName)
def studentsMapList = importer.getStudents()
[/groovy]
here fileName is the path where you have saved the file on your server, and in studentsMapList you get the list of Maps. Now iterate over the list and use the maps to create and save objects. That is about it. 🙂 . The Plug-in provides many more features which we can use.

There was a small problem which I faced with the date, as you would have noticed I am trying to get value as a string for the birthday field (propertyConfigurationMap) getting a date was giving me joda time date. But it was not working properly for some dates like 08/15/2010 (15th August 2010) I was getting a String value but for dates like 08/05/2010 (5th August 2010) it gave me an object of jodatime date. I found a work around for it
[groovy]
// student Map is one object from list of maps.
if (studentMap.birthday instanceof String) {
student.birthday = new Date(studentMap.birthday)
} else {
String tempDate = studentMap.birthday.toString()
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-dd-MM");
student.birthday = sdf.parse(tempDate)
}
[/groovy]

looking at the code it is clear that I am saving the date as java.util.Date not as jodatime date.
Other than this small issue it was great using this plugin and it saves a lot of effort.
Hope it helps.

With Regards
Sachin Anand
sachin@intelligrape.com