Excel 读写操作

package trans;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ReadWExcel {

 // 从源文件中获取oui信息
 public static List<String> getOUI(String path, int snum) {
  List<String> lst = new ArrayList<String>();
  try {
   InputStream is = new FileInputStream(path);
   jxl.Workbook rwb = Workbook.getWorkbook(is);
   // 获得总 Sheets
   Sheet[] sheets = rwb.getSheets();
   int sheetLen = sheets.length;
   // 多个sheet
   // for (int i = 0; i < sheetLen; i++) {
   // // 获得单个Sheets 含有的行数
   // jxl.Sheet rs = rwb.getSheet(i); //
   // Cell[] cell_domain = rs.getColumn(0);// 读取第一列的值
   // for (int j = 1; j < cell_domain.length; j++) {
   // //
   // System.out.println("第"+j+"Values:"+cell_domain[j].getContents());
   // String sno = "";
   // if(cell_domain[j].getContents().indexOf("-")>0){
   // String[] slist = cell_domain[j].getContents().split("-");
   // sno = slist[1];
   // }
   // lst.add(sno);
   // }
   // }
   // 获得单个Sheets 含有的行数
   jxl.Sheet rs = rwb.getSheet(snum); //
   Cell[] cell_domain = rs.getColumn(0);// 读取第一列的值
   for (int j = 1; j < cell_domain.length; j++) {
    // System.out.println("第"+j+"Values:"+cell_domain[j].getContents());
    String sno = "";
    if (cell_domain[j].getContents().indexOf("-") > 0) {
     String[] slist = cell_domain[j].getContents().split("-");
     sno = slist[1];
    } else {
     sno = cell_domain[j].getContents();
    }
    lst.add(sno);
   }
   rwb.close();
  } catch (Exception ex) {
   ex.printStackTrace();
  }
  return lst;
 }

 // 像源文件中写入读取的数据
 public static void writeSno(String path, int snum, int cnum,
   Map<String, String> map) {
  List<String> lst = new ArrayList<String>();
  try {
   Workbook wb = Workbook.getWorkbook(new File(path));
   jxl.Sheet rs = wb.getSheet(snum); //
   // 打开一个文件的副本，并且指定数据写回到原文件
   WritableWorkbook book = Workbook.createWorkbook(new File(path), wb);
   WritableSheet sheet = book.getSheet(snum);
   Cell[] cell_domain = rs.getColumn(0);
   // System.out.println("cell_domain1.length：" + cell_domain.length);
   for (int i = 1; i < cell_domain.length; i++) {
    String sno = "";
    if (cell_domain[i].getContents().indexOf("-") > 0) {
     String[] slist = cell_domain[i].getContents().split("-");
     sno = slist[1];
    } else {
     sno = cell_domain[i].getContents();
    }
    if (map.containsKey(sno)) {
     jxl.write.Label label = new jxl.write.Label(cnum, i, map
       .get(sno));
     sheet.addCell(label);
    } else {
     jxl.write.Label label = new jxl.write.Label(cnum, i, "无设备");
     sheet.addCell(label);
    }
   }
   book.write();
   book.close();
  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }

 // 将oui——sno转换成含，分隔的字符串
 public static String getSnoString(List<String> list) {
  String sno = "";
  for (String num : list) {
   sno += "'" + num + "',";
  }
  return sno.substring(0, sno.length() - 1);
 }

 // 从查询出的结果文件中读取信息
 public static Map<String, String> getResult(String path) {
  Map<String, String> map = new HashMap<String, String>();
  try {
   System.out.println("start ==================================");
   File files = new File(path);
   if (!files.exists()) {
    files.createNewFile();
    System.exit(1);
   }
   InputStream in = new FileInputStream(files);
   BufferedReader contentSteam = new BufferedReader(
     new InputStreamReader(in));
   List<String> content = new ArrayList<String>();
   String tempLine = null;
   while ((tempLine = contentSteam.readLine()) != null) {
    content.add(tempLine);
   }
   for (int i = 0; i < content.size(); i++) {
    String lineString = content.get(i);
    String[] args = lineString.split(",");
    // System.out.println("size:" + args.length);
    if (args.length > 1) {
     if (args[0].trim().indexOf("-") > 0) {
      map.put(args[0].trim().substring(
        args[0].trim().indexOf("-") + 1,
        args[0].trim().length()), args[1].trim());
     } else {
      map.put(args[0].trim(), args[1].trim());
     }
     // System.out.println("key: " + args[0].trim() +" value:"+
     // args[1].trim());
    } else {
     if (args[0].trim().indexOf("-") > 0) {
      map.put(args[0].trim().substring(
        args[0].trim().indexOf("-") + 1,
        args[0].trim().length()), "");
     } else {
      map.put(args[0], "");
     }
     // System.out.println("key: " + args[0].trim() +" value:");
    }
   }
   contentSteam.close();
   in.close();
   System.out.println("end  ==================================");
   return map;
  } catch (Exception e) {
   e.printStackTrace();
   System.exit(1);
  }
  return map;
 }

 // 从数据库中读取信息放入map中
 public static Map<String, String> getResultDB(String sql) {
  Map<String, String> map = new HashMap<String, String>();
  Connection cn = null;
  Statement stm = null;
  ResultSet rs = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   cn = DriverManager.getConnection(
     "jdbc:oracle:thin:@192.168.10.204:1521:ora102", "sa", "sa");
   stm = cn.createStatement();
   rs = stm.executeQuery(sql);
   while (rs.next()) {
    String oui_sno = rs.getString("ou");
    String pppoe = rs.getString("po");
    if (oui_sno.indexOf("-") > 0) {
     map.put(oui_sno.substring(oui_sno.indexOf("-") + 1, oui_sno
       .length()), pppoe);
    } else {
     map.put(oui_sno, pppoe);
    }
   }
   closeDB(cn, stm, rs);
   // for(Iterator<String> iterator = map.keySet().iterator(); iterator
   // .hasNext();)
   // {
   // System.out.println("Key: " + iterator.next());
   // }
   return map;
  } catch (Exception e) {
   e.printStackTrace();
   closeDB(cn, stm, rs);
  }
  return map;

 }

 // 关闭数据库
 private static void closeDB(Connection cn, Statement stm, ResultSet rs) {
  try {
   if (null != rs) {
    rs.close();
   }
   if (null != stm) {
    stm.close();
   }
   if (null != cn) {
    cn.close();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 public static void main(String[] args) {
  String sourcefile = "D://2.xls";
  List<String> list = getOUI(sourcefile, 2);
  String s = getSnoString(list);
  // Map<String, String> map = getResultDB(sql);
  // Map<String, String> map = getResult("d:\\jieguo.txt");
  // writeSno(sourcefile, 2, 4, map);
 }

}