文件上传和导出

最新推荐文章于 2024-01-25 09:00:00 发布

原创最新推荐文章于 2024-01-25 09:00:00 发布 · 575 阅读

CC 4.0 BY-SA版权

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>>();
		//得到课程Id
		Course 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) {
		//添加业务逻辑——导入数据
		//得到teacherId
		Object  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>