views:

110

answers:

2

I have a list with information and I want to export it to Excel. How do I do it?

Is the "Export Plugin" any good? I think I saw one a while ago to export files to Excel but I can't find it anymore.

A: 

A quick search for excel in the plugins portal found this plugin. I didn't work with it so I can't comment about it's quality.

Asaf David
So you basically gave me something stated on the question :S
fgualda87
+3  A: 

If you want actual Excel documents (rather than just CSV files), I've used the JExcel library with some success. Here's a quickly-written example that could probably be Groovy-fied a little bit.

Edit: Updated my example to do this in a controller. Architecturally it would make more sense to split this up a bit, but this is just for example's sake.

import jxl.*
import jxl.write.*

class SomeController {

    def report = {
        def file = createReport(MyDomain.list())

        response.setHeader('Content-disposition', 'attachment;filename=Report.xls')
        response.setHeader('Content-length', "${file.size()}")

        OutputStream out = new BufferedOutputStream(response.outputStream)

        try {
            out.write(file.bytes)

        } finally {
            out.close()
            return false
        }
    }

    private File createReport(def list) {
        WorkbookSettings workbookSettings = new WorkbookSettings()
        workbookSettings.locale = Locale.default

        def file = File.createTempFile('myExcelDocument', '.xls')
        file.deleteOnExit()

        WritableWorkbook workbook = Workbook.createWorkbook(file, workbookSettings)

        WritableFont font = new WritableFont(WritableFont.ARIAL, 12)
        WritableCellFormat format = new WritableCellFormat(font)

        def row = 0
        WritableSheet sheet = workbook.createSheet('MySheet', 0)

        list.each {
            // if list contains objects with 'foo' and 'bar' properties, this will
            // output one row per list item, with column A containing foo and column
            // B containing bar
            sheet.addCell(new Label(0, row, it.foo, format))
            sheet.addCell(new Label(1, row++, it.bar, format))
        }
    }
}

Using this library lets you do things like formatting, using multiple worksheets, etc.

Rob Hruska
Ok, but I have an Icon, to export everything I have on the list to Excel.
fgualda87
Would that work?? Like how do I link it to the icon??
fgualda87
I'm afraid I'm not following you. What do you mean when you refer to 'icon'?
Rob Hruska
Oh, I think I see what you're saying. Do you want this to be present via a button on every scaffolded list view?
Rob Hruska
It contains just regular data.
fgualda87
Ok, so I have a search section on top, like a header, and in it there is a button designed for exporting the search results into an Excel file.
fgualda87
Well, you'd have the button go to a controller action that (probably calls a service that) generates the file. The controller would then provide the file to the response's output stream so that the user could download it.
Rob Hruska
And the code you wrote should go in the controller??
fgualda87
Yes, you could cram it all into a controller action, I suppose. I won't go into a big discussion on architecture, but it might be better in a service or elsewhere.
Rob Hruska
What do you mean in a service??
fgualda87
Grails has Services (http://www.grails.org/Services), which are for business logic. I've updated my example to just do this in a controller, but reading up on the Grails' link above might help give you a better understanding.
Rob Hruska
So, all I need is to update my controller to something similar to the code you provided??
fgualda87
Yes, give it a try.
Rob Hruska
Sorry, I had to finish some other things before exporting to Excel. I am getting an error on this line `WorkbookSettings workbookSettings = new WorkbookSettings()` it says : density.DensityController: 128: unable to resolve class WorkbookSettings @ line 128, column 26. WorkbookSettings workbookSettings = new WorkbookSettings()
fgualda87
Make sure 1) your controller has the imports from my example, and 2) the jxl jar is on the classpath. Where'd you put the jar file?
Rob Hruska
What jar file?? Maybe I'm having problems with the controller and domain names. The domain is density, and the controller is DensityController
fgualda87
In the first sentence of my answer there's a link to the JExcel home page. Go to that page and click the "Files" link at the top. Here's a shortcut: http://sourceforge.net/projects/jexcelapi/files/jexcelapi/2.6.12/jexcelapi_2_6_12.tar.gz/download ... you need to put the jar file in that zip in your lib directory.
Rob Hruska