scala 使用jxl导出多sheet页

川长思鸟来 2022-06-13 05:08 160阅读 0赞

//入參  
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  
    \}  
  \}