And the second sheet will contain this:
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>
In BuildConfig.groovy, add "net.sourceforge.jexcelapi:jxl:2.6.12" to the dependencies section:
dependencies { runtime 'net.sourceforge.jexcelapi:jxl:2.6.12' }
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"))