Import Data from Excel in Groovy/Grails(3)
Student data (Sample Excel file for upload)
Create a Grails application
First of all, Assuming that you have installed the required tools like JDK 8 and Groovy/Grails 3, we go on to create a grails application. Nevertheless, if you haven’t, you can installjava, install groovy and install grails.
Open you terminal/cmd and type the following command
grails create-app ExcelImporter
Add Grails Import Plugin Dependency
Add the following line to your build.gradle file
compile 'org.grails.plugins:excel-import:3.0.0.RC2'
Create a Domain Class for destination data
We would be creating a domain class that represents the destination of our uploaded excel. Go to your terminal/cmd and type the below command
grails create-domain-class Student
Creating a Student Domain class that has three fields. name,email and score. Open ‘grails-app/domain/excelImporter/Student.groovy’ and add the following lines of code
class Student { String email String name String score static constraints = { }}
Create Controller for routing Request
We need to create a controller that would handle the request for uploading the excel file. Open your terminal and type the following command
grails create-controller ExcelImport
The above command would create a controller and view folder at ‘grails-app/views/excelImport’. Create an index.gsp file in the folder location and add the following lines of code.
<!doctype html><html><head> <meta name="layout" content="main"/> <title>Excel Importer</title></head><body> <div id="content" role="main"> <section class="row colset-2-its"> <h2>Excel Importer</h2> <g:if test="${flash.message}"> <div class="message" role="alert"> ${flash.message} </div> </g:if> <g:uploadForm action="uploadFile" > <fieldset> <div class="form-group"> <input type="file" name="excelFile" class="form-control"/> </div> </fieldset> <fieldset> <g:submitButton name="uploadbutton" class="save" value="Upload" /> </fieldset> </g:uploadForm> </section> </div></body>
Next, we create a method(controller action) In our controller to handle the file upload request. Open and edit ‘grails-app/controllers/excelImporter/ExcelImportController.groovy’ and add the following lines of code.
class ExcelImportController {
/** * Excel import Service */ XlsxImportService xlsxImportService
def index(){
}
/** * Check if a file is of type, excel * @param fileType * @return */ protected boolean isValidFileType(String fileType) {
List allowedFileTypes = [
'application/vnd.openxmlformats- officedocument.spreadsheetml.sheet'
]
if (fileType in allowedFileTypes) {
return true
}
return false
}
def uploadFile(){
MultipartFile excel = request.getFile('excelFile') // get Excel file from form
String fileType = excel.getContentType()
// check if file is of type,excel.
if (!isValidFileType(fileType) || excel.isEmpty()) {
flash.message = 'The uploaded file is empty or is not an excel file'
redirect(action: 'index')
return
}
if(xlsxImportService.importData(excel.getInputStream())){
flash.message = 'Excel file was uploaded successfully'
return redirect(action:'index')
}
flash.message = 'There was error during excel upload'
return redirect(action:'index')
}
isValidFileType() action is used to validate the type of file being uploaded to make sure it is an excel file.
isValidFileType() action has the protected access modifier which makes it not accessible as an endpoint. i.e visiting localhost:8080/excelImport/isValidFileType won’t trigger the action.
Create Service class for data import
Following grails loose coupling strategy, we need a service class to handle the data import. Open your terminal and type the following command.
grails create-service XlsxImport
Open and edit grails-app/services/excelImporter/XlsxImportService and add the following code.
@Transactional
class XlsxImportService extends AbstractExcelImporter {
/** * Excel import service from excel import plugin */ ExcelImportService excelImportService
/** * Apache Poi service */ PoiService poiService
/** * Mapping the excel columns to columns on the DB */ static Map EXCEL_COLUMN_MAP = [
sheet : '',
startRow : 1,
columnMap: [
'A': 'name',
'B': 'email',
'C': 'score'
]
]
/** * Import the messages from excel and save to the DB * We are using the first sheet from the excel * also assigning the sheet-name to the sheet config in EXCEL_COLUMN_MAP * * @param inputStream * @param handle * @return */def importData(InputStream inputStream) {
def workbook = poiService.getXlsxWorkbook(inputStream)
def firstSheet = workbook.getSheetAt(0) // get the first sheet in the excel
EXCEL_COLUMN_MAP.sheet = firstSheet.getSheetName()
List studentList = excelImportService.
convertColumnMapConfigManyRows(workbook, EXCEL_COLUMN_MAP)
if (studentList.size() == 0) {
return false
}
studentList.each { student->
new Student(name:student.name,email:student.email,
score:student.score).save(flush:true,failOnError:true)
}
return true
}
The static Map EXCEL_COLUMN_MAP represents configuration for the ExcelImport Plugin.
The sheet represents the excel-sheet to import. The startRow represents the row from which importation should start, While the columnMap represents the column headers in the excel file mapping to database column/Domain class properties.
Conclusion
You can now test out your Excel upload/import. Let me know if you have any issues. You can also play around with the implementation, making changes where necessary to suit your needs.
