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:

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

@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

Invoking Groovy Script Directly on Vim using quickrun.vim and GroovyServ

There is an excellent vim plugin, quickrun.vim.

• https://github.com/thinca/vim-quickrun/blob/master/doc/quickrun.txt

It makes your trial and error cycle much faster. It makes your script edited on vim invoke by shortcut keys. It's very useful for writing a script.

The plugin supports many programming languages including Groovy. But still the original Groovy command has the weak point; the starting-up of invocation is slow. Your "flow" or "zone" is interrupted by each invocation. But now, there is GroovyServ for you. It's very easy to configure your vim to use GroovyServ's groovyclient instead of original Groovy's command. Add the following lines at .vimrc:

let g:quickrun_config = {}
let g:quickrun_config.groovy = {'command' : 'groovyclient'}

Of course, GroovyServ must be installed and groovyclient must be added into PATH environment variables.

The default key bind to run a script is <Leader>r. If you want to modify it, add the following lines:

let g:quickrun_no_default_key_mappings = 1
nmap <Leader>r <Plug>(quickrun)

In my configuration, the <Leader> is a comma. So, when I input the keys of ",r", quickrun.vim works.

If you run a script by quickrun.vim first, a groovyserver's preparation might take several seconds. The starting-up messages aren't emitted to a result buffer, so you might think it's a freeze, but it's still working.
If you invoke groovyserver explicitly before you use quickrun.vim, the first invocation of quickrun.vim will be quite fast.

BTW, there are scripts which doesn't work well with GroovyServ. If your script is so, use original Groovy command on your terminal.

Enjoy your Groovy and Vim life!

High-speed start-up Jython/Clojure by GroovyServ

GroovyServ:
https://github.com/kobo/groovyserv

GroovyServ made a start-up time of the invocation of Groovy script very quick. However, it can be used not only for Groovy but for all JVM languages, e.g. Scala, Jython and Clojure, etc.

On Linux, when setting the following aliases, You can enjoy the power of GroovyServ for Jython:

alias gython="groovyclient -cp /tmp/jython.jar -e 'import org.python.util.jython; jython.main(args)' --"

$ time jython -c "print('Hello')"
Hello

real    0m2.503s
user    0m2.891s
sys     0m0.431s

$ time jython -c "print('Hello')"
Hello

real    0m2.613s
user    0m2.889s
sys     0m0.435s

$ time gython -c "print('Hello')"
Hello

real    0m0.959s
user    0m0.001s
sys     0m0.003s

$ time gython -c "print('Hello')"
Hello

real    0m1.156s
user    0m0.001s
sys     0m0.003s

Gython(Jython + GroovyServ) is faster by one second than normal Jython. In the above way, the overhead which groovyclient passes the classpath of the additional jython.jar to groovyserver at each invocation is so large. You can do it still faster by starting groovyserver with CLASSPATH environment variable which has jython.jar.

$ alias gython="groovyclient -e 'import org.python.util.jython; jython.main(args)' --"
$ export CLASSPATH=/tmp/jython.jar
$ groovyserver -r

$ time gython -c "print('Hello')"
Hello

real    0m0.909s
user    0m0.001s
sys     0m0.002s

$ time gython -c "print('Hello')"
Hello

real    0m0.076s
user    0m0.001s
sys     0m0.003s

$ time gython -c "print('Hello')"
Hello

real    0m0.045s
user    0m0.001s
sys     0m0.003s

Yeah!

Notice 1: I can't assure that a testing framework or a complex library works well. Do it by your self-responsibility ;-)

Notice 2: If your OS is Windows, use Groovy and GroovyServ which are installed from Zip archives. I experienced a trouble by using them of Windows-installer of version 1.8.0.

Examples in my .bashrc

alias glojureserver="env CLASSPATH=/usr/local/Cellar/clojure/1.2.0/clojure.jar groovyserver"
alias glojure="dgroovyclient -e 'import clojure.main;main.main(args)' --"
alias gythonserver="env CLASSPATH=/usr/local/Cellar/jython/2.5.2/libexec/jython.jar groovyserver"
alias gython="groovyclient -e 'import org.python.util.jython; jython.main(args)' --"
alias jvmserver="env CLASSPATH=/usr/local/Cellar/jython/2.5.2/libexec/jython.jar:/usr/local/Cellar/clojure/1.2.0/clojure.jar groovyserver"

Usage:

$ glojureserver -r -v
Groovy command path: /usr/local/bin/groovy (found at PATH)
GroovyServ home directory: /usr/local/Cellar/groovyserv/0.7/libexec
Original classpath: /usr/local/Cellar/clojure/1.2.0/clojure.jar
GroovyServ default classpath: /usr/local/Cellar/clojure/1.2.0/clojure.jar:/usr/local/Cellar/groovyserv/0.7/libexec/lib/*
Killed groovyserver of 56789(1961)
Restarting groovyserver
Starting....
groovyserver 59802(1961) is successfully started

$ time glojure -e "(println 'Hello)"
Hello

real    0m0.867s
user    0m0.001s
sys     0m0.004s

$ time glojure -e "(println 'Hello)"
Hello

real    0m0.053s
user    0m0.001s
sys     0m0.004s

$ gythonserver -r -v
Groovy command path: /usr/local/bin/groovy (found at PATH)
GroovyServ home directory: /usr/local/Cellar/groovyserv/0.7/libexec
Original classpath: /usr/local/Cellar/jython/2.5.2/libexec/jython.jar
GroovyServ default classpath: /usr/local/Cellar/jython/2.5.2/libexec/jython.jar:/usr/local/Cellar/groovyserv/0.7/libexec/lib/*
Killed groovyserver of 59802(1961)
Restarting groovyserver
Starting....
groovyserver 59938(1961) is successfully started

$ time gython -c "print('Hello')"
Hello

real    0m1.540s
user    0m0.001s
sys     0m0.004s

$ time gython -c "print('Hello')"
Hello

real    0m0.108s
user    0m0.001s
sys     0m0.003s

$ time gython -c "print('Hello')"
Hello

real    0m0.057s
user    0m0.001s
sys     0m0.004s