Excel导入实例-2021/12/23

@Positive校验参数必须是正整数

Validation 注解

@AssertFalse 被注解的元素必须为false
@AssertTrue 被注解的元素必须为True
@DecimalMax(value) 被注解的元素必须为一个数字，其值必须小于等于指定的最小值
@DecimalMin(Value) 被注解的元素必须为一个数字，其值必须大于等于指定的最小值
@Digits(integer=, fraction=) 被注解的元素必须为一个数字，其值必须在可接受的范围内
@Future 被注解的元素必须是日期，检查给定的日期是否比现在晚.
@Max(value) 被注解的元素必须为一个数字，其值必须小于等于指定的最小值,检查该值是否小于或等于约束条件中指定的最大值. 会给对应的数据库表字段添加一个 check的约束条件.
@Min BigDecimal，BigInteger, byte,short, int, long，等任何Number或CharSequence（存储的是数字）子类型 验证注解的元素值大于等于@Min指定的value值
@NotNull 被注解的元素必须不为null
@Null 被注解的元素必须为null
@Past(java.util.Date/Calendar); 被注解的元素必须过去的日期，检查标注对象中的值表示的日期比当前早.
@Pattern(regex=, flag=) 被注解的元素必须符合正则表达式，检查该字符串是否能够在match指定的情况下被regex定义的正则表达式匹配.
@Size(min=, max=) 被注解的元素必须在制定的范围(数据类型:String, Collection, Map and arrays)
@Valid 递归的对关联对象进行校验, 如果关联对象是个集合或者数组, 那么对其中的元素进行递归校验,如果是一个map,则对其中的值部分进行校验.
@CreditCardNumber 对信用卡号进行一个大致的验证
@Email 被注释的元素必须是电子油箱地址
@NotEmpty 被注释的对象必须为空(数据:String,Collection,Map,arrays)
@Range(min=, max=) 被注释的元素必须在合适的范围内 (数据：BigDecimal, BigInteger, String, byte, short, int, long and 原始类型的包装类 )
@SafeHtml(whitelistType=, additionalTags=)被注解的元素检查是否标注值包含恶意的片段如
@ScriptAssert(lang=, script=, alias=) 任何类型,要使用这个约束条件,必须先要保证Java Scripting API 即JSR 223 (“Scripting for the JavaTM Platform”)的实现 在类路径当中. 如果使用的时Java 6的话,则不是问题, 如果是老版本的话, 那么需要把 JSR 223的实现添加进类路径. 这个约束条件中的表达式可以使用任何兼容JSR 223的脚本来编写. (更多信息请参考javadoc)
@URL(protocol=, host=, port=, regexp=, flags=) 被注解的对象必须是字符串，检查是否是一个有效的URL，如果提供了protocol，host等，则该URL还需满足提供 的条件
@Length(min=下限, max=上限) CharSequence子类型 验证注解的元素值长度在min和max区间内
@NotBlank CharSequence子类型 验证注解的元素值不为空（不为null、去除首位空格后长度为0），不同于@NotEmpty，@NotBlank只应用于字符串且在比较时会去除字符串的首位空格
@FutureOrPresent
@Negative
@NegativeOrZero
@PastOrPresent
@Positive
@PositiveOrZero
@CreditCardNumber(ignoreNonDigitCharacters=)
@Currency(value=)
@DurationMax(days=, hours=, minutes=, seconds=, millis=, nanos=,inclusive=)
@DurationMin(days=, hours=, minutes=, seconds=, millis=, nanos=,inclusive=)
@EAN
@ISBN

StringUtils.isBlank（）

判断某字符串是否为空或长度为0或由空白符(whitespace)构成

StringUtils.isNotBlank（）

判断某字符串是否不为空且长度不为0且不由空白符(whitespace)构成，等于!isBlank(String str)

Map的 getOrDefault() 方法

返回 key 相映射的的 value，如果给定的 key 在映射关系中找不到，则返回指定的默认值。

实例

import java.util.HashMap;

class Main {
    public static void main(String[] args) {
        // 创建一个 HashMap
        HashMap<Integer, String> sites = new HashMap<>();

        // 往 HashMap 添加一些元素
        sites.put(1, "Google");
        sites.put(2, "Runoob");
        sites.put(3, "Taobao");
        System.out.println("sites HashMap: " + sites);

        // key 的映射存在于 HashMap 中
        // Not Found - 如果 HashMap 中没有该 key，则返回默认值
        String value1 = sites.getOrDefault(1, "Not Found");
        System.out.println("Value for key 1:  " + value1);

        // key 的映射不存在于 HashMap 中
        // Not Found - 如果 HashMap 中没有该 key，则返回默认值
        String value2 = sites.getOrDefault(4, "Not Found");
        System.out.println("Value for key 4: " + value2);
    }
}

List 转化为数组

方式1

 List<Map<String, Object>> deviceTypeList = getDeviceTypeList(companyId);
        if (deviceTypeList != null && !deviceTypeList.isEmpty() && deviceTypeList.get(0) != null) {
            String[] deviceTypeArray = deviceTypeList.stream().map(e -> e.get("name").toString()).toArray(String[]::new);
            ExcelUtil.addValidationToSheet(workbook,sheet, deviceTypeArray,'B',1,1000);
        }

方式2 遍历

        List<Map<String, Object>> deviceTypeList = getDeviceTypeList(companyId);
        if (deviceTypeList != null && !deviceTypeList.isEmpty() && deviceTypeList.get(0) != null) {
            String[] array = new String[deviceTypeList.size()];
            for (int i = 0; i < deviceTypeList.size(); i++) {
                array[i] = deviceTypeList.get(i).get("name").toString();
            }
            ExcelUtil.addValidationToSheet(workbook,sheet, array,'B',1,1000);
        }

List T 转化为List String

        // List<T> 转化为List<String>
        List<String> collect = deviceList.stream().map(DeviceInfoVo::getDeviceType).collect(Collectors.toList());
        //过滤
        List<String> list = deviceList.stream().filter(e -> e.getPlateNo().equals("1")).map(DeviceInfoVo::getDeviceType).collect(Collectors.toList());
	//或
	List<DeviceInfoVo> collect1 = deviceList.stream().filter(e -> e.getPlateNo().equals("1")).collect(Collectors.toList());

下载模板

大概

@GetMapping("/downDeviceTemplate")
    public ResponseEntity<byte[]> downDeviceTemplate(HttpServletRequest request) {
        String[] a = {"设备编号","设备类型","制造商","条码"};
        String name = "设备信息";
        HSSFWorkbook workbook = ExcelUtil.createExcel(a, name);
        HSSFSheet sheet = workbook.getSheet(name);
        String[] b = {"aa","bb"};  //下拉框
        ExcelUtil.addValidationToSheet(workbook,sheet,b,'B',1,1000);
        Map<String,List<String>> map = new HashMap<>();
        map.put("1", Arrays.asList("cc","dd")); //联动
        map.put("2", Arrays.asList("ff","ee"));
        ExcelUtil.addValidationToSheet(workbook,sheet,map,'C','D',1,1000);

        return ExcelUtil.outputExcel(workbook,request,"设备.xls");
    }

主要方法：

excel模板—日期格式

工具方法

public static <T> void addValueTypeValid(Sheet targetSheet, Class<T> clazz, String dateFormat, char column, int fromRow, int endRow) {
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
        String typeName = clazz.getName();
        if (Objects.equals(typeName, "java.lang.Integer") || Objects.equals(typeName, "java.lang.Long")) {
            String minValue = String.valueOf(Integer.MIN_VALUE);
            String maxValue = String.valueOf(Integer.MAX_VALUE);
            DVConstraint dvConstraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.BETWEEN, minValue, maxValue);
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
            dataValidation.createPromptBox("提示", "请输入整数类型的值");
            dataValidation.setShowPromptBox(true);
            targetSheet.addValidationData(dataValidation);
        } else if (Objects.equals(typeName, "java.lang.Float") || Objects.equals(typeName, "java.lang.Double")) {
            String minValue = String.valueOf(Double.MIN_VALUE);
            String maxValue = String.valueOf(Double.MAX_VALUE);
            DVConstraint dvConstraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, minValue, maxValue);
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
            dataValidation.createPromptBox("提示", "请输入小数类型的值");
            dataValidation.setShowPromptBox(true);
            targetSheet.addValidationData(dataValidation);
        } else if (Objects.equals(typeName, "java.util.Date")) {
            if (Objects.equals(dateFormat, cn.iovnet.commons.utils.util.DateUtil.YYYY_MM_DD_HHMMSS)) {
                DVConstraint dvConstraint = DVConstraint.createDateConstraint(DVConstraint.ValidationType.DATE, "1900-01-01 00:00:00", "2999-12-31 23:59:59", dateFormat);
                HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
                dataValidation.createPromptBox("提示", "请输入[" + dateFormat + "]格式的值");
                dataValidation.setShowPromptBox(true);
                targetSheet.addValidationData(dataValidation);
            } else if (Objects.equals(dateFormat, cn.iovnet.commons.utils.util.DateUtil.YYYY_MM_DD)) {
                DVConstraint dvConstraint = DVConstraint.createDateConstraint(DVConstraint.ValidationType.DATE, "1900-01-01", "2999-12-31", dateFormat);
                HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
                dataValidation.createPromptBox("提示", "请输入[" + dateFormat + "]格式的值");
                dataValidation.setShowPromptBox(true);
                targetSheet.addValidationData(dataValidation);
            } else if (Objects.equals(dateFormat, cn.iovnet.commons.utils.util.DateUtil.HH_MM_SS)) {
                DVConstraint dvConstraint = DVConstraint.createTimeConstraint(DVConstraint.ValidationType.TIME, "00:00:00", "23:59:59");
                HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
                dataValidation.createPromptBox("提示", "请输入[" + dateFormat + "]格式的值");
                dataValidation.setShowPromptBox(true);
                targetSheet.addValidationData(dataValidation);
            }
        }
    }

使用

		ExcelUtil.addValueTypeValid(sheet,Date.class,"yyyy-MM-dd HH:mm:ss",'F',1,1000);
        ExcelUtil.addValueTypeValid(sheet,Date.class,"yyyy-MM-dd HH:mm:ss",'P',1,1000);
        ExcelUtil.addValueTypeValid(sheet,Date.class,"yyyy-MM-dd",'R',1,1000);

ExcelUtil

public static HSSFWorkbook createExcel(String[] titleArray, String sheetName) {
        //创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style = workbook.createCellStyle();
        //字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);

        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置背景颜色
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        // solid 填充  foreground  前景色
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 表头
        HSSFRow excelRoot = sheet.createRow(0);
        // 此处设置数据格式
        for (int i = 0; i < titleArray.length; i++) {
            HSSFCell rootCell = excelRoot.createCell(i);
            rootCell.setCellValue(titleArray[i]);
            sheet.setColumnWidth(i, 5000);
            rootCell.setCellStyle(style);
        }
        return workbook;
    }

/**
     * 给sheet页，添加下拉列表
     *
     * @param workbook    excel文件
     * @param targetSheet 需要操作的sheet页
     * @param options     下拉列表数据
     * @param column      下拉列表所在列 从'A'开始
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
        if (options != null && options.length > 0) {
            String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
            Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
            String nameName = column + "_parent";

            int rowIndex = 0;
            for (Object option : options) {
                int columnIndex = 0;
                Row row = optionsSheet.createRow(rowIndex++);
                Cell cell = row.createCell(columnIndex++);
                cell.setCellValue(option.toString());
            }
            createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);
            DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
            CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
            targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
            // 隐藏sheet页
            int sheetIndex = workbook.getSheetIndex(optionsSheet);
            workbook.setSheetHidden(sheetIndex, true);
        }
    }


/**
     * 给sheet页  添加级联下拉列表
     *
     * @param workbook    excel
     * @param targetSheet 需要操作的sheet页
     * @param options     要添加的下拉列表内容
     * @param keyColumn   下拉列表1位置
     * @param valueColumn 级联下拉列表位置
     * @param fromRow     级联限制开始行
     * @param endRow      级联限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
        if (options != null && !options.isEmpty()) {
            String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
            Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
            List<String> firstLevelItems = new ArrayList<>();

            int rowIndex = 0;
            for (Map.Entry<String, List<String>> entry : options.entrySet()) {
                String parent = formatNameName(entry.getKey());
                firstLevelItems.add(parent);
                List<String> children = entry.getValue();

                int columnIndex = 0;
                Row row = hiddenSheet.createRow(rowIndex++);
                Cell cell = null;

                for (String child : children) {
                    cell = row.createCell(columnIndex++);
                    cell.setCellValue(child);
                }
                char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
                createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));

                DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
                CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
                targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
            }
            addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);
            // 隐藏sheet页
            int sheetIndex = workbook.getSheetIndex(hiddenSheet);
            workbook.setSheetHidden(sheetIndex, true);
        }
    }


/**
     * 输出创建的Excel文件
     */
    public static ResponseEntity<byte[]> outputExcel(Workbook workbook, HttpServletRequest request, String fileName) {
        //设置头信息
        HttpHeaders headers = new HttpHeaders();
        //设置响应的文件名
        String downloadFileName = DownloadUtil.getEncodedFilename(request, fileName);
        headers.setContentDispositionFormData("attachment", downloadFileName);
        headers.add("Access-Control-Expose-Headers", "filename");
        headers.add("filename", downloadFileName);
        //application/octet-stream二进制流数据的形式下载
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        try {
            workbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                byteArrayOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

实例

下载模板

controller

/**
     * 下载车载设备信息模板
     * @param request
     * @return org.springframework.http.ResponseEntity<byte[]>
     * @author guozh
     * @date 2021/12/23 16:21
     */
    @GetMapping("/downDeviceTemplate")
    public ResponseEntity<byte[]> downDeviceTemplate(HttpServletRequest request) {
        Integer companyId = Integer.valueOf(HttpUtil.getCompanyId(request));
        return deviceService.downDeviceTemplate(companyId,request);
    }

serviceImpl

@Override
    public ResponseEntity<byte[]> downDeviceTemplate(Integer companyId, HttpServletRequest request) {
        String fileName = "车载设备信息模板.xls";
        String[] titleArray = {"设备编号(必填)","设备类型","制造商","条码","设备状态","安装时间","车牌号","SIM卡号(必填)","营运商","账户状态(0：已开户，1：已销户)",
                "摄像头数量","是否视频(0：否，1：是)","协议类型(必填)","套餐流量","SMSI卡号","开卡时间","结算方式","结算日期","备注"};
        String sheetName = "车载设备信息";
        HSSFWorkbook workbook = ExcelUtil.createExcel(titleArray, sheetName);
        HSSFSheet sheet = workbook.getSheet(sheetName);
        //设备类型下拉框
        List<Map<String, Object>> deviceTypeList = getDeviceTypeList(companyId);
        if (deviceTypeList != null && !deviceTypeList.isEmpty() && deviceTypeList.get(0) != null) {
            String[] deviceTypeArray = deviceTypeList.stream().map(e -> e.get("name").toString()).toArray(String[]::new);
            ExcelUtil.addValidationToSheet(workbook,sheet,deviceTypeArray,'B',1,1000);
        }
        //设备状态下拉框
        List<Map<String, Object>> deviceStateList = getDeviceStateList(companyId);
        if (deviceStateList != null && !deviceStateList.isEmpty() && deviceStateList.get(0) != null){
            String[] deviceStateArray = deviceStateList.stream().map(e -> e.get("name").toString()).toArray(String[]::new);
            ExcelUtil.addValidationToSheet(workbook,sheet,deviceStateArray,'E',1,1000);
        }
        //协议类型下拉框
        List<Map<String, Object>> protocolTypeList = getProtocolTypeList(companyId);
        if (protocolTypeList != null && !protocolTypeList.isEmpty() && protocolTypeList.get(0) != null){
            String[] protocolTypeArray = protocolTypeList.stream().map(e -> e.get("name").toString()).toArray(String[]::new);
            ExcelUtil.addValidationToSheet(workbook,sheet,protocolTypeArray,'M',1,1000);
        }
        //账户状态选择
        String[] accountState = {"已开户","已销户"};
        ExcelUtil.addValidationToSheet(workbook,sheet,accountState,'J',1,1000);
        //是否视频选择
        String[] canVideo = {"是","否"};
        ExcelUtil.addValidationToSheet(workbook,sheet,canVideo,'L',1,1000);
        return ExcelUtil.outputExcel(workbook,request,"车载设备信息.xls");
    }

效果

导入（解析）

Controller

 @PostMapping("/importDeviceData")
    public CommonResponse importDeviceData(MultipartFile file,HttpServletRequest request) {
        CommonResponse instance = CommonResponse.getInstance();
        Integer companyId = Integer.valueOf(HttpUtil.getCompanyId(request));
        ImportResultVo<ImportDeviceVo> result = deviceService.importDeviceData(companyId,file);
        instance.setResultData(result);
        IovnetLog log = IovnetLogUtil.getIovnetLog(request, DdyModuleNames.DEVICE_MANAGE_ID, DdyModuleNames.DEVICE_MANAGE_NAME, DdyModuleNames.DEVICE_ID, DdyModuleNames.DEVICE_NAME, "车载设备-导入设备信息(解析数据)");
        instance.setIovnetLog(log);
        return instance;
    }

ServiceImpl

@Override
    public ImportResultVo<ImportDeviceVo> importDeviceData(Integer companyId, MultipartFile file) {
        ImportResultVo<ImportDeviceVo> importResultVo = new ImportResultVo<>();
        List<ImportDeviceVo> deviceVoList = ExcelUtil.parseExcelData(file, 0, 1, 2, ImportDeviceVo.class);
        if (!deviceVoList.isEmpty()){
            //正常数据
            List<ImportDeviceVo> normalList = new ArrayList<>();
            //错误信息
            List<Map<String,Object>> errorMessageList = new ArrayList<>();
            //上传总条数
            importResultVo.setTotalNum(deviceVoList.size());

            //验证数据
            for (int i = 0; i < deviceVoList.size(); i++) {

                Map<String, String> deviceTypeMap = getDeviceTypeMap(companyId);
                Map<String, String> deviceStateMap = getDeviceStateMap(companyId);
                Map<String, String> protocolTypeMap = getProtocolTypeMap(companyId);

                ImportDeviceVo importDeviceVo = deviceVoList.get(i);
                importDeviceVo.setCompanyId(companyId);

                //该条数据是否正常(true-正常,false-异常)
                boolean flag = true;

                //设备类型
                String deviceTypeName = deviceVoList.get(i).getDeviceType();
                if (StringUtils.isNotBlank(deviceTypeName)){
                    importDeviceVo.setDeviceTypeCode(deviceTypeMap.getOrDefault(deviceTypeName,null));
                }
                //设备状态
                String deviceStateName = deviceVoList.get(i).getDeviceState();
                if (StringUtils.isNotBlank(deviceStateName)){
                    importDeviceVo.setDeviceStateCode(deviceStateMap.getOrDefault(deviceStateName,null));
                }
                //协议类型
                String protocolTypeName = deviceVoList.get(i).getProtocolType();
                if (StringUtils.isNotBlank(protocolTypeName)){
                    importDeviceVo.setProtocolTypeCode(protocolTypeMap.getOrDefault(protocolTypeName,null));
                }

                //设备编号 必填
                String deviceNo = deviceVoList.get(i).getDeviceNo();
                if (StringUtils.isBlank(deviceNo)){
                    Map<String,Object> map = new HashMap<>(2);
                    map.put("rowNo",i + 1);
                    map.put("message","设备编号为空");
                    errorMessageList.add(map);
                    flag = false;
                }else {
                    importDeviceVo.setDeviceNo(deviceNo);
                }
                //SIM卡号 必填
                String simNo = deviceVoList.get(i).getSimNo();
                if (StringUtils.isBlank(simNo)){
                    Map<String,Object> map = new HashMap<>(2);
                    map.put("rowNo",i + 1);
                    map.put("message","SIM卡号为空");
                    errorMessageList.add(map);
                    flag = false;
                }else {
                    importDeviceVo.setSimNo(simNo);
                }

                //安装时间
                String installTime = deviceVoList.get(i).getInstallTime();
                if (StringUtils.isNotBlank(installTime)){
                    try {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        sdf.parse(installTime);
                    } catch (ParseException e) {
                        Map<String,Object> map = new HashMap<>(2);
                        map.put("rowNo",i + 1);
                        map.put("message","安装时间格式错误,必须为[yyyy-MM-dd HH:mm:ss]");
                        errorMessageList.add(map);
                        flag = false;
                    }
                }
                //开卡时间
                String openCardTime = deviceVoList.get(i).getOpenCardTime();
                if (StringUtils.isNotBlank(openCardTime)){
                    try {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        sdf.parse(openCardTime);
                    } catch (ParseException e) {
                        Map<String,Object> map = new HashMap<>(2);
                        map.put("rowNo",i + 1);
                        map.put("message","开卡时间格式错误,必须为[yyyy-MM-dd HH:mm:ss]");
                        errorMessageList.add(map);
                        flag = false;
                    }
                }
                //结算日期
                String settlementDate = deviceVoList.get(i).getSettlementDate();
                if (StringUtils.isNotBlank(settlementDate)){
                    try {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        sdf.parse(settlementDate);
                    } catch (ParseException e) {
                        Map<String,Object> map = new HashMap<>(2);
                        map.put("rowNo",i + 1);
                        map.put("message","开卡时间格式错误,必须为[yyyy-MM-dd]");
                        errorMessageList.add(map);
                        flag = false;
                    }
                }

                //账户状态
                String accountStateName = deviceVoList.get(i).getAccountStateName();
                if (StringUtils.isNotBlank(accountStateName)){
                    if (accountStateName.equals("已开户")){
                        importDeviceVo.setAccountState(0);
                    }else {
                        importDeviceVo.setAccountState(1);
                    }
                }
                //是否视频
                String canVideoName = deviceVoList.get(i).getCanVideoName();
                if (StringUtils.isNotBlank(canVideoName)){
                    if (canVideoName.equals("否")){
                        importDeviceVo.setCanVideo(0);
                    }else {
                        importDeviceVo.setCanVideo(1);
                    }
                }

                //摄像头数量
                Integer cameraNum = deviceVoList.get(i).getCameraNum();
                if (cameraNum < 0){
                    Map<String,Object> map = new HashMap<>(2);
                    map.put("rowNo",i + 1);
                    map.put("message","摄像头数量为负数");
                    errorMessageList.add(map);
                    flag = false;
                }else {
                    importDeviceVo.setCameraNum(deviceVoList.get(i).getCameraNum());
                }

                //剩余字段
                importDeviceVo.setManufacturer(deviceVoList.get(i).getManufacturer());
                importDeviceVo.setDeviceCode(deviceVoList.get(i).getDeviceCode());
                importDeviceVo.setPlateNo(deviceVoList.get(i).getPlateNo());
                importDeviceVo.setOperator(deviceVoList.get(i).getOperator());
                importDeviceVo.setPackageFlow(deviceVoList.get(i).getPackageFlow());
                importDeviceVo.setSmsiNo(deviceVoList.get(i).getSmsiNo());
                importDeviceVo.setSettlementType(deviceVoList.get(i).getSettlementType());
                importDeviceVo.setRemark(deviceVoList.get(i).getRemark());

                if (flag){
                    normalList.add(importDeviceVo);
                }

            }
            //正常数据
            importResultVo.setNormalList(normalList);
            importResultVo.setNormalNum(normalList.size());
            //异常数据
            importResultVo.setAbnormalList(errorMessageList);
            importResultVo.setAbnormalNum(deviceVoList.size() - normalList.size());

        }
        return importResultVo;

返回数据vo

泛型为导入数据列表vo

package com.spiov.cloud.schedule.vo;

import java.util.List;
import java.util.Map;

/**
 * 导入返回数据
 *
 * @author guozh
 * @date 2021/12/24 9:04
 */
public class ImportResultVo<T> {

    /**
     * 上传的数据总条数
     */
    private Integer totalNum = 0;
    /**
     * 正常数据条数
     */
    private Integer normalNum = 0;
    /**
     * 异常数据条数
     */
    private Integer abnormalNum = 0;
    /**
     * 异常数据信息
     */
    private List<Map<String, Object>> abnormalList;
    /**
     * 正常数据
     */
    private List<T> normalList;

    public Integer getTotalNum() {
        return totalNum;
    }

    public void setTotalNum(Integer totalNum) {
        this.totalNum = totalNum;
    }

    public Integer getNormalNum() {
        return normalNum;
    }

    public void setNormalNum(Integer normalNum) {
        this.normalNum = normalNum;
    }

    public Integer getAbnormalNum() {
        return abnormalNum;
    }

    public void setAbnormalNum(Integer abnormalNum) {
        this.abnormalNum = abnormalNum;
    }

    public List<Map<String, Object>> getAbnormalList() {
        return abnormalList;
    }

    public void setAbnormalList(List<Map<String, Object>> abnormalList) {
        this.abnormalList = abnormalList;
    }

    public List<T> getNormalList() {
        return normalList;
    }

    public void setNormalList(List<T> normalList) {
        this.normalList = normalList;
    }
}

导入（提交正确信息）

Controller

 @PostMapping("/submitDeviceData")
    public CommonResponse submitDeviceData(@RequestBody List<ImportDeviceVo> vo, HttpServletRequest request) {
        CommonResponse instance = CommonResponse.getInstance();
        deviceService.submitDeviceData(vo);
        IovnetLog log = IovnetLogUtil.getIovnetLog(request, DdyModuleNames.DEVICE_MANAGE_ID, DdyModuleNames.DEVICE_MANAGE_NAME, DdyModuleNames.DEVICE_ID, DdyModuleNames.DEVICE_NAME, "车载设备-导入设备信息(提交解析正常的数据)");
        instance.setIovnetLog(log);
        return instance;
    }

ServiceImpl
获取正确数据完成添加

@Override
    public void submitDeviceData(List<ImportDeviceVo> vo) {
        for (ImportDeviceVo deviceVo : vo) {
            DdyDeviceData ddyDeviceData = new DdyDeviceData();
            ddyDeviceData.setCompanyId(deviceVo.getCompanyId());
            ddyDeviceData.setDeviceNo(deviceVo.getDeviceNo());
            ddyDeviceData.setDeviceTypeCode(deviceVo.getDeviceTypeCode());
            ddyDeviceData.setManufacturer(deviceVo.getManufacturer());
            ddyDeviceData.setDeviceCode(deviceVo.getDeviceCode());
            ddyDeviceData.setDeviceStateCode(deviceVo.getDeviceStateCode());
            ddyDeviceData.setInstallTime(DateUtil.StringToDate(deviceVo.getInstallTime(),"yyyy-MM-dd HH:mm:ss"));
            ddyDeviceData.setVehicleId(deviceVo.getVehicleId());
            ddyDeviceData.setSimNo(deviceVo.getSimNo());
            ddyDeviceData.setOperator(deviceVo.getOperator());
            ddyDeviceData.setAccountState(deviceVo.getAccountState());
            ddyDeviceData.setCameraNum(deviceVo.getCameraNum());
            ddyDeviceData.setCanVideo(deviceVo.getCanVideo());
            ddyDeviceData.setProtocolTypeCode(deviceVo.getProtocolTypeCode());
            ddyDeviceData.setPackageFlow(deviceVo.getPackageFlow());
            ddyDeviceData.setSmsiNo(deviceVo.getSmsiNo());
            ddyDeviceData.setOpenCardTime(DateUtil.StringToDate(deviceVo.getOpenCardTime(),"yyyy-MM-dd HH:mm:ss"));
            ddyDeviceData.setSettlementType(deviceVo.getSettlementType());
            ddyDeviceData.setSettlementDate(DateUtil.StringToDate(deviceVo.getSettlementDate(),"yyyy-MM-dd"));
            ddyDeviceData.setRemark(deviceVo.getRemark());
            ddyDeviceData.setDisable(0);
            ddyDeviceData.setDeleted(0);
            ddyDeviceData.setUpdateDate(new Date());
            ddyDeviceData.setCreateDate(new Date());
            ddyDeviceDataMapper.insert(ddyDeviceData);
        }
    }

解析单元格数据方法

public static <T> List<T> parseExcelData(MultipartFile file, int sheetNum, int titleRowNum, int startRow, Class<T> clazz) {
        List<T> list = new ArrayList<>();
        try {
            Field[] fields = clazz.getDeclaredFields();
            Arrays.stream(fields).forEach(field -> field.setAccessible(true));
            InputStream inputStream = file.getInputStream();
            //创建Workbook对象
            Workbook workbook = WorkbookFactory.create(inputStream);
            //获取工作表
            if (workbook != null) {
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null || sheet.getPhysicalNumberOfRows() == 0) {
                    return list;
                }
                //获取标题行的第一列和最后一列的标记
                Row titleRow = sheet.getRow(titleRowNum - 1);
                short firstCellNum = titleRow.getFirstCellNum();
                short lastCellNum = titleRow.getLastCellNum();
                //获取表格中最后一行的行号
                int lastRowNum = sheet.getLastRowNum();
                //获取行
                for (int rowNum = startRow - 1; rowNum <= lastRowNum; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row == null || isRowEmpty(row)) {
                        continue;
                    }
                    // 创建返回结果对象
                    T object = clazz.getDeclaredConstructor().newInstance();
                    //循环列
                    for (int i = firstCellNum; i < lastCellNum; i++) {
                        Cell cell = row.getCell(i);
                        ExcelImportColumn annotation = fields[i].getAnnotation(ExcelImportColumn.class);
                        if (cell != null && annotation != null && annotation.index() == i + 1) {
                            String parseCell = parseCell(cell);
                            handleField(object, parseCell, fields[i]);
                        }
                    }
                    list.add(object);
                }
            }
        } catch (IOException | InvalidFormatException | IllegalAccessException | InstantiationException | NoSuchMethodException | InvocationTargetException e) {
            e.printStackTrace();
        }
        return list;
    }

日期判断工具类

public static <T> void addValueTypeValid(Workbook workbook, Sheet targetSheet, Class<T> clazz, char column, int fromRow, int endRow) {
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
        String typeName = clazz.getName();
        if (Objects.equals(typeName, "java.lang.Integer") || Objects.equals(typeName, "java.lang.Long")) {
            String minValue = String.valueOf(Integer.MIN_VALUE);
            String maxValue = String.valueOf(Integer.MAX_VALUE);
            DVConstraint dvConstraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.BETWEEN, minValue, maxValue);
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
            dataValidation.createPromptBox("提示", "请输入整数类型的值");
            dataValidation.setShowPromptBox(true);
            targetSheet.addValidationData(dataValidation);
        } else if (Objects.equals(typeName, "java.lang.Float") || Objects.equals(typeName, "java.lang.Double")) {
            String minValue = String.valueOf(Double.MIN_VALUE);
            String maxValue = String.valueOf(Double.MAX_VALUE);
            DVConstraint dvConstraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, minValue, maxValue);
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
            dataValidation.createPromptBox("提示", "请输入小数类型的值");
            dataValidation.setShowPromptBox(true);
            targetSheet.addValidationData(dataValidation);
        } else if (Objects.equals(typeName, "java.time.LocalDate")) {
            String minValue = "0000-01-01";
            String maxValue = "9999-12-31";
            String dateFormat = DateUtil.YYYY_MM_DD;
            DVConstraint dvConstraint = DVConstraint.createDateConstraint(DVConstraint.ValidationType.DATE, minValue, maxValue, dateFormat);
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
            dataValidation.createPromptBox("提示", "请输入日期["+ dateFormat +"]类型的值");
            dataValidation.setShowPromptBox(true);
            targetSheet.addValidationData(dataValidation);
        } else if (Objects.equals(typeName, "java.time.LocalTime")) {
            String minValue = "00:00:00";
            String maxValue = "23:59:00";
            String dateFormat = DateUtil.HH_MM_SS;
            DVConstraint dvConstraint = DVConstraint.createTimeConstraint(DVConstraint.ValidationType.TIME, minValue, maxValue);
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
            dataValidation.createPromptBox("提示", "请输入时间["+ dateFormat +"]类型的值");
            dataValidation.setShowPromptBox(true);
            targetSheet.addValidationData(dataValidation);
        }



    }