使用插件导入Excel表到数据库

<script type="text/javascript" src="${ctxStatic}/admin/js/Huploadify/jquery.Huploadify.js"></script>

在使用插件之前首先到入js库

$('#excle').Huploadify({        'uploader': '${ctx}/meeting/import',        'fileObjName': 'fileToUpload',        'fileTypeExts': '*.xlsx; *.xsl;',        'auto': true,        'multi': false,        'fileObjName': 'file',        'fileSizeLimit': 20480 * 60,        'showUploadedPercent': true,        'showUploadedSize': true,        'onUploadSuccess': function (file, data) {//            var josobj = JSON.parse(data);//            var url=josobj.src;//            var fileName=josobj.name;//            $("#picture_address").val(url);            if(data=='2'){                layer.msg("导入会议签到表失败",{icon:2});            }            if(data ==""){                layer.msg("有非参会人员签到，请确认信息是否正确!导入失败",{icon:2});            }            if(data =='1'){                layer.msg("导入会议签到表成功",{icon:1});            }            //window.location.reload();            //$('#file_picShow').html(' <span class="picSpan"><img class="picture" height="80px" width="80px" vedioSrc="' + josobj.src + '"  src="${ctxStatic}/Huploadify/fujian.png"></img>' + josobj.name + '</span>');        },        'onUploadError': function (file, errorCode, errorMsg, errorString) {            alert('文件' + file.name + ' 上传错误: ' + errorMsg);        },    });
将一个div绑定上面的事件，在后台将导入的Excel数据存入磁盘
/** * 导入Excel数据 */@RequiresPermissions(value = {"meeting:meeting:import", "meetingParticipants:meetingParticipants:export"}, logical = Logical.OR)@RequestMapping(value = "import", method = RequestMethod.POST)@ResponseBodypublic String importFile(MultipartFile file, RedirectAttributes redirectAttributes, Meeting meeting,Model model,HttpServletRequest request) {    String filepath = "";    LayFileJsonData data = new LayFileJsonData();    // 判断文件是否为空    if (!file.isEmpty()) {        // 文件保存路径        String realPath = "files/";        // 转存文件        File saveDir = new File(request.getSession().getServletContext().getRealPath(realPath));        //如果目录不存在，就创建目录?        if(!saveDir.exists()){            saveDir.mkdir();        }        String meetingNo = file.getOriginalFilename().substring(5,19);        //根据会议的编号查找会议        meeting=meetingService.getMeetingNo(meetingNo);        //String type = file.getContentType().substring(file.getContentType().indexOf("/") + 1);        if(meeting==null){            return "2";        }        try {            file.transferTo(new File(request.getSession().getServletContext().getRealPath("/") + realPath +Md5Util.parseStrToMd5L32(file.getOriginalFilename()+new SimpleDateFormat("yyyy-MM-dd").format(new Date()))));        } catch (IOException e) {            e.printStackTrace();        }        filepath = request.getContextPath() + realPath + Md5Util.parseStrToMd5L32(file.getOriginalFilename()+new SimpleDateFormat("yyyy-MM-dd").format(new Date()));        data.setName(file.getOriginalFilename());        data.setSrc(filepath);    }    try {        InputStream input = new FileInputStream(Global.getUrlPath()+data.getSrc());  //建立输入流        //这里导入Excel表的数据        //XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());        XSSFWorkbook wb = new XSSFWorkbook(input);        XSSFSheet sheet = wb.getSheetAt(0);        Integer rowNum = sheet.getLastRowNum();        //遍历单元格的每一行        for (int i = 1; i <= rowNum; i++) {            XSSFRow row = sheet.getRow(i);            //获取第i行第一列            XSSFCell cel0 = row.getCell(0);            //获取第i行第二列            XSSFCell cel1 = row.getCell(1);            //获取第i行第三列            XSSFCell cel2 = row.getCell(2);            XSSFCell cel3 = row.getCell(3);            XSSFCell cel4 = row.getCell(4);            XSSFCell cel5 = row.getCell(5);            XSSFCell cel6 = row.getCell(6);            XSSFCell cel7 = row.getCell(7);            XSSFCell cel8 = row.getCell(8);            XSSFCell cel9 = row.getCell(9);            XSSFCell cel10 = row.getCell(10);            MeetingIn meetingIn = new MeetingIn();            //单位电话和名称,如果不为空说明,这是新的单位            if (cel0 != null && (!cel0.getStringCellValue().equals(""))) {                User user = new User();                user.setName(cel0.getStringCellValue());                if (cel1 == null) {                    user.setOfficePhone("");                } else {                    user.setOfficePhone(cel1.getStringCellValue());                }                user = systemService.getNamneAndPhone(user);                if(user !=null){                    //根据userID会议的ID查找                    meetingIn.setUserId(user.getId());                    meetingIn.setMeetingId(meeting.getId());                    meetingIn.setType("2");                    MeetingIn meetingIn1 =meetingInService.getMeetingidAndUseridAndType(meetingIn);                    if (meetingIn1!=null){                        meetingIn1.setMeetingId(meeting.getId());                        //根据会议ID和自己的ID查找                        List<MeetingParticipants> list=meetingInService.getByMeetingidAndMeetingInidAndimport(meetingIn1);                        if (list.size() != 0){                            for (MeetingParticipants meetingParticipants:list){                                String type = cel10.getStringCellValue();                                //判断签到状态                                if ("未签到".equals(type)) {                                    meetingParticipants.setType("0");                                } else if ("已签到".equals(type)) {                                    meetingParticipants.setType("1");                                } else if ("迟到".equals(type)) {                                    meetingParticipants.setType("2");                                } else {                                    //如果有请假人要对整个meetingIn更新                                    meetingParticipants.setType("3");                                    meetingIn = meetingInService.getId(meetingIn.getId());                                    meetingIn.setWhetherLeave("1");                                    meetingInService.save(meetingIn);                                }                                meetingParticipantsService.save(meetingParticipants);                            }                        }else {                            addMessage(redirectAttributes,"有非参会人员签到，请确认信息是否正确导入失败");                            //return "redirect:" + Global.getAdminPath() + "/meeting/detail?id="+meeting.getId();                            return "";                        }                    }                }else {                    addMessage(redirectAttributes,"有非参会人员签到，请确认信息是否正确导入失败");                    //return "redirect:" + Global.getAdminPath() + "/meeting/detail?id="+meeting.getId();                    return "";                }            }        }        input.close();    } catch (Exception e) {        addMessage(redirectAttributes, "导入会议失败！失败信息：" + e.getMessage());        return "2";    }    //return "redirect:" + Global.getAdminPath() + "/meeting/detail?id="+meeting.getId();    return "1";}
返回的事json数据，这里有两点很重要，一是要对系统做区别，还有对文件进行加密（不是必须的）下面是加密的类
public class Md5Util {   /**    * @Description: 32位小写MD5    */   public static String parseStrToMd5L32(String str) {      String reStr = null;      try {         MessageDigest md5 = MessageDigest.getInstance("MD5");         byte[] bytes = md5.digest(str.getBytes());         StringBuffer stringBuffer = new StringBuffer();         for (byte b : bytes) {            int bt = b & 0xff;            if (bt < 16) {               stringBuffer.append(0);            }            stringBuffer.append(Integer.toHexString(bt));         }         reStr = stringBuffer.toString();      } catch (NoSuchAlgorithmException e) {         e.printStackTrace();      }      return reStr;   }   /**    * @Description: 32位大写MD5    */   public static String parseStrToMd5U32(String str) {      String reStr = parseStrToMd5L32(str);      if (reStr != null) {         reStr = reStr.toUpperCase();      }      return reStr;   }   /**    * @Description: 16位小写MD5    */   public static String parseStrToMd5U16(String str) {      String reStr = parseStrToMd5L32(str);      if (reStr != null) {         reStr = reStr.toUpperCase().substring(8, 24);      }      return reStr;   }   /**    * @Description: 16位大写MD5    */   public static String parseStrToMd5L16(String str) {      String reStr = parseStrToMd5L32(str);      if (reStr != null) {         reStr = reStr.substring(8, 24);      }      return reStr;   }   public static String encodePassword(String rawPass) {      String saltedPass = mergePasswordAndSalt(rawPass, "PONY", false);      MessageDigest messageDigest = getMessageDigest();      byte[] digest;      try {         digest = messageDigest.digest(saltedPass.getBytes("UTF-8"));      } catch (UnsupportedEncodingException e) {         throw new IllegalStateException("UTF-8 not supported!");      }      return null;   }   protected static String mergePasswordAndSalt(String password, Object salt, boolean strict) {      if (password == null) {         password = "";      }      if ((strict) && (salt != null) && ((salt.toString().lastIndexOf("{") != -1) || (salt.toString().lastIndexOf("}") != -1))) {         throw new IllegalArgumentException("Cannot use { or } in salt.toString()");      }      if ((salt == null) || ("".equals(salt))) {         return password;      }      return password + "{" + salt.toString() + "}";   }   protected final static MessageDigest getMessageDigest() {      String algorithm = "MD5";      try {         return MessageDigest.getInstance(algorithm);      } catch (NoSuchAlgorithmException e) {         throw new IllegalArgumentException("No such algorithm [" + algorithm + "]");      }   }   public boolean isPasswordValid(String encPass, String rawPass) {      String pass1 = "" + encPass;      String pass2 = encodePassword(rawPass);      return pass1.equals(pass2);   }   public static void main(String[] args) {      System.out.println(parseStrToMd5L32("111"));      System.out.println(parseStrToMd5U32("111"));      System.out.println(encodePassword("111"));   }}
这个是区分系统的，这个很重要
/** * 获取硬盘上的绝对路径 */public static String getUrlPath(){   //linux下   String classPath = Global.class.getClassLoader().getResource("/").getPath();   String rootPath  = "";   if("/".equals(File.separator)){      rootPath  = classPath.substring(0,classPath.indexOf("/WEB-INF/classes"));      rootPath = rootPath.replace("\\", "/")+"/";   }   //windows下   if("\\".equals(File.separator)){      rootPath = classPath.substring(1,classPath.indexOf("/WEB-INF/classes"));      rootPath = rootPath.replace("/", "\\")+"\\";   }   return rootPath;}