CODEWITHSUNDEEP
javagroovy
Aravind Yarram
Aravind Yarram

Reputation: 80176

Easiest way to read Excel files in groovy?

Are there any warappers/utils available to read Excel files in Groovy. I am looking for something similar to Groovy SQL's rows function as shown in below spock test example. My intention is to use this for data driven testing using excel in Spock test framework

import groovy.sql.Sql

import spock.lang.*

class DatabaseDriven extends Specification {
  @Shared sql = Sql.newInstance("jdbc:h2:mem:", "org.h2.Driver")

  // normally an external database would be used,
  // and the test data wouldn't have to be inserted here
  def setupSpec() {
    sql.execute("create table maxdata (id int primary key, a int, b int, c int)")
    sql.execute("insert into maxdata values (1, 3, 7, 7), (2, 5, 4, 5), (3, 9, 9, 9)")
  }

  def "maximum of two numbers"() {
    expect:
    Math.max(a, b) == c

    where:
    [a, b, c] << sql.rows("select a, b, c from maxdata")
  }
}

Upvotes: 11

Views: 30622

Answers (4)

walker
walker

Reputation: 199

@Grab('org.apache.poi:poi:3.8')
@Grab('org.apache.poi:poi-ooxml:3.8')
@GrabExclude('xml-apis:xml-apis')
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.*;
import org.apache.poi.ss.usermodel.*;
def excelFile = new File('F:/test.xlsx')


excelFile.withInputStream { is ->
    workbook = new XSSFWorkbook(is)
    (0..<workbook.numberOfSheets).each { sheetNumber ->
        XSSFSheet sheet = workbook.getSheetAt( sheetNumber )
        sheet.rowIterator().each { row ->
            row.cellIterator().each { cell ->
                println cell.toString()
            }
        }
    }
}

Upvotes: 1

Hubbitus
Hubbitus

Reputation: 5349

I could also recommend use Groovy Spreadsheet Builder. It available in maven repository (contrary to ExcelBuilder) and also have expressive Groovy-syntax:

SpreadsheetQuery query = PoiSpreadsheetCriteria.FACTORY.forFile(file)                      // <1>

Collection  cells = query.query {
    sheet {                                                                            
        row {                                                                           
            cell {
                value 'B'
            }
        }
    }
}

assert cells.size() == 1
assert cells.first().value == 'B'

Or:

Collection rows = query.query {
    sheet(name({ name.startsWith('Con') })) {
        row(1)
    }
}.rows

Documentation contains many examples. It even may write Excel files in same way!

Upvotes: 0

xlson
xlson

Reputation: 2737

One of my fellow GUG members has created a tool for working with Excel using Apache POI in very much the same way you describe. It's not formalized into a library yet (AFAIK) but is available on his blog.

It allows you to write code like this:

new ExcelBuilder("customers.xls").eachLine([labels:true]) {
  new Person(name:"$firstname $lastname",
    address:address, telephone:phone).save()
}

Check it out here: http://www.technipelago.se/content/technipelago/blog/44

Upvotes: 15

sMoZely
sMoZely

Reputation: 379

POI is what your after http://poi.apache.org/ its a Java Lib so you can use it from Groovy. Not sure if there are Groovy wrappers for it anywhere

Upvotes: 6

Related Questions