Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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"},
]
}
@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