scala 使用jxl导出多sheet页 川长思鸟来 2022-06-13 05:08 123阅读 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 \} \} \}
还没有评论,来说两句吧...