目录:
- 方式一:通过jxl jar包来解析xls文件
- 方式二:通过poi jar包来解析xls文件javascript:void(0)
- 个人建议使用poi 来解析,因为jxl目前没有维护了,而且对日期时间的处理不友好,本人开始是使用的jxl,但是因为有时间的问题,进而使用poi,但是刚刚看到对时间可以处理了,我再尝试一下
/注意,这里的两个参数,第一个是表示列的,第二才表示行
cell=sheet.getCell(j, i);
//要根据单元格的类型分别做处理,否则格式化过的内容可能会不正确
if(cell.getType()==CellType.NUMBER){
System.out.print(((NumberCell)cell).getValue());
}
else if(cell.getType()==CellType.DATE){
System.out.print(((DateCell)cell).getDate());
}
else{
System.out.print(cell.getContents());
}
方式一:通过jxl jar包来解析xls文件
手机、邮箱、姓名校验
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;
public class PhoneFormatCheckUtils {
/**
* ^ 匹配输入字符串开始的位置
* \d 匹配一个或多个数字,其中 \ 要转义,所以是 \\d
* $ 匹配输入字符串结尾的位置
*/
private static final Pattern HK_PATTERN = Pattern.compile("^(5|6|8|9)\\d{7}$");
private static final Pattern CHINA_PATTERN = Pattern.compile("^((13[0-9])|(14[0,1,4-9])|(15[0-3,5-9])|(16[2,5,6,7])|(17[0-8])|(18[0-9])|(19[0-3,5-9]))\\d{8}$");
private static final Pattern NUM_PATTERN = Pattern.compile("[0-9]+");
/**
* 大陆号码或香港号码均可
*/
public static boolean isPhoneLegal(String str) throws PatternSyntaxException {
return isChinaPhoneLegal(str) || isHKPhoneLegal(str);
}
/**
* 大陆手机号码11位数,匹配格式:前三位固定格式+后8位任意数
* 此方法中前三位格式有:
* 13+任意数
* 145,147,149
* 15+除4的任意数(不要写^4,这样的话字母也会被认为是正确的)
* 166
* 17+3,5,6,7,8
* 18+任意数
* 198,199
*/
public static boolean isChinaPhoneLegal(String str) throws PatternSyntaxException {
Matcher m = CHINA_PATTERN.matcher(str);
return m.matches();
}
/**
* 香港手机号码8位数,5|6|8|9开头+7位任意数
*/
public static boolean isHKPhoneLegal(String str) throws PatternSyntaxException {
Matcher m = HK_PATTERN.matcher(str);
return m.matches();
}
/**
* 判断是否是正整数的方法
*/
public static boolean isNumeric(String string) {
return NUM_PATTERN.matcher(string).matches();
}
/**
* 中国姓名验证
*/
public static boolean ChineseNameTest(String name) {
if (!name.matches("[\u4e00-\u9fa5]{2,4}")) {
return false;
}
return true;
}
/**
* 邮箱验证
*/
public static boolean isEmail(String email){
String str="^" +
"([a-zA-Z0-9]*[-_]?[a-zA-Z0-9]+)*@" +
"([a-zA-Z0-9]*[-_]?[a-zA-Z0-9]+)+" +
"[\\.][A-Za-z]{2,3}" +
"([\\.][A-Za-z]{2})?" +
"$";
Pattern p = Pattern.compile(str);
Matcher m = p.matcher(email);
return m.matches();
}
}
1.添加支持导入xls文件的jar包
<!-- jxl.jar实现excel文件的获取 -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
2.业务类
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.transaction.annotation.Transactional;
import cn.rntd.common.util.StringUtil;
import cn.rntd.common.util.log.LogUtil;
import cn.rntd.pc.shop.utils.PhoneFormatCheckUtils;
import cn.rntd.vo.gift.CompanyMemberUser;
import cn.rntd.vo.wx.wxUser.WxUser;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class Test1 {
/**
* @Title: addCompanyMemberUsers
* @Description: 批量添加员工
* @param params
* @return
* @throws
* @author yiguang
* @date 2020-08-17
*/
@Override
@Transactional
public Object addCompanyMemberUsers(Map<String, Object> params) {
String fileSource = String.valueOf(params.get("fileSource"));
//String fileSource = "d://downloads/员工导入模板.xls";
String companyMemberId = String.valueOf(params.get("companyMemberId"));
//查询对应adminId和agentId
Map<String,String> mapCompany = companyMemberUserMapper.findIdByCompanyMemberId(companyMemberId);
Date now=new Date();
SimpleDateFormat ft = new SimpleDateFormat ("yy-M-d");//时间格式处理
File file=new File(fileSource);
Map<String,Object> resultMap= new HashMap();
resultMap.put("code", "0");
if (StringUtil.isBlank(fileSource)) {
resultMap.put("code","-1");
resultMap.put("errmsg","文件不存在");
return resultMap;
}
//记录集合
List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
//插入成功的条数
int sucNum=0;
//回复消息
String reqMsg = "";
//报错消息数
Integer errorNum = 0;
//解析文件
try {
//转换成输入流
InputStream fis=new FileInputStream(file);
//得到excel
Workbook workbook = Workbook.getWorkbook(fis);
//得到sheet
Sheet sheet = workbook.getSheet(0);
//得到列数
int colsNum = sheet.getColumns();
//得到行数
int rowsNum = sheet.getRows();
if (rowsNum == 9) {
resultMap.put("code","-1");
resultMap.put("errmsg","没有数据");
return resultMap;
}
LogUtil.info("=======总行数======:"+rowsNum);
LogUtil.info("=======总列数======:"+colsNum);
//单元格
Cell cell;
//数据校验
for (int i = 9; i < rowsNum; i++) {//前9行是说明以及标题,所以i从10开始,因为i从0开始,所以是9
Map<String, Object> mapUser = new HashMap<String, Object>();
CompanyMemberUser companyMemberUser=new CompanyMemberUser();
//校验结果
boolean reqFlag = true;
for (int j = 0; j < colsNum; j++) {
cell = sheet.getCell(j, i);//第一个参数是列.第二个参数是行
if (j < 2 && StringUtil.isBlank(cell.getContents())) { //----这里判断必填项(前2列)
reqFlag = false;
reqMsg += "第" + (i + 1) + "行错误,错误信息:" + "必填项缺漏;";
//reqMsg += "<br>";
errorNum++;
break;
}
String cellString = cell.getContents();
cellString = cellString.trim();
//System.out.println("=======行数======:"+i+"==列数:"+j+"=="+cellString);
switch (j) {
case 0:{
//姓名校验
if(PhoneFormatCheckUtils.ChineseNameTest(cellString)){
mapUser.put("userName", cellString);
companyMemberUser.setUserName(cellString);
}else{
reqFlag = false;
reqMsg += "第" + (i + 1) + "行第"+ j+"列错误,错误信息:" + "姓名有误;";
errorNum++;
}
break;
}
case 1: {
//手机号校验
if(PhoneFormatCheckUtils.isPhoneLegal(cellString)){
CompanyMemberUser memberUser=companyMemberUserMapper.findCompanyMemberUserByMobile(cellString, mapCompany.get("adminId"));//验证手机号是否已经存在,如果存在,则不能插入
if(memberUser!=null){
reqFlag = false;
reqMsg += "第" + (i + 1) + "行错误,错误信息:" + "联系电话已存在;";
errorNum++;
}else{
mapUser.put("mobile", cellString);
companyMemberUser.setMobile(cellString);
}
}else{
reqFlag = false;
reqMsg += "第" + (i + 1) + "行第"+ j+"列错误,错误信息:" + "联系电话有误;";
errorNum++;
}
break;
}
case 2:{
if (StringUtil.isNotBlank(cellString)) {
mapUser.put("sex", cellString);
companyMemberUser.setSex(cellString);
}
break;
}
case 3:{
if (StringUtil.isNotBlank(cellString)) {
if(PhoneFormatCheckUtils.isEmail(cellString)){
mapUser.put("mailbox", cellString);
companyMemberUser.setMailbox(cellString);
}else{
reqMsg += "第" + (i + 1) + "行错误,错误信息:" + "邮箱格式有误,请检查后重新设置;";
}
}
break;
}
case 4:{
if (StringUtil.isNotBlank(cellString)) {
Date birthday = ft.parse(cellString);
mapUser.put("birthday", birthday);
companyMemberUser.setBirthday(birthday);
}
break;
}
case 5:{
if (StringUtil.isNotBlank(cellString)) {
Date entryTime = ft.parse(cellString);
mapUser.put("entryTime", entryTime);
companyMemberUser.setEntryTime(entryTime);
}
break;
}
case 6:{
if (StringUtil.isNotBlank(cellString)) {
mapUser.put("position", cellString);
companyMemberUser.setPosition(cellString);
}
break;
}
//无需校验,归入default
default: {
break;
}
}
}
if (reqFlag) {//校验通过
mapUser.put("id", companyMemberUser.getId());
mapUser.put("companyMemberId", companyMemberId);
mapUser.put("amindId", mapCompany.get("adminId"));
mapUser.put("agentId", mapCompany.get("agentId"));
mapUser.put("createTime", now);
mapUser.put("updateTime", now);
mapUser.put("level", 1);
mapUser.put("isDelete", 0);
companyMemberUser.setCompanyMemberId(companyMemberId);
companyMemberUser.setAdminId(mapCompany.get("adminId"));
companyMemberUser.setAgentId(mapCompany.get("agentId"));
companyMemberUser.setCreateTime(now);
companyMemberUser.setUpdateTime(now);
companyMemberUser.setLevel(1);
companyMemberUser.setIsDelete(0);
//入库
try{
int result=companyMemberUserMapper.insertSelective(companyMemberUser);//添加员工
if(result==1){
sucNum+=1;
}
}catch(Exception e){
e.printStackTrace();
}
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}
//入库
/*
try {
if (mapList.size() > 0 && reqFlag) {
companyMemberUserMapper.insertCompanyMemberUsers(mapList);//批量添加员工
}
/*
else {
if (error > 10) {//设置要显示的错误数
//int index = StringUtil.getIndex(reqMsg, 10, "<br>");
//reqMsg = reqMsg.substring(0, index + 4);
reqMsg += "未显示错误数:" + (error - 10) + "条";
reqMsg += "<br>";
}
}
} catch (Exception e) {
rusultMap.put("code", "-1");
rusultMap.put("errmsg","入库失败,请联系管理员");
e.printStackTrace();
}
*/
resultMap.put("errNum", errorNum);
resultMap.put("errmsg",reqMsg);
resultMap.put("sucNum",sucNum);
return resultMap;
}
}
2.dao层接口
@Mapper
public interface CompanyMemberUserMapper {
int insertSelective(CompanyMemberUser record);
@Select("select admin_id adminId,agent_id agentId from cloud_gift.tb_company_member where id = #{companyMemberId}")
Map<String, String> findIdByCompanyMemberId(String companyMemberId);
@Select("select * from cloud_gift.tb_company_member_user where mobile = #{mobile} and admin_id = #{adminId} and is_delete = 0 limit 1")
CompanyMemberUser findCompanyMemberUserByMobile(@Param("mobile")String mobile,@Param("adminId") String adminId);
}
3.mybatis.xml 对应的插入到数据库的xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.rntd.pc.shop.mapper.CompanyMemberUserMapper">
<insert id="insertSelective" parameterType="cn.rntd.vo.gift.CompanyMemberUser">
insert into cloud_gift.tb_company_member_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="companyMemberId != null">
company_member_id,
</if>
<if test="userName != null">
user_name,
</if>
<if test="mobile != null">
mobile,
</if>
<if test="mailbox != null">
mailbox,
</if>
<if test="createTime != null">
create_time,
</if>
<if test="updateTime != null">
update_time,
</if>
<if test="level != null">
level,
</if>
<if test="adminId != null">
admin_id,
</if>
<if test="agentId != null">
agent_id,
</if>
<if test="isDelete != null">
is_delete,
</if>
<if test="birthday != null">
birthday,
</if>
<if test="sex != null">
sex,
</if>
<if test="position != null">
position,
</if>
<if test="entryTime != null">
entry_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=VARCHAR},
</if>
<if test="companyMemberId != null">
#{companyMemberId,jdbcType=VARCHAR},
</if>
<if test="userName != null">
#{userName,jdbcType=VARCHAR},
</if>
<if test="mobile != null">
#{mobile,jdbcType=VARCHAR},
</if>
<if test="mailbox != null">
#{mailbox,jdbcType=VARCHAR},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="level != null">
#{level,jdbcType=INTEGER},
</if>
<if test="adminId != null">
#{adminId,jdbcType=VARCHAR},
</if>
<if test="agentId != null">
#{agentId,jdbcType=VARCHAR},
</if>
<if test="isDelete != null">
#{isDelete,jdbcType=INTEGER},
</if>
<if test="birthday != null">
#{birthday,jdbcType=TIMESTAMP},
</if>
<if test="sex != null">
#{sex,jdbcType=VARCHAR},
</if>
<if test="position != null">
#{position,jdbcType=VARCHAR},
</if>
<if test="entryTime != null">
#{entryTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
</mapper>
前端调用该接口返回结果:
如:
方式二:通过poi jar包来解析xls文件