SpringMVC 下载Excel模板和导入数据

最新推荐文章于 2025-06-30 10:14:43 发布

原创最新推荐文章于 2025-06-30 10:14:43 发布 · 3.6k 阅读

0 ·

CC 4.0 BY-SA版权

spring 专栏收录该内容

53 篇文章

订阅专栏

本文介绍了一个基于Java Web的应用中如何实现文件模板下载和Excel数据导入的功能，详细展示了前后端交互流程及服务端处理逻辑。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一：模板下载功能

页面：

    window.location.href="/test/downloadModel?id="+result;

id是uuid生成的一个请求ID;

java:

    @ResponseBody
    @RequestMapping(value ="/downloadModel", method = {RequestMethod.GET})
    public ResponseEntity<byte[]> downloadModel(@RequestParam("id") String id,HttpServletRequest req) throws InterruptedException {
        RedisClient.getInstance().put("progress_"+id,0,60*60);
        ResponseEntity<byte[]> download = fileService.download("files/cardKind/cardKindModel.xlsx", id, req);
        RedisClient.getInstance().put("progress_"+id,100,60*60);
        return download;
    }

download方法

@Override
    public ResponseEntity<byte[]> download(String path, String id, HttpServletRequest req) {
        downloadLogger.info("【文件下载】 download --> 执行开始，请求文件相对路径：" + path);
        File file = null;
        try {
//            Resource resource = new ClassPathResource(path);
//            file = resource.getFile();
//            file=new File("c:/1.xlsx");
            Resource resource=new ServletContextResource(req.getServletContext(),"file/cardKindModel.xlsx");
            file = resource.getFile();
//        } catch (IOException e) {
        } catch (Exception e) {
            downloadLogger.info("【文件下载】 download -->执行异常，无法加载到服务端文件，请求文件相对路径：" + path, e);
            return null;
        }
        RedisClient.getInstance().put("progress_" + id, 10, 60 * 60);
        String fileName = null;
        try {
            fileName = new String(file.getName().getBytes("utf-8"), "ISO-8859-1");
        } catch (UnsupportedEncodingException e) {
            downloadLogger.info("【文件下载】 download -->执行异常，无法解析服务端文件，请求文件相对路径：" + path, e);
            return null;
        }

        RedisClient.getInstance().put("progress_" + id, 20, 60 * 60);
        HttpHeaders header = new HttpHeaders();
        header.setContentDispositionFormData("attachment", fileName);
        header.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        RedisClient.getInstance().put("progress_" + id, 30, 60 * 60);
        byte[] res = null;
        try {
            res = FileUtils.readFileToByteArray(file);
        } catch (IOException e) {
            downloadLogger.info("【文件下载】 download -->执行异常，解析服务端文件为字节数组异常，请求文件相对路径：" + path, e);
            return null;
        }
        RedisClient.getInstance().put("progress_" + id, 90, 60 * 60);
        return new ResponseEntity<byte[]>(res, header, HttpStatus.CREATED);
    }

ps: 模板文件在webapp目录下，可以使用ServletContextResource获取文件

Resource resource=new ServletContextResource(req.getServletContext(),"file/cardKindModel.xlsx");
file = resource.getFile();

二：导入数据功能

页面：

jquery.form.js 需要引入这个js

 $("#importExcel").ajaxSubmit({
                url:"/card/uploadeCountsExcel?id="+id,
                type:"post",
                timeout: 1000*60*60,
                success:function(data){
                    $('#uploadp').hide();
                    // $("#excel_file").val("");
                    // console.log($(data).text());
                    var res = JSON.parse($(data).text());
                    if(res.code=="0"){
                        $.messager.alert('tip',  '导入成功');
                        $("#excel_file").val("");
                    }else if (res.code=="20001"){
                        $("#excel_file").val("");

                        $.messager.confirm('确认对话框', '导入失败，是否查看详情？', function(r){
                            if (r){
                                // window.location.href="/card/importError?id="+key; //跳转到列表查询页
                                var iTop = (window.screen.availHeight - 30 - 750) / 2;
                                var iLeft = (window.screen.availWidth - 10 - 1200) / 2;
                                window.open("/card/importError?id="+key,"查看详情",'location=no,resizable=no,height=700,width=1200,innerWidth=1200,top='+iTop+',left='+iLeft);
                            }else{
                                // alert("否"); //清除缓存
                            }
                        });

                    }else{
                        $.messager.alert('tip',  '导入失败，系统内部错误！');
                    }
                }
            });

java

@ResponseBody
    @RequestMapping(value ="/uploadeCountsExcel", method = {RequestMethod.POST})
    public JSONObject uploadeCountsExcel(@RequestParam(value="filename") MultipartFile file,@RequestParam(value="id") String id, HttpServletRequest req){
        JSONObject res = fileService.importExcel(file, id);
        RedisClient.getInstance().put("progress_" + id, 100, 60 * 60);
        return res;
    }

service

    @Override
    public JSONObject importExcel(MultipartFile file, String id) {
        importExcelLogger.info("【导入次数】 importExcel -->执行开始。。。，请求ID=" + id);
        RedisClient.getInstance().put("progress_" + id, 1, 60 * 60);

        JSONObject res = new JSONObject();

        //执行校验
        String checkRes = checkFile(file);
        if (checkRes != null) {
            res.put("code", 10001);
            res.put("msg", checkRes);
            return res;
        }
        RedisClient.getInstance().put("progress_" + id, 5, 60 * 60);

        //扫描数据
        JSONObject exeImportRes = exeImport(file.getOriginalFilename(), file);
        if(!"0".equals(exeImportRes.getString("code"))){
            res.put("code",exeImportRes.getString("code"));
            res.put("msg",exeImportRes.getString("msg"));
            return res;
        }
        List<String[]> exeImportData = ( List<String[]>)exeImportRes.get("data");
        RedisClient.getInstance().put("progress_" + id, 20, 60 * 60);

        //分析数据
        JSONObject applyDataRes = applyData(exeImportData,id);

        RedisClient.getInstance().put("progress_" + id, 100, 60 * 60);
        return applyDataRes;
    }

    @Override
    public JSONArray queryImportError(String id) {
        Object o = RedisClient.getInstance().get(id);
        if(o==null){
            return null;
        }
        JSONArray res= JSONArray.parseArray(o.toString());
        if(res.size()<2){
            return null;
        }

        res.remove(0);
        return res;
    }

    private String checkFile(MultipartFile file) {
        String msg = null;
        if (file == null) {
            msg = "上传的文件为空！";
            return msg;
        }
        String fileName = file.getOriginalFilename();
        if (fileName == null || fileName == "") {
            msg = "文件名不能为空！";
            return msg;
        }
        Long size = file.getSize();
        if (size == 0) {
            msg = "文件大小应该大于0！";
            return msg;
        }
        if(size>1024 * 1024){
            msg = "文件大小应该不超过1MB！";
            return msg;
        }
        return msg;
    }

    private JSONObject exeImport(String name, MultipartFile file) {
        JSONObject res = new JSONObject();

        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        InputStream is = null;
        String extName = name.substring(name.lastIndexOf("."));
        try {
            is = file.getInputStream();
        } catch (IOException e) {
            importExcelLogger.error("【导入次数】 importExcel.exeImport -->无法根据提交的文件获取输入流！", e);
            res.put("code", "10002");
            res.put("msg", "文件解析异常");
            return res;
        }

        try {
//            if (".xls".equals(extName)) {
//                wb = new HSSFWorkbook(is);
//            } else if (".xlsx".equals(extName)) {
//                wb = new XSSFWorkbook(is);
//            }
            if (".xlsx".equals(extName)) {
                wb = new XSSFWorkbook(is);
            }else{
                importExcelLogger.error("【导入次数】 importExcel.exeImport -->文件扩展名无效，只支持xlsx类型文件解析！");
                res.put("code", "10002");
                res.put("msg", "文件解析异常");
                return res;
            }
        } catch (IOException e) {
            importExcelLogger.error("【导入次数】 importExcel.exeImport -->无法根据输入流得到Excel WorkBook！", e);
            res.put("code", "10002");
            res.put("msg", "文件解析异常");
            return res;
        }

        if(wb==null){
            importExcelLogger.error("【导入次数】 importExcel.exeImport -->Excel WorkBook未被初始化！");
            res.put("code", "10002");
            res.put("msg", "文件解析异常");
            return res;
        }

        sheet = wb.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        List<String[]> resList = new ArrayList<String[]>();
        for(int j=0;j<rows;j++){
            row = sheet.getRow(j);
//            int colNum = row.getPhysicalNumberOfCells();
            String[] rowContent = new String[8];
            for (int i = 0; i < 5; i++) {
                Cell cell = row.getCell(i);
                if("error".equals(getCellValueByCell(cell))){
                    importExcelLogger.error("【导入次数】 importExcel.exeImport -->存在未知的Cell类型！,RowNum="+j);
                    res.put("code", "10002");
                    res.put("msg", "文件解析异常");
                    return res;
                }
                rowContent[i]=getCellValueByCell(cell);
                if(i==2 && rowContent[i]!=null && !"".equals(rowContent[i]) && rowContent[i].indexOf(".")>-1){
                    String[] split = rowContent[i].split("[.]");
                    rowContent[i]=split[0];
                }
            }
            resList.add(rowContent);
        }

        res.put("code","0");
        res.put("data",resList);
        return res;
    }

    private  String getCellValueByCell(Cell cell){
        String cellVal="";
        if(cell==null||cell.toString().trim().equals("")){
            return cellVal;
        }else{
            switch (cell.getCellType()){
                case XSSFCell.CELL_TYPE_STRING:
                    cellVal=cell.getStringCellValue();
                    break;
                case XSSFCell.CELL_TYPE_NUMERIC:
                    cellVal=String.valueOf(cell.getNumericCellValue());
                    break;
                case XSSFCell.CELL_TYPE_BLANK:
                    cellVal="";
                    break;
                default:
                    cellVal="error";
                    break;
            }
            return cellVal;
        }
    }

分析数据是业务代码就不上了。