Java - excel文件导入并插入到数据库

谁借莪１个温暖的怀抱￠ 2022-11-29 11:59 180阅读 0赞

**目录：**

1.  方式一：通过jxl jar包来解析xls文件
2.  方式二：通过poi jar包来解析xls文件[https://blog.csdn.net/weixin\_43923436/article/details/90605636][https_blog.csdn.net_weixin_43923436_article_details_90605636]
3.  个人建议使用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包

<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>

前端调用该接口返回结果：

如：

![20200820150808484.png][]

###  方式二：通过poi jar包来解析xls文件 ###

[https_blog.csdn.net_weixin_43923436_article_details_90605636]: https://blog.csdn.net/weixin_43923436/article/details/90605636
[20200820150808484.png]: /images/20221124/4921f2b0aa644ab5baa9f9b876f0df95.png