从服务器下载Excel模板到本地，然后本地再将数据填好，再上传Excel新增到数据库[springboot]

一、Excel下载

1.在本地新建Excel文件模板，并设置文件单元格格式（方便存储时读取数据）

2.选择字段，右键，设置单元格格式

3.将Id设置为数值类型，并且是正数值类型，将Name,Address,Sex,Content设置为文本类型

4.将文件存在服务器上，放在template下，也可以自定义字段，写代码时注意路径问题

5.代码

    @Resource
    private ResourceLoader resourceLoader;


//controller接口
@RequestMapping(value="/down",method= RequestMethod.GET)
    public void down(HttpServletRequest request, HttpServletResponse response,
                              @RequestParam("fileName") String fileName){
        if(fileName==null||"".equals(fileName)){
            logger.info("============================文件不存在");
            return;
        }
        messageTaskService.down(request,response,fileName);
    }

//service代码
 /**
     * 下载任务详情模板
     * @param
     * @return
     */
    public void down(HttpServletRequest request, HttpServletResponse response,String fileName){
        if(fileName==null|| !fileName.endsWith(".xls")){
            logger.info("=============downLoadModel's fileName is null or file type is wrong !");
            return;
        }
        logger.info("=============start in downLoadModel , fileName = "+fileName);
        InputStream inputStream = null;
        ServletOutputStream servletOutputStream = null;
        try {
            String filename = fileName;
            String path = "template/"+fileName;
            org.springframework.core.io.Resource resource = resourceLoader.getResource("classpath:"+path);

            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.addHeader("charset", "utf-8");
            response.addHeader("Pragma", "no-cache");
            String encodeName = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition", "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);

            inputStream = resource.getInputStream();
            servletOutputStream = response.getOutputStream();
            IOUtils.copy(inputStream, servletOutputStream);
            response.flushBuffer();
            logger.info("=============success in downLoadModel");
        } catch (Exception e) {
            logger.error("=============filed in downLoadModel , fileName = "+fileName);
            e.printStackTrace();
        } finally {
            try {
                if (servletOutputStream != null) {
                    servletOutputStream.close();
                    servletOutputStream = null;
                }
                if (inputStream != null) {
                    inputStream.close();
                    inputStream = null;
                }
                // 召唤jvm的垃圾回收器
                System.gc();
            } catch (Exception e) {
                logger.error("=============filed in downLoadModel , fileName = "+fileName);
                e.printStackTrace();
            }
        }
    }

二、Excel上传

1.将数据写到刚下载下来的文件夹

2.代码编写

//controller
@RequestMapping(value="/upLoad",method= RequestMethod.POST,consumes = "multipart/*",headers = "content-type=multipart/form-data")
    public ResultData  uploadImg( @RequestParam("file") MultipartFile file,HttpServletRequest request, HttpServletResponse response) {
        ResultData resultData=new ResultData(Constant.SUCCESS,Constant.RETURN_SUCCESS_CODE,Constant.RETURN_SUCCESS_MESSAGE);
        if(file==null||file.getName()==null){
            return new ResultData(Constant.FAIL,Constant.RETURN_FAIL_CODE,"文件上传有误");
        }
        try {
            resultData=messageTaskService.upLoadModel(request,response,file);
            return resultData;
        } catch (Exception e) {
            logger.error(e.toString());
            resultData.setResult(Constant.FAIL);
            resultData.setCode(Constant.RETURN_FAIL_CODE);
            resultData.setMessage("任务详情模板上传失败！");
            return resultData;
        }
    }



//service
/*
    上传任务详情Excel
     */
    @Transactional
    public ResultData upLoadModel(HttpServletRequest request,HttpServletResponse response,MultipartFile file) throws Exception {
        ResultData resultData=new ResultData(Constant.SUCCESS,Constant.RETURN_SUCCESS_CODE,Constant.RETURN_SUCCESS_MESSAGE);
        logger.info("=============start in  upLoadModel");
        logger.info("=============start to trans data");
        ExcelUtil ept = new ExcelUtil();
        List<MessageTaskDetail> list = ept.uploadExcelData(file);
        logger.info("=============end to trans data , and MessageTaskDetailList size="+list.size());
        System.out.println(list.size());
        if(list==null||list.size()==0){
            resultData.setMessage("请上传xls或xlsx带数据的文件！");
        }else{
            logger.info("=============start to delete database taskDetail");
            //删除
            int deleteCount = messageTaskDetailMapper.deleteByTaskId(list.get(0).getTaskId());
            logger.info("=============end to delete taskDetail . Count = " +deleteCount);
            logger.info("=============start to insert taskDetail");
            MessageTaskDetail stu=null;
            for (int i=0;i< list.size();i++) {
                stu=list.get(i);
                messageTaskDetailMapper.insertSelective(stu);
                System.out.println("### "+stu.toString());
            }
            logger.info("=============insert taskDetail success");
        }
        logger.info("=============end in upLoadModel");
        return resultData;
    }

/**
     * 上传Excel表格
     * @param file
     * @return
     */
   public List<MessageTaskDetail> uploadExcelData(MultipartFile file) throws Exception {
        List<MessageTaskDetail> list = new ArrayList<MessageTaskDetail>();
       String fileName=file.getOriginalFilename();
        InputStream is = null;
        try {
            is = file.getInputStream();
            Workbook workbook = null;
            if(fileName.toLowerCase().endsWith(".xls")||fileName.toLowerCase().endsWith(".xlsx")){
                workbook = new XSSFWorkbook(is);
                logger.info("=============start to uploadExcelData");
                if(fileName.contains("user")){//当下载文件夹已存在时，文件会自动重命名user(1).xls,user(2).xls,user(3).xls...所以只要判断文件名是否包含user就行了
                    list=this.transUserData(workbook,list);
                }else {
                    logger.error("=============file model is wrong ,please upload file model by '.xls' type");
                    return null;
                }
                logger.info("=============success uploadExcelData");
            }else{
                logger.error("=============文件格式不正确，请上传.xls或xlsx标准模板");
                return null;
           }

        }finally {
            if(is!=null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }



 /**
     * 转换User数据
     * @param workbook
     * @param list
     * @return
     */
    public List<MessageTaskDetail> transUserData( Workbook workbook,List<MessageTaskDetail> list ){
        Sheet sheet = null;
        Row row = null;
        Integer succ = 0;
        Cell headCell=null;
        if(workbook!=null){
            MessageTaskDetail messageTaskDetail = null;
            int index = 0;//表  列索引
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//循环表
                sheet = workbook.getSheetAt(i);// 第i张表格

                //表头判断是否正确
                row =sheet.getRow(0);// 第1行，表头
                // TaskId	Name AppId	Tags	Content
                if(row == null){break;};//判断行是否还有数据
                if(row.getCell(0)==null){break;};//判断第一行第一列是否有值
                headCell=row.getCell(1);
                if(!headCell.getStringCellValue().equals("Name")){
                    logger.info("============= Excel's "+headCell.getStringCellValue()+" column is wrong ! It's should be 'Name' !");
                    break;
                }
                headCell=row.getCell(2);
                if(!headCell.getStringCellValue().equals("Address")){
                    logger.info("============= Excel's "+headCell.getStringCellValue()+" column is wrong ! It's should be 'Address' !");
                    break;
                }
                headCell=row.getCell(3);
                if(!headCell.getStringCellValue().equals("Sex")){
                    logger.info("============= Excel's "+headCell.getStringCellValue()+" column is wrong ! It's should be 'Sex' !");
                    break;
                }
                headCell=row.getCell(4);
                if(!headCell.getStringCellValue().equals("Content")){
                    logger.info("============= Excel's "+headCell.getStringCellValue()+" column is wrong ! It's should be 'Content' !");
                    break;
                }

                for(int j=1;j < sheet.getPhysicalNumberOfRows();j++){//循环行
                    row = sheet.getRow(j);
                    if(row == null){break;};//判断行是否还有数据
                    index=0;//每次换行，重新从第一列开始
                    if(row.getCell(index)==null){break;};
                    messageTaskDetail  = new MessageTaskDetail();
                    //任务ID
                    Cell cell = row.getCell(index);
                    if(cell!=null && !cell.equals("")){
                        messageTaskDetail.setTaskId((long)cell.getNumericCellValue());
                    }
                    //姓名
                    cell = row.getCell(++index);
                    if(cell!=null && !cell.equals("")){
                        messageTaskDetail.setName(cell.getStringCellValue());
                    }
                    //Address
                    cell = row.getCell(++index);
                    if(cell!=null && !cell.equals("")){
                        messageTaskDetail.setAddress(cell.getStringCellValue());
                    }

                    //Sex
                    cell = row.getCell(++index);
                    if(cell!=null && !cell.equals("")){
                        messageTaskDetail.setSex(cell.getStringCellValue());
                    }

                    //推送内容
                    cell = row.getCell(++index);
                    if(cell!=null && !cell.equals("")){
                        messageTaskDetail.setContent(cell.getStringCellValue());
                    }
                    //赋值
                    list.add(messageTaskDetail);
                }
            }
        }
        return list;
    }

【注意】ResultData 是返回类型，不用在意