后台java导入excel到数据库代码

最新推荐文章于 2022-03-17 18:41:44 发布

黑皮爱学习

最新推荐文章于 2022-03-17 18:41:44 发布

阅读量1k

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/qq_22701869/article/details/85108851

版权

java 专栏收录该内容

42 篇文章 6 订阅

订阅专栏

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
	
	@RequestMapping("/ajaxUploadFile")
	@ResponseBody
	public String upload(@RequestParam MultipartFile[] userfiles) throws IOException {
		String result = "success";
		for (MultipartFile userfile : userfiles) {
			if (userfile.isEmpty()) {
				result = "failure";
			} else {
				try {
					// 通过工具类 解析上传的excle文件
					List<CastModal> modals = new ArrayList<CastModal>();
					modals.add(new CastModal(0, "account", "String", true));
					modals.add(new CastModal(1, "name", "String", true));
					modals.add(new CastModal(2, "sex", "String", true));
					modals.add(new CastModal(3, "mobile", "String", true));
					modals.add(new CastModal(4, "phone", "String"));
					modals.add(new CastModal(5, "email", "String"));
					modals.add(new CastModal(6, "orgName", "String"));

					List<SysUserVo> userVoList = ExcelUtil.getList(userfile.getInputStream(), SysUserVo.class, modals);

					// -----校验开始-----
					int rowNum = 1;
					SessionParam sessionParam = (SessionParam) SecurityUtils.getSubject()
							.getSession()
							.getAttribute("sessionParam");
					SysUser opUser = sessionParam.getUser();
					for (SysUserVo sysUserVo : userVoList) {
						// rowNum ++;
						// 帐号
						result = checkField("account", sysUserVo.getAccount(), "^[\\u0391-\\uFFE5\\w\\.@-]{1,25}$",
								true);
						if (!"success".equals(result)) {
							return "第" + rowNum + "行登录账号" + result;
						}
						// 校验帐号唯一性
						if (StringUtils.isNotBlank(ajaxCheckAccount(sysUserVo.getAccount()))) {
							return "第" + rowNum + "行登录账号已存在";
						}

						// 员工姓名
						result = checkField("name", sysUserVo.getName(), "^[\\u0391-\\uFFE5\\w\\.@-]{1,50}$", true);
						if (!"success".equals(result)) {
							return "第" + rowNum + "行员工姓名" + result;
						}

						// 性别
						result = checkField("gender", sysUserVo.getSex(), "^[\\u7537\\u5973]{1}$", true);
						if (!"success".equals(result)) {
							return "第" + rowNum + "行性别" + result;
						}

						// 手机号码
						result = checkField("mobile", sysUserVo.getMobile(), "^1[3-9]\\d{9}$", true);
						if (!"success".equals(result)) {
							return "第" + rowNum + "行手机号码" + result;
						}

						// 固定电话
						if (StringUtils.isNotBlank(sysUserVo.getPhone())) {
							result = checkField("phone", sysUserVo.getPhone(), "^\\d{7,11}$", false);
							if (!"success".equals(result)) {
								return "第" + rowNum + "行固定电话" + result;
							}
						}
						// 邮箱地址
						if (StringUtils.isNotBlank(sysUserVo.getEmail())) {
							result = checkField("email", sysUserVo.getEmail()
									.toLowerCase(),
									"^[\\w\\+\\-]+(\\.[\\w\\+\\-]+)*@[a-z\\d\\-]+(\\.[a-z\\d\\-]+)*\\.([a-z]{2,4})$",
									false);
							if (!"success".equals(result)) {
								return "第" + rowNum + "行邮箱地址" + result;
							}
						}
						
						// 添加创建人创建时间
						sysUserVo.setCreateTime(new Date());
						sysUserVo.setCreateUserId(opUser.getId());

						if ("男".equals(sysUserVo.getSex()))
							sysUserVo.setSex("man");
						else
							sysUserVo.setSex("woman");

						sysUserVo.setPassword(MD5Utils.md5(ConstantsUtils.UAC_USER_PASSWORD_DEFAULT));
						sysUserVo.setStatus("SA");
						;
					}
					// ------校验结束------
					// 业务层去做 插入
					sysUserService.batchSaveUser(userVoList);
				} catch (Exception e) {
					e.printStackTrace();
					result = "failure";
				}

			}
		}
		return result;
	}
	public String checkField(String fieldName, String fieldValue, String reg, Boolean isNotNull) {
		if (null != isNotNull && isNotNull) {
			if (StringUtils.isBlank(fieldValue))
				return "不能为空";
		}
		if (null == fieldValue)
			return "success";
		if (!fieldValue.matches(reg)) {
			if ("account".equals(fieldName))
				return "请填写中英文/数字/_/-/.或@,最大长度25字";
			else if ("name".equals(fieldName))
				return "请填写中英文/数字/_/-/.或@,最大长度50字";
			else if ("gender".equals(fieldName))
				return "只能是男女";
			else if ("mobile".equals(fieldName))
				return "请填写有效的手机号";
			else if ("phone".equals(fieldName))
				return "请填写有效的固定电话";
			else if ("email".equals(fieldName))
				return "请填写有效的邮箱";
			else if ("orgName".equals(fieldName))
				return "请填写中英文/数字/_/-/.或@,最大长度150字";
			else
				return "格式不匹配";
		}
		;
		return "success";
	}