java excel导入并多线程批量插入数据库

蔚落 2022-05-22 04:09 945阅读 0赞

最近写了个excel导入并多线程持久化到数据库的功能，捣鼓了一天才弄好，先记录下来防止自己忘了。

（1）先controller类中方法。

@AccessLog
        @ApiOperation(value = "导入excel", httpMethod = "POST", notes = "导入excel")
        @RequestMapping(value = "/importExcel",method = RequestMethod.POST)
        @ApiImplicitParams({
                @ApiImplicitParam(name="postionId",value="岗位ID",dataType="long", paramType = "query"),
                @ApiImplicitParam(name="typeId",value="类型ID(1：岗位 2：人员)",dataType="int", paramType = "query"),
                @ApiImplicitParam(name="agencyId",value="部门ID",dataType="long", paramType = "query")
        })
        public ResponseResult importExcel(@RequestParam(value="file") MultipartFile file,
                                            Long postionId, Integer typeId, Long agencyId) {
            SelAgencyAndPostionVO selAgencyAndPostionVO = new SelAgencyAndPostionVO(agencyId,postionId,typeId);
            if (null == selAgencyAndPostionVO) {
                return new ResponseResult(ExceptionCode.PARAM_IS_NULL);
            }
            //类型标识（1：岗位 2：人员）
            typeId = selAgencyAndPostionVO.getTypeId();
            if (null == typeId) {
                log.info("1", "typeId is null");
                return new ResponseResult(ExceptionCode.PARAM_IS_NULL);
            }
            //获取上传的文件名称；
            String name = file.getOriginalFilename();
            //判断是否为excel类型文件
            if(!name.endsWith(".xls") && !name.endsWith(".xlsx")){
                log.info("导入的文件不是excel类型");
                return  new ResponseResult<>("导入的文件不是excel类型");
            }
    
            try {
                HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.
                        getRequestAttributes()).getRequest();
    
                //上传至绝对路径
                String path = request.getSession().getServletContext().getRealPath(File.separator);
                String uploadDir = path+"upload"+File.separator;
                log.info(this.getClass().getName()+"临时保存图片路径saveImgUrl："+uploadDir);
    
                File f = new File(uploadDir);
                //如果不存在该路径就创建
                if (!f.exists()) {
                    f.mkdir();
                }
                //获取文件名
                String uuid= new Date().getTime()+"_"+UUID.randomUUID().toString().
                        replace("-","").substring(0,6);
    
                //文件保存绝对路径
                String newName = uploadDir+ uuid + "_"+name;
    
                //上传文件位置
                File dir = new File(uploadDir);
                if (!dir.exists()) {
                    dir.mkdirs();
                }
                File imgFile = new File(newName);
                //存入临时内存
                FileUtils.writeByteArrayToFile(imgFile, file.getBytes());
    
                //获取excel中的数据信息
                List<Map<String, Object>> maps = ImportExcelFileUtil.getDataFromExcel(newName,typeId == 1 ? new ElPositionDTO() :
                                                                                        typeId == 2 ? new ElUserInfoDTO(): null);
                //删除临时保存的图片
                if(imgFile.exists() && imgFile.isFile()) {
                    imgFile.delete();
                }
    
                if (CollectionUtils.isEmpty(maps)) {
                    log.error("ElAttachmentController的importExcel方法获取导入的excel数据为空");
                    return  new ResponseResult<>(ExceptionCode.METHOD_FAILURE);
                }
    
                //获取的是成功插入的次数
                int row = elAgencyPositionUserService.importBatchData(maps,selAgencyAndPostionVO);
                String result = "";
                if ((maps.size() - row) == 0  ) {
                    result = "全部导入成功"+row+"条";
                } else if ((maps.size() - row) > 0) {
                    result ="导入成功"+row+"条，导入失败" + (maps.size() - row) + "条（错误或重复）";
                }
                return new ResponseResult(result);
            }catch(BusinessException e){
                log.error("ElAttachmentController的importExcel方法error"+e.getMessage(),e);
                return new ResponseResult<>(e);
            }catch (Exception e) {
                log.error("ElAttachmentController的importExcel异常"+e.getMessage(), e);
                return new ResponseResult(ExceptionCode.INTERFACE_USE_FAILURE);
            }
        }

（2）InportExcelFileUtil类处理excel文件的信息。此excel方法是通用的方法

package com.xxx.utils;
    
    import com.xxx.dto.ElUserInfoDTO;
    import org.apache.commons.lang3.reflect.FieldUtils;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.formula.functions.T;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.lang.reflect.Field;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * Author xz
     * Date 2018/6/11、9:18
     * Version 1.0
     **/
    public class ImportExcelFileUtil {
        private static final Logger log = LoggerFactory.getLogger(ImportExcelFileUtil.class);
        private final static String excel2003L =".xls";    //2003- 版本的excel
        private final static String excel2007U =".xlsx";   //2007+ 版本的excel
    
        /**
         * 拼装单个obj  通用
         *
         * @param obj
         * @param row
         * @return
         * @throws Exception
         */
        private  static Map<String, Object> dataObj(Object obj, Row row) throws Exception {
            Class<?> rowClazz= obj.getClass();
            Field[] fields = FieldUtils.getAllFields(rowClazz);
            if (fields == null || fields.length < 1) {
                return null;
            }
            //容器
            Map<String, Object> map = new HashMap<String, Object>();
            //注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
            for (int j = 0; j < fields.length; j++) {
                map.put(fields[j].getName(), getVal(row.getCell(j)));
            }
            return map;
        }
        /**
         * 处理val
         *
         * @param cell
         * @return
         */
        public static String getVal(Cell cell) {
            Object value = null;
            DecimalFormat df = new DecimalFormat("0");  //格式化字符类型的数字
            SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
            DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if("General".equals(cell.getCellStyle().getDataFormatString())){
                        value = df.format(cell.getNumericCellValue());
                    }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
                        value = sdf.format(cell.getDateCellValue());
                    }else{
                        value = df2.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    break;
            }
            return value.toString();
        }
        /**
         * * 读取出filePath中的所有数据信息
         *
         * @param filePath excel文件的绝对路径
         * @param obj
         * @return
         */
        public static List<Map<String, Object>> getDataFromExcel(String filePath, Object obj){
    
            if (null == obj) {
                return null;
            }
            List<Map<String, Object>> ret = null;
            FileInputStream fis =null;
            Workbook wookbook = null;
            int lineNum = 0;
            Sheet sheet = null;
            try{
                //获取一个绝对地址的流
                fis = new FileInputStream(filePath);
                wookbook = getWorkbook(fis,filePath);
                //得到一个工作表
                sheet = wookbook.getSheetAt(0);
                //获得表头
                Row rowHead = sheet.getRow(0);
                //列数
                int rows = rowHead.getPhysicalNumberOfCells();
                //行数
                lineNum = sheet.getLastRowNum();
                if(0 == lineNum){
                    log.info("ImportExcelFileUtil中的getDataFromExcel方法导入的Excel内没有数据！");
                }
                ret = getData(sheet, lineNum, rows, obj);
            } catch (Exception e){
                e.printStackTrace();
            }
            return ret;
        }
       
    
    
         * @param obj
         * @return
         */
        public static List<Map<String, Object>>  getData(Sheet sheet, int lineNum, int rowNum, Object obj){
            List<Map<String, Object>> ret = null;
            try {
                //容器
                ret = new ArrayList<Map<String, Object>>();
                //获得所有数据
                for(int i = 1; i <= lineNum; i++){
                    //获得第i行对象
                    Row row = sheet.getRow(i);
                    if(row!=null){
                        //装载obj
                        ret.add(dataObj(obj,row));
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return ret;
        }
    
        /**
         * 描述：根据文件后缀，自适应上传文件的版本
         *
         * @param inStr,fileName
         * @return
         * @throws Exception
         */
        public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{
            Workbook wb = null;
            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if(excel2003L.equals(fileType)){
                wb = new HSSFWorkbook(inStr);  //2003-
            }else if(excel2007U.equals(fileType)){
                wb = new XSSFWorkbook(inStr);  //2007+
            }else{
                throw new Exception("解析的文件格式有误！");
            }
            return wb;
        }
    
        public static void main(String[] args) throws Exception{
            ElUserInfoDTO dto = new ElUserInfoDTO();
            List<Map<String, Object>> dataFromExcel = getDataFromExcel("D:\\img\\测试4.xls", dto);
            for (int i = 0; i < dataFromExcel.size(); i++) {
                for (Map.Entry<String, Object> entry : dataFromExcel.get(i).entrySet()) {
                    System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
                }
            }
            System.out.println(dataFromExcel);
        }
    }

（3）创建多线程，并计算线程数，此实现的线程是Call,为了可以返回成功的结果

public int importBatchData(List<Map<String, Object>> list,SelAgencyAndPostionVO selAgencyAndPostionVO) {
            //部门主键ID
            Long agencyId = selAgencyAndPostionVO.getAgencyId();
            //类型ID（1：岗位 2：人员 ）
            Integer typeId = selAgencyAndPostionVO.getTypeId();
            //岗位主键ID
            Long postionId = selAgencyAndPostionVO.getPostionId();
    
            int row = 0;
            try {
                if (typeId == 1) {
                    row = savePositionInfoList(list,agencyId);
                } else if (typeId == 2) {
                    Long orgId = elAppInfoService.getOrg().getOrgId();
                   //在导入之前，把同orgId下的用户全部放进缓存，防止重复导入
                    ElUserInfo elUserInfo = new ElUserInfo();
                    elUserInfo.setOrgId(orgId);
                    List<ElUserInfo> users = userInfoMapper.getUsersByOrgId(elUserInfo);
                    //Redis的key值
                    String userCodeKey = AppConstants.Flag.USERKEY + orgId;
                    //存入Redis之前，把之前的清空
                    redisCacheService.deleteRedisCacheByKey(userCodeKey);
                    //规则：key==>"userCode"+orgId,value==>users   存入Redis
                    redisCacheService.setRedisCacheByKey(userCodeKey,JSON.toJSONString(users),3L,TimeUnit.MINUTES);
                    row = saveUserInfoList(list,agencyId,postionId);
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            }
            return row;

public int saveUserInfoList(List<Map<String, Object>> list, Long agencyId, Long postionId) {
            Integer row = 1;
            Integer successCount = 0;
            int count = 50;// 一个线程处理50条数据
            int listSize = list.size();// 数据集合大小
            int runThreadSize = (listSize / count) + 1; // 开启的线程数
            List<Map<String, Object>> newlist = null;// 存放每个线程的执行数据
            ExecutorService executor = Executors.newFixedThreadPool(runThreadSize);// 创建一个线程池，数量和开启线程的数量一样
    
            // 创建两个个计数器
            CountDownLatch begin = new CountDownLatch(1);
            CountDownLatch end = new CountDownLatch(runThreadSize);
            // 循环创建线程
            for (int i = 0; i < runThreadSize; i++) {
                if ((i + 1) == runThreadSize) {
                    int startIndex;
                    startIndex = (i * count);
                    int endIndex = list.size();
                    newlist = list.subList(startIndex, endIndex);
                } else {
                    int startIndex = (i * count);
                    int endIndex = (i + 1) * count;
                    newlist = list.subList(startIndex, endIndex);
                }
    
                //线程类，处理数据持久化
                UserInfoThread userInfoThread = new UserInfoThread(newlist,begin,end,agencyId,postionId);
                //executor.execute(userInfoThread);
                Future<Integer> submit = executor.submit(userInfoThread);
                try {
                    //提交成功的次数
                    row = submit.get();
                    successCount += row;
                } catch (InterruptedException e1) {
                    log.error("ElAgencyPositionUserServiceImpl的saveUserInfoList方法error"+e1.getMessage(),e1);
                } catch (ExecutionException e2) {
                    log.error("ElAgencyPositionUserServiceImpl的saveUserInfoList方法error"+e2.getMessage(),e2);
                }
            }
            try{
                begin.countDown();
                end.await();
                //执行完关闭线程池
                executor.shutdown();
            }catch (Exception e) {
                log.error("ElAgencyPositionUserServiceImpl的saveUserInfoList方法error"+e.getMessage(),e);
            }
            return successCount;
        }

（4）UserInfoThread具体实现业务

package com.xxx.service;
    
    import com.alibaba.fastjson.JSON;
    import com.xxx.utils.redis.RedisCacheService;
    import com.xxx.bean.ElUserInfo;
    import com.xxx.common.AppConstants;
    import com.xxx.dao.UserInfoMapper;
    import com.xxx.utils.SpringUtil;
    import org.apache.commons.collections.CollectionUtils;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.util.StringUtils;
    
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    import java.util.concurrent.Callable;
    import java.util.concurrent.CountDownLatch;
    
    import static com.xxx.utils.MD5Utils.MD5;
    
    /**
     * Author xz
     * Date 2018/6/11、17:24
     * Version 1.0
     **/
    public class UserInfoThread implements Callable<Integer> {
    
        private static final Logger log = LoggerFactory.getLogger(PostionThread.class);
        private List<Map<String, Object>> list;
        private CountDownLatch begin;
        private CountDownLatch end;
        private Long agencyId;
        private Long postionId;
        private UserInfoMapper userInfoMapper;
        private OrgEmployeeService orgEmployeeService;
        private RedisCacheService redisCacheService;
        private ElAppInfoService elAppInfoService;
    
        //创建个构造函数初始化 list,和其他用到的参数
        public UserInfoThread(List<Map<String, Object>> list,CountDownLatch begin,CountDownLatch end, Long agencyId, Long postionId){
            this.list = list;
            this.begin = begin;
            this.end = end;
            this.agencyId = agencyId;
            this.postionId = postionId;
            userInfoMapper = (UserInfoMapper)SpringUtil.getBean("userInfoMapper");
            orgEmployeeService = (OrgEmployeeService)SpringUtil.getBean(OrgEmployeeService.class);
            redisCacheService = (RedisCacheService)SpringUtil.getBean(RedisCacheService.class);
            elAppInfoService = (ElAppInfoService)SpringUtil.getBean(ElAppInfoService.class);
    
        }
    
        @Override
        public Integer call(){
            int row = 0;
            try {
                List<ElUserInfo>  userList = new ArrayList<ElUserInfo>();
                if (CollectionUtils.isNotEmpty(list)) {
                    //组织id
                    Long orgId = elAppInfoService.getOrg().getOrgId();
                    A:for (int i = 0; i < list.size(); i++) {
                        Map<String, Object> map = list.get(i);
                        String userSex = map.get("userSex").toString().trim();
                        String userName = map.get("userName").toString().trim();
                        String userTel = map.get("userTel").toString().trim();
                        String passWord = map.get("passWord").toString().trim();
                        String key = AppConstants.Flag.USERKEY+orgId;
    
                        //导入的人员信息字段有一个是为空的，就不持久化数据库。
                        if (StringUtils.isEmpty(userSex)) {
                            continue;
                        }
                        if (StringUtils.isEmpty(userName)) {
                            continue;
                        }
                        if (StringUtils.isEmpty(userTel)) {
                            continue;
                        }
                        if (StringUtils.isEmpty(passWord)) {
                            continue;
                        }
    
                        //获取的是数据库存在的同orgId下用户信息，以json字符串形式
                        String userListValue = redisCacheService.getRedisCacheByKey(key);
                        //把json字符串转ElUserInfo用户对象
                        List<ElUserInfo> elUserInfos = JSON.parseArray(userListValue, ElUserInfo.class);
                        //去重，若有重复的就结束此次循环
                        for (ElUserInfo userInfo: elUserInfos) {
                            if (userTel.equals(userInfo.getUserTel())) {
                                continue A;
                            }
                        }
    
                        if ("男".equals(userSex)) {
                            userSex = "0";
                        } else if ("女".equals(userSex)){
                            userSex = "1";
                        }
                        ElUserInfo user = new ElUserInfo();
                        user.setUserName(userName);
                        user.setUserTel(userTel);
                        user.setPassWord(MD5(passWord));
                        user.setUserSex(userSex);
                        user.setPositionId(postionId);
                        user.setAgencyId(agencyId);
                        user.setCreateDate(new Date());
                        user.setUpdateDate(new Date());
                        user.setDelMark(0);
                        user.setRoleId(1L);
                        user.setEmployeeId(0L);
                        user.setOrgId(orgId);
                        userList.add(user);
                    }
                    if (CollectionUtils.isNotEmpty(userList)) {
                        //先持久化本地
                        row = userInfoMapper.createBatchUserInfoList(userList);
                        if (row > 0) {
                            //持久化成功后同步组织平台
                            String add = orgEmployeeService.addOrganRoleUserToPlatform(userList, "add");
                            if (!StringUtils.isEmpty(add) && !"-1".equals(add) && !"null".equals(add)) {
                                //同步成功后,修改OrgId和EmployeeId
                                for (ElUserInfo user : userList) {
                                    user.setEmployeeId(1L);
                                }
                                //以用户手机号码为唯一标示，批量修改OrgId和EmployeeId
                                userInfoMapper.updateBatchOrgId(userList);
                            }
                            log.info(this.getClass().getName()+"的UserInfoThread"+add.toString());
                        }
                    }
                }
                //....
                //执行完让线程直接进入等待
                // begin.await();
            } catch (Exception e) {
                log.error("elPositionInfoServiceImpl的UserInfoThread方法error"+e.getMessage(),e);
            }finally{
                //这里要主要了，当一个线程执行完了计数要减一不要这个线程会被一直挂起
                //，end.countDown()，这个方法就是直接把计数器减一的
                end.countDown();
            }
            return row;
        }
    }

@Component
    public class SpringUtil implements ApplicationContextAware {
    
        private static ApplicationContext applicationContext;
    
        @Override
        public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
            if(SpringUtil.applicationContext == null) {
                SpringUtil.applicationContext = applicationContext;
            }
        }
    
        //获取applicationContext
        public static ApplicationContext getApplicationContext() {
            return applicationContext;
        }
    
        //通过name获取 Bean.
        public static Object getBean(String name){
            return getApplicationContext().getBean(name);
        }
    
        //通过class获取Bean.
        public static <T> T getBean(Class<T> clazz){
            return getApplicationContext().getBean(clazz);
        }
    
        //通过name,以及Clazz返回指定的Bean
        public static <T> T getBean(String name,Class<T> clazz){
            return getApplicationContext().getBean(name, clazz);
        }
    
    }

（5）最后，欢迎大家关注【**码农新锐**】公众号，加入我们一起来进阶Java。

![70][]

[70]: /images/20220522/00ffff18a99341448ac746c33a5b652c.png