WEB 报表导入导出操作

需要导poi包:

         <dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>3.17</version>
 <classifier>sources</classifier>
</dependency>

请求路径:/stuExcel

/**
     * 报表导出
     * @param response
     */
    @RequestMapping("/stuExcel")
    @LogAnno(value="对学生数据进行了excel表格导出",grade="info")
    public void stuExcel(HttpServletResponse response){
        //查询所有学生信息
        List<Student> list = stuService.selectAll();
        //创建输出流
        OutputStream fileOut = null;
        try {
            // 导出
            // 重置输出流
            response.reset();
            // 设置导出Excel报表的导出形式
            response.setContentType("application/vnd.ms-excel");
            // 自定义响应文件名
            String fileName = new String("学生信息表".getBytes("utf-8"),
                    "ISO-8859-1");
            response.setHeader("Content-disposition", "attachment;filename="
                    + fileName + ".xls");
            fileOut = response.getOutputStream();
            // 创建工作空间
            Workbook wb = new HSSFWorkbook();
            // 创建sheet
            Sheet sheet = wb.createSheet("sheet1");
            // 设置列宽
            sheet.setColumnWidth(0, 2300);
            sheet.setColumnWidth(1, 2300);
            sheet.setColumnWidth(2, 2300);
            sheet.setColumnWidth(3, 3400);

            CreationHelper creationHelper = wb.getCreationHelper();
            // 创建行 从 0 开始为第一行
            Row row = sheet.createRow((short) 0);
            row.setHeight((short) 450);// 目的是想把行高设置成25px
            // 创建列 从0 开始为第一列
            // 第一行的数据
            row.createCell(0).setCellValue(
                    creationHelper.createRichTextString("学生编号")
            );
            row.createCell(1).setCellValue(
                    creationHelper.createRichTextString("学生姓名"));
            // 设置String
            row.createCell(2).setCellValue(
                    creationHelper.createRichTextString("就业单位"));
            row.createCell(3).setCellValue(
                    creationHelper.createRichTextString("学生图片"));
            row.createCell(4).setCellValue(
                    creationHelper.createRichTextString("学生薪资"));
            row.createCell(5).setCellValue(
                    creationHelper.createRichTextString("入职时间"));
            row.createCell(6).setCellValue(
                    creationHelper.createRichTextString("培训时间"));
            row.createCell(7).setCellValue(
                    creationHelper.createRichTextString("是否是明星学员"));
            row.createCell(8).setCellValue(
                    creationHelper.createRichTextString("学校"));
            row.createCell(9).setCellValue(
                    creationHelper.createRichTextString("学历"));
            row.createCell(10).setCellValue(
                    creationHelper.createRichTextString("工作地址"));
            row.createCell(11).setCellValue(
                    creationHelper.createRichTextString("学生感言"));
            row.createCell(12).setCellValue(
                    creationHelper.createRichTextString("状态"));
            row.createCell(13).setCellValue(
                    creationHelper.createRichTextString("备注"));
            row.createCell(14).setCellValue(
                    creationHelper.createRichTextString("作者"));

            int i=1;
            for (Student stu : list) {
                
                Row row1 = sheet.createRow((short) i);
                row1.setHeight((short) 450);// 目的是想把行高设置成25px
                
                String uid=String.valueOf(stu.getStuId()); 
                // 第二行的数据
                row1.createCell(0).setCellValue(
                        creationHelper.createRichTextString(uid));
                row1.createCell(1).setCellValue(
                        creationHelper.createRichTextString(stu.getStuName()));
                // 设置String
                row1.createCell(2).setCellValue(
                        creationHelper.createRichTextString(stu.getStuCompany()));
                row1.createCell(3).setCellValue(
                        creationHelper.createRichTextString(stu.getStuPicture()));
                row1.createCell(4).setCellValue(
                        creationHelper.createRichTextString(String.valueOf(stu.getStuSalary())));
                row1.createCell(5).setCellValue(
                        creationHelper.createRichTextString(String.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(stu.getStuEntrytime()))));
                row1.createCell(6).setCellValue(
                        creationHelper.createRichTextString(String.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(stu.getStuTrainingtime()))));
                String isStart="否";
                if(stu.getStuIsstar()!=null){
                    if(stu.getStuIsstar()==1){
                        isStart="是";
                    }
                }
                row1.createCell(7).setCellValue(
                        creationHelper.createRichTextString(isStart));
                row1.createCell(8).setCellValue(
                        creationHelper.createRichTextString(String.valueOf(stu.getStuSchool())));
                row1.createCell(9).setCellValue(
                        creationHelper.createRichTextString(stu.getStuEducation()));
                row1.createCell(10).setCellValue(
                        creationHelper.createRichTextString(stu.getStuWorkaddress()));
                row1.createCell(11).setCellValue(
                        creationHelper.createRichTextString(stu.getStuRecollections()));
                //1表示缉编中       2 待审核   3  待发布   4 取消发布    5 过期 
                String style="";
                //判断状态
                switch (stu.getStuState()) {
                case 1:style="缉编中";break;
                case 2:style="待审核";break;
                case 3:style="待发布";break;
                case 4:style="取消发布";break;
                case 5:style="过期 ";break;
                }
                row1.createCell(12).setCellValue(
                        creationHelper.createRichTextString(style));
                row1.createCell(13).setCellValue(
                        creationHelper.createRichTextString(stu.getStuNote()));
                row1.createCell(14).setCellValue(
                        creationHelper.createRichTextString(stu.getStuWriter()));
                
                i++;
            }

            
            wb.write(fileOut);
            fileOut.close();
            
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }

/**
     * 报表导入
     */
    @RequestMapping("/ExcelInfo")
    @LogAnno(value="对学生数据进行了excel表格导入",grade="info")
    public RespModel ExcelInfo(MultipartFile file,HttpServletRequest request){
        RespModel rm=new RespModel();
        InputStream fileIn=null;
        try {
            fileIn=file.getInputStream();
            //根据指定的文件输入流导入Excel从而产生Workbook对象
             Workbook wb0 = new HSSFWorkbook(fileIn);
             //获取Excel文档中的第一个表单
             Sheet sht0 = wb0.getSheetAt(0);
             //对Sheet中的每一行进行迭代
             for (Row r : sht0) {
                  //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
                 if(r.getRowNum()<1){
                     continue;
                 }
                 //创建实体类
                 Student stu=new Student();
                 //取出当前行第1个单元格数据，并封装在info实体stuName属性上
                 stu.setStuName(r.getCell(1)==null?null:r.getCell(1).getStringCellValue());
                 stu.setStuCompany(r.getCell(1)==null?null:r.getCell(2).getStringCellValue());
                 stu.setStuEducation(r.getCell(1)==null?null:r.getCell(9).getStringCellValue());
                 stu.setStuEntrytime(r.getCell(5)==null?null:new SimpleDateFormat("yyyy-MM-dd").parse(r.getCell(5).getStringCellValue()));
                 stu.setStuIsstar("是".equals(r.getCell(7))==true?1:0);
                 stu.setStuNote(r.getCell(13)==null?null:r.getCell(13).getStringCellValue());
                 stu.setStuPicture(r.getCell(3)==null?null:r.getCell(3).getStringCellValue());
                 stu.setStuRecollections(r.getCell(11)==null?null:r.getCell(11).getStringCellValue());
                 stu.setStuSalary(r.getCell(4)==null?null:Float.valueOf(r.getCell(4).getStringCellValue()));
                 stu.setStuSchool(r.getCell(8)==null?null:r.getCell(8).getStringCellValue());
                 String state = r.getCell(12)==null?"":r.getCell(12).getStringCellValue();
                 int sta=1;
                 switch (state) {
                    case "缉编中": sta=1;break;
                    case "待审核": sta=2;break;
                    case "待发布": sta=3;break;
                    case "取消发布": sta=4;break;
                    case "过期": sta=5;break;
                    }
                 stu.setStuState(sta);
                 stu.setStuTrainingtime(r.getCell(6)==null?null:new SimpleDateFormat("yyyy-MM-dd").parse(r.getCell(6).getStringCellValue()));
                 stu.setStuWiter(r.getCell(14)==null?null:r.getCell(14).getStringCellValue());
                 stu.setStuWorkaddress(r.getCell(10)==null?null:r.getCell(10).getStringCellValue());
                 //添加学生
                 stuService.addStuInfo(stu);
             }
                
            rm.setFlag(true);
            rm.setMsg("数据导入成功!");
            fileIn.close();
        } catch (Exception e) {
            System.out.println("异常");
            rm.setFlag(false);
            rm.setMsg("数据导入失败!");
            e.printStackTrace();
        }
        return rm;
    }