java导出excel

  /**
  * 返回文件
  * @param map
  * @param response
  * @return
  */
 private String exportExcleImage(HashMap map, HttpServletResponse response,String procFlag) {
   // 创建一个工作薄
  HSSFWorkbook wb = new HSSFWorkbook();
   HSSFSheet sheet1 = wb.createSheet("first page");
  HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
         if(!"".equals(this.getValue(title))){
     String[] sTitle = title.split("~");
           for(int i=0;i<sTitle.length;i++){
               sheet1.setColumnWidth((short)(i + 1), (short)(20 * 256));
                 HSSFRow ss = sheet1.createRow(0);
            HSSFCell cell=ss.createCell((short) (i));
               cell.setCellValue(new HSSFRichTextString(sTitle[i]));  //插入单元格内容
            }
   }
  FileOutputStream fileOut = null;
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  try {
   conn = super.getDataSource().getConnection();
   stmt = conn.createStatement();
   String sql = map.get("sql") + "";
   boolean f = false;
   this.sql = sql;
   short ii = 0;
   String maxidsql="select max(id) maxid  from ( "+sql+" )";
   int maxid=jdbcTemplate.queryForInt(maxidsql);
   String sqlcount="select count(id)  totals   from ( "+sql+" )";
   int totals= jdbcTemplate.queryForInt(sqlcount);
   int idstart=maxid;
    HSSFRow ss = null;
   while (rs.next()) {
    ResultSetMetaData rsm = rs.getMetaData();
    if (colNum == 0) {
     colNum = rsm.getColumnCount();
     for(int k=1;k<=colNum;k++){
      if ("ID".equals(rsm.getColumnName(k).toUpperCase())) {
       f = true;
      }
     }
    }
    int yy = 0; 
    for (int i = 1; i <= colNum; i++) {
     String coluName = PubFunction.getNulltoStr(rs.getObject(i));
     if (coluName.indexOf("IMG-IMG~") != -1) {
      yy = 1;
     }
    }
        ss = sheet1.createRow(ii+1);  //每一行
    if(yy > 0){  //  图片字段  设置高度
      ss.setHeight((short)(5 * 256));
    }
    for (int i = 1; i <colNum; i++) {
      String coluName = PubFunction.getNulltoStr(rs.getObject(i));
      if (coluName.indexOf("IMG-IMG~") == -1) {
                  //f 为 true 表示存在ID,
                 if(f == true){
                     if (i == 1) {
          continue;
         }    
                     HSSFCell cell=ss.createCell((short) (i-2));
                  cell.setCellValue(new HSSFRichTextString(PubFunction.getNulltoStr(rs.getObject(i))));  //插入单元格

                 } else {
                     HSSFCell cell=ss.createCell((short) (i-1));
                  cell.setCellValue(new HSSFRichTextString(PubFunction.getNulltoStr(rs.getObject(i))));  

                 }
         } else {
          //图片的二进制
         StringBuffer stringBuffer = this.getPhoto(PubFunction.getNulltoStr(rs.getObject(i)));
          if(stringBuffer != null && !"0".equals(stringBuffer + "")&&!"".equals(stringBuffer + "")&&!"FAIL".equals(stringBuffer + "")){
           BASE64Decoder decoder = new BASE64Decoder();
//           转成字节码
        byte[] b = decoder.decodeBuffer(stringBuffer + "");
        ByteArrayInputStream in = new ByteArrayInputStream(b);
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        try {
         ImageIO.write(ImageIO.read(in), "jpg", byteArrayOut);
         HSSFClientAnchor anchor = null;
//            anchor.setAnchorType(3);
           if(f == true){
               anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) (i-2), 1 + ii, (short) (i-1), 2 + ii);
           } else {
                anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) (i-1), 1 + ii, (short) (i), 2 + ii);
           }
         patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));// 插入图片
        } catch (Exception e) {
          if(f == true){
          HSSFCell cell=ss.createCell((short) (i-3));
                 cell.setCellValue(new HSSFRichTextString(PubFunction.getNulltoStr(rs.getObject(i))));  //插入单元格

           } else {
           HSSFCell cell=ss.createCell((short) (i-1));
                  cell.setCellValue(new HSSFRichTextString(PubFunction.getNulltoStr(rs.getObject(i))));  

           }
        }
          } else {
              if(f == true){
              HSSFCell cell=ss.createCell((short) (i-2));
                      cell.setCellValue(new HSSFRichTextString(PubFunction.getNulltoStr(rs.getObject(i))));  //插入单元格内容
          } else {
               HSSFCell cell=ss.createCell((short) (i-1));
                      cell.setCellValue(new HSSFRichTextString(PubFunction.getNulltoStr(rs.getObject(i))));  //插入单元格内容
          }
          }
        }
      
      }
    ii++;
   }
   rs=null;
   }//lil for
   HSSFRow row = sheet1.createRow(10);
    short s = 10;
   HSSFCell cell = row.createCell(s);
   HSSFCellStyle style = wb.createCellStyle();
   HSSFFont font = wb.createFont();
   font.setStrikeout(true);
   style.setFont(font);
   cell.setCellStyle(style);    
   String orgCode = (ServletActionContext.getRequest().getSession().getAttribute("orgcode") + "").toUpperCase();
          fimeName = fimeName + "_" + orgCode + "_.xls";
   String path = ServletActionContext.getRequest().getRealPath("download");
        path = path + "/" + fimeName;
     fileOut = new FileOutputStream(path,true);
   wb.write(fileOut);
   fileOut.close();
   return fimeName;
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  } finally {
   try {
    if (null != rs)
     rs.close();
    if (null != stmt)
     stmt.close();
    if (null != conn)
     conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  return fimeName;
   }
/**
 * 获取图片
 */
 public StringBuffer getPhoto(String photoInfo){
//  String photoInfo = searchMap.get("photoInfo")+"";
  photoInfo = photoInfo.replaceAll("IMG-IMG~", "");
  String[] len = photoInfo.split("-");
  StringBuffer strBuf = null;
  if(len.length !=3){
   return strBuf;
  }
  String rowid = "";
  String fieldName = "";
  String tableName = "";
  if(len.length == 3){
     rowid = len[0];
     fieldName = len[1];
     tableName = len[2];
  } else {
     rowid = "0";
     fieldName = len[0];
     tableName = len[1];
  }
  String sql = "SELECT "+fieldName+" FROM "+tableName+" WHERE ID='"+rowid+"'";
  strBuf = this.queryCodeStringBuffer(sql);
  if(null!=strBuf&&!"".equals(strBuf)){
   try{
   }catch (Exception e) {
   }
 }
  return strBuf;
 }
 public StringBuffer queryCodeStringBuffer(String sql){
  StringBuffer str = new StringBuffer("");
  Statement stmt = null;
  ResultSet rs = null;
  Connection con = null;
  try {
   con = jdbcTemplate.getDataSource().getConnection();
   stmt = con.createStatement();
   rs = stmt.executeQuery(sql);
   while (rs.next()) {
      str = str.append(this.clobConversion((CLOB) rs.getClob(1)));     
   }
  } catch (Exception e) {
   e.printStackTrace();
  }finally{
   
   }
  return str;
 }