grails - Grails/Groovy:使用grails.plugin.jxl.builder.ExcelBuilder格式化列

Question

我有一份工作报告，可以通过电子邮件发送电子表格。

我希望列的大小正确(或根据已知条件关闭)，并希望格式(例如货币)符合要求。我进行的搜索没有让我到处都可以找到，但我是新手。

我的 Controller (简体):

def myReport = {

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

服务(也简化了):

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()
    }
}

我很乐意回答我的问题，以及我可以更好地格式化问题以获取答复的方式。

Answer 1

使用插件DSL(工作簿，工作表，单元格)时，基本上是从grails.plugin.jxl.builder.ExcelBuilder调用方法。ExcelBuilder#cell()方法返回一个grails.plugin.jxl.Cell对象，您可以将其用于单元格格式和功能的进一步操作。插件提供了一些内置的格式化方法，即文档here。但是，您可以通过处理底层的JXL对象来应用进一步的格式。

例如，对于具有货币格式的单元格，您可以执行以下操作:

Controller 代码

class ReportController {

    def reportService

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

服务编号

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