excel-export

version: 0.1
approval status: APPROVED
source: https://github.com/TouK/excel-export
submitted by jakub 1 year ago

This plugin exports your objects to an xlsx (MS Excel 2007+) file, while still allowing you to handle the export file on a cell-by-cell basis. (Documentation is also available at https://github.com/TouK/excel-export) There are two scenarios on which this plugin was created: 1. When you want to export data from your controllers ('download to excel' button) and want to maintain full control of how you handle this data. 2. When your customer says: 'I want 100 reports in this new project' and nobody has any clue what those reports look like, you can use this plugin as a DSL, i.e. tell your client 'Hey, I've got good news. We have a nice DSL for you, so that you can write all those reports yourself. And it's free!' (or charge them anyway). In both cases you can export either to a file on disk, or to outputStream (download as xlsx). This plugin has been used like this in commercial projects. How to use it? Say, in your controller you have a list of objects, like so: List products = productFactory.createProducts() To export chosen properties of those products to a file, you do this: def withProperties = ['name', 'description', 'validTill', 'productNumber', 'price.value'] new XlsxExporter('/tmp/myReportFile.xlsx'). add(products, withProperties). save() withProperties is a list of properties that are going to be exported to xlsx, in the given order. Notice, that you can use nested properties (price.value) of your objects. To add a header, and make it downloadable from a controller, you do this: def headers = ['Name', 'Description', 'Valid Till', 'Product Number', 'Price'] def withProperties = ['name', 'description', 'validTill', 'productNumber', 'price.value'] new WebXlsxExporter().with { setResponseHeaders(response) fillHeader(headers) add(products, withProperties) save(response.outputStream) } WebXlsxExporter is the same thing as XlsxExporter, plus it handles HTTP response headers. To manipulate the file on a cell-by-cell basis, you do this: new WebXlsxExporter().with { setResponseHeaders(response) fillRow(["aaa", "bbb", 13, new Date()], 1) fillRow(["ccc", "ddd", 87, new Date()], 2) putCellValue(3, 3, "Now I'm here") save(response.outputStream) } You can mix playing on cell-by-cell approach with add method def withProperties = ['name', 'description', 'validTill', 'productNumber', 'price.value'] new WebXlsxExporter().with { setResponseHeaders(response) fillRow(["aaa", "bbb", 13, new Date()], 1) fillRow(["ccc", "ddd", 87, new Date()], 2) putCellValue(3, 3, "Now I'm here") add(products, withProperties, 4) //NOTICE: we are adding objects starting from line 4 save(response.outputStream) } This plugin handles basic property types pretty well (String, Date, Boolean, Timestamp, NullObject, Long, Integer, BigDecimal, BigInteger, Byte, Double, Float, Short), it also handles nested properties, but sooner or later, you'll want to export a property of a different type. What you need to write, is a Getter. Or, better, a PropertyGetter. It's super easy, here is example of one that takes Currency and turns it into a String class CurrencyGetter extends PropertyGetter { //From Currency, to String CurrencyGetter(String propertyName) { super(propertyName) } @Override protected String format(Currency value) { return value.displayName //you can do anything you like in here } } The 'format' method, allows you to do anything, before the object is saved in an xlsx cell. And, of course, to use it, just add it into withProperties list, like this: def withProperties = ['name', new CurrencyGetter('price.currency'), 'price.value'] new WebXlsxExporter().with { setResponseHeaders(response) add(products, withProperties) save(response.outputStream) } Of course we could have just used 'currency.displayName' in withProperties, but you get the idea. There are two Getters ready for your convenience. LongToDatePropertyGetter gets a long and saves it as a date in xlsx, while MessageFromPropertyGetter handles i18n. Speaking about which... To get i18n of headers in your controller, just use controller's message method: def headers = [message(code: 'product.name.header'), message(code: 'product.description.header'), message(code: 'product.validTill.header'), message(code: 'product.productNumber.header'), message(code: 'price.value.header')] You can do more though. To i18n values, use MessageFromPropertyGetter: MessageSource messageSource //injected in the controller automatically by Grails, just declare it def export() { List products = productFactory.createProducts() def headers = ['name', 'currency', 'value'] def withProperties = ['name', new CurrencyGetter('price.currency'), 'price.value'] new WebXlsxExporter().with { setResponseHeaders(response) fillHeader(headers) add(products, withProperties) save(response.outputStream) } } This will use grails i18n, based on the value of some property ('type' in here) of your objects. Fancy diagrams, colours, and other stuff in my Excel. Making xlsx files look really great with Apache POI is pretty fun. But not very efficient. So we have found out, that it's easier to create a template manually (in MS Excel or Open Office), load this template in your code, fill it up with data, and handle back to the user. For this scenario, every constructor takes a path to a template file (just normal xlsx file). After loading the template, fill the data, and save to the output stream new WebXlsxExporter('/tmp/myTemplate.xlsx').with { setResponseHeaders(response) add(products, withProperties) save(response.outputStream) } If you just want to save the file to disk instead of a stream, use a different constructor: new XlsxExporter('/tmp/myTemplate.xlsx', '/tmp/myReport.xlsx") If you just open an existing file, and save it, like this: new XlsxExporter('/tmp/myReport.xlsx").with { add(products, withProperties) save() } you are going to override it. Instalation Ok, it's released here: http://maven.touk.pl/nexus/content/repositories/releases Here is what you need to add to your BuildConfig.groovy grails.project.dependency.resolution = { inherits("global") { ... excludes 'xercesImpl' //#1 important thing } repositories { mavenRepo("http://maven.touk.pl/nexus/content/repositories/releases") //#2 important thing ... } plugins { runtime ":excel-export:0.1" //#3 important thing ... } ... Excluding xerces may or may not be needed, depending on your setup. If you get Error executing script RunApp: org/apache/xerces/dom/DeferredElementImpl (Use --stacktrace to see the full trace) or Error executing script RunApp: org/apache/xerces/dom/DeferredElementImpl (NOTE: Stack trace has been filtered. Use --verbose to see entire trace.) java.lang.NoClassDefFoundError: org/apache/xerces/dom/DeferredElementImpl You NEED to exclude xercesImpl to use ApachePOI. Don't worry, it won't break anything. If you have more strange problems with xml and you are using Java 7, exclude xml-apis as well: grails.project.dependency.resolution = { inherits("global") { ... excludes 'xercesImpl', 'xml-apis' } ... If you want a working example, clone this project: https://github.com/TouK/excel-export-samples

blog comments powered by Disqus