excelutil.java

WAP PUSH后台源码,WAP PUSH后台源码

package com.sxit.wap.department;
import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.*;
import com.sxit.wap.common.*;
import com.sxit.wap.exception.*;

public class ExcelUtil {
  HSSFWorkbook wb = null;
  HSSFSheet sheet = null;
  public ExcelUtil() throws Exception {

  }
  public Collection read(String fileName) throws AppException {
    Collection value = new ArrayList();
    try {
      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));
      wb = new HSSFWorkbook(fs);
      sheet = wb.getSheetAt(0);
    } catch (IOException e) {
      throw new AppException("读取EXCEL文件失改");
    }
    int rowCount = sheet.getLastRowNum();
    int columnCount  = 10;
    HSSFRow hssfrow = null;
    HSSFCell cell = null;
    for (int row=1; row<=rowCount; row++) {
      hssfrow = sheet.getRow(row);
      if (hssfrow == null) continue;
      Hashtable element = new Hashtable();
      for (short column=0; column<columnCount; column++) {
        cell = hssfrow.getCell(column);
        String s = "";
        if (cell != null) {
          if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            double d = cell.getNumericCellValue();
            long l = (long)d;
            s = "" + l;
          } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            s = cell.getStringCellValue();
          }
        }

        try {
          switch (column) {
            case 0:
              if (StringUtil.isEmpty(s))
                throw new AppException("手机号不能为空");
              s = PageUtil.parseStringField(s, "手机号码", false, 11);
              element.put("userMdn", s);
              break;
            case 1:
              if (StringUtil.isEmpty(s))
                throw new AppException("用户姓名不能为空");
              s = PageUtil.parseStringField(s, "用户姓名", false, 20);
              element.put("userName", s);
              break;
            case 2:
              if (! ("男".equals(s) || "女".equals(s) || StringUtil.isEmpty(s)))
                throw new AppException("性别填写错误");
              s = "男".equals(s)?"1":"女".equals(s)?"0":"100";
              element.put("sex", s);
              break;
            case 3:
              s = PageUtil.parseStringField(s, "电话", true, 20);
              element.put("tel", s);
              break;
            case 4:
              s = PageUtil.parseStringField(s, "传真", true, 20);
              element.put("fax", s);
              break;
            case 5:
              s = PageUtil.parseStringField(s, "职位", true, 100);
              element.put("job", s);
              break;
            case 6:
              s = PageUtil.parseStringField(s, "地址", true, 255);
              element.put("address", s);
              break;
            case 7:
              s = PageUtil.parseStringField(s, "邮编", true, 6);
              element.put("post", s);
              break;
            case 8:
              s = PageUtil.parseStringField(s, "电子邮件", true, 50);
              element.put("email", s);
            case 9:
              s = PageUtil.parseStringField(s, "备注", true, 255);
              element.put("remark", s);
              break;
          }
        } catch (AppException e) {
          throw new AppException(e.getMessage() + "，出错位置在文件中的第" + (row+1) + "行，第" + (column+1) + "列");
        }
      }
      value.add(element);
    }
    return value;
  }
  public static void main(String[] args) throws Exception {
    ExcelUtil excelUtil1 = new ExcelUtil();
    Collection coll = excelUtil1.read("D:\\s.xls");
    Iterator it = coll.iterator();
    System.out.println(coll.size());
    while (it.hasNext()) {
      Hashtable element = (Hashtable)it.next();
      String userMdn = (String)element.get("userMdn");
      String userName = (String)element.get("userName");
      String sex = (String)element.get("sex");
      String tel = (String)element.get("tel");
      String fax = (String)element.get("fax");
      String job = (String)element.get("job");
      String address = (String)element.get("address");
      String post = (String)element.get("post");
      String remrak = (String)element.get("remrak");
      System.out.print(userMdn + "\t");
      System.out.print(userName + "\t");
      System.out.print(sex + "\t");
      System.out.print(tel + "\t");
      System.out.print(fax + "\t");
      System.out.print(job + "\t");
      System.out.print(address + "\t");
      System.out.print(post + "\t");
      System.out.println();
    }
  }
}