jxl excel批量导入

/*
* 通过excel实例化对象
*/
private Map<String,List> initOrderTransitBeans(HttpServletRequest request)throws Exception {
List<OrderTransitBean> beanList = new ArrayList<OrderTransitBean>();
List<String> errorMsgs = new ArrayList<String>();
try {
InputStream is = null;
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
if (isMultipart) {
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
Iterator items;
try {
items = upload.parseRequest(request).iterator();
while (items.hasNext()) {
FileItem item = (FileItem) items.next();
if (!item.isFormField()) {
is = item.getInputStream();
}
}
} catch (Exception e) {
throw new AppException(e.getMessage());
}
}
Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0);
int rows = rs.getRows();
int j = 0;
while (!(rs.getCell(0, j).getContents().trim()).equals("车牌号(必填)")) {
j++;
}
j++;
if (rows > j) {
if (rows - j > 50) {
throw new AppException("每次最大可以填写50行记录!");
}
for (int i = rows - 2; i >= j; i--) {
String errorMsg = "";
int n = 0;
String car_no = rs.getCell(n++, i).getContents().trim();// 车牌号
String brand = rs.getCell(n++, i).getContents().trim();// 车辆品牌
String load = rs.getCell(n++, i).getContents().trim();// 准载重量
String recipients = rs.getCell(n++, i).getContents().trim();// 联系人
String car_type = rs.getCell(n++, i).getContents().trim();// 车辆类型
String boxtype = rs.getCell(n++, i).getContents().trim();// 车厢类型
String spaces = rs.getCell(n++, i).getContents().trim();// 有效容积
String shiptype = rs.getCell(n++, i).getContents().trim();// 运输类型
String aspect = rs.getCell(n++, i).getContents().trim();// 车辆状态
String phone_re = rs.getCell(n++, i).getContents().trim();// 联系电话
String att63 = rs.getCell(n++, i).getContents().trim();// 车辆描述
String remark = rs.getCell(n++, i).getContents().trim();// 发证机关
if ("".equals(car_no)) {
errorMsg = errorMsg + "第" + (i - 9) + "行数据的“车牌号”不能为空;";
errorMsgs.add(errorMsg);
continue;
}
if ("".equals(brand)) {
errorMsg = errorMsg + "第" + (i - 9)+ "行数据的“车辆品牌”不能为空;";
errorMsgs.add(errorMsg);
continue;
}else{
List<Long> list = this.carsManageDealService.queryIdByName("tabf_car_brand", brand, getDomainNo(request)+"");
if(list.size()==0){
errorMsg = errorMsg + "第" + (i - 9)+ "行数据的“车辆品牌”填写错误;";
errorMsgs.add(errorMsg);
continue;
}else{
brand = list.get(0).toString();
}
}
if ("".equals(load)) {
errorMsg = errorMsg + "第" + (i - 9) + "行数据的“准载重量”不能为空;";
errorMsgs.add(errorMsg);
continue;
} else {
try{
Long d = Long.parseLong(load);
if(d<0){
errorMsg = errorMsg + "第" + (i - 9) + "行数据的“准载重量”必须大于0;";
errorMsgs.add(errorMsg);
continue;
}
}catch(Exception e){
errorMsg = errorMsg + "第" + (i - 9) + "行数据的“准载重量”数据类型不对;";
errorMsgs.add(errorMsg);
continue;
}
}
if ("".equals(recipients)) {
errorMsg = errorMsg + "第" + (i - 9) + "行数据的“联系人”不能为空;";
errorMsgs.add(errorMsg);
continue;
}

if ("".equals(car_type)) {
errorMsg = errorMsg + "第" + (i - 9) + "行数据的“车辆类型”不能为空;";
errorMsgs.add(errorMsg);
continue;
}else{
List<Long> list = this.carsManageDealService.queryIdByName("tabf_car_type", car_type, getDomainNo(request)+"");
if(list.size()==0){
errorMsg = errorMsg + "第" + (i - 9)+ "行数据的“车辆类型”填写错误;";
errorMsgs.add(errorMsg);
continue;
}else{
car_type = list.get(0).toString();
}
}

if (!"".equals(boxtype)) {
List<Long> list = this.carsManageDealService.queryIdByName("tabf_car_carriage", boxtype, getDomainNo(request)+"");
if(list.size()==0){
errorMsg = errorMsg + "第" + (i - 9)+ "行数据的“车箱类型”填写错误;";
errorMsgs.add(errorMsg);
continue;
}else{
boxtype = list.get(0).toString();
}
}

if (!"".equals(spaces)) {
try{
Long d = Long.parseLong(spaces);
if(d<0){
errorMsg = errorMsg + "第" + (i - 9) + "行数据的“有效容积”必须大于0;";
errorMsgs.add(errorMsg);
continue;
}
}catch(Exception e){
errorMsg = errorMsg + "第" + (i - 9) + "行数据的“有效容积”数据类型不对;";
errorMsgs.add(errorMsg);
continue;
}
}

if (!"".equals(shiptype)) {
List<Long> list = this.carsManageDealService.queryIdByName("tabf_car_shiptype", shiptype, getDomainNo(request)+"");
if(list.size()==0){
errorMsg = errorMsg + "第" + (i - 9)+ "行数据的“运输类型”填写错误;";
errorMsgs.add(errorMsg);
continue;
}else{
shiptype = list.get(0).toString();
}
}

if ("".equals(phone_re)) {
errorMsg = errorMsg + "第" + (i - 9)+ "行数据的“联系电话”不能为空;";
errorMsgs.add(errorMsg);
continue;
}

OrderTransitBean bean = new OrderTransitBean();
String city_st_name = ParamUtil.getParameter(request,"city_st_name");
String province_re_n = ParamUtil.getParameter(request,"province_re_n");
String addr_re = ParamUtil.getParameter(request,"addr_re");
Long province_st = ParamUtil.getParameterLong(request,"province_st",0);
Long county_st = ParamUtil.getParameterLong(request,"county_st",0);
String cancel = ParamUtil.getParameter(request,"cancel");
String cancel_ip = ParamUtil.getParameter(request,"cancel_ip");
// Timestamp validity =DateUtil.convertGMTTimestamp(ParamUtil.getParameter(request,"validity",null)) ;
Timestamp validity =DateUtil.convertGMTTimestamp("9999-12-31");//注意修改????????????
Long trade_mode = ParamUtil.getParameterLong(request,"trade_mode",0);
Timestamp date2 = DateUtil.convertGMTTimestamp(ParamUtil.getParameter(request,"date2",null)) ;
Long penalty = ParamUtil.getParameterLong(request,"penalty",0);
String city_re_name = ParamUtil.getParameter(request,"city_re_name");
Timestamp date1 = DateUtil.convertGMTTimestamp(ParamUtil.getParameter(request,"date1",null)) ;
Long validates = ParamUtil.getParameterLong(request,"validates",0);
Long ctr = ParamUtil.getParameterLong(request,"ctr",0);
Long val_unit = ParamUtil.getParameterLong(request,"val_unit",0);
Timestamp canceltime = DateUtil.convertGMTTimestamp(ParamUtil.getParameter(request,"canceltime",null)) ;
String county_lo_n = ParamUtil.getParameter(request,"county_lo_n");
Long order_type = ParamUtil.getParameterLong(request,"order_type",0);
Long status = ParamUtil.getParameterLong(request,"status",1);
Long mark = ParamUtil.getParameterLong(request,"mark",0);
Timestamp auth_time = DateUtil.convertGMTTimestamp(ParamUtil.getParameter(request,"auth_time",null)) ;
Long credit_frz = ParamUtil.getParameterLong(request,"credit_frz",0);
String oper_no =getEmpNo(request)+"";
Long city_st = ParamUtil.getParameterLong(request,"city_st",0);
Long client_id = Long.parseLong(getCustNo(request)+"");
String qq_re = ParamUtil.getParameter(request,"qq_re");
Long province_re = ParamUtil.getParameterLong(request,"province_re",0);
String addr_st = ParamUtil.getParameter(request,"addr_st");
Long arrived_type = ParamUtil.getParameterLong(request,"arrived_type",0);
Long cancel_flat = ParamUtil.getParameterLong(request,"cancel_flat",0);
Long num2 = ParamUtil.getParameterLong(request,"num2",0);
String province_st_n = ParamUtil.getParameter(request,"province_st_n");
Double num3 = ParamUtil.getParameterDouble(request,"num3",0);
String city_locus_n = ParamUtil.getParameter(request,"city_locus_n");
Long num1 = ParamUtil.getParameterLong(request,"num1",0);
String cancel_op = ParamUtil.getParameter(request,"cancel_op");
String att64 = ParamUtil.getParameter(request,"att64");
Long trade_type = ParamUtil.getParameterLong(request,"trade_type",0);
Long deposit_other = ParamUtil.getParameterLong(request,"deposit_other",0);
String att62 = ParamUtil.getParameter(request,"att62");
String order_ip = getLoginIP(request);
String att61 = ParamUtil.getParameter(request,"att61");
Long file_code = ParamUtil.getParameterLong(request,"file_code",0);
if(bean.getFileCode()!=null&&bean.getFileCode().longValue()>0){
file_code=bean.getFileCode();
}
Long carrybail = ParamUtil.getParameterLong(request,"carrybail",0);
Long logis_resource = ParamUtil.getParameterLong(request,"logis_resource",0);
String auth_op = ParamUtil.getParameter(request,"auth_op");
String carry_name = ParamUtil.getParameter(request,"carry_name");
Long logis_mode = ParamUtil.getParameterLong(request,"logis_mode",0);
Long unit_time = ParamUtil.getParameterLong(request,"unit_time",0);
Long recommend = ParamUtil.getParameterLong(request,"recommend",0);
Long consignbail = ParamUtil.getParameterLong(request,"consignbail",0);
String author = ParamUtil.getParameter(request,"author");
String county_st_n = ParamUtil.getParameter(request,"county_st_n");
Long poundage = ParamUtil.getParameterLong(request,"poundage",0);
Long county_re = ParamUtil.getParameterLong(request,"county_re",0);
String client_name =getLoginName(request);
String county_re_n = ParamUtil.getParameter(request,"county_re_n");
Long deposit_value = ParamUtil.getParameterLong(request,"deposit_value",0);
Long domain_no = Long.parseLong(getDomainNo(request)+"");
Double transit_time = ParamUtil.getParameterDouble(request,"transit_time",0);
Long order_id = ParamUtil.getParameterLong(request,"order_id",0);
Long cgn_id = ParamUtil.getParameterLong(request,"cgn_id",0);
Long cust_id = getCustNo(request);
if(bean.getCustId()!=null&&bean.getCustId().longValue()!=0&&cust_id.longValue()!=bean.getCustId().longValue()){
throw new AppException("登录信息不一致,请关闭重试！");
}
Long city_re = ParamUtil.getParameterLong(request,"city_re",0);
Long poundage_f = ParamUtil.getParameterLong(request,"poundage_f",0);
Timestamp order_time = DateUtil.convertGMTTimestamp(ParamUtil.getParameter(request,"order_time",null)) ;
String province_lo_n = ParamUtil.getParameter(request,"province_lo_n");
Long carry_id = ParamUtil.getParameterLong(request,"carry_id",0);
Long other_value = ParamUtil.getParameterLong(request,"other_value",0);
String cgn_name = ParamUtil.getParameter(request,"cgn_name");
String order_no = ParamUtil.getParameter(request,"order_no");
String unit_name = ParamUtil.getParameter(request,"unit_name");
Long city_locus = ParamUtil.getParameterLong(request,"city_locus",0);
String addr_locus = ParamUtil.getParameter(request,"addr_locus");
String oper_name =getLoginName(request);
Long county_locus = ParamUtil.getParameterLong(request,"county_locus",0);
Long province_locus = ParamUtil.getParameterLong(request,"province_locus",0);
Long deposit_id = ParamUtil.getParameterLong(request,"deposit_id",0);
String att65 = ParamUtil.getParameter(request,"att65");
String attachid=ParamUtil.getParameter(request, "attachid");

bean.setGSCAttachid(attachid);
bean.setCityStName(city_st_name);
bean.setProvinceReN(province_re_n);
bean.setAddrRe(addr_re);
bean.setProvinceSt(province_st);
bean.setRemark(remark);
bean.setCountySt(county_st);
bean.setCancel(cancel);
bean.setCancelIp(cancel_ip);
bean.setValidity(validity);
bean.setTradeMode(trade_mode);
bean.setDate2(date2);
bean.setPenalty(penalty);
bean.setCityReName(city_re_name);
bean.setDate1(date1);
bean.setValidates(validates);
bean.setCtr(ctr);
bean.setValUnit(val_unit);
bean.setCanceltime(canceltime);
bean.setCountyLoN(county_lo_n);
if(!"".equals(spaces)){
bean.setSpaces(Long.parseLong(spaces));
}else{
bean.setSpaces(0l);
}
bean.setOrderType(order_type);
bean.setStatus(status);
bean.setMark(mark);
bean.setAuthTime(auth_time);
if(!"".equals(shiptype)){
bean.setShiptype(Long.parseLong(shiptype));//注意修改-->这里写死了
}else{
bean.setShiptype(0l);
}
bean.setCreditFrz(credit_frz);
bean.setOperNo(oper_no);
bean.setCitySt(city_st);
bean.setClientId(client_id);
bean.setAspect(aspect);
bean.setQqRe(qq_re);
bean.setProvinceRe(province_re);
bean.setAddrSt(addr_st);
bean.setArrivedType(arrived_type);
bean.setCancelFlat(cancel_flat);
bean.setNum2(num2);
bean.setProvinceStN(province_st_n);
bean.setLoad(Long.parseLong(load));
bean.setNum3(num3);
bean.setCityLocusN(city_locus_n);
bean.setNum1(num1);
bean.setCancelOp(cancel_op);
bean.setAtt64(att64);
bean.setTradeType(trade_type);
bean.setAtt63(att63);
bean.setDepositOther(deposit_other);
bean.setAtt62(att62);
bean.setOrderIp(order_ip);
bean.setAtt61(att61);
bean.setFileCode(file_code);
bean.setCarrybail(carrybail);
bean.setLogisResource(logis_resource);
bean.setPhoneRe(phone_re);
bean.setAuthOp(auth_op);
bean.setCarryName(carry_name);
bean.setLogisMode(logis_mode);
bean.setUnitTime(unit_time);
bean.setRecommend(recommend);
bean.setConsignbail(consignbail);
bean.setCarType(Long.parseLong(car_type));//注意修改-->这里写死了
bean.setAuthor(author);
bean.setCountyStN(county_st_n);
if(!"".equals(boxtype)){
bean.setBoxtype(Long.parseLong(boxtype));//注意修改-->这里写死了
}else{
bean.setBoxtype(0l);
}
bean.setPoundage(poundage);
bean.setCountyRe(county_re);
bean.setClientName(client_name);
bean.setCountyReN(county_re_n);
bean.setDepositValue(deposit_value);
bean.setDomainNo(domain_no);
bean.setTransitTime(transit_time);
bean.setOrderId(order_id);
bean.setCgnId(cgn_id);
bean.setCustId(cust_id);
bean.setCityRe(city_re);
bean.setRecipients(recipients);
bean.setPoundageF(poundage_f);
bean.setOrderTime(order_time);
bean.setProvinceLoN(province_lo_n);
bean.setCarryId(carry_id);
bean.setOtherValue(other_value);
bean.setCarNo(car_no);
bean.setCgnName(cgn_name);
bean.setOrderNo(order_no);
bean.setUnitName(unit_name);
bean.setCityLocus(city_locus);
bean.setAddrLocus(addr_locus);
bean.setOperName(oper_name);
bean.setCountyLocus(county_locus);
bean.setProvinceLocus(province_locus);
bean.setBrand(Long.parseLong(brand));
bean.setDepositId(deposit_id);
bean.setAtt65(att65);

beanList.add(bean);
}
}
Map<String,List> map = new HashMap<String,List>();
map.put("list", beanList);
map.put("msg", errorMsgs);
return map;
} catch (AppException e) {
e.printStackTrace();
throw e;
} catch (Exception e) {
e.printStackTrace();
throw new AppException("批量导入出错!");
}
}


excel模板见附件