一、导入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();//用户等级
          
         /* 判断格式
         String qty = "0";
          switch (row.getCell(1).getCellType()) {
              case HSSFCell.CELL_TYPE_STRING:
                    qty = row.getCell(1).getRichStringCellValue().getString().trim();
                    break;
              case HSSFCell.CELL_TYPE_NUMERIC:
                    qty = nf.format(row.getCell(1).getNumericCellValue());
                    break;
              default:
                    qty = "";
                }
*/
          //判断是否重复用户名重复
          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();
   }