import jxl.Workbook
import jxl.Sheet
/**
* 使用jxl实现将数据导入Excel表
*/
class ExcelExportController {
/**
* 用FORM提交请求action=exportData
*/
def exportData = {
def products = Products.list() //导出产品表数据
if (products?.size() > 0) {
render '没有数据'
return
}
def myParams = [:]
myParams.fileName = "产品表"
myParams.sheetName = "产品表"
myParams.rowData = products
myParams.response = response
myParams.columnNames = ['产品ID','产品名','产品类型','产品价格'] //字段名
excelExport(response, myParams) {wsheet, rowData-> //{sheet, rowData->......}作为闭包传给excelExport()方法
rowData.eachWithIndex {object, index->
jxl.write.Label wlabelContent
/**
* 产品ID
*/
wlabelContent = new jxl.write.Label(0, index + 1, object.id)
wsheet.addCell(wlabelContent)
/**
* 产品名
*/
wlabelContent = new jxl.write.Label(1, index + 1, object.productName)
wsheet.addCell(wlabelContent)
/**
* 产品类型
*/
wlabelContent = new jxl.write.Label(2, index + 1, object.productType)
wsheet.addCell(wlabelContent)
/**
* 产品价格
*/
wlabelContent = new jxl.write.Label(4, index + 1, object.productPrice)
wsheet.addCell(wlabelContent)
}
}
}
/**
* closure闭包指向excelExport(response, myParams)后面的代码
*/
def excelExport(response, params, Closure closure) {
def fileName = new String("${params.fileName}".getBytes("UTF-8"),"8859_1")
def os = response.getOutputStream()
response.reset()
response.setHeader("Content-disposition", "attachment;filename=${fileName}.xls")
response.setContentType("application/msexcel")
jxl.write.WritableWorkbook wbook = Workbook.createWorkbook(os)
jxl.write.WritableSheet wsheet = wbook.createSheet("${params.sheetName}", 0)
params.columnNames.eachWithIndex {object, index->
jxl.write.Label wlabel0
wlabel0 = new jxl.write.Label(index, 0, object) //index:列号 0:行号 object:字段名
wsheet.addCell(wlabel0)
}
closure(wsheet, rowData) //闭包调用,把产品表中的数据写到sheet里
try {
wbook.write()
response.flushBuffer()
wbook.close()
os?.close()
} catch (Exception e) {
println "exception"
}
}
}
import jxl.Sheet
/**
* 使用jxl实现将数据导入Excel表
*/
class ExcelExportController {
/**
* 用FORM提交请求action=exportData
*/
def exportData = {
def products = Products.list() //导出产品表数据
if (products?.size() > 0) {
render '没有数据'
return
}
def myParams = [:]
myParams.fileName = "产品表"
myParams.sheetName = "产品表"
myParams.rowData = products
myParams.response = response
myParams.columnNames = ['产品ID','产品名','产品类型','产品价格'] //字段名
excelExport(response, myParams) {wsheet, rowData-> //{sheet, rowData->......}作为闭包传给excelExport()方法
rowData.eachWithIndex {object, index->
jxl.write.Label wlabelContent
/**
* 产品ID
*/
wlabelContent = new jxl.write.Label(0, index + 1, object.id)
wsheet.addCell(wlabelContent)
/**
* 产品名
*/
wlabelContent = new jxl.write.Label(1, index + 1, object.productName)
wsheet.addCell(wlabelContent)
/**
* 产品类型
*/
wlabelContent = new jxl.write.Label(2, index + 1, object.productType)
wsheet.addCell(wlabelContent)
/**
* 产品价格
*/
wlabelContent = new jxl.write.Label(4, index + 1, object.productPrice)
wsheet.addCell(wlabelContent)
}
}
}
/**
* closure闭包指向excelExport(response, myParams)后面的代码
*/
def excelExport(response, params, Closure closure) {
def fileName = new String("${params.fileName}".getBytes("UTF-8"),"8859_1")
def os = response.getOutputStream()
response.reset()
response.setHeader("Content-disposition", "attachment;filename=${fileName}.xls")
response.setContentType("application/msexcel")
jxl.write.WritableWorkbook wbook = Workbook.createWorkbook(os)
jxl.write.WritableSheet wsheet = wbook.createSheet("${params.sheetName}", 0)
params.columnNames.eachWithIndex {object, index->
jxl.write.Label wlabel0
wlabel0 = new jxl.write.Label(index, 0, object) //index:列号 0:行号 object:字段名
wsheet.addCell(wlabel0)
}
closure(wsheet, rowData) //闭包调用,把产品表中的数据写到sheet里
try {
wbook.write()
response.flushBuffer()
wbook.close()
os?.close()
} catch (Exception e) {
println "exception"
}
}
}