Example how to convert a result set (not a list of domain objects)

[hansgru]

Please add an example of how to export a result set (not just list of domain objects)

[jakubnabrdalik]

Not sure, what you mean. What class do you want to convert? Can you give an example?

[hansgru]

Not sure, what you mean. What class do you want to convert? Can you give an example?

I don't want to convert a domain object but directly the result set of a direct SQL Select query.
Of course, I could convert the result in a list of maps and than pass it wot the excel-export closure, but that would be quite inefficient.

This is mostly required, since reports are too complicated to be able to express them as simple objects queries, and also span too many entities, hence the requirement for direct SQLs (resultset to XSLX) conversion.

[jakubnabrdalik]

Now I get it. But I don't have an example (never had that business case in grails).
Perhaps you can make a pull request, once you have that working?

[hansgru]

Perhaps you can make a pull request, once you have that working?

I have it working as I mentioned above: " I could convert the result in a list of maps and than pass it wot the excel-export closure, but that would be quite inefficient."

class ExportController {
    def dataSource
    def reportOne() {
        def sql = new Sql(dataSource)

        // file with complicated SQL
        def filePath = "resources/sqls/export_report_one.sql"

        // This line lets the application know where the file is located:
        def fileHolder = grailsApplication.parentContext.getResource("classpath:$filePath")

        // headers to extract from the result
        def headers = ['col1','col2', ...]  

        // copy everything from the resultset.
        def exports  = []
        sql.eachRow(sqlString, {
            exports << it.toRowResult()
        })
        def exportFile = "export_report_one_"+(new Date().format("yyyy-MM-dd_HH-mm-ss"))+".xlsx"
        response.setHeader("Content-Disposition", "attachment; filename=${exportFile}");

        new WebXlsxExporter().with {
            fillHeader(headers)
            add(exports, headers)
            save(response.outputStream)
        }
    }
}

POI has a streaming API however, so that must be used in order to be able to push something directly from the resultset.

[jravinder]

I think you can simply use the results from the sql.rows() as is

       results = sql.rows(query)

        WebXlsxExporter webXlsxExporter = new WebXlsxExporter()

        webXlsxExporter.with {
            setResponseHeaders(response)
            fillHeader(headers)
            add(results, withProperties)
            save(response.outputStream)
        }