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:
On click, a download confirmation will be shown instead of the browser trying to render the content:
The downloaded Excel file will have two sheets:
The first sheet will contain this:
And the second sheet will contain this:
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
Grails Project Themes
