批量导入Excle表中的数据

工具类

package com.lenovo.tdp.appauthz.common.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
 
/**
 * 新增值班排班表导入Excel表工具类
 *
 */
public class ImportExcelUtil {
    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel
 
    /**
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象
     * @param in,fileName
     * @return
     * @throws Exception
     */
    public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
        List<List<Object>> list = null;
 
        //创建Excel工作薄
        Workbook work = ImportExcelUtil.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
 
        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}
 
            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {
                boolean isNullLine = true;
                row = sheet.getRow(j);
                //if(row==null||row.getFirstCellNum()==j){continue;}
                 if(row==null){continue;}
                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = 0; y < 3; y++) {
                    cell = row.getCell(y);
                    if (cell == null || StringUtils.isBlank(ImportExcelUtil.getCellValue(cell).toString())) {
                        li.add("");
                    } else {
                        isNullLine = false;
                        li.add(ImportExcelUtil.getCellValue(cell));
                    }
                }
                if (isNullLine) {
                    continue;
                } else {
                    list.add(li);
                }
            }
 
        }
//        work.close();
        return list;
    }
 
    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * @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;
    }
 
    /**
     * 描述：对表格中数值进行格式化
     * @param cell
     * @return
     */
    public  static Object getCellValue(Cell cell){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
//        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
 
        if (cell!=null){
            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 = sdf.format(cell.getDateCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    break;
            }
        }
 
        return value;
    }
}

1、controller 

   @ApiOperation(value = "批量录入账号并赋予指定角色", notes = "批量录入账号并赋予指定角色")
    @PostMapping("/upload")
    public Result<String> excelProTbZbzs(@RequestPart("multipartFile") MultipartFile file) throws Exception {
        String result = authUserService.importUserTemplate(file);
        return Result.success(result);
    }

 2、service

多线程执行任务

//用户信息导入
    String importUserTemplate(MultipartFile file) throws Exception;

@Override
    public String importUserTemplate(MultipartFile file) throws Exception {
        List<UserRoleDTO> userRoles = new ArrayList<>();
        //验证文件类型
        ExceptionAssert.assertIsFalse(!file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equals(".xls")
                        && !file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equals(".xlsx"),
                BusinessException.BusinessExceptionEnum.FILE_FORMAT_ERROR.getException());
        //获取数据
        List<List<Object>> olist = ImportExcelUtil.getListByExcel(file.getInputStream(), file.getOriginalFilename());
        ExceptionAssert.assertIsFalse(CollectionUtils.isEmpty(olist),
                BusinessException.BusinessExceptionEnum.TABLE_IS_EMPTY.getException());
        ExceptionAssert.assertIsFalse(olist.size()==1,
                BusinessException.BusinessExceptionEnum.TABLE_CONTENT_IS_EMPTY.getException());
        ExceptionAssert.assertIsFalse(olist.size() > 1001,
                BusinessException.BusinessExceptionEnum.DATA_SIZE_OVERSTEP.getException());
        //封装数据
        for (int i = 0; i < olist.size(); i++) {
            List<Object> list = olist.get(i);
            if(i==0){
                if ("".equals(list.get(0)) || !"user_id".equals(list.get(0))||"".equals(list.get(1))  || !"role_id".equals(list.get(1))||"".equals(list.get(2))  || !"id_source".equals(list.get(2))) {
                    ExceptionAssert.assertIsFalse(true,
                            BusinessException.BusinessExceptionEnum.FIELD_NAME_ERROR.getException());
                }
            }
            UserRoleDTO userRole = new UserRoleDTO("", "", "",0);
                //根据下标获取每一行的每一条数据
                if(list.get(0).equals("user_id")){
                    continue;
                }
            userRole.setCloumn(i);
                userRole.setUserId("null".equals(String.valueOf(list.get(0)))?"":String.valueOf(list.get(0)));
                userRole.setRoleId("null".equals(String.valueOf(list.get(1)))?"":String.valueOf(list.get(1)));
                userRole.setIdSource("null".equals(String.valueOf(list.get(2)))?"":String.valueOf(list.get(2)));
                if (StringUtils.isBlank(userRole.getUserId()) &&StringUtils.isBlank(userRole.getRoleId()) &&StringUtils.isBlank(userRole.getIdSource()) ) {
                    continue;
                }
                userRoles.add(userRole);
        }
        //最大线程数控制
        int maxthreadNum = 10;
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        // 设置核心线程数
        executor.setCorePoolSize(maxthreadNum);
        // 设置最大线程数
        executor.setMaxPoolSize(20);
        //配置队列大小
        executor.setQueueCapacity(Integer.MAX_VALUE);
        // 设置线程空闲时间，当超过核心线程之外的线程在空闲到达之后会被销毁（秒）
        executor.setKeepAliveSeconds(60);
        // 设置默认线程名称
        executor.setThreadNamePrefix("ThreadExcutor-importUserTemplate");
        // 等待所有任务结束后再关闭线程池
        executor.setWaitForTasksToCompleteOnShutdown(true);
        //拒绝策略
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.AbortPolicy());
        //执行初始化
        executor.initialize();
        int size = userRoles.size();
        int begin = 0;
        int end = size / maxthreadNum;
        int gap = size / maxthreadNum;
        ArrayList<Future<String>> futures = new ArrayList<>();
        CountDownLatch countDownLatch = new CountDownLatch(maxthreadNum);
        StringBuilder stringBuilder = new StringBuilder();
        String clientId = LocalHolder.getClientId();
        for (int i = 0; i < maxthreadNum; i++) {
            //最后一次把剩下的数据都处理掉
            List<UserRoleDTO> userRoleDTOS;
            if (i == maxthreadNum - 1) {
                userRoleDTOS = userRoles.subList(begin, size);
            } else {
                userRoleDTOS = userRoles.subList(begin, end);
            }
            int finalI = i;
            Future<String> submit = executor.submit(new Callable<String>() {
                @Override
                public String call() throws Exception {
                    log.info("importUserTemplate 第{}次批处理开始", finalI);
                    long start = System.currentTimeMillis();
                    String userForImport = createUserForImport(userRoleDTOS,clientId);
                    countDownLatch.countDown();
                    long end = System.currentTimeMillis();
                    log.info("importUserTemplate 第{}次批处理结束,耗时:{}", finalI,end-start);

                    return userForImport;
                }
            });
            futures.add(submit);
            begin = end;
            end = end + gap;
        }
        countDownLatch.await();
        executor.shutdown();
        for (Future<String> future : futures) {
            String s = future.get();
            if (StringUtils.isNotBlank(s)) {
                stringBuilder.append(s).append(";");
            }
        }
        if (stringBuilder.length() > 0) {
            return stringBuilder.substring(0,stringBuilder.length() - 1);
        }
        return "";
    }

创建用户赋予角色的方法 

private String createUserForImport(List<UserRoleDTO> authUserDTOS,String clientId){
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < authUserDTOS.size(); i++) {
            UserRoleDTO userRole = authUserDTOS.get(i);
            AuthUserDTO authUserDTO = new AuthUserDTO();
            authUserDTO.setUserId(userRole.getUserId());
            authUserDTO.setIdpName(userRole.getIdSource());
            authUserDTO.setRoleId(userRole.getRoleId());
            String result = validUser(authUserDTO, userRole.getCloumn());
            if (StringUtils.isNotBlank(result)) {
                sb.append(result).append(";");
                continue;
            }
            //创建用户
            try {
                createOrUpdateUser(authUserDTO,clientId);
            } catch (BusinessException e) {
                e.printStackTrace();
                sb.append(authUserDTO.getUserId()).append("用户已存在,");
            } catch (Exception e) {
                e.printStackTrace();
            }
            //查询用户id
            KeycloakClientUsersManager manager = keycloakManagerService.keycloakManagerWithoutApp().clientUsersManager();
            UserRepresentation users = manager.getOneByUsername(userRole.getUserId());
            //查询用户可用角色
            List<AuthRoleVO> availableList = roles(users.getId(), "client", true, clientId);
            Set<String> collect = availableList.stream().filter(vo -> userRole.getRoleId().equals(vo.getRoleName())).map(AuthRoleVO::getRoleId).collect(Collectors.toSet());
            if (!CollectionUtils.isEmpty(collect)) {
                //为用户赋予角色
                userAddRole(users.getId(), "client", collect, clientId);
            }
        }
        if(sb.length()>0){
            return sb.toString().substring(0,sb.length() - 1);
        }
        return "";
    }

对excle数据进行校验 

private String validUser(AuthUserDTO authUserDTO,int i){
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isBlank(authUserDTO.getUserId())) {
            sb.append("第").append(i+1).append("行").append("user_id为空,");
        } else if (!authUserDTO.getUserId().matches(REGEX)){
            sb.append("第").append(i+1).append("行").append("user_id格式不正确,");
        }
        if (StringUtils.isBlank(authUserDTO.getRoleId())) {
            sb.append("第").append(i+1).append("行").append("role_id为空,");
        } else if (!authUserDTO.getRoleId().contains("_INIT_BASIC") && !authUserDTO.getRoleId().contains("_INIT_DEVELOPER")&&!authUserDTO.getRoleId().contains("_INIT_ADMIN")) {
            sb.append("第").append(i+1).append("行").append("role_id不存在,");
        }
        if (StringUtils.isBlank(authUserDTO.getIdpName())) {
            sb.append("第").append(i+1).append("行").append("id_source为空,");
        } else if (!authUserDTO.getIdpName().contains("ADFS") && !authUserDTO.getIdpName().contains("LENOVOID")) {
            sb.append("第").append(i+1).append("行").append("id_source不存在,");
        }
        if (sb.length() > 0) {
            return sb.toString().substring(0,sb.length() - 1);
        } else {
            return "";
        }
    }