package com.sxit.wap.bookcard;
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() {
  }
  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  = 8;
  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:
              s = PageUtil.parseStringField(s, "電話", true, 20);
              element.put("userTel", s);
              break;
            case 3:
              s = PageUtil.parseStringField(s, "傳真", true, 20);
              element.put("userFax", s);
              break;
            case 4:
              s = PageUtil.parseStringField(s, "職位", true, 30);
              element.put("userJob", s);
              break;
            case 5:
              s = PageUtil.parseStringField(s, "公司", true, 100);
              element.put("userCom", s);
              break;
            case 6:
              s = PageUtil.parseStringField(s, "地址", true, 255);
              element.put("userAdress", s);
              break;
            case 7:
              s = PageUtil.parseStringField(s, "Email", true, 100);
              element.put("userEmail", s);
              break;
        }
      } catch (AppException e) {
        e.printStackTrace();
        throw new AppException(e.getMessage() + "，出錯位置在文件中的第" + (row+1) + "行，第" + (column+1) + "列");
      }
    }
    value.add(element);
  }
  return value;
}
}