使用jxl实现将数据导入Excel表

chenfeng0104

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"
}
}
}