How to Export Data to Excel File using Grails 3 and MongoDB

This step by step tutorial on how to export data to excel file using Grails 3, MongoDB and Grails 3 Export Plugin. There's a new update of Grails 3 Excel-Export plugin which updates a few months ago. Because there's a long time to wait for the new update of the excel-export plugin so we are using Grails 3 Export plugin that already updated and ready for Grails 3.

What we export is excel worksheet to display employees attendance which attendance data get dynamically from child collections of the employee. So, we have to create 2 collections that have relations they are Employee and Attendance collections. The export results should be like this.

The following tools and dependencies are required to achieve this tutorial:

- Grails 3.3.0
- MongoDB
- Grails 3 MongoDB Plugin
- Grails 3 Export Plugin

1.  Create a New Grails 3 Application

As usual, we are doing a tutorial from scratch that means starting from creating a new Grails 3 application. We assume you are already installed latest Grails 3.3.0 and latest MongoDB on your machine. So, we do not show how to install that required tools for this tutorial. Now, open the terminal or cmd then go to your Grails projects folder then type this command to create a new Grails 3 application.

grails create-app grails3-excel-export

Now, go to the newly created Grails 3 project folder.

cd grails3-excel-export

Run this command to enter the Grails 3 interactive console.

grails

Now, you ready to type some required command for this tutorial. The first thing to make sure everything still on the path, type this command.

run-app

Now, open the browser then go to this address `http://localhost:8080`, you should see this page if everything working properly.

To stop the application just type this command.

stop-app

2. Add and Configure Grails 3 MongoDB and Export Plugin Dependencies

To add Grails 3 MongoDB and Export plugin dependencies, open and edit `build.gradle` on the root of project folder then add this lines inside dependencies.

compile "org.grails.plugins:mongodb:6.1.3"
compile "org.grails.plugins:export:2.0.0"

Don't forget to comment out all plugin and dependencies related to `hibernate` and `h2`. Now, type this command in the Grails 3 interactive console.

compile

Next, open and edit `grails-app/conf/application.yml` and replace all Hibernate and H2 related datasource section with this.

environments:
    development:
        grails:
            mongodb:
                host: "localhost"
                port: 27017
                username: ""
                password: ""
                databaseName: "grails-mongodb"

    production:
        grails:
            mongodb:
                host: "localhost"
                port: 27017
                username: ""
                password: ""
                databaseName: "grails-mongodb"

That configuration tells Grails to use MongoDB on localhost and port 27017 with database name `grails-mongodb`. For Export plugin, there's no configuration required for standard usage.

3. Create Required Domain Class

As mention above, we need to create to related domain class for Employee and Attendance. Type this command in the Grails 3 interactive console to create it.

create-domain-class Employee
create-domain-class Attendance

Open and edit `grails-app/domain/grails3/excel/export/Employee.groovy` then replace all codes with this.

package grails3.excel.export

class Employee {

  String employeeId
  String employeeName
  static hasMany = [attendances:Attendance]

  static constraints = {
  }
}

Open and edit `grails-app/domain/grails3/excel/export/Attendance.groovy` then replace all codes with this.

package grails3.excel.export

class Attendance {

  static belongsTo = [employee:Employee]
  Date attDate
  String timeIn
  String timeOut

  static constraints = {
    timeIn nullable: true
    timeOut nullable: true
  }
}

4. Populate Employee and Attendance with Dummy Data

For testing export to excel we need to populate employee and attendance with some dummy data. For that, open and edit `grails-app/init/grails3/excel/export/Bootstrap.groovy` then add this lines inside `init`.

def emp1 = Employee.findByEmployeeId("EMP001")?:new Employee(employeeId:"EMP001", employeeName:"John Doe").save(flush:true, failOnError:true)
def emp2 = Employee.findByEmployeeId("EMP002")?:new Employee(employeeId:"EMP002", employeeName:"Jane Doe").save(flush:true, failOnError:true)
def att1 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("01/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("01/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att2 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("02/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("02/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att3 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("03/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("03/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att4 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("04/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("04/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att5 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("05/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("05/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att6 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("06/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("06/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att7 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("07/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("07/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att8 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("08/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("08/08/2017")).save(flush:true, failOnError: true)
def att9 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("09/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("09/08/2017")).save(flush:true, failOnError: true)
def att10 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("10/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("10/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att11 = Attendance.findByEmployeeAndAttDate(emp1,new SimpleDateFormat("dd/MM/yyyy").parse("11/08/2017"))?:new Attendance(employee: emp1, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("11/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att12 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("01/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("01/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att13 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("02/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("02/08/2017")).save(flush:true, failOnError: true)
def att14 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("03/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("03/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att15 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("04/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("04/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att16 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("05/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("05/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att17 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("06/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("06/08/2017")).save(flush:true, failOnError: true)
def att18 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("07/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("07/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att19 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("08/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("08/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att20 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("09/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("09/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att21 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("10/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("10/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)
def att22 = Attendance.findByEmployeeAndAttDate(emp2,new SimpleDateFormat("dd/MM/yyyy").parse("11/08/2017"))?:new Attendance(employee: emp2, attDate: new SimpleDateFormat("dd/MM/yyyy").parse("11/08/2017"), timeIn: "08:01", timeOut: "16:11").save(flush:true, failOnError: true)

Now, run again the application and check on the MongoDB database, you should see those tables exist.

> use grails-mongodb
switched to db grails-mongodb
> show collections
attendance
attendance.next_id
employee
employee.next_id
system.indexes

5. Create Controller for Display Attendance List and Export to Excel

Now, it's time to implement the export to excel. First, create a new controller for it by type this command.

create-controller Employee

That command will create a new `EmployeeController`. Open and edit `grails-app/controllers/grails3/excel/export/EmployeeController.groovy` then replace all codes with this.

package grails3.excel.export

import java.text.SimpleDateFormat

class EmployeeController {

  def exportService

  def index(Integer max) {
    params.max = Math.min(max ?: 10, 100)
    respond Employee.list(params), model:[employeeCount: Employee.count()]
  }

  def exportExcel() {
    params.exportFormat = "excel"
    params.extension = "xls"

    if(params.exportFormat && params.exportFormat != "html") {

      def employees = Employee.list(params)

      def results = []
      employees.each { e ->
        def map = [:]
        map.put("employeeId",e.employeeId)
        map.put("employeeName",e.employeeName)

        def attendances = Attendance.findAllByEmployee(e, [sort: "attDate", order: "asc"])
        attendances.each { a ->
          if(a.timeIn && a.timeOut) {
            map.put(new SimpleDateFormat("MMM dd, yyyy").format(a.attDate),a.timeIn+" - "+a.timeOut)
          } else {
            map.put(new SimpleDateFormat("MMM dd, yyyy").format(a.attDate),"-")
          }
        }

        results.add(map)
      }

      response.contentType = grailsApplication.config.grails.mime.types[params.exportFormat]
      response.setHeader("Content-disposition", "attachment; filename=attendance-"+new SimpleDateFormat("ddMMyyy").format(new Date())+".xls")

      List fields = [
        "employeeId",
        "employeeName"
      ]

      Map labels = ["employeeId": "Employee ID", "employeeName": "Employee Name"]

      def atts = Attendance.findAllByEmployee(employees[0])

      atts.each { a ->
        fields.add(new SimpleDateFormat("MMM dd, yyyy").format(a.attDate))
        labels.put(new SimpleDateFormat("MMM dd, yyyy").format(a.attDate), new SimpleDateFormat("MMM dd, yyyy").format(a.attDate))
      }

      Map formatters = [:]
      Map parameters = [:]

      exportService.export(params.exportFormat, response.outputStream, results, fields, labels, formatters, parameters)
    }
  }
}

Now, create a new `grails-app/views/employee/index.gsp` as the view for `EmployeeController`. Open and edit that file then replace all HTML tags with this.

<!DOCTYPE html>
<html>
    <head>
        <meta name="layout" content="main" />
        <g:set var="entityName" value="${message(code: 'employee.label', default: 'Employee')}" />
        <title><g:message code="default.list.label" args="[entityName]" /></title>
    </head>
    <body>
        <a href="#list-employee" class="skip" tabindex="-1"><g:message code="default.link.skip.label" default="Skip to content&hellip;"/></a>
        <div class="nav" role="navigation">
            <ul>
                <li><a class="home" href="${createLink(uri: '/')}"><g:message code="default.home.label"/></a></li>
                <li><g:link class="create" action="exportExcel">Export to Excel</g:link></li>
            </ul>
        </div>
        <div id="list-employee" class="content scaffold-list" role="main">
            <h1><g:message code="default.list.label" args="[entityName]" /></h1>
            <g:if test="${flash.message}">
                <div class="message" role="status">${flash.message}</div>
            </g:if>
            <f:table collection="${employeeList}" properties="['employeeId', 'employeeName']" />

            <div class="pagination">
                <g:paginate total="${employeeCount ?: 0}" />
            </div>
        </div>
    </body>
</html>

That it's, now you can run again the Grails 3 application then open in the browser. Click the link of `EmployeeController` in the Available Controller and you should redirect to this page.

Now, you can click Export to Excel button and see the result of downloaded Excel file.

Please, leave the comment below for any suggestions, critics, and problems. If you need the full source code, you can find it on our GitHub.

Thanks!