A few years ago, there is few plugin for importing data from Excel file in Grails 1 and 2. But some of them are not continuing plugin development for Grails 3 until a few months ago Grails Excel Import plugin update for use with Grails 3. But, this time I write about importing data from Excel file using Grails 3 directly using Apache POI java library that uses as the dependency.
As usually, we are starting the tutorial from scratch by creating new project or application.
1. Create Grails 3 Application
We assume that you are already installed required tools like JDK 8 and Grails 3, this tutorial using latest Grails version 3.2.6. Open the terminal or cmd then type this command to create new Grails 3 application.
grails create-app GrailsImportExcelEnter Grails 3 interactive console.
grailsBefore doing anything else, make sure your Grails 3 application is running correctly by type this command in the interactive console.
run-appOpen and view it in your browser. If everything is OK, stop the application.
stop-app
2. Add Apache POI Dependency.
To make Grails 3 works with Apache POI, just add two lines of dependencies of Apache POI. Open and edit build.gradle from the root of project folder then add this lines at dependencies section.
compile 'org.apache.poi:poi:3.9'
compile 'org.apache.poi:poi-ooxml:3.9'Go to terminal then type this command to compile the application.
compile
3. Create Domain for destination data
Right now, we will create a domain class that uses as destination data from imported Excel data. Go to your terminal and Grails 3 interactive console then type this command.
create-domain-class SubscriberWill are creating a domain class name Subscriber which is an email subscriber list that has two fields. Open and edit 'grails-app/domain/grailsimportexcel/Subscriber.groovy' then add this lines of code.
class Subscriber {
String email
String fullname
static constraints = {
email email:true
fullname blank: false, size: 0..100
}
}
4. Create controller and view for import Excel data
We need to create controller and view for importing Excel data using file uploader. The view just contains a form with an input file and upload button. Open terminal and Grails 3 interactive console then type this command to create the controller.
create-controller ExcelImporterThis will create excelImporter controller and folder in view. Now, create GSP file in folder views/excelImporter and name it 'index.gsp'. Open and edit this file the add HTML tag like this.
<!doctype html>
<html>
<head>
<meta name="layout" content="main"/>
<title>Excel Importer</title>
<asset:link rel="icon" href="favicon.ico" type="image/x-ico" />
</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="fieldcontain">
<input type="file" name="excelFile" />
</div>
</fieldset>
<fieldset>
<g:submitButton name="uploadbutton" class="save" value="Upload" />
</fieldset>
</g:uploadForm>
</section>
</div>
</body>Create new method for upload and import operation. Open and edit grails-app/controllers/grailsimportexcel/ExcelImportController.groovy then add this method.
def uploadFile() {
def file = request.getFile('excelFile')
if(!file.empty) {
def sheetheader = []
def values = []
def workbook = new XSSFWorkbook(file.getInputStream())
def sheet = workbook.getSheetAt(0)
for (cell in sheet.getRow(0).cellIterator()) {
sheetheader << cell.stringCellValue
}
def headerFlag = true
for (row in sheet.rowIterator()) {
if (headerFlag) {
headerFlag = false
continue
}
def value = ''
def map = [:]
for (cell in row.cellIterator()) {
switch(cell.cellType) {
case 1:
value = cell.stringCellValue
map["${sheetheader[cell.columnIndex]}"] = value
break
case 0:
value = cell.numericCellValue
map["${sheetheader[cell.columnIndex]}"] = value
break
default:
value = ''
}
}
values.add(map)
}
values.each { v ->
if(v) {
Subscriber.findByEmail(v.email)?: new Subscriber(email:v.email,fullname:v.fullname).save flush:true, failOnError:true
}
}
flash.message = "Subscriber imported successfully"
redirect action:"index"
}
}Don't forget to add imports below package.
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import static org.apache.poi.ss.usermodel.Cell.*
import java.io.File
5. Run and Test Excel Importer
This time to run and test Excel importer. First, create excel file contains 2 column with headers.
The excel file should have the header to match our method for getting column name.
Now, run Grails 3 application and open in the browser. In homepage click the link that points to Excel importer page.
That it's, you can try to upload your newly created Excel file. If there's some error occurs, please fill the comment below. If you need the source code, you can find on my Github.
Thanks.
