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;
}
}
此例子只做功能实现,没有优化代码