gistfile1

// g100pon *9 Jakarta POI のGroovyサンプル
//
// usage: groovy sampleOfGExcelAPI.groovy <入力Excelファイル>
// 入力用に適当なExcelファイルを用意してください

// ----------------------------
// Grapeによるライブラリ取得
@GrabResolver(name="kobo-repo", root="http://github.com/kobo/maven-repo/raw/master/snapshot")
@GrabConfig(systemClassLoader=true) // for workaround a permgen problem with GroovyServ
@Grab("org.jggug.kobo:gexcelapi:0.2-SNAPSHOT")
import org.jggug.kobo.gexcelapi.GExcel

// ----------------------------
// Excelファイルの読み込み
def book = GExcel.open(args[0])

// ----------------------------
// シートの取得
def sheet1 = book[0] // 第1シート
def sheet2 = book[1] // 第2シート
def sheet3 = book["Sheet3"] // シート名で指定も可能

def sheet = sheet1
//def sheet = sheet2
//def sheet = sheet3

// ----------------------------
// セルの参照

println sheet.A1.stringCellValue
println sheet.A2.stringCellValue
println sheet.A3.numericCellValue.intValue() // (double->int)
println sheet.A4.dateCellValue
println sheet.A5.booleanCellValue
println sheet.A6.booleanCellValue
println sheet.B1.stringCellValue
println sheet.B2.stringCellValue

println "-" * 20

println sheet.A1.value
println sheet.A2.value
println sheet.A3.value
println sheet.A4.value
println sheet.A5.value
println sheet.A6.value
println sheet.B1.value
println sheet.B2.value

sheet.A1.value = "New value of A1"
sheet.A2.value = "New value of A2"
sheet.A3.value = "New value of A3"
sheet.A4.value = "New value of A4"
sheet.A5.value = "New value of A5"
sheet.A6.value = "New value of A6"
sheet.B1.value = "New value of B1"
sheet.B2.value = "New value of B2"

println "-" * 20

// ----------------------------
// おまけ

// イテレータ各種
def dump = { println "${it?.label}: ${it?.value}" }
sheet.A_.findAll{it}.each dump
println "-" * 20
sheet.B_.findAll{it}.each dump
println "-" * 20
sheet._1.findAll{it}.each dump
println "-" * 20
sheet._2.findAll{it}.each dump
println "-" * 20
sheet.A1_B6.findAll{it}.each dump
println "-" * 20

// XML出力との組合せ
def writer = new StringWriter()
def xml = new groovy.xml.MarkupBuilder(writer)
xml.excel() {
    columnA {
        sheet.A_.each {
            "${it.label}" (value:it.value)
        }
    }
    columnB {
        B1 (value: sheet.B1.value)
        B2 (value: sheet.B2.value)
    }
    all {
        sheet.A1_B6.findAll{it}.each {
            "${it.label}" (value:it.value)
        }
    }
}
println writer.toString()