jxt excel操作

WageDistribute.java

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import dangxiao.training.statistic.StatisticUtil;

import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WritableFont;

public class WageDistribute {
 
 
 

 /**
    * 工资单分发
    * @param fileName
    * @return
    */
 public String wageDistribute(String fileName,String subject,String remark)
 {
  Connection con = null;
  Statement sat = null;
  ResultSet res = null;
  String error = "";
  //List excel = new ArrayList();
  
  // 导入EXCEL
  File f = new File(fileName);
  FileInputStream fis;
     try {
   con = DBConnection.getConnection();
   sat = con.createStatement();
   String sql = "select role_name,email from hdrole where type=1 and state=1 ";
   res = sat.executeQuery(sql);  
   HashMap roleHash = new HashMap();
   while(res.next())
   {
    if(res.getString("role_name")!=null&&!"".equals(res.getString("role_name"))){
     roleHash.put(res.getString("role_name"), res.getString("email"));
    }
   }
   
   
   StringBuffer errorbf = new StringBuffer();
   if (fileName.lastIndexOf(".xls") == -1){
      fis= new FileInputStream(f);
      error = "文件格式不正确!<br><font color='red'>提示: 文件格式只能是.xls</font>";
      fis.close();
      f.delete();
      return error;
     } else {
      MailManager mail=null;
      try{
        mail = new MailManager();
      }
      catch(Exception e)
      {
       error = "邮箱用户名验证失败！";
       return error;
      }
         fis= new FileInputStream(f);
         jxl.Workbook rwb= Workbook.getWorkbook(fis);
         Sheet[] sheets = rwb.getSheets();
         for(int t=0; t<sheets.length; t++){
        jxl.Sheet sh= rwb.getSheet(t);
        //System.out.println("&&&&^^^^^^^^(((((((%%%%%%%%          " + sh.getName());
        String fName = "Excel工作簿<font color='green'>" + sh.getName() + "</font>";
        //获得所有行
        int rowCount= sh.getRows();
       // System.out.println("rowCount:" + rowCount);
        if(rowCount == 2){
         error = "<center><font color=/"red/">Excel文件中没有任何数据!</font></center>";
         return error;
        }
        StringBuffer tablesb = new StringBuffer();
        jxl.Cell[] firstCell= sh.getRow(1);
        tablesb.append("<table border=1>");
        tablesb.append("<tr>");
        for(int l=0;l<firstCell.length;l++)
        {
         tablesb.append("<td>");
         tablesb.append(firstCell[l].getContents());
         tablesb.append("</td>");
        }
        //System.out.printllln("        *****************" + rowCount);
       
        //获得所有列
        //int columns = sh.getColumns();
        //System.out.println("columns" + columns);
       
        String userName="";
        for (int i= 2; i < rowCount; i++) {
         StringBuffer bodysb = new StringBuffer();
         bodysb.append(tablesb.toString());
         bodysb.append("<tr>");
         jxl.Cell[] ce= sh.getRow(i);
         for(int j=0;j< ce.length;j++) {
           String str = ce[j].getContents().trim();
          
           if(j == 1 && str.equals("")){
            error = fName + "第" + (i+1) + "行: <br><font color='blue'>姓名</font>不能为空!";
           // System.out.println(error);
            rwb.close();
             fis.close();
             f.delete();
            return error;
           }else if(j==1&& !str.equals(""))
           {
            userName =  ce[j].getContents();
           }
           bodysb.append("<td>");
           bodysb.append(ce[j].getContents());
           bodysb.append("</td>");
        
           
          }
         if(firstCell.length>ce.length)
         {
          for(int j=0;j<firstCell.length-ce.length;j++)
          {
           bodysb.append("<td>");
            bodysb.append("&nbsp;");
            bodysb.append("</td>");
          }
         }
          bodysb.append("</tr>");
          bodysb.append("</table>");
          bodysb.append("<br>附注：");
          bodysb.append(remark);
          if(!"".equals(userName)&&roleHash.get(userName)!=null&&!roleHash.get(userName).equals(""))
          {
         
           try{
            mail.sendMail(roleHash.get(userName).toString(), subject, bodysb.toString(),
            StaticData.HD_FROM_ADDR, "text/html;charset=GBK");
           }catch(Exception e)
           {
            error = fName + "第" + (i+1) + "行: <br><font color='blue'>"+userName+"</font>的邮箱发送失败，可能是发送者邮箱有误！<br>";
            // System.out.println(error);
            errorbf.append(error);
           }
          }
          else
          {
           error = fName + "第" + (i+1) + "行: <br><font color='blue'>"+userName+"</font>的邮箱为空或没有找到！<br>";
          // System.out.println(error);
           errorbf.append(error);
          }
          }
        }
        rwb.close();
        fis.close();
        f.delete();
        roleHash.clear();
        
        error = "分发成功!<br><font color=red>但是</font><br>";
        
        error =error+ errorbf.toString();
       
     }
  
  
    
 } catch (Exception e) {
  error = "<center>分发失败!</center><br>"+error;
  e.printStackTrace();
 } finally {
  
  if(res != null){
   try {
    res.close();
    res = null;
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  
  if(sat != null){
   try {
    sat.close();
    sat = null;
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  
  if(con != null){
   try {
    con.close();
    con = null;
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
  return error;
 }
  public static void main(String agv[])
  {
   WageDistribute w = new WageDistribute();
  // w.wageDistribute("F:/党校/工资表.xls");
   String sss = "aasdf,,sdf";
   System.out.println(sss.indexOf(",,"));
   System.out.println(sss.substring(sss.indexOf(",,")+1));
  }
}