SpringMVC 下载Excel模板和导入数据

来源：互联网发布：什么是淘宝旺旺号啊编辑：程序博客网时间：2024/05/01 15:52

一：模板下载功能

页面：

    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;        }    }


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

阅读全文

0 0