Excel上传下载

普通操作

 <dependency>
   			<groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
</dependency>

public static void testExcel2() throws IOException {
        //创建工作薄对象
        HSSFWorkbook workbook=new HSSFWorkbook();//这里也可以设置sheet的Name
        //创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        //创建工作表的行
        HSSFRow row = sheet.createRow(0);//设置第一行，从零开始
        row.createCell(2).setCellValue("aaaaaaaaaaaa");//第一行第三列为aaaaaaaaaaaa
        row.createCell(0).setCellValue(new Date());//第一行第一列为日期
        workbook.setSheetName(0,"sheet的Name");//设置sheet的Name

        //文档输出
        FileOutputStream out = new FileOutputStream("D:/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
        workbook.write(out);
        out.close();
    }

    public static void main(String[] args) throws IOException {
        testExcel2();
    }

打包jar上传

public void test(HttpServletRequest request, HttpServletResponse response) {
        try {
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            response.setContentType("application/vnd.ms-excel");

            String outFileName = "position";
            String filenameSend = "";
            if (agent != null && agent.toLowerCase().indexOf("firefox") > 0) {
                filenameSend = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(outFileName.getBytes("UTF-8")))) + "?=";
            } else {
                filenameSend = java.net.URLEncoder.encode(outFileName, "UTF-8");
            }
            if (agent.contains("firefox")) {
                response.setCharacterEncoding("utf-8");
                response.setHeader("content-disposition", "attachment;filename=" + filenameSend + ".xlsx");
            } else {
                response.setHeader("content-disposition", "attachment;filename=" + filenameSend + ".xlsx");
            }
//            File file = ResourceUtils.getFile("classpath:templates/position.xlsx");
//            File file = ResourceUtils.getFile("/app/tmp/download/position.xlsx");
//            springboot 项目打成 jar 包后，找不到 ResourceUtils.getFile 读取的文件。使用 ResourceUtils.getFile 读取的文件为空
//            解决办法：
//            不使用 ResourceUtils.getFile 方法，改用 ClassPathResource 读取，具体代码如下
            ClassPathResource classPathResource = new ClassPathResource("templates/position.xlsx");
            InputStream is = classPathResource.getInputStream();

            String filePath = classPathResource.getPath();
            log.info("filePath---------------" + filePath);

            String fileName = classPathResource.getFilename();
            log.info("fileName---------------" + fileName);
//            InputStream is = new FileInputStream(file);
            Workbook wb = ExcelImportUtils.getWorkbook(is, fileName);
            is.close();

            PositionTypeEnum[] values = PositionTypeEnum.values();
            for (int i = 0; i < values.length; i++) {
                Sheet sheet = wb.getSheetAt(i);
                Row row = sheet.createRow(1);
                for (int j = 0; j < 14; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(j + 1);
                }
            }
            OutputStream out = response.getOutputStream();
            wb.write(out);
        } catch (Exception e) {
            log.error("" + e);
        }
    };

打包jar下载

 @ResponseBody
    @RequestMapping(value = "/batchImport", method = RequestMethod.POST)
    public String batchImportUserKnowledge(@RequestParam(value = "file") MultipartFile file) {

        long startMili = System.currentTimeMillis();// 当前时间对应的毫秒数
        //判断文件是否为空
        if (file == null) {
            return ("文件不能为空");
        }
        long size = file.getSize();
        long big = 1024 * 128;
        log.info("文件大小为：" + size);
        if (size > big) {
            return ("文件大小超过128kb,");
        }
        //获取文件名
        int filesize = file.getOriginalFilename().length();
        String fileName = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("\\") + 1, filesize);
        log.info("文件名称为：" + fileName);
        //验证文件名是否合格l
        if (!ExcelImportUtils.validateExcel(fileName)) {
            return ("文件类型不正确,请上传excel文件");
        }
        //进一步判断文件内容是否为空（即判断其大小是否为0或其名称是否为null）
        if (StringUtils.isEmpty(fileName) || size == 0) {
            return ("文件内容不可为空");
        }
//        String path1 = ClassUtils.getDefaultClassLoader().getResource("").getPath();
//        String tempUrl = "/app/tmp/" + "uploadTemp" + "/";
//        log.info(path1);
//       springboot中的配置文件中，可以使用${user.dir}获取 小型服务中涉及文件上传下载到本地服务器；规避打jar包后发生java.io.FileNotFoundException
        String path = System.getProperty("user.dir");
        log.info(path);
        String tempUrl = path  + "/uploadTemp" + "/";
        log.info("______________________________>" + tempUrl);
        File uploadDir = new File(tempUrl);
        log.info("+++++++++++++>");
        //创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。）
        if (!uploadDir.exists())
            uploadDir.mkdirs();
        //新建一个文件
        File tempFile = new File(tempUrl + new Date().getTime() + fileName);
        InputStream is = null;
        try {
            file.transferTo(tempFile);
            is = new FileInputStream(tempFile);
            Workbook workbook = ExcelImportUtils.getWorkbook(is, fileName);
            String sheetName = workbook.getSheetAt(0).getSheetName();
            log.info("sheet名称: "+sheetName);
            //删除上传的临时文件
            if (tempFile.exists()) {
                tempFile.delete();
            }
        } catch (Exception e) {
            e.getMessage();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                log.error("报错+++++++++++++++++++++++报错");
                e.printStackTrace();
            }
        }


        long endMili = System.currentTimeMillis();
        log.info("excel批量导入总耗时为：" + (endMili - startMili) + "毫秒");
        return ("导入成功");
    }