Skip to content

Instantly share code, notes, and snippets.

@jkeam
Created November 16, 2012 02:57
Show Gist options
  • Star 19 You must be signed in to star a gist
  • Fork 8 You must be signed in to fork a gist
  • 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"},
]
}
@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