文件上传和导出

1、导出

/**导出报表*/@RequestMapping(value = "/exportExcel", method = RequestMethod.GET)public void exportErrorExcel( HttpServletRequest request,HttpServletResponse response) {String courseId = request.getParameter("courseId");String classId = request.getParameter("classId");List<String> classIdStrList = Arrays.asList(classId.split(","));List<Long> classIdList = new ArrayList<Long>();for (int i=0;i<classIdStrList.size();i++){classIdList.add(Long.valueOf(classIdStrList.get(i)));}List<UserRegister> userRegisters = userRegisterService.findByClassIds(classIdList);String loginNames = "";for (int i=0;i<userRegisters.size();i++){if (i == userRegisters.size()-1){loginNames += userRegisters.get(i).getLoginName();}else{loginNames += userRegisters.get(i).getLoginName()+",";}}//获得的听课详情List<Map<String, Object>> courseCountList = new ArrayList<Map<String, Object>>();//得到课程IdCourse course = courseService.load(Long.valueOf(courseId));courseCountList = selectCourseStatisticByCourse(course.getCwCourseId(),loginNames,0,0).get("lectureInfoList");String fileName = "听课统计.xls";ArrayList<ArrayList<String>> datas = new ArrayList<ArrayList<String>>(); //导出的数据集合if (courseCountList.size() > 0){for (int i=0;i<courseCountList.size();i++){ArrayList<String> data = new ArrayList<String>();data.add(courseCountList.get(i).get("lectureName").toString());data.add(courseCountList.get(i).get("coursewareTime").toString());data.add(courseCountList.get(i).get("listenCount").toString());data.add(courseCountList.get(i).get("listenRate").toString());data.add(courseCountList.get(i).get("averageListenTime").toString());data.add(courseCountList.get(i).get("listenCount60").toString());data.add(courseCountList.get(i).get("listenCount80").toString());data.add(courseCountList.get(i).get("listenCount100").toString());datas.add(data);}}ArrayList<String> titles = new ArrayList<String>();titles.add("讲次名称");titles.add("总时长");titles.add("听课人数");titles.add("听课率");titles.add("平均听课时长");titles.add("听课完成率0%－60％人数");titles.add("听课完成率60%－80%人数");titles.add("听课完成率80%－100%人数");ExcelUtils excel = new ExcelUtils(titles,datas);try {String agent = request.getHeader("User-Agent");boolean isMSIE = (agent != null && (agent.indexOf("MSIE") != -1|| agent.indexOf("Trident") != -1));//fileName = URLEncoder. encode(fileName, "UTF-8");if(isMSIE){ //是IE浏览器fileName = URLEncoder.encode(fileName, "UTF-8");} else {fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");}response.reset();//清空输出流response.setHeader("Content-disposition", "attachment; filename=" +fileName);response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("UTF-8");excel.exportExcel(response.getOutputStream());} catch (Exception e) {e.printStackTrace();}}

2、上传

（1）导入controller

/**导入*/@RequestMapping(value = "/tofileUploadCommonIndex")public String tofileUploadCommonIndex(Model model, HttpServletRequest request) {return "modules/userbaseinfo/userBaseInfo_upload";}/**导入数据*/@RequestMapping(value = "/saveUser", method = RequestMethod.POST)public String saveUser(@ModelAttribute("UserBaseInfo") UserBaseInfo userBaseInfo, HttpServletRequest request,RedirectAttributes redirectAttributes,Model model) {//添加业务逻辑——导入数据//得到teacherIdObject  userIdO =  request.getSession().getAttribute("userId");Long userId = Long.valueOf(String.valueOf(userIdO));PartnerTeacher partnerTeacher = partnerTeacherService.selectByUserId(userId);userBaseInfo.setCreateUser(partnerTeacher.getUserId());userBaseInfo.setTeacherId(partnerTeacher.getId());userBaseInfo.setPartnerId(partnerTeacher.getPartnerId());userBaseInfo.setOrganizationalId(partnerTeacher.getOrganizationalId());Map<String, Object> importTeacher = userBaseInfoService.importUser(userBaseInfo);redirectAttributes.addFlashAttribute("msg", importTeacher.get("daoru").toString());return "redirect:/partnerteacheruser/partnerTeacherUser";}

（2）导入service

 @Override    @Transactional(propagation= Propagation.REQUIRED, isolation= Isolation.READ_COMMITTED,timeout=10)    public Map<String, Object> importUser(UserBaseInfo userBaseInfo) {        //删除之前错误信息        UserBaseInfoFromExcel userBaseInfoFromExcel = new UserBaseInfoFromExcel();        userBaseInfoFromExcel.setBatchNo("老师导入"+String.valueOf(userBaseInfo.getCreateUser()));        userBaseInfoFromExcel.setPartnerId(userBaseInfo.getPartnerId());        List<UserBaseInfoFromExcel> uList = userBaseInfoFromExcelMapper.selectError(userBaseInfoFromExcel);        if (uList.size()>0){            for (UserBaseInfoFromExcel userBaseInfoFromExcel1:uList                    ) {                userBaseInfoFromExcel1.setIsValid(IS_VALID.NO.getValue());                userBaseInfoFromExcel1.setUpdateUser(userBaseInfo.getCreateUser());                userBaseInfoFromExcel1.setUpdateDate(new Date());                userBaseInfoFromExcelMapper.update(userBaseInfoFromExcel1);            }        }        //excel中数据放入columnIndexPropertyNameMap中        String[] array = userBaseInfo.getImportExceForBath().split("/");        String fileName = array[array.length - 1];        String fileUrl = FileUploadConstants.getPropValue("FILE_PATH_EXCEL") + File.separator + fileName;        ImportDataUtil i = new ImportDataUtil(fileUrl);        LinkedHashMap<Integer, String> columnIndexPropertyNameMap = new LinkedHashMap<>();        columnIndexPropertyNameMap.put(0, "realName");        columnIndexPropertyNameMap.put(1, "studentNum");        columnIndexPropertyNameMap.put(2, "mobilephone");        List<?> excelList = i.makeRelationShip("com.dongao.modules.userbaseinfo.model.UserBaseInfo", columnIndexPropertyNameMap);        Map<String,Object> map = new HashMap<String,Object>(); //返回数据        int count = 0; // 成功导入条数        int errorCount = 0; // 错误数据条数        if (excelList.size() > 0 ){            for (int j = 0; j < excelList.size(); j++) {                StringBuilder checkMsg = new StringBuilder();                StringBuilder info = new StringBuilder(); //条目                UserBaseInfo excelData = (UserBaseInfo)excelList.get(j); //读取一行                //过滤空行                if(StringUtils.isBlank(excelData.getRealName())&&StringUtils.isBlank(excelData.getStudentNum())&&StringUtils.isBlank(excelData.getMobilephone())){                    continue;//不可见字符过滤                }                if(StringUtils.isBlank(excelData.getRealName())){                    checkMsg.append("姓名不能为空 /");                }else{                }                if(StringUtils.isBlank(excelData.getStudentNum())){                    checkMsg.append("学号不能为空 /");                }else{                    UserBaseInfo userBaseInfo1 = new UserBaseInfo();                    userBaseInfo1.setStudentNum(excelData.getStudentNum());                    UserBaseInfo userBaseInfo2= userBaseInfoMapper.findByObj(userBaseInfo1);                    if (userBaseInfo2 != null) {                        checkMsg.append("学号重复 /");                    }                }                if (StringUtils.isNotBlank(excelData.getMobilephone())){                    boolean bl = isMobilePhoneLegal(excelData.getMobilephone());                    if (false == bl){                        checkMsg.append("手机号格式错误/");                    }                }                if (StringUtils.isNotBlank(String.valueOf(checkMsg))) {                    //数据插入da_edu_user_base_info_from_excel表                    UserBaseInfoFromExcel userBaseInfoFromExcel2 = new UserBaseInfoFromExcel();                    userBaseInfoFromExcel2.setRealName(excelData.getRealName());                    userBaseInfoFromExcel2.setStudentNum(excelData.getStudentNum());                    if (StringUtils.isNotBlank(excelData.getMobilephone())){                        userBaseInfoFromExcel2.setMobilephone(excelData.getMobilephone());                    }                    userBaseInfoFromExcel2.setBatchNo("老师导入"+String.valueOf(userBaseInfo.getCreateUser()));//错误标记                    userBaseInfoFromExcel2.setNoPassReason(checkMsg.toString());                    userBaseInfoFromExcel2.setPartnerId(userBaseInfo.getPartnerId());                    userBaseInfoFromExcel2.setIsValid(IS_VALID.YES.getValue());                    userBaseInfoFromExcel2.setCreateUser(userBaseInfo.getCreateUser());                    userBaseInfoFromExcel2.setCreateDate(new Date());                    userBaseInfoFromExcelMapper.insert(userBaseInfoFromExcel2);                    errorCount++;                } else {                    //数据插入da_edu_user_register表                    UserRegister userRegister = new UserRegister();                    userRegister.setLoginName(excelData.getStudentNum());                    //默认密码123456                    String salt = createSalt();//获取随机的盐值                    String password= new KeyBean().getkeyBeanofStr(salt+Constants.PASSWORD);                    userRegister.setSalt(salt);                    userRegister.setPassword(password);                    userRegister.setStatus(Constants.Status.START.getValue());                    userRegister.setType(Constants.REGISTER_TYPE.BATCH.getValue());                    userRegister.setUserRole(Constants.USER_ROLE.STUDENT.getValue());                    userRegister.setIsValid(IS_VALID.YES.getValue());                    userRegister.setCreateUser(userBaseInfo.getCreateUser());                    userRegister.setCreateDate(new Date());                    userRegisterMapper.insert(userRegister);                    //数据插入da_edu_user_base_info表                    UserRegister userRegister1 = userRegisterMapper.loadUserRegisterByLoginName(excelData.getStudentNum());                    UserBaseInfo userBaseInfo1 = new UserBaseInfo();                    userBaseInfo1.setUserId(userRegister1.getId());                    userBaseInfo1.setLoginName(userRegister1.getLoginName());                    userBaseInfo1.setRealName(excelData.getRealName());                    userBaseInfo1.setStudentNum(excelData.getStudentNum());                    if (StringUtils.isNotBlank(excelData.getMobilephone())){                        userBaseInfo1.setMobilephone(excelData.getMobilephone());                    }                    userBaseInfo1.setOrganizationalId(userBaseInfo.getOrganizationalId());                    userBaseInfo1.setPartnerId(userBaseInfo.getPartnerId());                    userBaseInfo1.setCreateUser(userBaseInfo.getCreateUser());                    userBaseInfo1.setOrigin(Constants.ORIGIN.BATCH.getValue());                    this.save(userBaseInfo1);                    //数据插入da_edu_partner_teacher_user表                    PartnerTeacherUser partnerTeacherUser = new PartnerTeacherUser();                    partnerTeacherUser.setTeacherId(userBaseInfo.getTeacherId());                    partnerTeacherUser.setUserId(userRegister1.getId());                    partnerTeacherUser.setCreateUser(userBaseInfo.getCreateUser());                    partnerTeacherUser.setCreateDate(new Date());                    partnerTeacherUser.setIsValid(IS_VALID.YES.getValue());                    partnerTeacherUserMapper.insert(partnerTeacherUser);                    count++;                }            }            map.put("daoru", "成功导入:" + count + "条。   错误的有：" + errorCount +"条。");        }else {            map.put("daoru", "表格中无数据，请重新导入！");        }        return map;    }    /**     * 生成salt     *     * @param     * @return     */    private String createSalt() {        String salt = "";        salt += (int) (Math.random() * 9 + 1);        for (int i = 0; i < 5; i++) {            salt += (int) (Math.random() * 10);        }        return salt;    }    /**手机号格式验证**/    public static boolean isMobilePhoneLegal(String str) throws PatternSyntaxException {        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(str);        return m.matches();    }

（3）导入页面

<style>    .manage_add{        margin-left: 100px;    }    .Validform_checktip{        margin-left:0px;    }    .ass_div{        width: 382px;    }</style><div class="manage_add">    <form class="addForm" target="_parent" id="userBaseInfoImportForm" action="${request.getContextPath()}/userbaseinfo/userBaseInfo/saveUser" method="post">        <div class="ass_div clearfix" >            <p class="fl">导入学员：</p>            <input type="hidden" name="importExceForBath" id="importExceForBath" value="" datatype="*" nullmsg="请上传文件！" errormsg="请上传文件！"/>            <span class="Validform_checktip" ></span>            <iframe scrolling="no" src="${request.getContextPath()}/uploadfile/uploadFile/uploadFileData?filePath=FILE_PATH_EXCEL&type=*.EXCEL&dom=importExceForBath&fileCategory=importUser"  id="main" frameborder="0" width="350" height="100">            </iframe>        </div>        <div class="ass_div clearfix">            <span class="Validform_checktip" ></span>        </div>        <div class="clearfix copy_btns" >            <td colspan="3" >                <p class="btn fl y_btn" type="button" id="saveBtn">保存</p>                <p type="button" class="btn fl g_btn" >取消</p>            </td>        </div>    </form></div><script type="text/javascript">    $(function(){        $('#userBaseInfoImportForm').Validform({            btnSubmit:"#saveBtn",            tiptype:2,            showAllError:true        });        $('.chosen').chosen({            "no_results_text":'未找到匹配数据!',            "width":"120px",            "allow_single_deselect":true        });        $('#cancelBtn').click(function(){            parent.jBox.close(true);        });    });</script>

（4）导入插件（存放在百度云中layui）

@RequestMapping(value = {"/uploadFileData"}, method = {RequestMethod.GET,RequestMethod.POST})public String uploadFileData(Model model, HttpServletRequest request) {//TODO...String filePath = request.getParameter("filePath");String type = request.getParameter("type");String dom = request.getParameter("dom");String imgSrc = request.getParameter("imgSrc");String saveFilePath = FileUploadConstants.getPropValue(filePath);String fileCategory = request.getParameter("fileCategory");//文件存储所在文件夹，回显路径=基路径+该文件夹String url=Constants.FIlE_BASE_URL + fileCategory+"/";request.setAttribute("filePath",filePath);request.setAttribute("type",type);request.setAttribute("dom",dom);request.setAttribute("imgSrc",imgSrc);request.setAttribute("saveFilePath",saveFilePath);request.setAttribute("url",url);HttpSession session = request.getSession();if(session !=null){request.setAttribute("jsessionid", session.getId());}return "modules/send/send2";

<html><head>    <meta charset="utf-8">    <title>layui</title>    <meta name="renderer" content="webkit">    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">    <link rel="stylesheet" href="${request.getContextPath()}/layui/css/layui.css"  media="all"></head><body><div class="layui-upload">    <input type="hidden" name="extname" id="extname" value="${extname}"/>    <input type="hidden" name="filename" id="filename" value="${filename}"/>    <input type="hidden" name="filePath" id="filePath" value="${filePath}"/>    <input type="hidden" name="saveFilePath" id="saveFilePath"  value="${saveFilePath}"/>    <input type="hidden" name="proVal" id="proVal" value="${filePath}" />    <input type="hidden" name="jsessionid" id="jsessionid" value="${jsessionid}" />    <button type="button" class="layui-btn layui-btn-danger" id="test1"><i class="layui-icon"></i>上传文件</button><#-- <#if type?? && type == "*.JPG" > <div class="layui-upload-list">     <blockquote class="layui-elem-quote" style="margin-top: 10px;">         <img src="http://static.dongao.com/image/no_photo.jpg" class="layui-upload-img" id="imgSrc" width="100px" height="100px">         <p id="demoText"></p>     </blockquote> </div> </#if>--></div><script src="${request.getContextPath()}/layui/layui.all.js" charset="utf-8"></script><!-- 注意：如果你直接复制所有代码到本地，上述js路径需要改成你本地的 --><script>    layui.use('upload', function(){        var $ = layui.jquery,upload = layui.upload;        //上传        var type = "${type}";        var desc = '';        var pic = 'gif|jpg|jpeg|bmp|png';        var doc = 'doc|docx|xls|xlsx|pdf';        var exceltype='xls|xlsx';        if(type != '' && type.toLocaleUpperCase() =='*.CSS'){            desc='css|CSS';        }else if(type != '' && type.toLocaleUpperCase()=='*.JPG'){            desc=pic;        }else if(type != '' && type.toLocaleUpperCase() =='*.FTL'){            desc='ftl';        }else if(type != '' && type.toLocaleUpperCase() =='*.EXCEL'){            desc=exceltype;        }else if(type != '' && type.toLocaleUpperCase() =='*.JS'){            desc='js|JS';        }        var uploadInst = upload.render({            elem: '#test1'            ,accept: 'file'            ,exts: desc            ,url: '${request.getContextPath()}/js/utils/expupload/upload.jsp;jsessionid=${jsessionid}?filetype='+$('#extname').val()+'&proVal='+$('#proVal').val()+'&filename='+$('#filename').val()            ,before: function(obj){                //预读本地文件示例，不支持ie8                layer.load(); //上传loading                obj.preview(function(index, file, result){                    $('#imgSrc').attr('src', result); //图片链接（base64）                });            }            ,done: function(res){                //如果上传失败                if(res.code == 0){                    layer.closeAll('loading'); //关闭loading                    return layer.msg('上传失败');                }else{                    layer.closeAll('loading'); //关闭loading                    $($(window.parent.document).find("input[name='${dom}']")[0]).attr("value", '${url}'+trim(res.name));                    $($(window.parent.document).find("img[id='${imgSrc}']")).attr("src", '${url}'+trim(res.name));                    return layer.msg('上传成功');                }                //上传成功            }            ,error: function(){                //演示失败状态，并实现重传                var demoText = $('#demoText');                demoText.html('<span style="color: #FF5722;">上传失败</span> <a class="layui-btn layui-btn-mini demo-reload">重试</a>');                demoText.find('.demo-reload').on('click', function(){                    uploadInst.upload();                });            }        });    });    function trim(str){        return str.replace(/(^\s*)|(\s*$)/g, "");    }</script></body></html>