Excel导入导出demo

POI-Excet导出并下载

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

	@ApiOperation(value = "文档测试")
    @GetMapping("/test")
    public ResponseEntity demoDownload(HttpServletResponse response) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();//创建HSSFWorkbook对象

        HSSFSheet sheet = wb.createSheet("成绩表");//建立sheet对象

        HSSFRow row1 = sheet.createRow(0); //在sheet里创建第一行，参数为行索引

        HSSFCell cell = row1.createCell(0); //创建单元格

        cell.setCellValue("学生成绩表");  //设置单元格内容

        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

        //在sheet里创建第二行

        HSSFRow row2 = sheet.createRow(1);

        //创建单元格并设置单元格内容

        row2.createCell(0).setCellValue("姓名");

        row2.createCell(1).setCellValue("班级");

        row2.createCell(2).setCellValue("语文成绩");

        row2.createCell(3).setCellValue("数学成绩");

        row2.createCell(4).setCellValue("英语成绩");

        //在sheet里创建第三行

        HSSFRow row3 = sheet.createRow(2);

        row3.createCell(0).setCellValue("小明");

        row3.createCell(1).setCellValue("1班");

        row3.createCell(2).setCellValue(80);

        row3.createCell(3).setCellValue(75);

        row3.createCell(4).setCellValue(88);

        HSSFRow row4 = sheet.createRow(3);

        row4.createCell(0).setCellValue("小红");

        row4.createCell(1).setCellValue("1班");

        row4.createCell(2).setCellValue(82);

        row4.createCell(3).setCellValue(70);

        row4.createCell(4).setCellValue(90);

        //输出Excel文件

        OutputStream output = response.getOutputStream();

        response.reset();

        //设置响应头，浏览器会提示(直接)下载文件

        response.setHeader("Content-disposition", "attachment; filename=Student.xls");

        response.setContentType("application/msexcel");

        wb.write(output);

        output.close();
        return ResponseEntity.ok("成功");
    }

Excel 导入数据

	@ApiOperation(value = "上传保存测试案例")
    @GetMapping("/test")
    public ResponseEntity  demoDownload1(@RequestParam("filePath") String filePath, HttpServletResponse response) throws IOException {
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            return ResponseEntity.ok("文件不是excel类型");
        }
        File file = new File(filePath);
        ArrayList<ScoreInfo> temp = new ArrayList<>();
//        FileInputStream fileIn = new FileInputStream(filePath);
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = null;
        FileInputStream fileIn = null;
        try {
            fileIn = new FileInputStream(file);
        } catch (Exception e) {
            System.out.println(e);
        }

        try {
            //2003版本的excel，用.xls结尾
            wb0 = new HSSFWorkbook(fileIn);
        } catch (Exception e) {
            try {
                //2007版本的excel，用.xlsx结尾
                wb0 = new XSSFWorkbook(fileIn);
            } catch (Exception e1) {
                System.out.println(e1);
               return ResponseEntity.ok("文件错误");
            }
        }


        //获取Excel文档中的第一个表单
        Sheet sht0 = wb0.getSheetAt(0);
//        int lastRowNum = sht0.getLastRowNum();
        //获取最后一行
//        System.out.println(lastRowNum);
        //对Sheet中的每一行进行迭代
        for (Row r : sht0) {
            //如果当前行的行号（因为索引从0开始）未达到2则重新循环
            if (r.getRowNum() < 2) {
                continue;
            }
            //创建实体类
            ScoreInfo info = new ScoreInfo();

            //取出当前行第1个单元格数据，并封装在info实体stuName属性上
            Cell cell1 = r.getCell(0);
            cell1.setCellType(CellType.STRING);
            info.setStuName(cell1.getStringCellValue());

            Cell cell2 = r.getCell(1);
            cell2.setCellType(CellType.STRING);
            info.setClassName(cell2.getStringCellValue());

            Cell cell3 = r.getCell(2);
            cell3.setCellType(CellType.STRING);
            info.setRscore(cell3.getStringCellValue());

            Cell cell4 = r.getCell(3);
            cell4.setCellType(CellType.STRING);
            info.setLscore(cell4.getStringCellValue());

            temp.add(info);
        }
        fileIn.close();

        System.out.println("到此位置 将所有数据导入到集合中：temp");
        
        return ResponseEntity.ok("成功");
    }

代码有待优化