Grails/ Groovy: Formatting Columns using grails.plugin.jxl.builder.ExcelBuilder

Question

I have a working report that results in an Emailed spreadsheet.

I want columns to be sized correctly (or to be close, based on known conditions) and for formats (currency for example) to match requirements. The searches I have done haven't gotten me anywhere, but I am a newbie.

My Controller (simplified):

def myReport = {

    def destEmail = 'whositz@domain.com'
    def reportDate
    if(params?.reportDate){
        reportDate = params.reportDate
    } else {
 reportDate = new Date()
    }
    myReportService.execute(reportEmail, reportDate)
}

The Service (also simplified):

import grails.plugin.jxl.builder.ExcelBuilder
import groovy.sql.Sql
import org.springframework.context.ApplicationContext
import org.springframework.context.ApplicationContextAware

@Mixin(ExcelBuilder)
class myReportService implements ApplicationContextAware {
ApplicationContext applicationContext

def mailService
def dataSource

def execute(String sendToEmail, Date reportDate){
    if(!mailService) {
        mailService = applicationContext.getBean('mailService')
    }
    String path = "/tmp/someReport.xls"
    String reportTitle = reportDate.format('yy.MM')+ ".thisReport"
    def sql = new Sql(dataSource)

    def sqlText = """
        SELECT ys.acct_num AS ACCTNUM, ys.st_name as ACCTNAME, SUM(i.amt_due) AS AMTDUE
        FROM invoice i, yax_sax ys
        WHERE ys.ss_it = true
        AND i.acct_id=ys.id
        AND i.is_on=true
        GROUP BY ys.acct_num, ys.sort_name
        ORDER BY ys.acct_num
    """
    try {
        workbook(path){
            sheet(reportTitle){
                def rowIdx = 0

                cell(0,rowIdx,"Acct #")
                cell(1,rowIdx,"Acct Name")
                cell(2,rowIdx,"Amt Due")
                cell(3,rowIdx,"Trans ID") //Trans Id is a blank field

                rowIdx++
                sql.eachRow(sqlText,[]){ row ->
                    cell(0,rowIdx, row.ACCTNUM)
                    cell(1,rowIdx, row.ACCTNAME)
                    cell(2,rowIdx, row.AMTDUE)
                    cell(3,rowIdx, "")
                    rowIdx++
                }
            }
        }
    } catch (Throwable e) {
    e.printStackTrace()
    throw (e)
    } finally {
    sql.close()
    }

    mailService.sendMail {
        multipart true
        to sendToEmail
        subject "An Report for $reportDate"
        body '''
           Report Attached.
       '''
        attachBytes "someReport.xls",'application/pdf', new File(path).readBytes()
    }
}

I would appreciate responses on my question as well as ways I can better format questions in order to get responses.

Answer 1

this works for me, using the mixin, and grails 2.3.5.

def setColWidth(def sheet, def col, def width){
    CellView cv = sheet.getColumnView(col);
    cv.setSize(width * 256 + 100); /* Every character is 256 units wide, so scale it. */
    sheet.setColumnView(col, cv);

}

def setColWidths(def sheet){
    setColWidth(sheet, 0, 16)
    setColWidth(sheet, 1, 14)
    setColWidth(sheet, 2, 14)
    setColWidth(sheet, 5, 14)
    setColWidth(sheet, 6, 14)
    setColWidth(sheet, 7, 14)
    setColWidth(sheet, 10, 20)
}

Answer 2

When using the plugin DSL (workbook, sheet, cell) you are basically calling methods from grails.plugin.jxl.builder.ExcelBuilder.
The ExcelBuilder#cell() method return a grails.plugin.jxl.Cell object which you can use for further manipulation of the cell format and features. The plugins offers some built-in formatting methods which are document here. However you can apply further formatting by manipulating the underlying JXL object.

For example, you can do the following for having a cell with currency format:

Controller code

class ReportController {

    def reportService

    def index() {
        response.setContentType('application/vnd.ms-excel')
        response.setHeader('Content-Disposition', 'Attachment;Filename="example.xls"')
        reportService.execute(response.outputStream)
    }
}

Service code

import grails.plugin.jxl.Cell
import grails.plugin.jxl.builder.ExcelBuilder
import jxl.write.NumberFormats
import jxl.write.WritableCellFormat

@Mixin(ExcelBuilder)
class ReportService {

    def execute(OutputStream outputStream){
        String reportTitle = new Date().format('yy.MM')+ ".thisReport"

        try {
            workbook(outputStream){
                sheet(reportTitle){
                    cell(0, 0, "Currency Example")

                    Cell myCell = cell(0,1, 1000)
                    WritableCellFormat currencyFormat = new WritableCellFormat(NumberFormats.ACCOUNTING_FLOAT);
                    myCell.format = currencyFormat
                }
            }
        } catch (Throwable e) {
            e.printStackTrace()
            throw (e)
        }
    }
}