写了一个上传excel到解析到数据库的通用类,只能作为参考

写了一个上传excel到解析到数据库的通用类,只能作为参考


先在本文件中判断有没有规定的列重复的,然后判断数据库有没有重复的,没有就insert 有就提示,次例为自定义orm框架中的一个写法

// An highlighted block
 protected String saveList(RestTemplate restTemplate, String pass, String addr, Integer port, Class<?> type, MultipartFile multipartFile) throws Exception {
        String moduleandDate = RedisUtil.getTable_DsModule(addr, port, pass, type);
        List<String> dbList = Arrays.asList(moduleandDate.split(","));
        String module = dbList.get(0);
        String datasouce = dbList.get(1);
        SaveOrUpdateResult saveOrUpdateResult = new SaveOrUpdateResult();
        RowCheck rowCheck = new RowCheck();
        if (multipartFile == null || multipartFile.getSize() == 0) {
            rowCheck.setCheck(false);
            saveOrUpdateResult.setData(rowCheck);
            rowCheck.getRowMessageList().add("文件上传错误,重新上传");
            return objectMapper.writeValueAsString(saveOrUpdateResult);
        }
        String filename = multipartFile.getOriginalFilename();
        if (!(filename.endsWith(".xls") || filename.endsWith(".xlsx"))) {
            rowCheck.setCheck(false);
            saveOrUpdateResult.setData(rowCheck);
            rowCheck.getRowMessageList().add("文件上传格式错误,请重新上传");
            return objectMapper.writeValueAsString(saveOrUpdateResult);
        }
        List<Map<String, Object>> accountMapList = new ArrayList<>();
        List<Object> objectList = new ArrayList<>();
        if (filename.endsWith(".xls")) {
            InputStream inputStream = multipartFile.getInputStream();
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            for(int s = 0; s <workbook.getNumberOfSheets(); s++){
                HSSFSheet sheet = workbook.getSheetAt(s);
                if(sheet.getLastRowNum()!=0){
                    String tablename = sheet.getSheetName();

                    int lastRowNum = sheet.getLastRowNum();//:读取总行数
                    //从第二行开始,第一行为标题
                    HSSFRow fieldName = sheet.getRow(0);//第一行的所有信息  隐藏行的字段信息
                    HashMap<Integer,Object> fieldNameset = new HashMap<>();
                    // 获取所有的字段 获取要去去重的列
                    List<Map<Integer, String>> ints = new ArrayList<Map<Integer, String>>();
                    for (int i = 0; i < fieldName.getPhysicalNumberOfCells(); i++) {
                        fieldName.getCell(i).getStringCellValue();  //各列的字段名称
                        List<String> logicIdFieldList = addLogicalJudgmentList(); //获取要去重的字段
                        if (logicIdFieldList != null && logicIdFieldList.size() > 0) {
//                        吧要去重的字段放到一个listmap中
                            for (int c = 0; c < logicIdFieldList.size(); c++) {
                                if (fieldName.getCell(i).getStringCellValue().equals(logicIdFieldList.get(c))) {
                                    Map<Integer, String> map = new HashMap<>();
                                    map.put(i, fieldName.getCell(i).getStringCellValue());
                                    ints.add(map);
                                }
                            }
                        }
//                    存入所有的字段名
                        fieldNameset.put(i,fieldName.getCell(i).getStringCellValue());
                    }

                    //从第二行开始,第一行为标题
                    for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
                        HSSFRow row = sheet.getRow(rowNum);//第一行的所有信息
                        if (row != null) {
                            // 获取列数
                            row.getPhysicalNumberOfCells();
                            Object object = type.newInstance();
                            Field field = object.getClass().getDeclaredField("db_id");
                            field.setAccessible(true);
                            field.set(object, IdUtil.genSnowflakeId());
                            Field[] fields = object.getClass().getDeclaredFields();
                            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                                for (Field fie : fields) {
                                    if ( row.getCell(i)==null){
                                        rowCheck.setCheck(false);
                                        saveOrUpdateResult.setData(rowCheck);
                                        rowNum=rowNum+1;
                                        i=i+1;
                                        rowCheck.getRowMessageList().add("第"+rowNum+"行,第"+i+"列数据为空,请检查");
                                        return objectMapper.writeValueAsString(saveOrUpdateResult);
                                    }
                                    //如果有创建时间就在这加上当前时间
                                    if ("db_createtime".equals(fie.getName())) {
                                        fie.setAccessible(true);
                                        fie.set(object, new Date());
                                    }
                                    if ("db_intime".equals(fie.getName())) {
                                        fie.setAccessible(true);
                                        fie.set(object, new Date());
                                    }
                                    //循环判断要去重的列
                                    for(Map vmap:ints){
                                        Iterator<Integer> it = vmap.keySet().iterator();
                                        while (it.hasNext()) {
                                            Integer value = Integer.valueOf(it.next());
                                            if(value.equals(i)){  //每一行的这列需要进行判断
                                                for(Object objects:objectList){
                                                    Field  ofield = objects.getClass().getDeclaredField(String.valueOf(vmap.get(value)));
                                                    ofield.setAccessible(true);
                                                    if(ofield.get(objects).equals(row.getCell(i).getStringCellValue())){
                                                        rowCheck.setCheck(false);
                                                        saveOrUpdateResult.setData(rowCheck);
                                                        rowNum=rowNum+1;
                                                        i=i+1;
                                                        rowCheck.getRowMessageList().add("第"+rowNum+"行,第"+i+"列数据重复");
                                                        return objectMapper.writeValueAsString(saveOrUpdateResult);
                                                    }
                                                }
                                            }
                                        }
                                    }
                                    //判断如果字段名相等就赋值

                                    Iterator<Integer> fieldNames = fieldNameset.keySet().iterator(); //遍历所有的字段名
                                    while (fieldNames.hasNext()) {
                                        Integer fieldValue = fieldNames.next();

                                        if (fieldNameset.get(fieldValue).equals(fie.getName())) {
                                            row.getCell(fieldValue).setCellType(CellType.STRING);
                                            fie.setAccessible(true);
                                            fie.set(object, row.getCell(fieldValue).getStringCellValue());
                                        }
                                    }
                                }
                            }
                            //判断数据库中是否有这个数据,如果有提醒

                            List<String> logicIdFieldList = addLogicalJudgmentList();
                            if (logicIdFieldList != null && logicIdFieldList.size() > 0) {

                                String sql = RedisUtil.getTable_SelectCount(addr, port, pass, type);
                                for (int i = 0; i < logicIdFieldList.size(); i++) {
                                    String fieldss = logicIdFieldList.get(i);
                                    sql += " and " + fieldss + " = ? ";
                                }
                                Map<String, String> dataMap = new LinkedHashMap<>();

                                for (int i = 0; i < logicIdFieldList.size(); i++) {
                                    Object value = ReflectUtil.getFieldValue(object, logicIdFieldList.get(i));
                                    String name = object.getClass().getDeclaredField(logicIdFieldList.get(i)).getType().getName();
                                    Object types = name.substring(name.lastIndexOf(".") + 1);
                                    //获取字段类型
                                    dataMap.put(i + "_" + types.toString().toLowerCase(), String.valueOf(value));
                                }

                                String url = "";
                                ResponseEntity<CommonResult> responseEntity = restTemplate.getForEntity(url, CommonResult.class);
                                CommonResult commonResult = responseEntity.getBody();
                                List<Map> results = new ArrayList<>();
                                if (commonResult.getData() instanceof ArrayList<?>) {
                                    for (Object o : (List<?>) commonResult.getData()) {
                                        results.add(Map.class.cast(o));
                                    }
                                    results.get(0).get("count");
                                }
                                if ((int) results.get(0).get("count") > 0) {
                                    rowCheck.getRowMessageList().add("逻辑主键重复");
                                    rowCheck.setCheck(false);
                                    saveOrUpdateResult.setData(rowCheck);
                                    return objectMapper.writeValueAsString(saveOrUpdateResult);
                                }
                            }

                            //吧entity转换成map
                            objectList.add(object); //redis存入格式
                            Map<String, Object> accountMap = RedisUtil.getInsertParamMap(addr, port, pass, object);
                            accountMapList.add(accountMap);
                        }
                    }

                }
            }

        } else {
            InputStream inputStream = multipartFile.getInputStream();
            XSSFWorkbook Workbook = new XSSFWorkbook(inputStream);
//                遍历所有的sheet
            for (int s = 0; s < Workbook.getNumberOfSheets(); s++) {
                XSSFSheet sheet = Workbook.getSheetAt(s);
                if(sheet.getLastRowNum()!=0){
                    String tablename = sheet.getSheetName();

                    XSSFRow fieldName = sheet.getRow(0);//第一行的所有信息
                    HashMap<Integer,Object> fieldNameset = new HashMap<>();
                    // 获取所有的字段 获取要去去重的列
                    List<Map<Integer, String>> ints = new ArrayList<Map<Integer, String>>();
                    for (int i = 0; i < fieldName.getPhysicalNumberOfCells(); i++) {
                        fieldName.getCell(i).getStringCellValue();  //各列的字段名称
                        List<String> logicIdFieldList = addLogicalJudgmentList(); //获取要去重的字段
                        if (logicIdFieldList != null && logicIdFieldList.size() > 0) {
//                        吧要去重的字段放到一个listmap中
                            for (int c = 0; c < logicIdFieldList.size(); c++) {
                                if (fieldName.getCell(i).getStringCellValue().equals(logicIdFieldList.get(c))) {
                                    Map<Integer, String> map = new HashMap<>();
                                    map.put(i, fieldName.getCell(i).getStringCellValue());
                                    ints.add(map);
                                }
                            }
                        }
//                    存入所有的字段名
                        fieldNameset.put(i,fieldName.getCell(i).getStringCellValue());
                    }

                    //从第二行开始,第一行为标题
                    for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
                        XSSFRow row = sheet.getRow(rowNum);//第一行的所有信息
                        if (row != null) {
                            // 获取列数
                            row.getPhysicalNumberOfCells();
                            Object object = type.newInstance();
                            Field field = object.getClass().getDeclaredField("db_id");
                            field.setAccessible(true);
                            field.set(object, IdUtil.genSnowflakeId());
                            Field[] fields = object.getClass().getDeclaredFields();
                            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                                for (Field fie : fields) {
                                    if ( row.getCell(i)==null){
                                        rowCheck.setCheck(false);
                                        saveOrUpdateResult.setData(rowCheck);
                                        rowNum=rowNum+1;
                                        i=i+1;
                                        rowCheck.getRowMessageList().add("第"+rowNum+"行,第"+i+"列数据为空,请检查");
                                        return objectMapper.writeValueAsString(saveOrUpdateResult);
                                    }
                                    //如果有创建时间就在这加上当前时间
                                    if ("db_createtime".equals(fie.getName())) {
                                        fie.setAccessible(true);
                                        fie.set(object, new Date());
                                    }
                                    if ("db_intime".equals(fie.getName())) {
                                        fie.setAccessible(true);
                                        fie.set(object, new Date());
                                    }
                                    //循环判断要去重的列
                                        for(Map vmap:ints){
                                            Iterator<Integer> it = vmap.keySet().iterator();
                                            while (it.hasNext()) {
                                                Integer value = Integer.valueOf(it.next());
                                                if(value.equals(i)){  //每一行的这列需要进行判断
                                                    for(Object objects:objectList){
                                                        Field  ofield = objects.getClass().getDeclaredField(String.valueOf(vmap.get(value)));
                                                        ofield.setAccessible(true);
                                                        if(ofield.get(objects).equals(getCellValue(row.getCell(i)))){
                                                            rowCheck.setCheck(false);
                                                            saveOrUpdateResult.setData(rowCheck);
                                                            rowNum=rowNum+1;
                                                            i=i+1;
                                                            rowCheck.getRowMessageList().add("第"+rowNum+"行,第"+i+"列数据重复");
                                                            return objectMapper.writeValueAsString(saveOrUpdateResult);
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    //判断如果字段名相等就赋值

                                    Iterator<Integer> fieldNames = fieldNameset.keySet().iterator(); //遍历所有的字段名
                                    while (fieldNames.hasNext()) {
                                        Integer fieldValue = fieldNames.next();

                                        if (fieldNameset.get(fieldValue).equals(fie.getName())) {
                                            row.getCell(fieldValue).setCellType(CellType.STRING);
                                            fie.setAccessible(true);
                                            fie.set(object, getCellValue(row.getCell(fieldValue)));
                                        }
                                    }
                                }
                            }
                            //判断数据库中是否有这个数据,如果有提醒

                            List<String> logicIdFieldList = addLogicalJudgmentList();
                            if (logicIdFieldList != null && logicIdFieldList.size() > 0) {

                                String sql = RedisUtil.getTable_SelectCount(addr, port, pass, type);
                                for (int i = 0; i < logicIdFieldList.size(); i++) {
                                    String fieldss = logicIdFieldList.get(i);
                                    sql += " and " + fieldss + " = ? ";
                                }
                                Map<String, String> dataMap = new LinkedHashMap<>();

                                for (int i = 0; i < logicIdFieldList.size(); i++) {
                                    Object value = ReflectUtil.getFieldValue(object, logicIdFieldList.get(i));
                                    String name = object.getClass().getDeclaredField(logicIdFieldList.get(i)).getType().getName();
                                    Object types = name.substring(name.lastIndexOf(".") + 1);
                                    //获取字段类型
                                    dataMap.put(i + "_" + types.toString().toLowerCase(), String.valueOf(value));
                                }

                                String url = "";
                                ResponseEntity<CommonResult> responseEntity = restTemplate.getForEntity(url, CommonResult.class);
                                CommonResult commonResult = responseEntity.getBody();
                                List<Map> results = new ArrayList<>();
                                if (commonResult.getData() instanceof ArrayList<?>) {
                                    for (Object o : (List<?>) commonResult.getData()) {
                                        results.add(Map.class.cast(o));
                                    }
                                    results.get(0).get("count");
                                }
                                if ((int) results.get(0).get("count") > 0) {
                                    rowCheck.getRowMessageList().add("逻辑主键重复");
                                    rowCheck.setCheck(false);
                                    saveOrUpdateResult.setData(rowCheck);
                                    return objectMapper.writeValueAsString(saveOrUpdateResult);
                                }
                            }

                            //吧entity转换成map
                            objectList.add(object); //redis存入格式
                            Map<String, Object> accountMap = RedisUtil.getInsertParamMap(addr, port, pass, object);
                            accountMapList.add(accountMap);
                        }
                    }

                }
                }

        }


        //在这添加
        String sql = RedisUtil.getTable_Insert(addr, port, pass, type);
        String inserturl = CURDURL + "/curd/ijsondata/updateSql?module=" + module + "&datasource=" + datasouce +
                "&sql=" + sql + "&jsonData=" + URLEncoder.encode(objectMapper.writeValueAsString(accountMapList), "utf-8");
        ResponseEntity<CommonResult> cresponseEntity = restTemplate.getForEntity(inserturl, CommonResult.class);
        CommonResult commonResult = cresponseEntity.getBody();
        if (commonResult.isSuccess()) {
            RedisUtil.setTableDatas(addr, port, pass, objectList);
            return objectMapper.writeValueAsString(saveOrUpdateResult);
        } else {
            saveOrUpdateResult.setSuccess(false);
            saveOrUpdateResult.setMessage(commonResult.getMessage());
            return objectMapper.writeValueAsString(saveOrUpdateResult);
        }
        //return null;
    }
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
好的,我可以为您提供一个基于EasyExcel的监听器通用模板工具,可以适配多个实体。以下是代码示例: ```java import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; import lombok.extern.slf4j.Slf4j; import org.springframework.util.CollectionUtils; import java.util.ArrayList; import java.util.List; /** * EasyExcel 监听器通用模板工具,可以适配多个实体 * * @param <T> 实体泛型 */ @Slf4j public class CommonExcelListener<T> extends AnalysisEventListener<T> { /** * 每隔N条存储数据库,默认N为1,可以在构造函数中传入自定义值 */ private static final int BATCH_COUNT = 1; /** * 数据行数 */ private int rowIndex = 0; /** * 数据存储集合 */ private List<T> dataList = new ArrayList<>(); /** * 构造函数 */ public CommonExcelListener() { } /** * 读取Excel数据行,每读取一行数据就会调用一次该方法 * * @param data 读取到的数据 * @param analysisContext 解析上下文 */ @Override public void invoke(T data, AnalysisContext analysisContext) { rowIndex++; log.info("解析到一条数据:{}", JSON.toJSONString(data)); dataList.add(data); // 达到BATCH_COUNT,需要去存储一次数据库,防止数据过多内存溢出 if (dataList.size() >= BATCH_COUNT) { saveData(); // 存储完成清理数据 dataList.clear(); } } /** * 所有数据解析完成后执行该方法 * * @param analysisContext 解析上下文 */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { // 数据解析完成后,如果数据集合中还有剩余数据,一并存储到数据库中 if (!CollectionUtils.isEmpty(dataList)) { saveData(); } log.info("读取Excel数据共{}行", rowIndex); } /** * 将数据存储到数据库中 */ private void saveData() { log.info("{}条数据,开始存储数据库!", dataList.size()); // TODO 数据存储逻辑 } } ``` 使用方法如下: ```java // 示例:读取 User 实体对应的 Excel 数据 String fileName = "user.xlsx"; InputStream inputStream = new BufferedInputStream(new FileInputStream(fileName)); EasyExcel.read(inputStream, User.class, new CommonExcelListener<User>()).sheet().doRead(); ``` 您只需将 `CommonExcelListener` 复制到您的项目中,并根据实际需要修改 `saveData` 中的数据存储逻辑即可。同时在读取 Excel 数据时,只需传入对应的实体和 `CommonExcelListener` 即可。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值