If you want to automate operations which use Excel, you can use a famous library, Apache POI. But I can't say that it's easy enough, even if using from Groovy. Especially, a identification of a cell to use an index is too complicated.
If you want to read a value of a cell labeled "A1", you must write as follows:
File inputFile = ... def book = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(inputFile))) def sheet = book.getSheetAt(0) // 1st sheet println "A1: " sheet.getRow(0)?.getCell((short) 0) println "A2: " sheet.getRow(1)?.getCell((short) 0) println "B1: " sheet.getRow(0)?.getCell((short) 1)...it's very difficult to read an write.
So I developed a wrapper library of Apache POI, called GExcelAPI. Version 0.2 was released at 2010-12-16. The name is very similar to "JExcelAPI" but there is no relationship. It's just a wrapper of "Apache POI".
By using GExcelAPI, you can rewrite the above sample:
File inputFile = ... def book = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(inputFile))) def sheet = book[0] // 1st sheet println "A1: " sheet.A1.value println "A2: " sheet.A2.value println "B1: " sheet.B1.valueYou can directly use a label of a cell to specify it. It's intuitive and obvious.
GExcelAPI v0.2 has been released on my maven repository on Github. So you can use it via Grape.
@GrabResolver(name="kobo-maven-repo", root="https://github.com/kobo/maven-repo/raw/master/release")
@GrabConfig(systemClassLoader=true) // necessary if you invoke it by GroovyServ
@Grab("org.jggug.kobo:gexcelapi:0.2")
import org.jggug.kobo.gexcelapi.GExcel
def book = GExcel.open(args[0])
def sheet = book[0]
println sheet.A1.value
If you like GExcelAPI, check it out at the github.
To tell the truth, I'm not using Excel so much recently. So I hope that someone who are usually using GExcelAPI would become a committer of GExcelAPI ;-)
- GitHub
- Test as a document which includes all features
- Sample script using Grape
