GExcelAPI_Color_Font_test.groovy

GExcel_ext.groovy

//
//   POI operation 
//      http://www.javadrive.jp/poi/style/index2.html
//      http://www.javadrive.jp/poi/style/index5.html
//
//  centering
//      http://tk-factory.net/wordpress/?p=1504
//      http://tk-factory.net/wordpress/?page_id=566

@GrabResolver(name="kobo-maven-repo", root="https://github.com/kobo/maven-repo/raw/master/snapshot")
//@GrabConfig(systemClassLoader=true) // ★GroovyServで実行する場合はこれが必須！
@Grab("org.jggug.kobo:gexcelapi:0.3-SNAPSHOT")
@Grab("commons-io:commons-io:2.0.1")
import org.jggug.kobo.gexcelapi.GExcel

import org.apache.poi.ss.usermodel.*
import org.apache.poi.util.*
import org.apache.poi.hssf.usermodel.*
import org.apache.commons.io.IOUtils

import org.jggug.kobo.gexcelapi.GExcel
class GExcel_ext {

  static {
    expandWorkbook()
    //expandSheet()
    //expandRow()
    expandCell()
    //expandCellRangeAddress()
  }

  private static expandWorkbook() {
    GExcel.metaClass.'static'.create << { 
      def wb = new HSSFWorkbook() 
      wb.createSheet()
      wb
    }

    Workbook.metaClass.define{
      getFont{idx->
          getFontAt(idx) 
      }

      save << { filename->
        FileOutputStream out = new FileOutputStream(filename)
        delegate.write(out)
        out.close()
      }
    }
  }

  private static expandCell() {
    Cell.metaClass.define{
        getColor{
            //see http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/IndexedColors.html
            def style=delegate.getCellStyle()
            def colorbuf=IndexedColors.values()
            def idx = style.getFillForegroundColor()
            colorbuf.size() > idx ? colorbuf[idx] : style.getFillForegroundColorColor().dump()
        }
        getColorIndex{
            def style=delegate.getCellStyle()
            style.getFillForegroundColor()
        }
        
        setColorIndex{index->
            def style=delegate.getCellStyle()
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor(index)
            delegate.setCellStyle(style)
        }
        
        getFontIndex{
            def style=delegate.getCellStyle()
            style.getFontIndex()
        }
        setFont{font->
            def style=delegate.getCellStyle()
            style.setFont(font)
            delegate.setCellStyle(style)    
        }
        
        // 
        // HSSFCellStyle.VERTICAL_TOP //上詰め
        // HSSFCellStyle.VERTICAL_CENTER //中央揃え
        // HSSFCellStyle.VERTICAL_BOTTOM //下詰め
        // HSSFCellStyle.VERTICAL_JUSTIFY //両端揃え
        //
        setAlignment{alignment->
            def style=delegate.getCellStyle()
            def verticalAlignment = HSSFCellStyle.VERTICAL_CENTER
            switch(alignment){
                case "VERTICAL_TOP":
                    verticalAlignment =HSSFCellStyle.VERTICAL_TOP //上詰め
                    break;
                case "VERTICAL_CENTER":
                    verticalAlignment =HSSFCellStyle.VERTICAL_CENTER //中央揃え
                    break;
                case "VERTICAL_BOTTOM":
                    verticalAlignment =HSSFCellStyle.VERTICAL_BOTTOM //下詰め
                        break;
                case "VERTICAL_JUSTIFY":
                    verticalAlignment =HSSFCellStyle.VERTICAL_JUSTIFY //両端揃え
                    break;
            }
            style.setVerticalAlignment(verticalAlignment)
            delegate.setCellStyle(style)    
        }
        
        setWrapText{flag->
            def style=delegate.getCellStyle()
            style.setWrapText(flag)
            delegate.setCellStyle(style)    
        }
        //
        // http://d.hatena.ne.jp/fumokmm/20081122/1227337357
        //
        addImage{filename,width_row,height_col->
            /* 画像をストリームで読み込む */
            def bytes = IOUtils.toByteArray(new FileInputStream(filename))
            
            //http://pacific.mpi-cbg.de/javadoc/loci/poi/hssf/usermodel/HSSFPicture.html
            int pic_type=HSSFWorkbook.PICTURE_TYPE_JPEG
            if(filename.toLowerCase().endsWith(".png")){
                pic_type=HSSFWorkbook.PICTURE_TYPE_PNG
            }
            if(filename.toLowerCase().endsWith(".bmp")){
                pic_type=HSSFWorkbook.PICTURE_TYPE_DIB
            }
  
            def book = delegate.getSheet().getWorkbook() 
            def pictureIdx = book.addPicture(bytes, pic_type)
            /* 描画オブジェクト生成(一回のみ実行する) */
            def patriarch = sheet.createDrawingPatriarch()
            /* アンカーの生成 */
            def anchor = new HSSFClientAnchor()
            // 0 = Move and size with Cells.
            // 2 = Move but don't size with cells.
            // 3 = Don't move or size with cells.
            anchor.setAnchorType(0) // ここではセルにあわせて移動＆リサイズを選択
            /*
             * 余白微調整？
             *   このsetD～を指定しないとセルにぴったりくっついた感じになる
             */
            anchor.setDx1(100)
            anchor.setDy1(100)
            anchor.setDx2(-100)
            anchor.setDy2(-100)
            /*
             * 画像の左上がセル(0,0)の左上～
             *       右下がセル(10,3)の左上(=セル(9,2)の右下)
             */
            anchor.setRow1(delegate.getRowIndex())
            anchor.setCol1(delegate.getColumnIndex())
            anchor.setRow2(delegate.getRowIndex() + width_row)
            anchor.setCol2(delegate.getColumnIndex() + height_col as short)
            /* partiachに画像を設定 */
            patriarch.createPicture(anchor, pictureIdx)    
        }
      }
  }
}

GExcelAPI_Color_Font_test.groovy

//   GExcelAPI http://d.hatena.ne.jp/nobeans/20101216/1292513322
//   src
//    https://github.com/nobeans/gexcelapi/blob/master/src/main/groovy/org/jggug/kobo/gexcelapi/GExcel.groovy
//   sample.xls is copy & edit
//      https://github.com/nobeans/gexcelapi/blob/master/src/test/resources/sample.xls
//
//   POI operation 
//      http://www.javadrive.jp/poi/style/index2.html
//      http://www.javadrive.jp/poi/style/index5.html
//
//  centering
//      http://tk-factory.net/wordpress/?p=1504
//      http://tk-factory.net/wordpress/?page_id=566

@GrabResolver(name="kobo-maven-repo", root="https://github.com/kobo/maven-repo/raw/master/snapshot")
//@GrabConfig(systemClassLoader=true) // ★GroovyServで実行する場合はこれが必須！
@Grab("org.jggug.kobo:gexcelapi:0.3-SNAPSHOT")
@Grab("commons-io:commons-io:2.0.1")
import org.jggug.kobo.gexcelapi.GExcel

/*
getClass().metaClass={  
   static {
        new GExcel_ext()
   }
}
*/
import org.codehaus.groovy.control.*
ClassLoader parent = this.class.getClassLoader()
CompilerConfiguration config = new CompilerConfiguration(sourceEncoding:'UTF-8')
loader = new GroovyClassLoader(parent,config,false)
def source = new GroovyCodeSource(new File("GExcel_ext.groovy"),"UTF-8") 
exClass = loader.parseClass(source)
exClass.newInstance()


// Excelファイルの読み込み
def book = GExcel.open("sample.xls")
//def book = GExcel.create()

// シートの取得
def sheet = book[0] // 第1シート
//def sheet = book[1] // 第2シート
//def sheet = book["Sheet3"] // シート名で指定も可能
/*
// セルの値の取得
println sheet.A1.value // -> "value of A1"
println sheet.B2.value // -> "value of B3"

println "="*20

// セルの値の書き換え
sheet.A1.value = "New value of A1"
sheet.B2.value = "New value of B3"

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

println "-"*20
sheet.A1_B6.each{ row -> row.each { cell -> dump cell } }
println "="*20
*/

println "="*20
println "sheet.A5=${sheet.A5.value}"
idx=sheet.A5.colorIndex
println "${sheet.A5.color}/${sheet.A5.colorIndex}"
println "${book.getFont(sheet.A5.fontIndex).fontName}/${sheet.A5.fontIndex}"
println "="*20

println "${sheet.A6.color}/${sheet.A6.colorIndex}"
println "sheet.A6=${sheet.A6.value}"
if(idx != sheet.A6.colorIndex)sheet.A6.colorIndex =idx
println "${sheet.A6.color}/${sheet.A6.colorIndex}"

sheet.C8 << "あいうえおかきくけこさしすせそたちつてと"
sheet.C8.setAlignment "VERTICAL_TOP"
sheet.C8.setWrapText true

println "="*20

sheet.C10.addImage "Capu001.png" ,20,6


// 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)
    }
}
println writer.toString()
*/


import java.awt.Desktop
Desktop desktop = Desktop.getDesktop()

//save test
new File("out.xls").withOutputStream { book.write(it) } 
//book.save "out.xls"

desktop.open new File("out.xls")

//html output 
def range = sheet.A1_F10
def tempFile = File.createTempFile("excel2html", "open.html")
tempFile.text = range.toHtml("test", "UTF-8")
desktop.open tempFile

output

====================
sheet.A5=true
TEAL/13
Arial/5
====================
<org.apache.poi.hssf.util.HSSFColor$AUTOMATIC@76800e>/64
sheet.A6=false
TEAL/13
====================