Changeset 8079 for lang/scala/sandbox

Timestamp:

03/18/08 18:29:01 (9 months ago)

Author:

ryugate

Message:

POI.scala

add MyBook#eachSheetsByName?
add MySheet#cell?(String)
add MySheet#cells?(String*)
add MySheet#setCell?
add MySheet#applyOrCreateCell?
add MyRow#applyOrCreateCell?
add Col#applyOrCreateCell
add MyCell#get?
add MyCell#set?
add MyCell#setFormula?
change MySheet#cell?(Int,Int) 行、桁の順で指定したのを、桁、行の順にした

Location:

lang/scala/sandbox/src/jp/ryugate/apache

Files:

• POI.scala (modified) (13 diffs)
• POISpecification.scala (modified) (4 diffs)

lang/scala/sandbox/src/jp/ryugate/apache/POI.scala

@@ -3 +3 @@
 /**
 * POI wrapper
-* 2008/3/12
+* 2008/3/17
 */
 object POI {
 
+  import scala.Math
   import java.io.InputStream
 
-  import org.apache.poi.hssf.usermodel.HSSFWorkbook
-  import org.apache.poi.hssf.usermodel.HSSFSheet
-  import org.apache.poi.hssf.usermodel.HSSFRow
-  import org.apache.poi.hssf.usermodel.HSSFCell
+  import org.apache.poi.hssf.usermodel.{HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell, HSSFRichTextString}
   import org.apache.poi.hssf.usermodel.HSSFCell._
 
-  private def makeSomeList[T](f:int => T, n:int*)
-    = n.foldLeft(List[T]()){(acc, i) => f(i) :: acc}.reverse
-    
+  private def makeSomeList[A,B](f:A => B, n:A*)
+    = n.foldLeft(List[B]()){(acc, i) => f(i) :: acc}.reverse
+
   private def eachWithIndex[T,V](lst:List[T], f2:(T,int) => V)
     = lst.zipWithIndex.foreach{case (row,idx) => f2(row,idx)}
 
+  private def stoi(str:String, conv:Char=>Int, radix:Int) = {
+    def pow(n:Int,i:Int):Int = i match {
+      case 0 => 1
+      case _ => n * pow(n, i-1)
+    }
+  
+    str.toArray.reverse.zipWithIndex.foldLeft(0) {
+      case (acc, (chr,digit)) => acc + conv(chr) * pow(radix,digit)
+    }
+  }
+  
+  private def colstrToInt(str:String) = stoi(str.replaceAll("[0-9]", ""), (c:Char)=>c-'A'+1, 26)
+  private def rowstrToInt(str:String) = str.replaceAll("[A-Z]", "").toInt
+
   /**
   * Book 
   */
   class MyBook(hssf_book:HSSFWorkbook) {
-    def sheet(index:int  ) = hssf_book.getSheetAt(index-1)                      // index start from 1
-    def sheet(name:String) = hssf_book.getSheet(name) match {
+    def sheet(index:Int  ):HSSFSheet = hssf_book.getSheetAt(index-1)            // index start from 1
+    def sheet(name:String):HSSFSheet = hssf_book.getSheet(name) match {
       case null => throw new NoSuchElementException(name + "(Sheet not found)")
       case v => v
@@ -34 +46 @@
     def withSheet[V](name:String)(f:HSSFSheet => V) = f(sheet(name))
 
-    def getSheets(idxs:int*  ) = makeSomeList(sheet, idxs:_*)
-    def getSheets(range:Range) = makeSomeList(sheet, range:_*)
+    def getSheets(idxs:Int*   ) = makeSomeList[Int,HSSFSheet](sheet, idxs:_*)
+    def getSheets(range:Range ) = makeSomeList[Int,HSSFSheet](sheet, range:_*)
 
     def eachSheets[V](idxs:int*  )(f:HSSFSheet => V) = getSheets(idxs:_* ).foreach{f(_)}
     def eachSheets[V](range:Range)(f:HSSFSheet => V) = getSheets(range:_*).foreach{f(_)}
+
+    def getSheetsByName(idxs:String*) = makeSomeList[String,HSSFSheet](sheet, idxs:_*)
+    def eachSheetsByName[V](idxs:String*)(f:HSSFSheet => V) = getSheetsByName(idxs:_* ).foreach{f(_)}
     
     def eachSheetsWithIndex[V](idxs:int*  )(f:(HSSFSheet,int) => V) = eachWithIndex(getSheets(idxs:_*), f)
@@ -55 +70 @@
       case row => row 
     }
-    def col(ci:int) = new Col(this, ci)
-
-    def getCell(ri:int,ci:int) = row(ri).get(ci)
-    def cell(ri:int,ci:int) = row(ri).apply(ci)
-
+    def col(ci:int    ):Col = new Col(this, ci)
+    def col(cis:String):Col = col(colstrToInt(cis))
+
+    def getCell(ci:int,ri:int ):Option[HSSFCell] = row(ri).get(ci)
+    def getCell(cellstr:String):Option[HSSFCell] = getCell(colstrToInt(cellstr), rowstrToInt(cellstr))
+
+    def cell(ci:int,ri:int ):HSSFCell = row(ri).apply(ci)
+    def cell(cellstr:String):HSSFCell = cell(colstrToInt(cellstr), rowstrToInt(cellstr))
+
+    def cells(cellstrs:String*) = makeSomeList(cell, cellstrs:_*)
+
+    def applyOrCreateCell(ci:int,ri:int ):HSSFCell = row(ri).applyOrCreate(ci)
+    def applyOrCreateCell(cellstr:String):HSSFCell = applyOrCreateCell(colstrToInt(cellstr), rowstrToInt(cellstr))
+    
     def withRow[V](ri:int)(f:HSSFRow => V) = f(row(ri))
-    def withCol[V](ci:int)(f:Col => V) = f(col(ci))
-
-    def rows(rowIdxs:int*) = makeSomeList(row, rowIdxs:_*)
-    def cols(colIdxs:int*) = makeSomeList(col, colIdxs:_*)
-    def rows(range:Range ) = makeSomeList(row, range:_*)
-    def cols(range:Range ) = makeSomeList(col, range:_*)
+    def withCol[V](ci:int)(f:Col     => V) = f(col(ci))
+
+    def rows(rowIdxs:int*) = makeSomeList(row,          rowIdxs:_*)
+    def cols(colIdxs:int*) = makeSomeList(col:int=>Col, colIdxs:_*)
+    def rows(range:Range ) = makeSomeList(row,          range:_*)
+    def cols(range:Range ) = makeSomeList(col:int=>Col, range:_*)
 
     def eachRows[V](rowIdxs:int*)(f:HSSFRow => V) = rows(rowIdxs:_*).foreach{f(_)}
-    def eachCols[V](colIdxs:int*)(f:Col => V) = cols(colIdxs:_*).foreach{f(_)}
+    def eachCols[V](colIdxs:int*)(f:Col     => V) = cols(colIdxs:_*).foreach{f(_)}
     def eachRows[V](range:Range )(f:HSSFRow => V) = rows(range     ).foreach{f(_)}
-    def eachCols[V](range:Range )(f:Col => V) = cols(range     ).foreach{f(_)}
+    def eachCols[V](range:Range )(f:Col     => V) = cols(range     ).foreach{f(_)}
 
     def eachRowsWithIndex[V](idxs:int*  )(f:(HSSFRow,int) => V) = eachWithIndex(rows(idxs:_*), f)
-    def eachColsWithIndex[V](idxs:int*  )(f:(Col,int) => V) = eachWithIndex(cols(idxs:_*), f)
+    def eachColsWithIndex[V](idxs:int*  )(f:(Col,    int) => V) = eachWithIndex(cols(idxs:_*), f)
     def eachRowsWithIndex[V](range:Range)(f:(HSSFRow,int) => V) = eachWithIndex(rows(range:_*), f)
-    def eachColsWithIndex[V](range:Range)(f:(Col,int) => V) = eachWithIndex(cols(range:_*), f)
+    def eachColsWithIndex[V](range:Range)(f:(Col,    int) => V) = eachWithIndex(cols(range:_*), f)
 
     def each[V](f:HSSFRow => V) = eachRows(0 to getLastRowNum)(f)
@@ -83 +107 @@
 
     //--------------------------------------------
-    def setCell(ri:Int, ci:Int, value:String, encoding:Short):HSSFCell = row(ri).setCell(ci, value, encoding)
-    def setCell(ri:Int, ci:Int, value:String):HSSFCell = row(ri).setCell(ci, value)
+    def setCell(ri:Int, ci:Int, value:Any, encoding:Short):HSSFCell = row(ri).setCell(ci, value, encoding)
+    def setCell(ri:Int, ci:Int, value:Any                ):HSSFCell = row(ri).setCell(ci, value)
+    def setCell(cellstr:String, value:Any                ):HSSFCell = row(rowstrToInt(cellstr)).setCell(colstrToInt(cellstr), value)
   }
   implicit def HSSFSheetToExtSheet(sheet:HSSFSheet) = new MySheet(sheet)
@@ -97 +122 @@
     }
 
-    def getOrCreate(ci:int) = get(ci) match {                                   // index start from 1
-      case None    => hssf_row.createCell((ci-1).toShort)
-      case Some(v) => v
-    }
-
     def apply(cellnum:int) = get(cellnum) match {
       case None => throw new NoSuchElementException(cellnum + " (Cell not found)")
@@ -108 +128 @@
     def col(cellnum:int) = apply(cellnum)
     def cell(cellnum:int) = apply(cellnum)
+
+    def applyOrCreate(ci:int) = get(ci) match {                                 // index start from 1
+      case None    => hssf_row.createCell((ci-1).toShort)
+      case Some(v) => v
+    }
 
     def getCells(idxs:int*  ) = makeSomeList(get, idxs:_*)
@@ -113 +138 @@
     def cells(idxs:int*  ) = makeSomeList(apply, idxs:_*)
     def cells(range:Range) = makeSomeList(apply, range:_*)
+
+    def applyOrCreateCells(idxs:Int*  ) = makeSomeList(applyOrCreate, idxs:_*)
+    def applyOrCreateCells(range:Range) = makeSomeList(applyOrCreate, range:_*)
 
     def eachCells[V](idxs:int*  )(f:HSSFCell => V) = cells(idxs:_* ).foreach{f(_)}
@@ -123 +151 @@
 
     //--------------------------------------------
-    def setCell(ci:int, value:String, encoding:Short):HSSFCell = {
-      val cell = getOrCreate(ci)
-      cell.setEncoding(encoding)
-      cell.setCellValue(value)
-      cell
-    }
-
-    def setCell(cellnum:int, value:String):HSSFCell = setCell(cellnum, value, ENCODING_UNCHANGED)
+    def setCell(ci:int, value:Any, encoding:Short):HSSFCell = applyOrCreate(ci).set(value, encoding)
+    def setCell(ci:int, value:Any                ):HSSFCell = setCell(ci, value, ENCODING_UNCHANGED)
   }
   implicit def HSSFRowToExtRow(row:HSSFRow) = new MyRow(row)
@@ -143 +165 @@
     def row(ri:int) = apply(ri)
     def cell(ri:int) = apply(ri)
+
+    def applyOrCreate(ri:int) = sheet.row(ri).applyOrCreate(colIdx)
 
     def getCells(idxs:int*  ) = makeSomeList(get, idxs:_*)
@@ -148 +172 @@
     def cells(idxs:int*  ) = makeSomeList(apply, idxs:_*)
     def cells(range:Range) = makeSomeList(apply, range:_*)
+    
+    def applyOrCreateCells(idxs:Int*  ) = makeSomeList(applyOrCreate, idxs:_*)
+    def applyOrCreateCells(range:Range) = makeSomeList(applyOrCreate, range:_*)
     
     def eachCells[V](idxs:int*  )(f:HSSFCell => V) = cells(idxs:_* ).foreach{f(_)}
@@ -158 +185 @@
 
     //--------------------------------------------
-    def setCell(cellnum:int, value:String, encoding:Short):HSSFCell = sheet.row(cellnum).setCell(colIdx, value, encoding)
-    def setCell(cellnum:int, value:String):HSSFCell = sheet.row(cellnum).setCell(colIdx, value)
+    def setCell(ri:int, value:Any, encoding:Short):HSSFCell = sheet.row(ri).setCell(colIdx, value, encoding)
+    def setCell(ri:int, value:Any                ):HSSFCell = sheet.row(ri).setCell(colIdx, value)
   }
   
@@ -166 +193 @@
   */
   class MyCell(hssf_cell:HSSFCell) {
-//    def get = apply match {
-//      case null => None
-//      case v    => new Some(v)
-//    }
+    def get = apply match {
+      case null => None
+      case v    => new Some(v)
+    }
 
     def apply = hssf_cell.getCellType match {
@@ -179 +206 @@
       case _ => hssf_cell.toString
     }
-    
     def value = apply
+    
+    def set(value:Any, encoding:Short):HSSFCell = {
+      import java.util.{Calendar,Date}
+
+      hssf_cell.setEncoding(encoding)
+      value match {
+        case v:boolean  => hssf_cell.setCellValue(v)
+        case v:Calendar => hssf_cell.setCellValue(v)
+        case v:Date     => hssf_cell.setCellValue(v)
+        case v:double   => hssf_cell.setCellValue(v)
+        case v:Int      => hssf_cell.setCellValue(v.toDouble)
+        case v:String   => hssf_cell.setCellValue(new HSSFRichTextString(v))
+      }
+      hssf_cell
+    }
+    def set(value:Any):HSSFCell = set(value, ENCODING_UNCHANGED)
+
+    def setFormula(value:String) = {
+      hssf_cell.setCellFormula(value)
+      hssf_cell
+    }
   }
   implicit def HSSFCellToExtCell(cell:HSSFCell) = new MyCell(cell)

lang/scala/sandbox/src/jp/ryugate/apache/POISpecification.scala

@@ -7 +7 @@
   import jp.ryugate.apache.POI._
   import org.apache.poi.hssf.usermodel.HSSFWorkbook
-  import org.apache.poi.hssf.usermodel.HSSFSheet
-  import org.apache.poi.hssf.usermodel.HSSFRow
-  import org.apache.poi.hssf.usermodel.HSSFCell
-  import org.apache.poi.hssf.usermodel.HSSFCell._
   import org.apache.poi.hssf.usermodel.HSSFCellStyle
+//  import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator
   
   import java.lang._
@@ -41 +38 @@
 
       book.eachSheets(1 to 3) {sheet => sheet must notBeNull}
+      book.eachSheetsByName("Sheet1","Sheet3") {sheet => sheet must notBeNull}
       book.eachSheets(1,10) {sheet => ; } must throwException(new IndexOutOfBoundsException)
 
@@ -60 +58 @@
     
     "get cell" in {     
-      sheet1.cell(1,1).value must is_==(1.0)
-      sheet1.col(1).row(1).value must is_==(1.0)
       sheet1.row(1).col(1).value must is_==(1.0)
+      sheet1.cell("B1").value    must is_==(2.0)
+      sheet1.cell(2,1).value     must is_==(2.0)
+      sheet1.col(3).row(1).value must is_==(3.0)
+
+      sheet1.row(1).col(1).get must is_==(Some(1.0))
+      sheet1.cell("B1").get    must is_==(Some(2.0))
+      sheet1.cell(2,1).get     must is_==(Some(2.0))
+      sheet1.col(3).row(1).get must is_==(Some(3.0))
+
+      sheet1.cells("A1","C1")
     }
 
@@ -72 +78 @@
       val sheet = bk.createSheet("test")
       bk.sheet(1) must notBeNull
-      sheet.row(1).setCell(1,"hoge").setCellStyle(style) 
-      sheet.col(1).setCell(1,"piyo")
-      sheet.setCell(3,3,"HOGE").setCellStyle(style)
+      sheet.row(1).setCell(1, "hoge").setCellStyle(style) 
+      sheet.col(1).setCell(1, "piyo")
+      sheet.setCell(3,3, "HOGE").setCellStyle(style)
+      sheet.setCell("A5", 1)
+      sheet.setCell("B5", 2)
+      sheet.applyOrCreateCell(5,3).setFormula("A5+B5")
+      sheet.row(5).col(2).value must is_==(2.0)
+      
+      sheet.row(10).applyOrCreateCells(1 to 10).foreach {cell =>
+        cell.setCellStyle(style)
+      }
+      
+//      val ev = new HSSFFormulaEvaluator(sheet, bk)
+//      ev.evaluate(sheet.col(3).row(5)).getNumberValue must is_==(3.0)
 
       bk.write(new FileOutputStream("testdata/testoutput.xls"))