GExcelAPI for Groovyist using MS Excel

Do you love Excel?

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:

view plainprint?

1. File inputFile = ...  
2. def book = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(inputFile)))  
3. def sheet = book.getSheetAt(0) // 1st sheet  
4. println "A1: "   sheet.getRow(0)?.getCell((short) 0)  
5. println "A2: "   sheet.getRow(1)?.getCell((short) 0)  
6. println "B1: "   sheet.getRow(0)?.getCell((short) 1)  

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:

view plainprint?

1. File inputFile = ...  
2. def book = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(inputFile)))  
3. def sheet = book[0] // 1st sheet  
4. println "A1: "   sheet.A1.value  
5. println "A2: "   sheet.A2.value  
6. println "B1: "   sheet.B1.value  

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.value

You 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.

view plainprint?

1. @GrabResolver(name="kobo-maven-repo", root="https://github.com/kobo/maven-repo/raw/master/release")  
2. @GrabConfig(systemClassLoader=true) // necessary if you invoke it by GroovyServ  
3. @Grab("org.jggug.kobo:gexcelapi:0.2")  
4. import org.jggug.kobo.gexcelapi.GExcel  
5.   
6. def book = GExcel.open(args[0])  
7. def sheet = book[0]  
8. println sheet.A1.value  

@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
• https://github.com/nobeans/gexcelapi
• Test as a document which includes all features
• https://raw.github.com/nobeans/gexcelapi/master/src/test/groovy/org/jggug/kobo/gexcelapi/GExcelTest.groovy
• Sample script using Grape
• https://gist.github.com/619325