【无标题】

Excel表格上传然后录入数据库

先写工具类，文中举例是Build

import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import javax.servlet.http.HttpServletRequest;

	@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    @Transactional
    public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        // 错误信息
        List<String> errorMessage = new ArrayList<>();
        int successLines = 0, errorLines = 0;
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            ImportParams params = new ImportParams();
            params.setNeedSave(false);
            try {
                List<BuildingVo> listBuildingVo = ExcelImportUtil.importExcel(file.getInputStream(), BuildingVo.class, params);
                List<Building> Buildinglist = new ArrayList<>();
                for(BuildingVo buildingVo:listBuildingVo){
                    if(StrUtil.isEmpty(buildingVo.getProjectId())){
                        continue;
                    }


                    Building building= new Building();
                    BeanUtils.copyProperties(buildingVo, building);

                    //获取项目id
                    LambdaQueryWrapper<Project> queryWrapperP = new LambdaQueryWrapper<>();
                    queryWrapperP.eq(Project::getPrjName, buildingVo.getProjectId());
                    Project project = projectService.getOne(queryWrapperP);
                    if(ObjectUtil.isEmpty(project)){
                        errorLines++;
                        errorMessage.add(buildingVo.getBuildName()+"-------项目名称不存在");
                        continue;
                    }
                    building.setProjectId(project.getId());
                    Buildinglist.add(building);
                    successLines=successLines+1;
                }
                 buildingService.saveBatch(Buildinglist);
            } catch (Exception e) {
                errorMessage.add("文件导入失败：" + e.getMessage());
                errorLines++;
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return Result.error("文件导入失败！");
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return ImportExcelUtil.imporReturnRes(errorLines,successLines,errorMessage);
    }

}

错误信息打印，导出返回信息

下面展示一些 内联代码片。

@Slf4j
public class ImportExcelUtil {

    public static Result<?> imporReturnRes(int errorLines,int successLines,List<String> errorMessage) throws IOException {
        if (errorLines == 0) {
            return Result.ok("共" + successLines + "行数据全部导入成功！");
        } else {
            JSONObject result = new JSONObject(5);
            int totalCount = successLines + errorLines;
            result.put("totalCount", totalCount);
            result.put("errorCount", errorLines);
            result.put("successCount", successLines);
            result.put("msg", "总上传行数：" + totalCount + "，已导入行数：" + successLines + "，错误行数：" + errorLines);
            String fileUrl = PmsUtil.saveErrorTxtByList(errorMessage, "userImportExcelErrorLog");
            int lastIndex = fileUrl.lastIndexOf(File.separator);
            String fileName = fileUrl.substring(lastIndex + 1);
            result.put("fileUrl", "/sys/common/static/" + fileUrl);
            result.put("fileName", fileName);
            Result res = Result.ok(result);
            res.setCode(201);
            res.setMessage("文件导入成功，但有错误。");
            return res;
        }
    }
    }

工具类

@Slf4j
@Component
public class PmsUtil {


    private static String uploadPath;

    @Value("${jeecg.path.upload}")
    public void setUploadPath(String uploadPath) {
        PmsUtil.uploadPath = uploadPath;
    }

    public static String saveErrorTxtByList(List<String> msg, String name) {
        Date d = new Date();
        String saveDir = "logs" + File.separator + DateUtils.yyyyMMdd.get().format(d) + File.separator;
        String saveFullDir = uploadPath + File.separator + saveDir;

        File saveFile = new File(saveFullDir);
        if (!saveFile.exists()) {
            saveFile.mkdirs();
        }
        name += DateUtils.yyyymmddhhmmss.get().format(d) + Math.round(Math.random() * 10000);
        String saveFilePath = saveFullDir + name + ".txt";

        try {
            //封装目的地
            BufferedWriter bw = new BufferedWriter(new FileWriter(saveFilePath));
            //遍历集合
            for (String s : msg) {
                //写数据
                if (s.indexOf("_") > 0) {
                    String arr[] = s.split("_");
                    bw.write("第" + arr[0] + "行:" + arr[1]);
                } else {
                    bw.write(s);
                }
                //bw.newLine();
                bw.write("\r\n");
            }
            //释放资源
            bw.flush();
            bw.close();
        } catch (Exception e) {
            log.info("excel导入生成错误日志文件异常:" + e.getMessage());
        }
        return saveDir + name + ".txt";
    }

}