springmvc+mybatis +poi实现导入exce数据l到数据库中

1.项目用到的是poi技术,需要在maven文件添加如下配置:

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.14</version>
</dependency>
<dependency>
	   <groupId>org.apache.poi</groupId>
	   <artifactId>poi-ooxml</artifactId>
	   <version>3.6</version>
</dependency>

2.在springMVC.servlet.xml配置bean:

<bean id="multipartResolver"  
        class="org.springframework.web.multipart.commons.CommonsMultipartResolver">  
        <!-- set the max upload size100MB -->  
        <property name="maxUploadSize">  
            <value>104857600</value>  
        </property>  
        <property name="maxInMemorySize">  
            <value>4096</value>  
        </property>  
    </bean> 

excel数据如下

3.jsp页面部分代码:

<form enctype="multipart/form-data" id="batchUpload" action="${ctx}/mobile/batchImport" method="post" class="form-horizontal">    
    <button class="btn btn-success btn-xs" id="uploadEventBtn" type="button" >选择文件</button>  
    <input type="file" name="filename"  style="width:0px;height:0px;" id="uploadEventFile">  
    <input id="uploadEventPath"  disabled="disabled"  type="text" placeholder="请选择excel表" >                                      
</form>  
<button type="button" class="btn btn-success btn-sm"  οnclick="user.uploadBtn()" >上传</button>

引入的js:

var MobileUser = function(){  
      
    this.init = function(){  
          
        //模拟上传excel  
         $("#uploadEventBtn").unbind("click").bind("click",function(){  
             $("#uploadEventFile").click();  
         });  
         $("#uploadEventFile").bind("change",function(){  
             $("#uploadEventPath").attr("value",$("#uploadEventFile").val());  
         });  
          
    };  
    //点击上传按钮  
    this.uploadBtn = function(){  
        var uploadEventFile = $("#uploadEventFile").val();  
        if(uploadEventFile == ''){  
            alert("请选择excel,再上传");  
        }else if(uploadEventFile.lastIndexOf(".xls")<0){//可判断以.xls和.xlsx结尾的excel  
            alert("只能上传Excel文件");  
        }else{  
            var url =  '/sign/mobile/batchImport';  
            var formData = new FormData($('form')[4]);  
            user.sendAjaxRequest(url,'POST',formData);  
        }  
    };  
      
    this.sendAjaxRequest = function(url,type,data){  
        $.ajax({  
            url : url,  
            type : type,  
            data : data,  
            success : function(result) {  
            	alertMsg.correct( "excel上传成功");  
            },  
            error : function() {  
            	alertMsg.error( "excel上传失败"); 
            },  
            cache : false,  
            contentType : false,  
            processData : false  
        });  
    };  
}  
      
  
var user;  
$(function(){  
    user = new MobileUser();  
    user.init();  
}); 

4.处理excel文件的工具类:

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

import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.gmcc.mobile.model.MobileUser;

public class ReadExcelUtil {
    private int totalRows = 0;  
   
    private int totalCells = 0; 
    
    private String errorMsg;
    

/**
   * 验证EXCEL文件
   * @param filePath
   * @return
   */
  public boolean validateExcel(String filePath){
        if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){  
            errorMsg = "文件名不是excel格式";  
            return false;  
        }  
        return true;
  }
    
  
  /**读EXCEL文件
   * @param fielName
   * @return
   */
  public List<MobileUser> getExcelInfo(String fileName,MultipartFile Mfile){
      
      //把spring文件上传的MultipartFile转换成File
       CommonsMultipartFile cf= (CommonsMultipartFile)Mfile; 
       DiskFileItem fi = (DiskFileItem)cf.getFileItem();
       File file = fi.getStoreLocation(); 
       
      List<MobileUser> userList=new ArrayList<MobileUser>();
      InputStream is = null;  
      try{
          //验证文件名是否合格
          if(!validateExcel(fileName)){
              return null;
          }
          //判断文件时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;
  }
  /**
   * 此方法两个参数InputStream是字节流。isExcel2003是excel是2003还是2007版本
   * @param is
   * @param isExcel2003
   * @return
   * @throws IOException
   */
  public  List<MobileUser> getExcelInfo(InputStream is,boolean isExcel2003){
      
       List<MobileUser> userList=null;
       try{
           Workbook wb = null;
           //当excel是2003时
           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<MobileUser> readExcelValue(Workbook wb){ 
       //得到第一个shell  
       Sheet sheet=wb.getSheetAt(0);
       
       //得到Excel的行数
       this.totalRows=sheet.getPhysicalNumberOfRows();
       
       //得到Excel的列数(前提是有行数)
       if(totalRows>=1 && sheet.getRow(0) != null){
            this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
       }
       
       List<MobileUser> userList=new ArrayList<MobileUser>();
       MobileUser mobileUser;            
       
       for(int r=1;r<totalRows;r++)
       {
           Row row = sheet.getRow(r);
           if (row == null) continue;
           
           mobileUser=new MobileUser();
           //循环Excel的列
           for(int c = 0; c <this.totalCells; c++)
           {    
               Cell cell = row.getCell(c); 
               if (null != cell)  
               {
                   //第一列
                   if(c==0){
                	   mobileUser.setUserName(cell.getStringCellValue());
                   }else if(c==1){
                      DecimalFormat df = new DecimalFormat("#");
                      String cellValue=df.format(cell.getNumericCellValue());
                      mobileUser.setUserNumber(cellValue);
                   
                       
                   }
               }
           }
          
           userList.add(mobileUser);
       }
       return userList;
  }
  public ReadExcelUtil(){}
  public int getTotalRows() {
		return totalRows;
	}


	public void setTotalRows(int totalRows) {
		this.totalRows = totalRows;
	}


	public int getTotalCells() {
		return totalCells;
	}


	public void setTotalCells(int totalCells) {
		this.totalCells = totalCells;
	}


	public String getErrorMsg() {
		return errorMsg;
	}


	public void setErrorMsg(String errorMsg) {
		this.errorMsg = errorMsg;
	}
}
/**  
* 
* 检验EXCEL文件版本
*/   
class WDWUtil  
{  
   // excel 2003 
  public static boolean isExcel2003(String filePath)  {  
      return filePath.matches("^.+\\.(?i)(xls)$");  
  }  

   //excel 2007 
  public static boolean isExcel2007(String filePath)  {  
      return filePath.matches("^.+\\.(?i)(xlsx)$");  
  }  
}

5.controller类

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.gmcc.common.app.BaseController;
import com.gmcc.common.utils.ExcelTools;
import com.gmcc.mobile.model.MobileUser;
import com.gmcc.mobile.service.MobileUserService;

import framework.generic.dao.Page;

@Controller
@RequestMapping(value = "/mobile")
public class MobileController extends BaseController{
	
	private static final Logger log = LoggerFactory.getLogger(MobileController.class);
	
	@Autowired
	MobileUserService mobileUserService;
	
	@Override
	protected String getSubPath() {
		return "mobile" + SEPARATOR;
	}
	@RequestMapping(value = "/list")
	public String list(HttpServletRequest request,
			Map<String, Object> model) {
		Page<MobileUser> page = this.createPage(request, MobileUser.class);
		MobileUser mobileCondition=new MobileUser();
		mobileCondition.setUserNumber(request.getParameter("userNumber"));
		mobileCondition.setUserName(request.getParameter("userName"));
		model.put("page",mobileUserService.findByPage(mobileCondition,page, this.realOrderField(request)));
		return getPath() + LIST;
	}
	
	@RequestMapping(value = "/delete")
	public String delete(@ModelAttribute("mobile") MobileUser user,Map<String, Object> model) {
		mobileUserService.remove(user);
		return ajaxForwardSuccess("success",model);
	}
	
	@RequiresPermissions("MobileUser:save")
	@RequestMapping(value = "/add")
	public String add(Map<String, Object> model) {
		return getPath() + ADD;
	}
	
	@RequiresPermissions("MobileUser:save")
	@RequestMapping(value = "/insert")
	public String insert(@ModelAttribute("mobile") MobileUser MobileUser,Map<String, Object> model) {
		mobileUserService.add(MobileUser);
		return ajaxForwardSuccess("success",model);
	}
	@RequestMapping(value = "/batchImport")
	public String  batchImport(@RequestParam("filename") MultipartFile file,HttpServletRequest request,HttpServletResponse response,Map<String, Object> model) {
		log.info("MobileController ..batchimport() start");
        //判断文件名是否为空
        if(file==null)
        return null;
        
        //获取文件名
        String name=file.getOriginalFilename();
        
        //判断文件大小、即名称
        long size=file.getSize();
        if(name==null || ("").equals(name) && size==0) 
        return null;
        
        try {
            //把文件转换成字节流形式
            InputStream in = file.getInputStream();
            int i=mobileUserService.batchImport(name,file);
            if(i>0){
            	return ajaxForwardSuccess("success",model);   
            }else{
            	return ajaxForwardError("fail",model); 
            }
        } catch (IOException e) {
            e.printStackTrace();
        } 
        return null;
    }
	
}