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

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:

view plainprint?

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

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:

view plainprint?

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

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:

view plainprint?

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

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

view plainprint?

1. $ time jython -c "print('Hello')"  
2. Hello  
3.   
4. real    0m2.503s  
5. user    0m2.891s  
6. sys     0m0.431s  
7.   
8. $ time jython -c "print('Hello')"  
9. Hello  
10.   
11. real    0m2.613s  
12. user    0m2.889s  
13. sys     0m0.435s  
14.   
15. $ time gython -c "print('Hello')"  
16. Hello  
17.   
18. real    0m0.959s  
19. user    0m0.001s  
20. sys     0m0.003s  
21.   
22. $ time gython -c "print('Hello')"  
23. Hello  
24.   
25. real    0m1.156s  
26. user    0m0.001s  
27. sys     0m0.003s  

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

view plainprint?

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

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

view plainprint?

1. $ time gython -c "print('Hello')"  
2. Hello  
3.   
4. real    0m0.909s  
5. user    0m0.001s  
6. sys     0m0.002s  
7.   
8. $ time gython -c "print('Hello')"  
9. Hello  
10.   
11. real    0m0.076s  
12. user    0m0.001s  
13. sys     0m0.003s  
14.   
15. $ time gython -c "print('Hello')"  
16. Hello  
17.   
18. real    0m0.045s  
19. user    0m0.001s  
20. sys     0m0.003s  

$ 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

view plainprint?

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

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:

view plainprint?

1. $ glojureserver -r -v  
2. Groovy command path: /usr/local/bin/groovy (found at PATH)  
3. GroovyServ home directory: /usr/local/Cellar/groovyserv/0.7/libexec  
4. Original classpath: /usr/local/Cellar/clojure/1.2.0/clojure.jar  
5. GroovyServ default classpath: /usr/local/Cellar/clojure/1.2.0/clojure.jar:/usr/local/Cellar/groovyserv/0.7/libexec/lib/*  
6. Killed groovyserver of 56789(1961)  
7. Restarting groovyserver  
8. Starting....  
9. groovyserver 59802(1961) is successfully started  
10.   
11. $ time glojure -e "(println 'Hello)"  
12. Hello  
13.   
14. real    0m0.867s  
15. user    0m0.001s  
16. sys     0m0.004s  
17.   
18. $ time glojure -e "(println 'Hello)"  
19. Hello  
20.   
21. real    0m0.053s  
22. user    0m0.001s  
23. sys     0m0.004s  
24.   
25. $ gythonserver -r -v  
26. Groovy command path: /usr/local/bin/groovy (found at PATH)  
27. GroovyServ home directory: /usr/local/Cellar/groovyserv/0.7/libexec  
28. Original classpath: /usr/local/Cellar/jython/2.5.2/libexec/jython.jar  
29. GroovyServ default classpath: /usr/local/Cellar/jython/2.5.2/libexec/jython.jar:/usr/local/Cellar/groovyserv/0.7/libexec/lib/*  
30. Killed groovyserver of 59802(1961)  
31. Restarting groovyserver  
32. Starting....  
33. groovyserver 59938(1961) is successfully started  
34.   
35. $ time gython -c "print('Hello')"  
36. Hello  
37.   
38. real    0m1.540s  
39. user    0m0.001s  
40. sys     0m0.004s  
41.   
42. $ time gython -c "print('Hello')"  
43. Hello  
44.   
45. real    0m0.108s  
46. user    0m0.001s  
47. sys     0m0.003s  
48.   
49. $ time gython -c "print('Hello')"  
50. Hello  
51.   
52. real    0m0.057s  
53. user    0m0.001s  
54. sys     0m0.004s  

$ 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