springboot excel导入导出问题

下载代码

• 方法一 部分后面提到的问题没解决，历史代码 只是贴出来供参考

try (InputStream input = new BufferedInputStream(getClass().getResource("/static/" + fileName).openStream())){
            //设置要下载的文件的名称
            response.reset();
            response.setHeader("Content-disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
            //通知客服文件的MIME类型
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //获取文件的路径
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len = 0;
            while ((len = input.read(b)) >0) {
                size += len;
                out.write(b, 0, len);
            }
            System.out.println(size);
//            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
        } catch (Exception ex) {
            throw new BaseException(ResponseCodeConstants.FAIL.getMessage(), ResponseCodeConstants.FAIL.getCode());
        }

• 方法2 推荐使用

        try {
            response.reset();
            response.setHeader("Content-disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
            //通知客服文件的MIME类型
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String extName = FilenameUtils.getExtension(fileName);
            Workbook workbook ;
            if (".xls".equals(extName)) {
                workbook = new HSSFWorkbook(getClass().getResource("/static/" + fileName).openStream());
            } else if (".xlsx".equals(extName)) {
                workbook = new XSSFWorkbook(getClass().getResource("/static/" + fileName).openStream());
            } else {
                // 无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式
                throw new IllegalArgumentException("Invalid excel version");
            }
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new BaseException(ResponseCodeConstants.FAIL.getMessage(), ResponseCodeConstants.FAIL.getCode());
        }

- excel对应的contentType  
  测试证明证明   作为response输出流的时候 contentType设置为application/vnd.ms-excel 均能正常返回
  上传文件时，获取到的contentType仅根据提交文件的后缀名匹配，  
  不能绝对判断是否是一个该类型的文件（即将xls文件后缀修改为xlsx后，得到的type为application/vnd.openxmlformats-officedocument.spreadsheetml.sheet）

xls	application/vnd.ms-excel
xlsx	application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

• 需要注意区分.xls 和 .xlsx的区别
  • xls office2003的excel后缀，对应的 HSSFWorkbook
  • xlsx office2007的excel后缀，对应的 XSSFWorkbook
• getClass().getResource("/static/" + fileName).openStream()
  • 通过getResource获取资源的根路径URI， 然后打开io流转换成我们要的表单 最后通过workbook.write()输出到httpResponse的输出流中，完成文件的下载
• fileName=" + URLEncoder.encode(fileName, “UTF-8”)
  • 输出文件名需要通过该方式进行编码的转换，具体原因没有深究，否则输出可能乱码
• maven打包时 xls文件意外被损坏，导致每次下载的时候提醒
  • org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)
  • <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-resources-plugin</artifactId> <configuration> <encoding>UTF-8</encoding> <nonFilteredFileExtensions> <nonFilteredFileExtension>xlsx</nonFilteredFileExtension> <nonFilteredFileExtension>xls</nonFilteredFileExtension> </nonFilteredFileExtensions> </configuration> </plugin>
  • 通过导入插件的方式解决了这个问题，具体原因不明，maven打包的时候对于资源文件不应该直接copy就好么，怎么会损坏文件格式的

参考

https://blog.csdn.net/u011374582/article/details/83270016 JAVA：excel导入后解析报错
https://blog.csdn.net/Olive_ZT/article/details/80726013 Java用POI读取excel文件，报异常：NotOLE2FileException

上传Excel

    @ResponseBody
    @PostMapping("/importFromExcel")
    public JsonResult<Long>  importFromExcel(@RequestParam MultipartFile file, HttpServletRequest request) throws IOException {
                UserDto userDto = UserUtil.getuser(request);
        if(null == userDto) {
            throw new BaseException(ResponseCodeConstants.NO_LOGIN.getMessage(), ResponseCodeConstants.NO_LOGIN.getCode());
        }
        List<EventLibraryTransactionDO> eventLibraryTransactionDoS = new LinkedList<>();
        String fileName = file.getOriginalFilename();
        String extName = FilenameUtils.getExtension(fileName);
        Workbook workbook;
//        File tempFile = File.createTempFile("prefix_", file.getOriginalFilename());

//        file.transferTo(tempFile);
        //创建Excel，读取文件内容
//        FileInputStream fileInputStream = FileUtils.openInputStream(tempFile);
        InputStream fileInputStream = file.getInputStream();
        if (SUFFIX_EXCEL2003.equals(extName)) {
            workbook = new HSSFWorkbook(fileInputStream);
        } else if (SUFFIX_EXCEL2007.equals(extName)) {
            workbook = new XSSFWorkbook(fileInputStream);
        } else {
            // 无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式
            return JsonResult.newFail(ResponseCodeConstants.PARAM_INPUT_INVALID.getCode(),"文件后缀名必须为xls或xlsx");
        }
        //获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        //获取sheet中第一行行号
        int firstRowNum = sheet.getFirstRowNum();
        //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();
        try {
            //循环插入数据
            for(int i=firstRowNum+1;i<=lastRowNum;i++){
               DO DO = new DO();
                Row row = sheet.getRow(i);
                //权力基本码
                Cell rightCode = row.getCell(RIGHT_CODE_INDEX);
                if(rightCode!=null){
                    rightCode.setCellType(CellType.STRING);
                    DO.setRightCode((rightCode.getStringCellValue()));
                }
                DoS.add(DO);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            workbook.close();
        }
        Long aLong = Service.importFromExcel(DoS);
        return JsonResult.newSucc(aLong);
    }