scala 使用jxl导出多sheet页

//入參
case class sheetParam(sheetTitle:String,subTitle:String,sql:String)


class ExportSheet {




  //导出Excel
  def exportExcelFilesSheet(title:String,selectCondition:String,sheetInfo:List[sheetParam])={
   
    //声明
    var fileName = "fail"
    var conn:Connection = null
    try{
      //生成一个文件
      val exportFile = new File(s"fileName.xls")
      if(!exportFile.getParentFile.exists())        //如果存在
        exportFile.getParentFile.mkdirs
      if(!exportFile.exists())
        exportFile.createNewFile


      //生成一个Excel
      val wwb:WritableWorkbook = Workbook.createWorkbook(new FileOutputStream(exportFile))
      //建立数据库连接
      import com.zte.bigdata.vmax.common.database.ConnectionFactoryPrefs.GBase
      conn = GBase.createConnection


      //生成多个sheet
      for(i<-0 to sheetInfo.length-1){


        createSheet(conn,wwb,sheetInfo(i).sheetTitle,i,sheetInfo(i).subTitle,sheetInfo(i).sql)
      }


      //关闭
      wwb.write()
      wwb.close()
      if(exportFile.exists && exportFile.length() > 0)
        fileName = exportFile.getName
    }catch {
      case e:Exception => e.printStackTrace()
    }finally{
      if(conn != null) conn.close()
    }
    fileName
  }


  //生成多个sheet
  def createSheet(conn:Connection,wwb:WritableWorkbook,sheetTitle:String,sheetIndex:Int,headTitle:String,sql:String)={
    var pstmt:PreparedStatement = null
    var rs:ResultSet = null
    try{
      pstmt = conn.prepareStatement(sql)
      rs = pstmt.executeQuery()
      val sheet:WritableSheet  = wwb.createSheet(sheetTitle, sheetIndex)
      genSheetTitle(sheet,sheetTitle,headTitle)
      WriteRs(sheet,rs)
    }catch{
      case e:Exception => e
    }finally{
      if(rs != null) rs.close()
      if(pstmt != null) pstmt.close()
    }
  }


  //生成表头
  def genSheetTitle(sheet:WritableSheet,sheetTitle:String,headTitle:String)={
    var label:Label  = null
    val headTitleList :Array[String] = headTitle.split(",")
    for (i <- 0.to(headTitleList.length-1)){
      label = new Label(i,0,headTitleList(i));
      sheet.addCell(label);
    }
  }




  //把数据写进工作表
  def WriteRs(sheet:WritableSheet,rs:ResultSet)={
    try{
      var label:Label  = null
      var numberWr: write.Number = null
      var row = 1
      val columns = rs.getMetaData.getColumnCount
      //定义通用类的WritableCellFormat,必须独立new出来常用的经度类型,暂取最长16经度
      val preNumFormatted = "#0" :: (1 to 16).toList.map(len => s"#0.${(1 to len).map(x => 0).mkString("")}")
      val preNf = preNumFormatted.map(x => new jxl.write.NumberFormat(x))
      val preWcfN = preNf.map(x => new jxl.write.WritableCellFormat(x))
      //
      while(rs.next()) {
        for (i <- 1 to columns) {
          val value = Try(rs.getString(i).replace("null","")).getOrElse("")
          if(isNumeric(value)) {
            val numFormatted = setNumberFormatted(value)
            val numIndex = preNumFormatted.indexOf(numFormatted)
            val wcfN = numIndex match {
              case -1 => preWcfN(16)
              case _ => preWcfN(numIndex)
            }
            numberWr = numFormatted match {
              case "#0" => new jxl.write.Number(i-1,row,value.toInt,wcfN)
              case _ => new jxl.write.Number(i-1,row,value.toDouble,wcfN)
            }
            sheet.addCell(numberWr)
          } else {
            label = new Label(i-1, row, value)
            sheet.addCell(label)
          }
        }
        row = row + 1
      }
    }catch {
      case e:Exception => e
    }
  }

}