Import Data from Excel file using Grails 3 and Apache POI

by Didin J. on Mar 03, 2017 Import Data from Excel file using Grails 3 and Apache POI

Simple and easy way to import data from Excel file using Grails 3 and Apache POI.

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 GrailsImportExcel

Enter Grails 3 interactive console.

grails

Before doing anything else, make sure your Grails 3 application is running correctly by type this command in the interactive console.

run-app

Open 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 Subscriber

Will 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 ExcelImporter

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

Grails 3 and Apache POI Import Data From Excel - Excel File Sample

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.

Grails 3 and Apache POI - Import Data from Excel - File importer view

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.

The following resources might be useful for you:

Loading…