JavaWeb项目excel文件导入

来源:转载

项目期间有Excel文件导入 今天来整理一下


1. 首先在web页面添加一个button按钮 “导入Excel”
注意: input 的类型必须是file才可以


2. html页面:

$(function() { $("#inputExcel").change(function(){ initProvince(); // 获得文件 var file = $("#inputExcel").val(); // 截取上传的文件后缀 var extension = file.substring(file.lastIndexOf("."),file.length); // 验证上传文件后缀是否合法 var strRegex = "(.xls|.xlsx)$"; var re = new RegExp(strRegex); var flag; // 如果不合法,在页面上显示出相应的提示消息 if (!re.test(extension.toLowerCase())) { layer.msg('文件名不合法,请上传Excel文件!', { icon : 2 }); return false; } if(extension == ".xls"){ flag = 0; } if(extension == ".xlsx"){ flag = 1; } var parms = new Object(); // Excel版本flag parms["flag"] = flag; parms["provinceNames"] = JSON.stringify(provinceNames); //parms["areaNames"] = JSON.stringify(areaNames); var options = { //这个url是后台的接口网址 url: ctx+'/organ/inputExcel.do?t=' + getNowDate(), type: "post", data: parms, dataType: "json", success: function (result) { var json = eval(result); if (json[Cons.RESULT] == Cons.SUCCESS) { layer.msg('导入成功!', { icon : 1 }); }else if(json[Cons.RESULT] == null){ layer.msg(json[Cons.RESULT_MSG], { icon : 2 }); }else if(json[Cons.RESULT] == "codeDuplication"){ layer.msg(json[Cons.RESULT_MSG], { icon : 2 }); }else if(json[Cons.RESULT] == "telError"){ layer.msg(json[Cons.RESULT_MSG], { icon : 2 }); }else if(json[Cons.RESULT] == "provinceError"){ layer.msg(json[Cons.RESULT_MSG], { icon : 2 }); }else { layer.msg('导入失败!', { icon : 2 }); } setTimeout('window.location.reload()',2000); } }; $("#form1").ajaxSubmit(options); }); });



3. controller: 用来读取excel文件将数据保存到数据中

@SuppressWarnings({ "deprecation", "unchecked" }) @RequestMapping(value = "/inputExcel", method=RequestMethod.POST) public void upload(@RequestParam(value="fileData", required=false) MultipartFile file , @RequestParam("flag")String flag, HttpServletRequest request, HttpServletResponse response)throws Exception { Map<String, Object> resMap = new HashMap<String, Object>(); // 文件转换成输入流 FileInputStream fis=(FileInputStream) file.getInputStream(); String provinceNames = request.getParameter("provinceNames"); JSONArray provinceNamesJson=JSONArray.fromObject(provinceNames); // 获取系统当前服务id ServiceAdapter adapter = ServiceAdapter.getInstance(); String serviceID = adapter.getServiceId(); DecimalFormat df = new DecimalFormat("#"); if("0".equals(flag)){ // 创建xls工作薄 HSSFWorkbook workbook = null; try { workbook = new HSSFWorkbook(fis); } catch (IOException e1) { e1.printStackTrace(); } // 得到工作表 HSSFSheet sheet = workbook.getSheetAt(0); // 对应Excel的行 HSSFRow row = null; // 得到Excel的总行数 int totalRows = sheet.getLastRowNum(); for (int i = 1; i <= totalRows; i++) { row = sheet.getRow(i); //这个实体是你需要导入的文件的实体 SysOrgan organ =new SysOrgan(); // 有序号的行 if(row.getCell(0)!=null){ // 机构名称 if(row.getCell(1)!=null){ String name = ""; if(row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ name= df.format(row.getCell(1).getNumericCellValue()); }else{ name = row.getCell(1).toString(); } if("".equals(name)){ resMap.put(Constants.RESULT, "null"); resMap.put(Constants.RESULT_MSG, "第"+i+"行机构名称不能为空!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; }else{ organ.setName(name); } }else{ resMap.put(Constants.RESULT, "null"); resMap.put(Constants.RESULT_MSG, "第"+i+"行机构名称不能为空!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; } // 简称 String simpleName = ""; if(row.getCell(2).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ simpleName= df.format(row.getCell(2).getNumericCellValue()); }else{ simpleName = row.getCell(2).toString(); } organ.setSimpleName(simpleName); // 编码 if(row.getCell(3)!=null){ String code = ""; if(row.getCell(3).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ code= df.format(row.getCell(3).getNumericCellValue()); }else{ code = row.getCell(3).toString(); } if("".equals(code)){ resMap.put(Constants.RESULT, "null"); resMap.put(Constants.RESULT_MSG, "第"+i+"行编码不能为空!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; } int codeNum = organService.selectOragnByCode(code,""); // 重复code if(codeNum > 0){ resMap.put(Constants.RESULT, "codeDuplication"); resMap.put(Constants.RESULT_MSG, "第"+i+"行编码已经存在!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; }else{ organ.setCode(code); } }else{ resMap.put(Constants.RESULT, "null"); resMap.put(Constants.RESULT_MSG, "第"+i+"行编码不能为空!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; } // 地址 String adress = ""; if(row.getCell(4).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ adress= df.format(row.getCell(4).getNumericCellValue()); }else{ adress = row.getCell(4).toString(); } organ.setAdress(adress); // 邮编 String zipCode = ""; if(row.getCell(5).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ zipCode= df.format(row.getCell(5).getNumericCellValue()); }else{ zipCode = row.getCell(5).toString(); } organ.setZipCode(zipCode); // 负责人 String master = ""; if(row.getCell(6).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ master= df.format(row.getCell(6).getNumericCellValue()); }else{ master = row.getCell(6).toString(); } organ.setMaster(master); // 电话 String tel = ""; if(row.getCell(7).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ tel= df.format(row.getCell(7).getNumericCellValue()); }else{ tel = row.getCell(7).toString(); } String regExp = "^((13[0-9])|(15[^4])|(18[0,2,3,5-9])|(17[0-8])|(147))//d{8}$"; Pattern p = Pattern.compile(regExp); Matcher m = p.matcher(tel); if(m.matches()){ organ.setTel(tel); }else{ resMap.put(Constants.RESULT, "telError"); resMap.put(Constants.RESULT_MSG, "第"+i+"行电话格式不正确!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; } // 传真 String fax = ""; if(row.getCell(8).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ fax= df.format(row.getCell(8).getNumericCellValue()); }else{ fax = row.getCell(8).toString(); } organ.setFax(fax); // 邮箱 String email = ""; if(row.getCell(9).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ email= df.format(row.getCell(9).getNumericCellValue()); }else{ email = row.getCell(9).toString(); } organ.setEmail(email); // 预约flag可预约 organ.setOrderFlag(1); // 保存数据 organ = organService.save(organ); } } }else{ // 创建xlsx工作薄 XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(fis); } catch (IOException e1) { e1.printStackTrace(); } // 得到工作表 XSSFSheet sheet = workbook.getSheetAt(0); // 对应Excel的行 XSSFRow row = null; // 得到Excel的总行数 int totalRows = sheet.getLastRowNum(); for (int i = 1; i <= totalRows; i++) { row = sheet.getRow(i); SysOrgan organ =new SysOrgan(); // 有序号的行 if(row.getCell(0)!=null){ // 机构名称 if(row.getCell(1)!=null){ String name = ""; if(row.getCell(1).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ name= df.format(row.getCell(1).getNumericCellValue()); }else{ name = row.getCell(1).toString(); } if("".equals(name)){ resMap.put(Constants.RESULT, "null"); resMap.put(Constants.RESULT_MSG, "第"+i+"行机构名称不能为空!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; }else{ organ.setName(name); } }else{ resMap.put(Constants.RESULT, "null"); resMap.put(Constants.RESULT_MSG, "第"+i+"行机构名称不能为空!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; } // 简称 String simpleName = ""; if(row.getCell(2).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ simpleName= df.format(row.getCell(2).getNumericCellValue()); }else{ simpleName = row.getCell(2).toString(); } organ.setSimpleName(simpleName); // 编码 if(row.getCell(3)!=null){ String code = ""; if(row.getCell(3).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ code= df.format(row.getCell(3).getNumericCellValue()); }else{ code = row.getCell(3).toString(); } if("".equals(code)){ resMap.put(Constants.RESULT, "null"); resMap.put(Constants.RESULT_MSG, "第"+i+"行编码不能为空!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; } int codeNum = organService.selectOragnByCode(code,""); // 重复code if(codeNum > 0){ resMap.put(Constants.RESULT, "codeDuplication"); resMap.put(Constants.RESULT_MSG, "第"+i+"行编码已经存在!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; }else{ organ.setCode(code); } }else{ resMap.put(Constants.RESULT, "null"); resMap.put(Constants.RESULT_MSG, "第"+i+"行编码不能为空!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; } // 地址 String adress = ""; if(row.getCell(4).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ adress= df.format(row.getCell(4).getNumericCellValue()); }else{ adress = row.getCell(4).toString(); } organ.setAdress(adress); // 邮编 String zipCode = ""; if(row.getCell(5).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ zipCode= df.format(row.getCell(5).getNumericCellValue()); }else{ zipCode = row.getCell(5).toString(); } organ.setZipCode(zipCode); // 负责人 String master = ""; if(row.getCell(6).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ master= df.format(row.getCell(6).getNumericCellValue()); }else{ master = row.getCell(6).toString(); } organ.setMaster(master); // 电话 String tel=""; if(row.getCell(7).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ tel= df.format(row.getCell(7).getNumericCellValue()); }else{ tel = row.getCell(7).toString(); } String regExp = "^((13[0-9])|(15[^4])|(18[0,2,3,5-9])|(17[0-8])|(147))//d{8}$"; Pattern p = Pattern.compile(regExp); Matcher m = p.matcher(tel); if(m.matches()){ organ.setTel(tel); }else{ resMap.put(Constants.RESULT, "telError"); resMap.put(Constants.RESULT_MSG, "第"+i+"行电话格式不正确!"); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); return; } // 传真 String fax = ""; if(row.getCell(8).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ fax= df.format(row.getCell(8).getNumericCellValue()); }else{ fax = row.getCell(8).toString(); } organ.setFax(fax); // 邮箱 String email = ""; if(row.getCell(9).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ email= df.format(row.getCell(9).getNumericCellValue()); }else{ email = row.getCell(9).toString(); } organ.setEmail(email); // 预约flag可预约 organ.setOrderFlag(1); // 保存数据 organ = organService.save(organ); } } } // 返回结果 resMap.put(Constants.RESULT, Constants.SUCCESS); ServletCom com = new ServletCom(request, response); com.renderText(JSONObject.fromObject(resMap).toString()); }




4. mapper.xml文件进行写入保存数据的sql

实体类中的属性:

sql :

☺☺☺这样一个excel的导入就完成了, 如果有不足之处,欢迎指教☺☺☺

分享给朋友:
您可能感兴趣的文章:
随机阅读: