Skip to content

Instantly share code, notes, and snippets.

@jkeam
Created November 16, 2012 02:57
Show Gist options
  • Save jkeam/4083570 to your computer and use it in GitHub Desktop.
Save jkeam/4083570 to your computer and use it in GitHub Desktop.
A groovy script to parse an excel document.
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)
}
}
}
{
"dependencies":[
{"group":"org.apache.poi", "id":"poi", "version":"3.8"},
{"group":"org.apache.poi", "id":"poi-ooxml", "version":"3.8"},
]
}
@michelleneverida
Copy link

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

@jkeam
Copy link
Author

jkeam commented Jan 23, 2014

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.

@JiboStore
Copy link

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`

@JiboStore
Copy link

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

@jkeam
Copy link
Author

jkeam commented Feb 28, 2015

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

@jkeam
Copy link
Author

jkeam commented Feb 28, 2015

My apologies for not being clear about this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment