eg:导入券码

获取导入的券码,并校验:

	@Override
	public List<String> importCouponCode(MultipartFile file) {
		List<CouponCodeVO> couponCodeVOList = new ArrayList<>();
		try {
			// 1.获取券码VO集合
			if (file == null || file.getSize() <= 0) {
				throw new BsException(CouponContants.C901, ErrorConstant.FORMATE(CouponContants.M901, "文件不能为空"));
			}
			InputStream input = file.getInputStream();
			Map<String, String> field = new HashMap<>(2);
			field.put("券码", "couponCode");
			couponCodeVOList = ExcelUtil.ExeclToList(input, CouponCodeVO.class, field);
		} catch (Exception ex) {
			throw new BsException(CouponContants.C901, ErrorConstant.FORMATE(CouponContants.M901, "文件导入异常"));
		}
		// 2.获取券码集合cardNoList
		Iterator<CouponCodeVO> iterator = couponCodeVOList.iterator();
		List<String> cardNoList = new ArrayList<>();
		// 去除空元素 + 校验
		while (iterator.hasNext()) {
			CouponCodeVO couponCodeVO = iterator.next();
			if (!StringUtil.isNullOrEmpty(couponCodeVO.getCouponCode())) {
				// 是纯数字,长度12
				Assert.isTrue(
						PatternUtil.isNum(couponCodeVO.getCouponCode()) && couponCodeVO.getCouponCode().length() == 12,
						CouponContants.C901, CouponContants.M901, "券码" + couponCodeVO.getCouponCode() + "不合法，请检查后再导入");
				// 有重复
				Assert.isTrue(!cardNoList.contains(couponCodeVO.getCouponCode()), CouponContants.C901,
						CouponContants.M901, "券码" + couponCodeVO.getCouponCode() + "存在重复，请检查后再导入");
				cardNoList.add(couponCodeVO.getCouponCode());
			} else {
				iterator.remove();
			}
		}
		return cardNoList;
	}

Excel转List

    /**
     * Excel转List <br>
     *
     * @param in 输入流  excel
     * @param entityClass excel中每一行数据的实体类 这里没法用泛型
     * @param fields 字段名字
     * @return java.util.List
     */
    @SuppressWarnings("AlibabaLowerCamelCaseVariableNaming")
    public static List ExeclToList(InputStream in, Class entityClass, Map<String, String> fields) throws Exception {

        List<Object> resultList = new ArrayList<Object>();

        XSSFWorkbook workbook = new XSSFWorkbook(in);

        // excel中字段的中英文名字数组
        String[] egtitles = new String[fields.size()];
        String[] cntitles = new String[fields.size()];
        Iterator<String> it = fields.keySet().iterator();
        int count = 0;
        while (it.hasNext()) {
            String cntitle = (String) it.next();
            String egtitle = fields.get(cntitle);
            egtitles[count] = egtitle;
            cntitles[count] = cntitle;
            count++;
        }

        // 得到excel中sheet总数
        int sheetcount = workbook.getNumberOfSheets();

        if (sheetcount == 0) {
            workbook.close();
            throw new Exception("Excel文件中没有任何数据");
        }
        sheetcount = 1;//sheet只取1

        // 数据的导出
        for (int i = 0; i < sheetcount; i++) {
            XSSFSheet sheet = workbook.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            // 每页中的第一行为标题行，对标题行的特殊处理
            XSSFRow firstRow = sheet.getRow(0);
            int celllength = firstRow.getLastCellNum();

            String[] excelFieldNames = new String[celllength];
            LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();

            // 获取Excel中的列名
            for (int f = 0; f < celllength; f++) {
                XSSFCell cell = firstRow.getCell(f);
                excelFieldNames[f] = cell.getStringCellValue().trim();
                // 将列名和列号放入Map中,这样通过列名就可以拿到列号
                for (int g = 0; g < excelFieldNames.length; g++) {
                    colMap.put(excelFieldNames[g], g);
                }
            }
            // 由于数组是根据长度创建的，所以值是空值，这里对列名map做了去空键的处理
            colMap.remove(null);
            // 判断需要的字段在Excel中是否都存在
            // 需要注意的是这个方法中的map中：中文名为键，英文名为值
            boolean isExist = true;
            List<String> excelFieldList = Arrays.asList(excelFieldNames);
            for (String cnName : fields.keySet()) {
                if (!excelFieldList.contains(cnName)) {
                    isExist = false;
                    break;
                }
            }
            // 如果有列名不存在，则抛出异常，提示错误
            if (!isExist) {
                workbook.close();
                throw new Exception("Excel中缺少必要的字段，或字段名称有误");
            }
            // 将sheet转换为list
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                XSSFRow row = sheet.getRow(j);
                // 根据Obj创建实体类
                if(row!=null){//过滤空行
                    Object entity =  entityClass.newInstance();
                    // 给对象中的字段赋值
                    for (Map.Entry<String, String> entry : fields.entrySet()) {
                        // 获取中文字段名
                        String cnNormalName = entry.getKey();
                        // 获取英文字段名
                        String enNormalName = entry.getValue();
                        // 根据中文字段名获取列号
                        int col = colMap.get(cnNormalName);
                        // 获取当前单元格中的内容
                        XSSFCell cell = row.getCell(col);
                        cell.setCellType(CellType.STRING);
                        String content = cell.toString().trim();
                        //去除数字的默认小数点 .0
                        int length = content.length();
                        if (length>=2) {  //这里大于等于2是防止有些列只有一个字符，到下面会报错
                            if (content.substring(length-2, length).equals(".0")){
                                content = content.substring(0,length-2);
                            } //通过截取最后两个字符，如果等于.0 就 去除最后两个字符
                        }
                        // 给对象赋值
                        setFieldValueByName(enNormalName, content, entity);
                    }
                    resultList.add(entity);
                }
            }
        }
        workbook.close();
        return resultList;
    }