Excel, Excel 2007 & CSV Importer Using Apache POI

14 votes

3% of Grails users

Dependency:
compile ":excel-import:1.0.0"
Custom repositories:
mavenRepo "http://repo.grails.org/grails/libs-releases/"
mavenRepo "http://m2repo.spockframework.org/ext/"

 Documentation  Source  Issues

Summary

Excel-Import plugin uses Apache POI http://poi.apache.org/ library (v 3.7) to parse Excel files. It's useful for either bootstrapping data, or when you want to allow your users to enter some data using Excel spreadsheets.

Installation

grails install-plugin excel-import

Description

Excel-Import plugin uses Apache POI http://poi.apache.org/ library (v 3.7) to parse Excel files.

It's useful for either bootstrapping data, or when you want to allow your users to enter some data using Excel spreadsheets, or CSV. You can also export data into Excel using very similar configuration to importing.

Note: This was tested with Grails 1.3. stream (Tomcat 6 7), not tested with 2.* yet. It also currently has a dependency on Joda-Time plugin (1.2), though it should work with both Joda and java.util.Date. .

Usage:

The core of the plugin is a service class ExcelImportService, which contains a number of useful methods for dealing with Excel, which you should dependency inject. There are also some helper classes to help report potential problems.

_org.grails.plugins.excelimport.ExcelImportService_

There is also an _AbstractExcelImporter_, which is a class you can extend - it opens and stores the workbook reference.

Reading Information Contained in Rows:

The plugin is designed to parse data out of spreadsheets into format that can then be used to create objects. For example, if you have data like this:

B                    C                D
Author             Book           # Sold
Shakespeare        King Lear        1000
Shakespeare        Hamlet            10000
Tolstoy            War and Peace   200

You can pass in parameters map that contains the name of the sheet, row where to start, and how the different columns map to properties of the object you are planning to populate (i.e. values in column B map to title property).

static Map CONFIG_BOOK_COLUMN_MAP = [
          sheet:'Sheet1', 
          startRow: 2,
          columnMap:  [
                  'B':'title',
                  'C':'author',
                  'D':'numSold',
          ]
  ]

List bookParamsList = excelImportService.columns(workbook, CONFIG_BOOK_COLUMN_MAP)

And you'll get back a list of maps:

assert bookParamsList, [
  [AuthorName:'Shakespeare', BookName:'King Lear', NumSold:1000],
  [AuthorName:'Shakespeare', BookName:'Hamlet', NumSold:10000],
  [AuthorName:'Tolstoy', BookName:'War and Peace', NumSold:200],
]

You can then pass the maps to constructors to create a bunch of objects, i.e. bookParamsList

bookParamsList.each { Map bookParamMap ->
    new Book (bookParamMap).save()
  }

Reading Information contained in Individual Cells:

Use excelImportService.cells(). Example:

static Map CONFIG_BOOK_CELL_MAP = [ 
     sheet:'Sheet2', 
     cellMap: [ 'D3':'title',
        'D4':'author',
        'D6':'numSold',
	  ]
  ]

Map bookParams = excelImportService.cells(workbook, CONFIG_BOOK_CELL_MAP )

There is also ability to handle type errors, empty cells, evaluate formulas, etc. There is also ability to pass in configuration information, i.e. to specify what to do if a cell is empty (i.e. to provide a default value), to make sure a cell is of expected type, etc.

Also, you can do similar things for individual cells, when that is the format, i.e. C10 maps to key "Author", D12 to "AuthorYearBorn', etc. Between targeting individual cells and columns / rows, that satisfied quite many requirements.

Validation

Excel being a very flexible format, it's difficult to expect the users to always enter valid data (e.g. writing one when asked to enter a number). The plugin provides some validation capabilities to check for correct types, provide default values when no values are entered, etc. The key methods (the ones that process cellMap and columnMaps) accept a configurations for the properties they try to extract.

Example:

static Map propertyConfigurationMap = [
          authorName:([expectedType: ExpectedPropertyType.StringType, defaultValue:null]),
          numSold:([expectedType: ExpectedPropertyType.StringType, defaultValue:0]),
          ('bookType'):[expectedType: ExpectedPropertyType.StringType, defaultValue:null, valueEquivalentToNull:'Select One'],
   ]

Map siteParams = excelImportService.convertFromCellMapToMapWithValues(sheet, cellMap, propertyConfigurationMap )

Configuration Parameters

expectedType - tries to enforce the right type. Possible values:

  • ExpectedPropertyType.IntType
  • ExpectedPropertyType.StringType
  • ExpectedPropertyType.DateType
  • ExpectedPropertyType.DateJavaType
  • ExpectedPropertyType.DoubleType
  • ExpectedPropertyType.EmailType
defaultValue: specifies a default value in case, for example, the type is not expected or cell has no value

valueEquivalentToNull: for cells that have drop down, you may have something like "Select A Value", which if not selected is really equivalent to null.

Formula Evaluation:

Internally, it will try to evaluate formulas when pulling up any of the cell values.

Sample Application:

For a sample of usage, please see a sample application you can download from the Plugins SVN

import org.grails.plugins.excelimport.ExcelImportUtils
import org.grails.plugins.excelimport.*
import sample.*

class BookExcelImporter extends AbstractExcelImporter {

static Map CONFIG_BOOK_CELL_MAP = [ sheet:'Sheet2', cellMap: [ 'D3':'title', 'D4':'author', 'D6':'numSold', ] ]

static Map CONFIG_BOOK_COLUMN_MAP = [ sheet:'Sheet1', startRow: 2, columnMap: [ 'B':'title', 'C':'author', 'D':'numSold', ] ]

//can also configure injection in resources.groovy def getExcelImportService() { ExcelImportService.getService() }

public BookExcelImporter(fileName) { super(fileName) }

List<Map> getBooks() { List bookList = excelImportService.columns(workbook, CONFIG_BOOK_COLUMN_MAP) }

Map getOneMoreBookParams() { Map bookParams = excelImportService.cells(workbook, CONFIG_BOOK_CELL_MAP ) }

}

Using the Importer (from sample project's Bootstrap.groovy):

String fileName = /.test-databooks.xls/
	 BookExcelImporter importer = new BookExcelImporter(fileName);

def booksMapList = importer.getBooks();

booksMapList.each { Map bookParams -> def newBook = new Book(bookParams) if (!newBook.save()) { println "Book not saved, errors = ${newBook.errors}" } }

new Book(importer.getOneMoreBookParams()).save()

Also, while formulas evaluation works for most cases, in some complicated cases it does not (i..e when you have a lookup of a looked up value using some unsupported formulas).

The plugin is licensed under Apache V2.

Release History:

  • Jun 21, 2010 - 0.3 - Initial Release
  • Sep 05, 2011 - 0.95 - Major Update
    • Upgrade to POI 3.7
    • Support for Excel 2007
    • Support for CSV Imports
    • Validation Reporting
    • Ability to export using similar format to import
  • Sep 05, 2011 - 0.96
    • Cleaned up the API and made it more consistent
    • Bug Fixes
  • Jan 07, 2012 - Created GitHub Repo http://github.com/jbarmash/grails-excel-import for plugin.
  • Jan 09, 2012 - 1.0 - Tested and upgraded the plugin to work with Grails 2.0.0 and updated the sample project to use Grails 2.0.0. I am not using it in production yet (my main project is not upgraded yet), but I got all the tests to pass, which gives me some basic assurance that it works.