Springboot使用模板导入导出excel文件

1. 制作excel模板
   
2. 将模板放入项目中
   
3. 下载模板代码接口

    @ApiOperation(value = "excel模板下载")
    @RequestMapping(value = "templateDownload", method = RequestMethod.GET)
    public void downloadExcel(HttpServletResponse response, HttpServletRequest request,
                              @RequestParam(value = "status") int status) {
        try {
            String name =null;
            InputStream inStream =null;
            //status=0下载线路部位;1题库
            //本地项目中的模板
            if (status == 0) {
                 inStream =
                        Thread.currentThread().getContextClassLoader().getResourceAsStream("templates" +
                                "/线路部位模板.xlsx");
                //线路名称
                 name = "线路部位模板";
            }else if (status==1){
                //任务名称
                 inStream =
                        Thread.currentThread().getContextClassLoader().getResourceAsStream("templates" +
                                "/选择题库模板.xlsx");
                name = "选择题库";
            }
            response.setContentType("application/octet-stream");
            response.addHeader("Content-Disposition",
                    "attachment; filename=" + getFileName(request, name) + ".xlsx");
            response.addHeader("Content-type", " multlpart/form-data");
            // 循环取出流中的数据
            byte[] b = new byte[2048];
            int len;
            while ((len = inStream.read(b)) > 0) {
                response.getOutputStream().write(b, 0, len);
            }
            inStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 区分ie 和其他浏览器的下载文件乱码问题
     *
     * @param req
     * @param fileName
     * @return
     */
    public String getFileName(HttpServletRequest req, String fileName) {
        String userAgent = req.getHeader("user-agent");
        userAgent = userAgent == null ? "" : userAgent.toLowerCase();
        String name = fileName;
        try {
            //针对IE或者以IE为内核的浏览器：
            if (userAgent.contains("msie") || userAgent.contains("trident")) {
                name = URLEncoder.encode(name, "UTF-8");
            } else {
                name = new String(name.getBytes(), "iso-8859-1");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

4. 导入模板

    @ApiOperation(value = "题库数据导入")
    @RequestMapping(value = "/importTopicTemplate", method = RequestMethod.POST)
    @Transactional
    public RestResult<Object> importTopicTemplate(@RequestParam(value = "file") MultipartFile file) {
        try {
            if (Utils.isEmpty(file)) {
                return RestResultUtil.ok("导入文件不能为空");
            }
            String fileName = file.getOriginalFilename();
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                return RestResultUtil.ok("上传文件格式错误，请上传后缀为.xls或.xlsx的文件");
            }
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            InputStream is = file.getInputStream();
            Workbook wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            Sheet sheet = wb.getSheetAt(0);
            for (int r = 3; r <= sheet.getLastRowNum(); r++) {
                List list = new ArrayList();
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                int totalCells = row.getPhysicalNumberOfCells();
                for (int j = 0; j < 3; j++) {
                    if (row.getCell(j) != null) {
                        row.getCell(j).setCellType(CellType.STRING);
                        String cellValue = row.getCell(j).getStringCellValue();
                        list.add(cellValue);
                    }
                }
                //数据添加到数据库
                DatagoBankDetailsWithBLOBs bankDetails = new DatagoBankDetailsWithBLOBs();
                bankDetails.setId(Utils.createUUID());
                bankDetails.setCreateTime(new Date());
                bankDetails.setQuestion((String) list.get(0));
                bankDetails.setAnswers((String) list.get(1));
                bankDetails.setCheckanswer((String) list.get(2));
                bankDetailsService.insertSelective(bankDetails);
            }
            is.close();
            return RestResultUtil.ok();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return RestResultUtil.failed();
    }

友情提示：
广大网友，即可修改部分代码，或者添加自己的业务代码，即可使用，不喜勿喷，喜欢就留个赞，点个关注吧。感谢