美文网首页SpringHome
Springboot poi 导入、导出excel 简单步骤

Springboot poi 导入、导出excel 简单步骤

作者: 西西弗斯XD | 来源:发表于2017-10-25 19:04 被阅读263次

    一、导入excel:

    首先来个区分2003 与2007 版的工具类

    public class ExcelImportUtils
    {
      // @描述:是否是2003的excel,返回true是2003   
      public static boolean isExcel2003(String filePath)  {    
          return filePath.matches("^.+\\.(?i)(xls)$");    
      }    
       
      //@描述:是否是2007的excel,返回true是2007   
      public static boolean isExcel2007(String filePath)  {    
          return filePath.matches("^.+\\.(?i)(xlsx)$");    
      }    
        
      /** 
       * 验证EXCEL文件 
       * @param filePath 
       * @return 
       */  
      public static boolean validateExcel(String filePath){  
          if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){    
              return false;    
          }    
          return true;  
      }  
    }
    
    

    后台代码

    controller层

    /**
         * 导入会员信息
         * @param file
         * @return
         */
        @RequestMapping("importExcel")
        @ResponseBody
        public Object importExcel(@RequestParam(value="filename") MultipartFile file){
          if(file.isEmpty()){  
            return ResponseUtil.fail(403, "文件为空!");
          } 
          InputStream is = null;
          try
          {
            is = file.getInputStream();
            //获取文件名  
            String fileName = file.getOriginalFilename(); 
            
            //根据版本选择创建Workbook的方式  
            Workbook wb = null;
            Sheet sheetAt = null;
            //根据文件名判断文件是2003版本还是2007版本  
            if(ExcelImportUtils.isExcel2007(fileName)){  
               wb = new XSSFWorkbook(is);
               sheetAt = wb.getSheetAt(0);
            }else{  
               wb = new HSSFWorkbook(is);  
               sheetAt = wb.getSheetAt(0);
            }
            
            List<User> userlist = new ArrayList<User>();
            //用于密码加密
            BCryptPasswordEncoder encoder = new BCryptPasswordEncoder();
            //用于生日转换
            DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            //double转String
            NumberFormat nf = NumberFormat.getInstance();
            nf.setGroupingUsed(false);
            //false则不分组显示数据, 如:999999999 
           //true则分组显示数据,即每三位数为一个分组,分组间以英文半角逗号分隔, 如:999,999,999
            
            for (Row row : sheetAt) {
              int rowNum = row.getRowNum();
              if (rowNum == 0) {
                  continue;
              }
              
              String name = row.getCell(0).getStringCellValue();//用户名
              Double phone = row.getCell(1).getNumericCellValue();//手机号
              String sex = row.getCell(2).getStringCellValue();//性别
              String birthdayStr = row.getCell(3).getStringCellValue();//生日
              String level = row.getCell(4).getStringCellValue();//用户等级
              
              //判断是否重复用户名重复
              List<User> userList = userService.queryByUsername(name);
              if(!userList.isEmpty()){
                return ResponseUtil.fail(403, "该用户名重复:"+ name);
              }
              
               //数据封装 ,存到数据库
              LitemallUser user = new LitemallUser();
              user.setUsername(name);
              user.setNickname(name);
              user.setGender(sex);
              user.setUserLevel(level);
              user.setAddTime(LocalDateTime.now());
              user.setStatus("可用");
              
              user.setMobile(nf.format(phone));
              user.setBirthday(LocalDate.parse(birthdayStr, df));
              user.setPassword(encoder.encode("123456"));
              
              userlist.add(user);
            }
            
           //保存数据到DB
            if(userlist.size()>0)
            userService.insertBatch(userlist);
          }
          catch (IOException e)
          {
            e.printStackTrace();
            return ResponseUtil.serious();
          }finally {
            if (is != null) {
              try {
                  is.close();
              } catch (IOException e) {
                  e.printStackTrace();
                  return ResponseUtil.serious();
              }
            }
          }
          
          return ResponseUtil.ok();
        }
    

    pom:

    <!--导入Excel-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>
    

    最后是测试:

    1536032496(1).jpg

    使用Postman测试

    Postman01.jpg Postman02.jpg

    这边key要和controller 参数名对应
    最后debug 可以获取数据

    二、导出excel:

    简单伪代码:

    /**
         * 条件导出用户信息
         * @param response
         * @param user
         * @return
         */
        @RequestMapping("exportExcel")
        @ResponseBody
        public Object exportExcel(HttpServletResponse response,User user){
      
          //条件导出
          String level = user.getUserLevel();
          String status = user.getStatus();
          
          ServletOutputStream outputStream = null;
          HSSFWorkbook workbook = null;
          try {
              // 创建 excel 文件
              workbook = new HSSFWorkbook();
              // 创建一个标签页
              HSSFSheet sheet = workbook.createSheet("用户信息");
    
              //设置列宽
              sheet.setColumnWidth(0, 3000);
              sheet.setColumnWidth(1, 3000);
              sheet.setColumnWidth(2, 3000);
              sheet.setColumnWidth(3, 3000);
              sheet.setColumnWidth(4, 3000);
              sheet.setColumnWidth(5, 3000);
              
              //创建格式
              HSSFCellStyle titleStyle = workbook.createCellStyle();
              titleStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
              titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            
              
              HSSFFont titleFont = workbook.createFont();
              titleFont.setBold(true);//粗体
              titleFont.setFontHeightInPoints((short)12);//大小
              titleFont.setFontName("宋体");//字体类型
              titleStyle.setFont(titleFont);
              
              // 创建标题行
              HSSFRow titleRow = sheet.createRow(0);
              
              HSSFCell cell0 = titleRow.createCell(0);
              cell0.setCellValue("用户名");
              cell0.setCellStyle(titleStyle);
              
              HSSFCell cell1 = titleRow.createCell(1);
              cell1.setCellValue("手机号");
              cell1.setCellStyle(titleStyle);
              
              HSSFCell cell2 = titleRow.createCell(2);
              cell2.setCellValue("性别");
              cell2.setCellStyle(titleStyle);
              
              HSSFCell cell3 = titleRow.createCell(3);
              cell3.setCellValue("生日");
              cell3.setCellStyle(titleStyle);
              
              HSSFCell cell4 = titleRow.createCell(4);
              cell4.setCellValue("用户等级");
              cell4.setCellStyle(titleStyle);
              
              HSSFCell cell5 = titleRow.createCell(5);
              cell5.setCellValue("状态");
              cell5.setCellStyle(titleStyle);
              
             //DB查询数据
             List<User> userList = userService.queryByLevelAndStatus(level, status);
             if(userList == null || userList.isEmpty()){
               return ResponseUtil.fail(403, "导出数据失败,无用户信息!");
             }
             
              
              // 封装excel数据
              DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
              for (int i = 0; i < userList.size(); i++) {
                  titleRow = sheet.createRow(i + 1);
                  titleRow.createCell(0).setCellValue(userList.get(i).getUsername());
                  titleRow.createCell(1).setCellValue(userList.get(i).getMobile());
                  titleRow.createCell(2).setCellValue(userList.get(i).getGender());
                  titleRow.createCell(4).setCellValue(userList.get(i).getUserLevel());
                  titleRow.createCell(5).setCellValue(userList.get(i).getStatus());
                  if(userList.get(i).getBirthday() !=null){
                    titleRow.createCell(3).setCellValue(df.format(userList.get(i).getBirthday()));
                  }
              }
    
              // 设置两个头 一个输出流
              String filename = "会员信息.xls";
              outputStream = response.getOutputStream();
              // 响应信息,弹出文件下载窗口
              response.setContentType("APPLICATION/OCTET-STREAM");
              response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
              workbook.write(outputStream);
          } catch (Exception e) {
              e.printStackTrace();
              return ResponseUtil.fail(403, "导出数据失败!" + e.getMessage());
          } finally {
              try {
                  if (outputStream != null) {
                      workbook.close();
                      outputStream.close();
                  }
              } catch (Exception e) {
                  e.printStackTrace();
                  return ResponseUtil.fail(403, "导出数据失败!" + e.getMessage());
              }
    
          }
          return ResponseUtil.ok();
       }
    

    相关文章

      网友评论

      • 扶摇直上_4300:您好!我看了您的代码,我自己根据您的代码写了一个,但是用Postman发送的数据就是不入库!我的QQ122743114,能方便给我发一个源码吗?谢谢
        西西弗斯XD:不好意思,很久没打开简书了,源码已发你qq邮箱,希望对你有用,谢谢!

      本文标题:Springboot poi 导入、导出excel 简单步骤

      本文链接:https://www.haomeiwen.com/subject/yukcpxtx.html