将Excel中的数据导入到数据库中

Controller层导入注解用@RequestParam，传入文件接收类型为MultipartFile

@ApiOperation(value="导入XXX表Excel",notes="返回导入情况接口",response = TestDemoPo.class)
    @PostMapping(value = "/excelTestDemoImport")
    public void excelTestDemoImport(@RequestParam("file") MultipartFile file){
       return importDemoService.excelImport(file);
    }

service层

void importDemoService(MultipartFile file);

serviceImpl层

@Override
    public void importDemoService(MultipartFile file) {
        //校验上传的文件格式，文件名等
        ExcelImportUtil.checkImportFile(importFile);
        log.info("导入XXX表Excel，开始解析文件>>>>>>>>>>>>>");
        //解析对象
        List<TestDemoImportDO> entityList = null;
        //开始导入时间
        long startTime = System.currentTimeMillis();
        try {
            ExcelHandleConfig config = new ExcelHandleConfig(1, "yyyy-MM-dd", 0);
            entityList = ExcelImportUtil.importExcelData(importFile.getInputStream(), config, TestDemoImportDO.class);
            if (CollectionUtils.isEmpty(entityList)) {
                return;
            }
            log.info("导入XXX表Excel，excel数据大小为>>>>>>>>>>>>>" + entityList.size());
            if (CollectionUtils.isEmpty(entityList)) {
                return;
            }
            // 生产厂家切换中文括号
            entityList.forEach(entity -> {
                entity.setManufacturer(StringUtils.isEmpty(entity.getManufacturer()) ? "" : entity.getManufacturer().replaceAll("\\(", "（").replaceAll("\\)", "）"));
                // 产品中文名称切换中文括号
                entity.setProductNameCn(StringUtils.isEmpty(entity.getProductNameCn()) ? "" : entity.getProductNameCn().replaceAll("\\(", "（").replaceAll("\\)", "）"));
            });
            //去重
            log.info("XXX表Excel去重之前条数：" + entityList.size());
            List<TestDemoImportDO> distinctData = dataMakeDistinct(entityList);
            log.info("XXX表Excel去重之后条数：" + distinctData.size());
            splitDataImport(distinctData);
        }  catch (Exception e) {            
            log.error("导入XXX表Excel，导入失败报错信息Exception>>>>>>>>>>>>>", e);
            ApplicationExceptionUtil.throwException(ResultEnum.COMMON.getCode(), "导入失败");
        } finally {
            log.info("导入XXX表Excel，导入数据结束>>>>>>>>>>>>>耗时{}ms", (System.currentTimeMillis() - startTime));
        }
    }

校验文件是否正确

@Slf4j
public class ExcelImportUtil {
    public static void checkImportFile(MultipartFile file) {
        if (Objects.isNull(file) || file.isEmpty()) {
            ApplicationExceptionUtil.throwParamsException("请上传文件");
        }
        if (StringUtil.isEmpty(file.getOriginalFilename())) {
            ApplicationExceptionUtil.throwParamsException("文件名不能为空");
        }
        log.info("上传文件文件名为=======>{}", file.getOriginalFilename());
        String originalFilename = file.getOriginalFilename();
        int i = originalFilename.lastIndexOf(".");
        String suffixString = originalFilename.substring(i + 1);
        String prefixString = originalFilename.substring(0, i);
        if (StringUtil.isBlank(suffixString) || StringUtil.isBlank(prefixString)) {
            ApplicationExceptionUtil.throwParamsException("文件格式有误");
        }
        if (StringUtils.isBlank(suffixString) || !("xlsx".equals(suffixString) || "xls".equals(suffixString))) {
            ApplicationExceptionUtil.throwParamsException("文件格式需要以xls或xlsx结尾");
        }
    }
    /**
     * 文件上传
     */
    public static <T> List<T> importExcelData(InputStream fis, ExcelHandleConfig config, Class<T> clazz) {
        Workbook wb = getWorkBook(fis);
        return getEntityList(wb, clazz, config);
    }
    //获取workbook
    private static Workbook getWorkBook(InputStream is) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (Exception e) {
            log.error("", e);
        }
        return wb;
    }
    //遍历excel取值,只取一张工作簿
    private static <T> List<T> getEntityList(Workbook wb, Class<T> clazz, ExcelHandleConfig config) {
        List<T> resList = new ArrayList<>();
        //for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(config.getSheetIndex());
        int rowIndex = 0;
        for (Row row : sheet) {
            //是否限制表头名称一致
            if (rowIndex == config.getHeadStartRow() && config.isCheckHead()) {
                validHeadName(row, clazz);
            }
            if (rowIndex++ < config.getStartRow()) {
                continue;
            }
            T obj = getInstance(row, clazz, config);
            if (Objects.nonNull(obj)) {
                resList.add(obj);
            }
        }
        return resList;
    }
    //验证表头名字是否跟预定义的一致
    private static <T> void validHeadName(Row row, Class<T> clazz) {
        Field[] fieldArr = clazz.getDeclaredFields();
        ExcelImport an = null;
        for (Field field : fieldArr) {
            field.setAccessible(true);
            an = field.getAnnotation(ExcelImport.class);
            if (Objects.isNull(an)) {
                continue;
            }
            String headName = an.headName();
            if (StringUtils.isNotEmpty(headName)) {
                int index = an.sort();
                Cell cell = row.getCell(index);
                String cellValue = cell.getStringCellValue();
                if (!headName.equals(cellValue)) {
                    ApplicationExceptionUtil.throwException(ResultEnum.COMMON.getCode(), "第" + (index + 1) + "列表头名称不一致，导入失败！名称应为：" + headName);
                }
            }
        }
    }
    private static <T> T getInstance(Row row, Class<T> clazz, ExcelHandleConfig config) {
        T target = null;
        try {
            target = clazz.newInstance();
            Field[] fieldArr = clazz.getDeclaredFields();
            ExcelImport an = null;
            for (Field field : fieldArr) {
                field.setAccessible(true);
                an = field.getAnnotation(ExcelImport.class);
                if (Objects.isNull(an)) {
                    continue;
                }
                int index = an.sort();
                Cell cell = row.getCell(index);
                //字段赋值
                String value = getCellValue(cell, config.getDateFormatPattern());
                if(StringUtils.isNotEmpty(value)) {
                    value = value.trim();
                }
                if (an.length() > 0 && value.length() > an.length()) {
                    ApplicationExceptionUtil.throwException(ResultEnum.COMMON.getCode(), "数据'" + value + "'长度过长，导入失败!");
                }
                BeanUtils.setProperty(target, field.getName(), value);
            }
        } catch (IllegalAccessException | InstantiationException | InvocationTargetException e) {
            log.error("", e);
            ApplicationExceptionUtil.throwException(ResultEnum.COMMON.getCode(), "数据导入失败");
        }
        return target;
    }
    private static String getCellValue(Cell cell, String dateFormatPattern) {
        String cellValue = "";
        if (Objects.isNull(cell)) {
            return cellValue;
        }
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            // 数字
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat(dateFormatPattern);
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            // 字符串
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
            // Boolean
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = cell.getBooleanCellValue() + "";
                break;
            // 公式
            case Cell.CELL_TYPE_FORMULA:
                cellValue = cell.getCellFormula() + "";
                break;
            // 空值
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            // 故障
            case Cell.CELL_TYPE_ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}

表的页码设置

@Data
public class ExcelHandleConfig {
    /**
     * 开始的行数 从0开始
     */
    private int startRow;//读取数据的开始行
    /**
     * 开始的行数 从0开始
     */
    private int headStartRow = 0;//读取表头的开始行
    /**
     * 日期格式化 格式
     */
    private String dateFormatPattern;
    /**
     * 读取第几页sheet，从0开始
     */
    private int sheetIndex;
    //是否强制校验表头一致性
    private boolean checkHead = true;
    public ExcelHandleConfig() {
        this.startRow = 0;
        this.dateFormatPattern = "yyyy/mm/dd";
        this.sheetIndex = 0;
    }
    public ExcelHandleConfig(int startRow, String dateFormatPattern, int sheetIndex) {
        this.startRow = startRow;
        this.dateFormatPattern = dateFormatPattern;
        this.sheetIndex = sheetIndex;
    }
    public ExcelHandleConfig(int startRow, int headStartRow, String dateFormatPattern, int sheetIndex) {
        this.startRow = startRow;
        this.headStartRow = headStartRow;
        this.dateFormatPattern = dateFormatPattern;
        this.sheetIndex = sheetIndex;
    }
    public ExcelHandleConfig(int startRow, String dateFormatPattern, int sheetIndex, boolean checkHead) {
        this.startRow = startRow;
        this.dateFormatPattern = dateFormatPattern;
        this.sheetIndex = sheetIndex;
        this.checkHead = checkHead;
    }
}

根据某几个字段去重

private static List<TestDemoImportDO> dataMakeDistinct(List<TestDemoImportDO> dtoList) {
        List<TestDemoImportDO> distinctData = dtoList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(d ->
                d.getType() + ";" + d.getNo() + ";" +
                d.getName() + ";" +d.getManufacturer() + ";" +
                d.getNameCn() + ";" +d.getApprovalDate() + ";" +
                d.getExpireDate()))), ArrayList::new));
        return distinctData;
    }

入库操作

private void splitDataImport(List<TestDemoImportDO> dataList) {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        //取数据，入库
        for (PcCertificateAuditImportMultiDO cerDO : dtoList) {
        TestDemoImportDO demoTest = new TestDemoImportDO();
        if (StringUtils.isBlank(cerDO.getType())) {
                demoTest.setType(cerDO.getType());
            } 
            .......
            demoTestMapper.insert(demoTest);
        }
    }