Reports

You can download a report containing all the data in your table. The data will be filtered and sorted in the same way as it is presented in the table. The default report file format is Comma-Separated Values (.csv).

Report Button

If you set the reportButton option to true, a Download Report button will be placed at the bottom of the table. You can also use your own custom button. The URL for the report can be obtained by calling a JavaScript function. The name of the function will be

#!javascript
getMyTableReportUrl()

where you replace MyTable with your actual table name. The URL returned by this function will contain the current sorting and filtering. You can obviously change this if you wish.

Report Filename

The default report filename is [MyTable]Report.csv. This can be overridden by setting the 'datatables.MyTable.reportFileName' i18n message.

Custom Reports

If you are using the plugin-provided Grails controller, you can customize the report generation by creating a custom report Grails service. (If you are using your own controller, then you can obviously do whatever you like). The service needs to provide two service methods:

/**
 * Generates a map containing the default filename, the content type and the report data.
 * @param items The list of items to report on.
 * @param tableDefinition The Table Definition that defines the columns in the report.
 */
def formatReport(def items, def tableDefinition) {
    def reportData = [:]
    reportData.defaultFileName = "MyDefaultFileName.txt" // This can be overridden by the datatables.MyTable.reportFileName i18n message.
    reportData.contentType = "text/plain"
    ...
    reportData.report = myReportObject
    return reportData
}

/**
 * Writes the report to the OutputStream.
 */
def writeReport(Map reportData, OutputStream outputStream) {
    OutputStream.write(...)
}

To use a custom report service, set the grails.plugin.datatables.reportService configuration property in your Config.groovy file to the name of your custom service.

Spreadsheet Report Example

The following example shows how you can create a custom spreadsheet report. It uses the Grails Excel Export Plugin to generate an Excel spreadsheet.

import org.apache.poi.ss.usermodel.CellStyle
import org.apache.poi.ss.usermodel.DataFormat
import org.apache.poi.ss.usermodel.IndexedColors
import org.apache.poi.ss.util.DateFormatConverter
import pl.touk.excel.export.WebXlsxExporter

/**
 * Generates and writes out reports based on data in DataTables. This class overrides the default service in the DataTables plugin to generate a spreadsheet report.
 */
class SpreadsheetReportService {
    static final LOCALE = Locale.getDefault()

    /**
     * Generates an Excel format report for the items.
     * @param items The list of items to report on.
     * @param tableDefinition The table definition that defines the columns in the report.
     * @return The report data.
     */
    def formatReport(def items, def tableDefinition) {
        log.debug "formatReport: ${items.size()} items."
        def reportData = [:]
        reportData.defaultFileName = tableDefinition.name + "Report.xls"
        reportData.contentType = "application/xls"
        reportData.items = items
        reportData.reportColumns = tableDefinition.getColumnsForReport()
        reportData
    }

    /**
     * Writes out the report.
     * @param reportData The same object that is returned by the formatReport method.
     * @param outputStream The outputStream to write the report to.
     */
    def writeReport(Map reportData, OutputStream outputStream) {
        log.debug "writeReport"
        new WebXlsxExporter().with {
            def reportColumns = reportData.reportColumns
            def numColumns = reportColumns.size()
            def numRows = reportData.items.size()
            if(0 == numRows) {
                putCellValue(0, 0, "This report contains no data.")
            } else {
                fillHeader(reportColumns.heading)

                // Add data to spreadsheet.
                add(reportData.items, reportColumns.name)

                // Set proper formatting for date and boolean values.
                def workbook = getWorkbook()
                def sheet = getSheet()

                reportColumns.eachWithIndex() { column, colIndex ->
                    def dateFormat = column.getDateFormat()
                    if (dateFormat) {
                        log.debug "Updating formatting for column ${column.name}..."
                        String excelFormatPattern = DateFormatConverter.convert(LOCALE, dateFormat);
                        CellStyle cellStyle = workbook.createCellStyle();
                        DataFormat poiFormat = workbook.createDataFormat();
                        cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
                        1.upto(numRows) {
                            getCellAt(it, colIndex).setCellStyle(cellStyle)
                        }
                    } else if ("boolean" == column.dataType) {
                        log.debug "Updating boolean text for column ${column.name}..."
                        reportData.items.eachWithIndex { item, itemIndex ->
                            def value = item[column.name]
                            def text = value ? (null == column.trueText ? "True" : column.trueText) : (null == column.falseText ? "False" : column.falseText)
                            putCellValue(itemIndex + 1, colIndex, text)
                        }
                    }
                }

                // Apply style to heading row.
                def cellStyle = getCellAt(0, 0).getCellStyle()
                def font = cellStyle.getFont()
                font.setBold(true)
                font.setFontHeightInPoints((short) 10)
                cellStyle.setFont(font)
                cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex())
                cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                0.upto(numColumns - 1) {
                    getCellAt(0, it).setCellStyle(cellStyle)
                }
            }
            // Set column widths.
            log.debug "Setting column widths..."
            0.upto(numColumns - 1) {
                sheet.autoSizeColumn(it)
            }

            save(outputStream)
        }
    }
}

Wiki

grails-datatables / Reports

Reports

Report Button

Report Filename

Custom Reports

Spreadsheet Report Example

See Also