用jxl导入Excel并验证的简单例子

package com.jxl.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.beanutils.BeanUtils;
import com.alibaba.common.lang.StringUtil;
import com.pamirs.point.dao.dateobject.CtBatchOrder;
import com.pamirs.point.dao.dateobject.CtExchangeOrder;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * ParseTool接口的实现，采用JXL来解析EXCEL
 * @since 2010-06-09
 */

public class JxlParseTool implements ParseTool{

 private static final long serialVersionUID = -1370751795864487625L;
 
 private static Map<Integer,String> rule;
 
 private static Map<Integer,String> connection;
 
 private static Map<Integer,String> checkRule;
 
 private String pattern = "第1行标题栏中的第{cellIndex}列应该为【{cellContent}】";
 
 private String sizeError = "上载文件条数必须小于等于{size}条!";
 
 private int size = 10;
 /**
  * 默认取第1个Sheet
  */
 private Integer sheetIndex = 0;
 
 static {
  
  connection = new HashMap<Integer, String>(){{
   put(1, "outerProxyNum");
   put(2, "gmtCreate");
   put(3, "siteNum");
   put(4, "siteName");
   //put(5, "网点电话");
   put(6, "receiveAddress");
   put(7, "productNum");
   put(8, "standardNum");
   put(9, "productName");
   //put(10, "宝贝地址");
   put(11, "buyerName");
   put(12, "buyerPhoneNum");
   put(13, "employeeName");
   put(14, "employeePhoneNum");
   put(15, "amount");
   put(16, "adPrice");
   //put(17, "目录价合计");
   put(18, "carriage");
   //put(19, "运费合计");
   put(20, "dealPrice");
   //put(21, "应付宝贝价合计");
   //put(22, "应付淘宝卖家合计");
   }};
  
  checkRule = new HashMap<Integer, String>(){{
   put(1, "\\w{1,50}");
   put(2, "^(?:(?!0000)[0-9]{4}([-]?)(?:(?:0?[1-9]|1[0-2])([-]?)(?:0?[1-9]|1[0-9]|2[0-8])|(?:0?[13-9]|1[0-2])([-]?)(?:29|30)|(?:0?[13578]|1[02])([-]?)31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)([-]?)0?2([-]?)29)$");
   put(3, "\\d{1,6}");
   put(4, ".{1,100}");
   put(6, ".{1,200}");
   put(7, "\\d{1,6}");
   put(8, "\\d{1,3}");
   put(9, ".{1,100}");
   put(11, ".{1,100}");
   put(12, "\\w{1,50}");
   put(13, ".{1,100}");
   put(14, "\\w{1,50}");
   put(15, "\\d{1,6}");
   put(16, "^[0-9]+(.[0-9]{2})?$");
   put(17, "^[0-9]+(.[0-9]{2})?$");
   put(18, "^[0-9]+(.[0-9]{2})?$");
   put(19, "^[0-9]+(.[0-9]{2})?$");
   put(20, "^[0-9]+(.[0-9]{2})?$");
   put(21, "^[0-9]+(.[0-9]{2})?$");
   put(22, "^[0-9]+(.[0-9]{2})?$");
   }};
  rule = new HashMap<Integer, String>(){{
   put(1, "代购订单编号");
   put(2, "下单时间");
   put(3, "网点编号");
   put(4, "网点名");
   put(5, "网点电话");
   put(6, "网点地址");
   put(7, "宝贝编码");
   put(8, "规格编码");
   put(9, "宝贝名称");
   put(10, "宝贝地址");
   put(11, "客户姓名");
   put(12, "客户手机");
   put(13, "店员姓名");
   put(14, "店员手机");
   put(15, "数量");
   put(16, "目录单价");
   put(17, "目录价合计");
   put(18, "运费单价");
   put(19, "运费合计");
   put(20, "应付宝贝单价");
   put(21, "应付宝贝价合计");
   put(22, "应付淘宝卖家合计");
   }};
 }

 public Map<String,Object> parseExcelByFile(File excel) {
  Map<String,Object> map = null;
  try {
   map = parseExcelByInputStream(new FileInputStream(excel));
  } catch (FileNotFoundException e) {
   if(map == null) {
    map = new HashMap<String,Object>();
   }
  }
  return map;
 }

 /**
  * 通过正则表达式检查数据格式
  * @param target
  * @param pattern
  * @return 检查结果；TRUE：格式匹配；FALSE：格式不匹配
  */
 public boolean check(String target,String regex) {
  Pattern p = Pattern.compile(regex);
  Matcher m = p.matcher(target);
  return m.matches();
 }
 
 public Map<String,Object> parseExcelByInputStream(InputStream input) {
  Map<String,Object> map = new HashMap<String,Object>();
  List<String> errors = new ArrayList<String>();
  map.put("errors", errors);
  Workbook work = null;
  try {
   boolean checkFlag = false;
   //获得workbook对象
   work = Workbook.getWorkbook(input);
   //获得第一个SHEET
   Sheet sheet = work.getSheet(sheetIndex);
   //获得标题栏，进行初步的格式检查
   Cell[] cells = sheet.getRow(0);
   if(cells.length != rule.values().size()) {
    errors.add("上载的文件标题栏格式不正确，请检查！");
    return map;
   }
   //对模板标题栏进行匹配，如出现错误，会记录下所有的错误并返回
   Set<Integer> keys = rule.keySet();
   for(Integer key:keys) {
    String value = StringUtil.defaultIfBlank(rule.get(key),"");
    String content = cells[key-1].getContents();
    //检查标题
    if(!value.equals(StringUtil.trim(content))) {
     String error = pattern.replace("{cellIndex}", key.toString());
     errors.add(error.replace("{cellContent}", value));
     checkFlag = true;
    }
   }
   //如果检查模板的标题栏出现错误就返回
   if(checkFlag) return map;
   //进行条数的检查  size <= 500
   int rows = sheet.getRows();
   if( rows > (size + 1) ) {
    errors.add(sizeError.replace("{size}", Integer.toString(size)));
    return map;
   }
   Map<CtBatchOrder,List<CtExchangeOrder>> objs = new HashMap<CtBatchOrder,List<CtExchangeOrder>>();
   CtBatchOrder ctb = null;
   CtExchangeOrder cte = null;
   for(int i = 1; i < rows; i++) {
    cells = sheet.getRow(i);
    ctb = new CtBatchOrder();
    cte = new CtExchangeOrder();
    for(Cell c:cells) {
     boolean checkDate = true;
     int key = c.getColumn() + 1;
     String regex = checkRule.get(key);
     String checkValue = c.getContents();
     if(c.getType() == CellType.DATE) {
      checkDate = false;
     }
     if(StringUtil.isBlank(regex)) continue;
     if(!check(checkValue,regex) && checkDate) {
      StringBuilder s = new StringBuilder("第");
      s.append(i).append("行第").append(c.getColumn() + 1)
      .append("列的数据格式[").append(checkValue).append("]不正确，请修改！");
      errors.add(s.toString());
     }
     if(errors.isEmpty()) {
      String p = connection.get(key);
      if(StringUtil.isNotBlank(p)) {
       Object v = checkValue;
       if(!checkDate) v = ((DateCell)c).getDate();
       try {
        BeanUtils.setProperty(ctb, p, v);
       } catch (IllegalAccessException e) {
        e.printStackTrace();
       } catch (InvocationTargetException e) {
        e.printStackTrace();
       }
       try {
        BeanUtils.setProperty(cte, p, v);
       } catch (IllegalAccessException e) {
        e.printStackTrace();
       } catch (InvocationTargetException e) {
        e.printStackTrace();
       }
      }
     }
     
     
    }
    List<CtExchangeOrder> ctes = null;
    if(objs.containsKey(ctb)) {
     ctes = objs.get(ctb);
    }else {
     ctes = new ArrayList<CtExchangeOrder>();
    }
    ctes.add(cte);
    objs.put(ctb, ctes);
   }
   map.put("list", objs);
  } catch (BiffException e) {
   errors.add("解析出现错误，请重新上载！");
  } catch (IOException e) {
   errors.add("解析出现错误，请重新上载！");
  } finally {
   if(work != null) {
    work.close();
   }
  }
  
  return map;
 }

 public Map<String,Object> parseExcelByString(String path) {
  Map<String,Object> map = null;
  try {
   map = parseExcelByInputStream(new FileInputStream(path));
  } catch (FileNotFoundException e) {
   if(map == null) {
    map = new HashMap<String,Object>();
   }
  }
  return map;
 }
}

 

 

此例子只做功能实现，没有优化代码