A groovy script to parse an excel document.

GroovyExcelParser.groovy

import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import org.apache.poi.ss.usermodel.*
import java.io.*

class GroovyExcelParser {
  //http://poi.apache.org/spreadsheet/quick-guide.html#Iterator

  def parse(path) {
    InputStream inp = new FileInputStream(path)
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);

    Iterator<Row> rowIt = sheet.rowIterator()
    Row row = rowIt.next()
    def headers = getRowData(row)

    def rows = []
    while(rowIt.hasNext()) {
      row = rowIt.next()
      rows << getRowData(row)
    }
    [headers, rows]
  }

  def getRowData(Row row) {
    def data = []
    for (Cell cell : row) {
      getValue(row, cell, data)
    }
    data
  }

  def getRowReference(Row row, Cell cell) {
    def rowIndex = row.getRowNum()
    def colIndex = cell.getColumnIndex()
    CellReference ref = new CellReference(rowIndex, colIndex)
    ref.getRichStringCellValue().getString()
  }
 
  def getValue(Row row, Cell cell, List data) {
    def rowIndex = row.getRowNum()
    def colIndex = cell.getColumnIndex()
    def value = ""
    switch (cell.getCellType()) {
      case Cell.CELL_TYPE_STRING:
        value = cell.getRichStringCellValue().getString();
        break;
      case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            value = cell.getDateCellValue();
        } else {
            value = cell.getNumericCellValue();
        }
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        value = cell.getBooleanCellValue();
        break;
      case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
      default:
        value = ""
    }
    data[colIndex] = value
    data
  }

  def toXml(header, row) {
    def obj = "<object>\n"
    row.eachWithIndex { datum, i -> 
      def headerName = header[i]
      obj += "\t<$headerName>$datum</$headerName>\n" 
    } 
    obj += "</object>"
  }

  public static void main(String[]args) {
    def filename = 'temp.xlsx'
    GroovyExcelParser parser = new GroovyExcelParser()
    def (headers, rows) = parser.parse(filename)
    println 'Headers'
    println '------------------'
    headers.each { header -> 
      println header
    }
    println "\n"
    println 'Rows'
    println '------------------'
    rows.each { row ->
      println parser.toXml(headers, row)
    }
  }
}

package.json

{
  "dependencies":[
    {"group":"org.apache.poi", "id":"poi", "version":"3.8"},
    {"group":"org.apache.poi", "id":"poi-ooxml", "version":"3.8"},
  ]
}

is this applicable for both .xls and .xlsx formats?

Oh sorry, I completely missed this comment! Ya it should work with both. Apache POI represents Excel workbooks in one of two ways, HSSFWorkbook (for xls) or XSSFWorkbook (for xlxs). The WorkbookFactory.create call you see on line 13 correctly creates the right version automatically for you. And since they both implement the same Workbook interface (and we only deal with that interface) it just works.

Is there anyway I can download the dependencies from package.json automatically (like using npm update)?

When I run it using:
groovy GroovyExcelParser.groovy

it failed with compilation error:

`org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 13: unable to resolve class Workbook
@ line 13, column 14.
Workbook wb = WorkbookFactory.create(inp);
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 14: unable to resolve class Sheet
@ line 14, column 11.
Sheet sheet = wb.getSheetAt(0);
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 16: unable to resolve class Row
@ line 16, column 14.
Iterator rowIt = sheet.rowIterator()
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 17: unable to resolve class Row
@ line 17, column 9.
Row row = rowIt.next()
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 28: unable to resolve class Row
@ line 28, column 18.
def getRowData(Row row) {
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 30: unable to resolve class Cell
@ line 30, column 5.
for (Cell cell : row) {
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 36: unable to resolve class Row
@ line 36, column 23.
def getRowReference(Row row, Cell cell) {
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 36: unable to resolve class Cell
@ line 36, column 32.
def getRowReference(Row row, Cell cell) {
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 39: unable to resolve class CellReference
@ line 39, column 19.
CellReference ref = new CellReference(rowIndex, colIndex)
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 39: unable to resolve class CellReference
@ line 39, column 25.
CellReference ref = new CellReference(rowIndex, colIndex)
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 43: unable to resolve class Row
@ line 43, column 16.
def getValue(Row row, Cell cell, List data) {
^

C:\Development\GitHub\GroovyExcelParser\GroovyExcelParser.groovy: 43: unable to resolve class Cell
@ line 43, column 25.
def getValue(Row row, Cell cell, List data) {
^

12 errors`

Solved. I remove the package.json and put this chunk on top of GroovyExcelParser.groovy instead:

@Grapes([
@grab(group='org.apache.poi', module='poi', version='3.8'),
@grab(group='org.apache.poi', module='poi-ooxml', version='3.8')
])

Reference: http://groovy.codehaus.org/Grape

package.json is useless, not sure how to use it, can't find in google too

Ah yes, the package.json is used by a tool that I'm developing that builds on top of grape, allowing you to define your dependencies in the package.json file instead of inside the script. The project is called soda and can be found here:
https://github.com/jkeam/soda

My apologies for not being clear about this.