Java開發(fā)過程中饶囚，有些需求需要實(shí)現(xiàn)Excel導(dǎo)入功能，具體就是Excel導(dǎo)入數(shù)據(jù)直接操作數(shù)據(jù)庫宠进，先將Excel導(dǎo)入實(shí)現(xiàn)方法貼出晕拆。實(shí)現(xiàn)過程如下。
1材蹬、xml配置实幕，本人使用較為成熟流行的ssm框架，即spring堤器、springMvc昆庇、mybiats整合而成框架。所以需要在springMvc的xml配置中加入如下代碼配置闸溃。這里非常重要

     <!-- 上傳配置 -->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">  
            <property name="maxUploadSize" value="2097152"></property> 
            <property name="defaultEncoding" value="UTF-8" />  
        </bean> 

2整吆、jsp頁面配置，上傳必須使用from表單辉川，且表單必須添加屬性enctype="multipart/form-data" onsubmit="return check();"并在上傳input中添加accept="xls/xlsx" size="50"表蝙。用于限制上傳文件格式。

    <form action="/Matton/batchimport.do" method="post" enctype="multipart/form-data" name="batchAdd" onsubmit="return check();">
        <div class="col-lg-4">
            <input id="excel_file" type="file" name="filename" accept="xls/xlsx" size="50"/>
        </div>
        <input id="excel_button" class="btn btn-success col-lg-1" type="submit" value="導(dǎo)入Excel"/>
       <%--  <font id="importMsg" color="red"><%=importMsg%></font><input type="hidden"/> --%>  
    </form>

3乓旗、xml配置和jsp配置已經(jīng)完成勇哗，現(xiàn)在就是后臺(tái)代碼(@RequestParam("filename") MultipartFile file這里的filename需與from表單中的input的name一致。MultipartFile 就是剛剛xml配置啟動(dòng)的寸齐。所以xml配置不可或缺。

    /**
     * excel批量導(dǎo)入
     * @param file
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "batchimport.do", method = RequestMethod.POST)
    public String batchimport(@RequestParam("filename") MultipartFile file,HttpServletRequest request,HttpServletResponse response) throws Exception{
     //   String a = "C:\Users\lg\Desktop\1.xlsx";
        //獲取路徑
        if(file==null) return null;
        //文件名
        String name=file.getOriginalFilename();
        String filePath = request.getSession().getServletContext().getRealPath("/") + "upload"+"\\"  
                + file.getOriginalFilename();  
        long size=file.getSize();
        if(name==null || ("").equals(name) && size==0) return null;
        try {
            InputStream in = file.getInputStream();
          //調(diào)用Service抄谐，將數(shù)據(jù)插入Excel
            int i=poiService.batchImport(filePath,file);
            if(i>0){
                 String Msg ="導(dǎo)入成功";
                 avg();
                 request.getSession().setAttribute("msg",Msg);
            }else{
                 String Msg ="導(dǎo)入失敗";
                 request.getSession().setAttribute("msg",Msg);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            excel();
        }
    //  return null; 
        return "forward:/jsp/excel/excel.jsp";
    }

Service實(shí)現(xiàn)渺鹦，將調(diào)用實(shí)現(xiàn)類。

    /**
     * excel倒庫
     * @param name
     * @param file
     * @return
     */
    public int batchImport(String name,MultipartFile file);

Service實(shí)現(xiàn)類蛹含，調(diào)動(dòng)實(shí)現(xiàn)方法毅厚。

    /**
     *批量導(dǎo)入 
     */
    public int batchImport(String name,MultipartFile file) {
                //這個(gè)是Excel導(dǎo)入的實(shí)現(xiàn)工具類。但不是直接導(dǎo)入數(shù)據(jù)庫
                //是將Excel數(shù)據(jù)分解成能夠?qū)霐?shù)據(jù)庫的數(shù)據(jù)浦箱。
        ExelUtil readExcel=new ExelUtil();
        List<MeatModel> userList=readExcel.getExcelInfo(name,file);
        if("".equals(userList) || userList.size() < 1){
            return 0;
        }else{
          //這才是導(dǎo)入操作
             return meatMapper.batchInsertStudent(userList);
        }
     }

導(dǎo)入工具類：

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.session.Session;
import org.apache.shiro.subject.Subject;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.matton.model.LoginUserModel;
import com.matton.model.MeatModel;
import com.matton.model.Student;
import com.sun.org.apache.xml.internal.serializer.utils.Utils;


public class ExelUtil {

    //總行數(shù)
    private int totalRows = 0;  
   
    //總條數(shù)
    private int totalCells = 0; 
    
    //錯(cuò)誤信息接收器
    private String errorMsg;
          
    //構(gòu)造方法
    public ExelUtil(){}
    
    //得到總行數(shù)
    public int getTotalRows()  { return totalRows;} 
    
    //得到總列數(shù)
    public int getTotalCells() {  return totalCells;} 
    
    public String getErrorInfo() { return errorMsg; }  
    
  /**
   * 描述：驗(yàn)證EXCEL文件
   * @param filePath
   * @return
   */
  public boolean validateExcel(String filePath){
      /** 檢查文件是否存在 */  
/*      File file = new File(filePath);  
      boolean a = file.exists();
      if (file == null || !file.exists()) {  
        errorMsg = "文件不存在";  
          return false;  
      } */ 
      
      if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){  
          errorMsg = "文件名不是excel格式";  
          return false;  
      }  
       
        return true;
  }
    
  
  /**描述 :讀EXCEL文件
   * @param fielName
   * @return
   */
  public List<MeatModel> getExcelInfo(String fileName,MultipartFile Mfile){
      
      //把spring文件上傳的MultipartFile轉(zhuǎn)換成File
       CommonsMultipartFile cf= (CommonsMultipartFile)Mfile; 
       DiskFileItem fi = (DiskFileItem)cf.getFileItem();
      File file = fi.getStoreLocation();
      List<MeatModel> userList=new ArrayList<MeatModel>();
//  fileName = "E:/ruanjian/apache-tomcat-6.0.41 -3/webapps/Matton/upload/1.xlsx";
      
      InputStream is = null;  
      try{
          //驗(yàn)證文件名是否合格
          if(!validateExcel(fileName)){
              return null;
          }
          //判斷文件時(shí)2003版本還是2007版本
          boolean isExcel2003 = true; 
          if(WDWUtil.isExcel2007(fileName)){
              isExcel2003 = false;  
          }
          is = new FileInputStream(file);
          userList=getExcelInfo(is, isExcel2003); 
          is.close();
      }catch(Exception e){
          e.printStackTrace();
      }
      finally{
          if(is !=null)
          {
              try{
                  is.close();
              }catch(IOException e){
                  is = null;    
                  e.printStackTrace();  
              }
          }
      }
      return userList;
  }
  /**
   * 此方法兩個(gè)參數(shù)InputStream是字節(jié)流吸耿。isExcel2003是excel是2003還是2007版本
   * @param is
   * @param isExcel2003
   * @return
   * @throws IOException
   */
  public  List<MeatModel> getExcelInfo(InputStream is,boolean isExcel2003){
      
       List<MeatModel> userList=null;
       try{
           /** 根據(jù)版本選擇創(chuàng)建Workbook的方式 */
           Workbook wb = null;
           //當(dāng)excel是2003時(shí)
           if(isExcel2003){
               wb = new HSSFWorkbook(is);
           }else{
               wb = new XSSFWorkbook(is);
           }
           userList=readExcelValue(wb);
       }
       catch (IOException e)  {  
           e.printStackTrace();  
       }  
       return userList;
  }
  /**
   * 讀取Excel里面的信息
   * @param wb
   * @return
   */
  private List<MeatModel> readExcelValue(Workbook wb){ 
       //得到第一個(gè)shell  
       Sheet sheet=wb.getSheetAt(0);
       //得到Excel的行數(shù)
       this.totalRows=sheet.getPhysicalNumberOfRows();
       //得到Excel的列數(shù)(前提是有行數(shù))
       if(totalRows>=1 && sheet.getRow(0) != null){
            this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
       }
       List<MeatModel> userList=new ArrayList<MeatModel>();
       MeatModel meat = null ;
       //獲取當(dāng)前用戶id
          Subject currentUser = SecurityUtils.getSubject();
          Session session = currentUser.getSession();
          session.getAttribute("currentUser");
          LoginUserModel loginUserModel = (LoginUserModel) session.getAttribute("currentUser");
       
       
       System.out.println(this.totalRows+"行行航哈哈哈哈哈哈哈哈哈哈哈哈");
       System.out.println(this.getTotalCells()+"行行航哈哈哈哈哈哈哈哈哈哈哈哈");
       /** 循環(huán)Excel的行祠锣，然后循環(huán)列，即先循環(huán)行咽安，再循環(huán)列 */  
       for (int r = 1; r < this.totalRows; r++) {  
           Row row = sheet.getRow(r);  
           if (row == null){  
               continue;  
           }  
          // List<String> rowLst = new ArrayList<String>();  
           meat = new MeatModel();
           /** 循環(huán)Excel的列 */  
           for (int c = 0; c < this.getTotalCells(); c++){  
               Cell cell = row.getCell(c);  
               String cellValue = getCellTypes(cell);  
               
               GenerateSequenceUtil strid = new GenerateSequenceUtil();
               meat.setWaterId(strid.generateSequenceNo());
               if(c == 0){
                   meat.setInsertData(cellValue);
               }else if(c == 1){
                   meat.setMeatNum(cellValue); 
               } else if(c == 2){
                   meat.setEmptyCupWeight(cellValue);//空杯子質(zhì)量
               } else if(c == 3){
                   meat.setWaterCupWeight(cellValue);//空杯+鮮肉質(zhì)量
               } else if(c == 4){
                   meat.setMeatWeight(cellValue);//鮮肉質(zhì)量
               } else if(c == 5){
                   meat.setNoWaterMeatWeight(cellValue);//空干后的肉+杯子的質(zhì)量
               } else if(c == 6){
                   if("".equals(meat.getWaterCupWeight()) || null == meat.getWaterCupWeight() ||
                           "".equals(meat.getNoWaterMeatWeight()) || null == meat.getNoWaterMeatWeight() ||  
                           "".equals(meat.getMeatWeight()) || null == meat.getMeatWeight() 
                           ){
                       //如果該字段存在值則使用伴网，不存在放空
                       if("".equals(cellValue) || null == cellValue ){
                           cellValue = "";
                       }else{
                           DecimalFormat  df = new DecimalFormat("0.0000");
                         cellValue = df.format(cellValue);
                       }
                   }else{
                   double WaterCupWeight = Double.parseDouble(meat.getWaterCupWeight());
                   double NoWaterMeatWeight = Double.parseDouble(meat.getNoWaterMeatWeight());
                   double MeatWeight = Double.parseDouble(meat.getMeatWeight());
                   double water = ((WaterCupWeight - NoWaterMeatWeight  ) / MeatWeight);
                  DecimalFormat  df = new DecimalFormat("0.0000");
                 cellValue = df.format(water);
                   }
                  cellValue = String.valueOf(cellValue);
                   meat.setWater(cellValue);//水分
               } else if(c == 7){
                   meat.setWateravg(cellValue);//平均值
               }
 
              meat.setUserId(loginUserModel.getUserId());
              
           }
           userList.add(meat);
       }
       
    return userList;
  }
  //判斷Excel倒入數(shù)據(jù)類型，轉(zhuǎn)換為數(shù)據(jù)庫可識(shí)別的數(shù)據(jù)類型
  public String getCellTypes(Cell cell){
      String cellValue = null;
      if (null != cell) {  
          // 以下是判斷數(shù)據(jù)的類型  
          switch (cell.getCellType()){  
          case HSSFCell.CELL_TYPE_NUMERIC: // 數(shù)字  
            // 處理日期格式妆棒、時(shí)間格式
              if (HSSFDateUtil.isCellDateFormatted(cell)) {
                  Date d = cell.getDateCellValue();
                  DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                  cellValue = formater.format(d);
              }
              else{ 
                  cellValue = cell.getNumericCellValue() + ""; 
              }  
                    
                    
              break;  

          case HSSFCell.CELL_TYPE_STRING: // 字符串  
              cellValue = cell.getStringCellValue();  
              break;  

          case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean  
              cellValue = cell.getBooleanCellValue() + "";  
              break;  

          case HSSFCell.CELL_TYPE_FORMULA: // 公式  
             // cellValue = cell.getCellFormula() + "";  
              try {
                  DecimalFormat  df = new DecimalFormat("0.0000");
                  cellValue = String.valueOf(df.format(cell.getNumericCellValue()));
                  
              } catch (IllegalStateException e) {
                  cellValue = String.valueOf(cell.getRichStringCellValue());
                     }
              break;  

          case HSSFCell.CELL_TYPE_BLANK: // 空值  
              cellValue = "";  
              break;  

          case HSSFCell.CELL_TYPE_ERROR: // 故障  
              cellValue = "非法字符";  
              break;  

          default:  
              cellValue = "未知類型";  
              break;  
          }  
      }  
    return cellValue;
  }
  
}

Excel文件格式判斷澡腾，判斷Excel是07版還是10版

       // 驗(yàn)證是否是excel2003
      public static boolean isExcel2003(String filePath)  {  
          return filePath.matches("^.+\\.(?i)(xls)$");  
      }  

       //驗(yàn)證是否是excel2007 
      public static boolean isExcel2007(String filePath)  {  
          return filePath.matches("^.+\\.(?i)(xlsx)$");  
      }  

Excel導(dǎo)入至此完成。
本人于2016年畢業(yè)糕珊，小白一枚动分，經(jīng)驗(yàn)不足。各位看官如有問題红选，請(qǐng)百度澜公。小白我也是一步一步百度而來。小白QQ群571669420喇肋，靜請(qǐng)打擾坟乾；