使用jxl导入excel文件
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
action中:
/** * 导入计划 * @author liubf * @version 1.0 * @createTime 2015-6-16 */ @RequestMapping("insertWBSPlan") public ResBean insertWBSPlan (HttpServletRequest request, HttpServletResponse response, ResBean handler) throws Exception { handler = new ResBean(SessionTools.getForBaseLoginDomainNo(request)); Map<String, Object> parameter = new HashMap<String, Object>(); try { Integer domainNo = SessionTools.getForBaseLoginDomainNo(request); if (domainNo == 0) { throw new AppException("获取域名失败！"); } parameter.put("domainNo", domainNo); InputStream is = readUpload(request); if (is == null || "".equals(is)) {// 空文件 handler.put("success", false); handler.put("msg", "上传文件为空！"); } else{// 非空 draftWBSService.readExcel(is,parameter); handler.put("success", true); handler.put("msg", "导入成功"); } } catch (Exception e) { e.printStackTrace(); log.error(e.getMessage(), e); handler.put("msg", e.getMessage()); handler.put("error", true); } return handler; } /** * 读文件转为流 * @param request * @return InputStream * liubf * @throws Exception */ private InputStream readUpload(HttpServletRequest request) throws Exception { InputStream is = null;// 文件输入流 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; CommonsMultipartFile orginalFile = (CommonsMultipartFile) multipartRequest.getFile("uploadFile");// 表单中对应的文件上传控件的名称； if (orginalFile != null && !orginalFile.isEmpty()) {// 如果有附件 is = orginalFile.getInputStream(); } return is; }
Service中:
/** * 导入计划 */ @SuppressWarnings("rawtypes") public void readExcel(InputStream is, Map<String, Object> parameter) throws Exception { HashMap resultMap = new HashMap(); Workbook rwb = Workbook.getWorkbook(is); Sheet sheet = rwb.getSheets()[0];// 读取第一个sheet表格 // List<TabAgHarvestForecast> insertlisth = new ArrayList<TabAgHarvestForecast>(); // 插入list //y用于存储从excel中读取的数据 List<Map<String, String>> mapList = new ArrayList<Map<String, String>>(); //获取已经存在的会员名称和编号 List<HashMap> infoList = infoBeanDao.getMySqlMapClient().queryForList("com.forlink.bankfinancing.memberCredit.query_membercredit_list",new HashMap()); //获取会员表中所有的会员名称,编号,id List<HashMap> tradeList = infoBeanDao.getMySqlMapClient().queryForList("com.forlink.bankfinancing.memberCredit.query_tradeMember_list"); //新建 一个map存已经存在的会员名称和编号 HashMap existMap = new HashMap(); if(infoList!=null&&infoList.size()>0){ for (HashMap map : infoList) { existMap.put(map.get("cust_no").toString(),map.get("cust_name").toString()); } } for (int i = 2; i < sheet.getRows(); i++) { Cell[] cel = sheet.getRow(i); String cust_no = ""; String cust_name = ""; String total_fund = ""; String str6 = ""; String cust_id = ""; for (int j = 0; j < cel.length; j++) { if (j == 0) cust_no = cel[j].getContents().trim(); if (j == 1) cust_name = cel[j].getContents().trim(); if (j == 2) total_fund = cel[j].getContents().trim(); if (j == 3) str6 = cel[j].getContents().trim(); } if ((cust_no == null || "".equals(cust_no)) && (cust_name == null || "".equals(cust_name)) && (total_fund == null || "".equals(total_fund)) && (str6 == null || "".equals(str6)) ) { } else { int flag = i+1; int flag_exist = 0; if(Double.parseDouble(total_fund)<0){ throw new AppException("第"+flag+"行总授信额度需大于等于0!"); } //如果已经存在这个会员编号,但输入的编号和名称不对应, 则报错 if (existMap.containsKey(cust_no)) { flag_exist = 1; if(!cust_name.equals(existMap.get(cust_no))) throw new AppException("第"+flag+"行会员编码和会员名称不对应!"); }else { //如果不存在这个会员编号.则从会员表中查的所有会员与之比较 for(HashMap map : tradeList){ if(cust_no.equals((String)map.get("cust_no_trade"))){ flag_exist = 1; //如果上传的cust_name和数据库中存的不一致则报错 if(!cust_name.equalsIgnoreCase((String)map.get("cust_name_trade"))){ throw new AppException("第"+flag+"行会员编码和会员名称不对应!"); }else{ cust_name = (String)map.get("cust_name_trade"); cust_id = (String)map.get("cust_id_trade"); } } } } if(0 == flag_exist){ throw new AppException("第"+flag+"行会员不存在!"); } for(Map m : mapList){ String rowNum = (String)m.get("rowNum"); if(cust_no.equals(m.get("cust_no"))){ throw new AppException("第"+flag+"行会员与第"+rowNum+"行会员重复!"); } } Map<String, String> map_temp = new HashMap<String, String>(); map_temp.put("cust_no", cust_no); map_temp.put("cust_name", cust_name); map_temp.put("cust_id", cust_id); map_temp.put("total_fund", total_fund); map_temp.put("str6", str6); map_temp.put("rowNum",(""+flag)); mapList.add(map_temp); } } for (int j = mapList.size() - 1; j >= 0; j--) { Map<String, String> map = mapList.get(j); FacilityInfoBean infoBean = new FacilityInfoBean(); infoBean.setCustName(map.get("cust_name")); infoBean.setCustNo(map.get("cust_no")); infoBean.setStr6(map.get("str6")); infoBean.setTotalFund(new Double(Double.parseDouble((map.get("total_fund")))*100).longValue()); if(StringUtils.isEmpty(map.get("cust_id"))){//为空说明已经有此条数据,update即可 FacilityInfoBeanExample infoBeanExample = new FacilityInfoBeanExample(); Criteria criteria = infoBeanExample.createCriteria(); criteria.andCustNoEqualTo(infoBean.getCustNo()); List<FacilityInfoBean> selectBeanList = infoBeanDao.selectByExample(infoBeanExample); if(selectBeanList.size()>1||selectBeanList.size()<1){ throw new AppException("编号为"+infoBean.getCustNo()+"的数据不存在或存在重复数据!"); } FacilityInfoBean selectInfoBean = selectBeanList.get(0); selectInfoBean.setTotalFund(infoBean.getTotalFund()); selectInfoBean.setStr6(infoBean.getStr6()); selectInfoBean.setTotalAvb(infoBean.getTotalFund()-selectInfoBean.getTotalFrz()); infoBeanDao.updateByPrimaryKeySelective(selectInfoBean); }else{ infoBean.setCustId(Long.parseLong(map.get("cust_id"))); infoBean.setOtherId(Long.parseLong(map.get("cust_id"))); infoBean.setTotalAvb(infoBean.getTotalFund()); //必填参数 //币种 infoBean.setCurrId(2L); //授信类型 infoBean.setFacilityType(3L); infoBean.setTotalFrz(0L); infoBean.setTotalUsed(0L); infoBean.setTotalOffset(0L); infoBean.setNeedOffset(0L); infoBean.setFrzOffset(0L); infoBean.setTotalPassed(0L); infoBean.setN1(0L); infoBean.setN2(0L); infoBean.setN3(0L); infoBean.setN4(0L); infoBean.setN5(0L); infoBean.setN6(0L); //获取域编号 Integer domainNo = (Integer)parameter.get("domainNo"); infoBean.setDomainNo(domainNo.longValue()); //建立时间 infoBean.setOperDate(new Timestamp(new Date().getTime())); Long accept_id = getNextSeq("seq_facility_info"); infoBean.setAcceptId(accept_id); //生成accept_no String accept_id_str = ""+accept_id; SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd"); format.setTimeZone(TimeZone.getTimeZone("GMT+8")); String date = format.format(new Date()); String accept_no = "SX"+date+ accept_id_str.substring(accept_id_str.length()-2, accept_id_str.length()); infoBean.setAcceptNo(accept_no); infoBeanDao.insertSelective(infoBean); } } }
