Skip to content

Instantly share code, notes, and snippets.

@makelefy
Forked from jkeam/GroovyExcelParser.groovy
Created January 30, 2017 20:12
Show Gist options
  • Save makelefy/d3accd06af42b5f2aa4e80d4cd609d37 to your computer and use it in GitHub Desktop.
Save makelefy/d3accd06af42b5f2aa4e80d4cd609d37 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"},
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment