Grails Cookbook - A collection of tutorials and examples

Grails Example Application - Download / Export Excel File

This example application will show how to export excel file in a Grails application without using a plugin. For simplicity, JExcelApi is used. The sample will include multiple sheet just in case you need such feature in your project. The output of the finished application will look like this:

A simple page with download link is shown:
zx0
On click, a download confirmation will be shown instead of the browser trying to render the content:
zx1
The downloaded Excel file will have two sheets:
zx2
The first sheet will contain this:
zx3

And the second sheet will contain this:
zx4

Render the download page

The controller and view code to display the download page should be simple:

SampleExcelController:

package asia.grails.excel
class SampleExcelController {
    def index() {}
}

index.gsp:

<!DOCTYPE html>
<html>
<head>
    <meta name="layout" content="main"/>
    <title>Simple Chat</title>
</head>
<body>
    <g:link action="downloadSampleExcel">Download Sample Excel</g:link>
</body>
</html>

Add JExcelApi library

In BuildConfig.groovy, add "net.sourceforge.jexcelapi:jxl:2.6.12" to the dependencies section:

dependencies {
    runtime 'net.sourceforge.jexcelapi:jxl:2.6.12'
}

Download Code

The complete code that contains the logic to generate the excel file is this:

package asia.grails.excel
import jxl.Workbook
import jxl.write.Label
import jxl.write.WritableSheet
import jxl.write.WritableWorkbook
class SampleExcelController {
    def index() {}
    def downloadSampleExcel() {
        response.setContentType('application/vnd.ms-excel')
        response.setHeader('Content-Disposition', 'Attachment;Filename="example.xls"')
        WritableWorkbook workbook = Workbook.createWorkbook(response.outputStream)
        WritableSheet sheet1 = workbook.createSheet("Students", 0)
        sheet1.addCell(new Label(0,0, "First Name"))
        sheet1.addCell(new Label(1,0, "Last Name"))
        sheet1.addCell(new Label(2,0, "Age"))
        sheet1.addCell(new Label(0,1, "John"))
        sheet1.addCell(new Label(1,1, "Doe"))
        sheet1.addCell(new Label(2,1, "20"))
        sheet1.addCell(new Label(0,2, "Jane"))
        sheet1.addCell(new Label(1,2, "Smith"))
        sheet1.addCell(new Label(2,2, "18"))
        WritableSheet sheet2 = workbook.createSheet("Courses", 1)
        sheet2.addCell(new Label(0,0, "Course Name"))
        sheet2.addCell(new Label(1,0, "Number of units"))
        sheet2.addCell(new Label(0,1, "Algebra"))
        sheet2.addCell(new Label(1,1, "3"))
        sheet2.addCell(new Label(0,2, "English Grammar"))
        sheet2.addCell(new Label(1,2, "5"))
        workbook.write();
        workbook.close();
    }
}

You need this code below to tell the browser about the content that you will deliver. This will tell the browser to download the file and what the filename is:

response.setContentType('application/vnd.ms-excel')
response.setHeader('Content-Disposition', 'Attachment;Filename="example.xls"')

You can create the excel file by creating an instance of WritableWorkbook. You need to pass the response.outputStream so that the excel file is not saved to the server's file system, but instead deliver to the browser:

WritableWorkbook workbook = Workbook.createWorkbook(response.outputStream)

When creating a sheet, you need to pass the name of the sheet and an integer to denote it's ordering. E.g. Since Students is 0 and Courses is 1, the sheet Students will appear first.

WritableSheet sheet1 = workbook.createSheet("Students", 0)

Writing contents to a cell in a sheet just needs the column, row, and string contents as parameters:

sheet2.addCell(new Label(0,1, "Algebra"))

Remarks

The above code is just an illustration of how to use JExcelApi with Grails without using any plugin. By providing a clean and simple example, you can develop your own helpers to suit your project's needs.
The full source code for this example can be viewed here or can be downloaded here.

List of Example Applications
  • Simple Ajax Chat
  • Simple Document Management System
  • Save Documents To Database
  • Download / Export Excel File
  • Download / Export ZIP File
  • Grails Forum Application

  • Tags: download, example, excel, jexcelapi