一、導入excel：

首先來個區(qū)分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(); 
        
        //根據(jù)版本選擇創(chuàng)建Workbook的方式  
        Workbook wb = null;
        Sheet sheetAt = null;
        //根據(jù)文件名判斷文件是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();
        //用于生日轉(zhuǎn)換
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        //double轉(zhuǎn)String
        NumberFormat nf = NumberFormat.getInstance();
        nf.setGroupingUsed(false);
        //false則不分組顯示數(shù)據(jù), 如：999999999 
       //true則分組顯示數(shù)據(jù)们衙，即每三位數(shù)為一個分組钾怔，分組間以英文半角逗號分隔, 如：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);
          }
          
           //數(shù)據(jù)封裝 ，存到數(shù)據(jù)庫
          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);
        }
        
       //保存數(shù)據(jù)到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 參數(shù)名對應
最后debug 可以獲取數(shù)據(jù)

二蒙挑、導出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 {
          // 創(chuàng)建 excel 文件
          workbook = new HSSFWorkbook();
          // 創(chuàng)建一個標簽頁
          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);
          
          //創(chuàng)建格式
          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);
          
          // 創(chuàng)建標題行
          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("狀態(tài)");
          cell5.setCellStyle(titleStyle);
          
         //DB查詢數(shù)據(jù)
         List<User> userList = userService.queryByLevelAndStatus(level, status);
         if(userList == null || userList.isEmpty()){
           return ResponseUtil.fail(403, "導出數(shù)據(jù)失敗宗侦，無用戶信息！");
         }
         
          
          // 封裝excel數(shù)據(jù)
          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, "導出數(shù)據(jù)失敺！" + e.getMessage());
      } finally {
          try {
              if (outputStream != null) {
                  workbook.close();
                  outputStream.close();
              }
          } catch (Exception e) {
              e.printStackTrace();
              return ResponseUtil.fail(403, "導出數(shù)據(jù)失敳鐾唷男旗！" + e.getMessage());
          }

      }
      return ResponseUtil.ok();
   }