Created
November 16, 2012 02:57
-
-
Save jkeam/4083570 to your computer and use it in GitHub Desktop.
A groovy script to parse an excel document.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"dependencies":[ | |
{"group":"org.apache.poi", "id":"poi", "version":"3.8"}, | |
{"group":"org.apache.poi", "id":"poi-ooxml", "version":"3.8"}, | |
] | |
} |
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
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