ssm框架上传Excel解析到数据库

最近在项目中有用到上传Excel，将数据解析到数据库，在网上找了很多资源，在此做个总结：
1、首先放上要运到的jar包
jar包传送门
提取码： ha4p
2、接下来有这两个工具类，不用改变，可以直接使用

package com.grand.common.util;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;
public class ExcelBean implements java.io.Serializable {  
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String headTextName;//列头（标题）名  
    private String propertyName;//对应字段名  
    private Integer cols;//合并单元格数  
    private XSSFCellStyle cellStyle;  
      
    public ExcelBean(){  
          
    }  
    public ExcelBean(String headTextName, String propertyName){  
        this.headTextName = headTextName;  
        this.propertyName = propertyName;  
    }  
      
    public ExcelBean(String headTextName, String propertyName, Integer cols) {  
        super();  
        this.headTextName = headTextName;  
        this.propertyName = propertyName;  
        this.cols = cols;  
    }   
      
    public String getHeadTextName() {  
       return headTextName;  
   }  
 
   public void setHeadTextName(String headTextName) {  
       this.headTextName = headTextName;  
   }  
 
   public String getPropertyName() {  
       return propertyName;  
   }  
 
   public void setPropertyName(String propertyName) {  
       this.propertyName = propertyName;  
   }  
 
   public Integer getCols() {  
       return cols;  
   }  
 
   public void setCols(Integer cols) {  
       this.cols = cols;  
   }  
 
   public XSSFCellStyle getCellStyle() {  
       return cellStyle;  
   }  
 
   public void setCellStyle(XSSFCellStyle cellStyle) {  
       this.cellStyle = cellStyle;  
   }  
}  

package com.grand.common.util;

import java.io.IOException;  
import java.io.InputStream;  
import java.math.BigDecimal;
 
import java.text.SimpleDateFormat;  
import java.util.ArrayList;  
import java.util.Date;
import java.util.List;  
  
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.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  
  
public class ExcelUtil {  
      
    private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel  
      
    /** 
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象 
     * @param in,fileName 
     * @return 
     * @throws IOException  
     */  
    public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
        List<List<Object>> list = null;  
          
        //创建Excel工作薄  
        Workbook work = this.getWorkbook(in,fileName);  
        if(null == work){  
            throw new Exception("创建Excel工作薄为空！");  
        }  
        Sheet sheet = null;  //页数
        Row row = null;  //行数
        Cell cell = null;  //列数
          
        list = new ArrayList<List<Object>>();  
        //遍历Excel中所有的sheet  
        for (int i = 0; i < work.getNumberOfSheets(); i++) {  
            sheet = work.getSheetAt(i);  
            if(sheet==null){continue;}  
              
            //遍历当前sheet中的所有行  
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {  
                row = sheet.getRow(j);  
                if(row==null||row.getFirstCellNum()==j){continue;}  
                  
                //遍历所有的列  
                List<Object> li = new ArrayList<Object>();  
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {  
                    cell = row.getCell(y);  
                    li.add(this.getValue(cell));  
                }  
                list.add(li);  
            }  
        }  
 
        return list;  
        
    }  
      
    /** 
     * 描述：根据文件后缀，自适应上传文件的版本  
     * @param inStr,fileName 
     * @return 
     * @throws Exception 
     */  
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
        Workbook wb = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            wb = new HSSFWorkbook(inStr);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            wb = new XSSFWorkbook(inStr);  //2007+  
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return wb;  
    }  
  
    /** 
     * 描述：对表格中数值进行格式化 
     * @param cell 
     * @return 
     */  
  //解决excel类型问题，获得数值  
    public  String getValue(Cell cell) {  
        String value = "";  
        if(null==cell){  
            return value;  
        }  
        switch (cell.getCellType()) {  
        //数值型  
        case Cell.CELL_TYPE_NUMERIC:  
            if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                //如果是date类型则 ，获取该cell的date值  
                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());  
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");  
                value = format.format(date);;  
            }else {// 纯数字  
                BigDecimal big=new BigDecimal(cell.getNumericCellValue());  
                value = big.toString();  
                //解决1234.0  去掉后面的.0  
                if(null!=value&&!"".equals(value.trim())){  
                     String[] item = value.split("[.]");  
                     if(1<item.length&&"0".equals(item[1])){  
                         value=item[0];  
                     }  
                }  
            }  
            break;  
            //字符串类型   
        case Cell.CELL_TYPE_STRING:  
            value = cell.getStringCellValue().toString();  
            break;  
        // 公式类型  
        case Cell.CELL_TYPE_FORMULA:  
            //读公式计算值  
            value = String.valueOf(cell.getNumericCellValue());  
            if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串  
                value = cell.getStringCellValue().toString();  
            }  
            break;  
        // 布尔类型  
        case Cell.CELL_TYPE_BOOLEAN:  
            value = " "+ cell.getBooleanCellValue();  
            break;   
        default:  
            value = cell.getStringCellValue().toString();  
    }  
    if("null".endsWith(value.trim())){  
        value="";  
    }  
  return value;  
    }  
}  

我的实体类：

public class MarketUser {
	
	private String cis;
	private String custName;
	private String salBalance;  // 交易金额
	private String tellPhone;   // 联系电话
	private String idCard;      // 身份证号
	private String belongOrgn;  // 归属机构
	private String capitalOutTime;  // 资金转出日期
	private String capitalOutOrgn;  // 资金转出机构
	private String capitalInOrgn;   // 资金转入机构
	private String productStartTime; // 产品起息日
	private String productEndTime;   // 产品到期日
	public String getCis() {
		return cis;
	}
	public void setCis(String cis) {
		this.cis = cis;
	}
	public String getCustName() {
		return custName;
	}
	public void setCustName(String custName) {
		this.custName = custName;
	}
	public String getSalBalance() {
		return salBalance;
	}
	public void setSalBalance(String salBalance) {
		this.salBalance = salBalance;
	}
	public String getTellPhone() {
		return tellPhone;
	}
	public void setTellPhone(String tellPhone) {
		this.tellPhone = tellPhone;
	}
	public String getIdCard() {
		return idCard;
	}
	public void setIdCard(String idCard) {
		this.idCard = idCard;
	}
	public String getBelongOrgn() {
		return belongOrgn;
	}
	public void setBelongOrgn(String belongOrgn) {
		this.belongOrgn = belongOrgn;
	}
	public String getCapitalOutTime() {
		return capitalOutTime;
	}
	public void setCapitalOutTime(String capitalOutTime) {
		this.capitalOutTime = capitalOutTime;
	}
	public String getCapitalOutOrgn() {
		return capitalOutOrgn;
	}
	public void setCapitalOutOrgn(String capitalOutOrgn) {
		this.capitalOutOrgn = capitalOutOrgn;
	}
	public String getCapitalInOrgn() {
		return capitalInOrgn;
	}
	public void setCapitalInOrgn(String capitalInOrgn) {
		this.capitalInOrgn = capitalInOrgn;
	}
	public String getProductStartTime() {
		return productStartTime;
	}
	public void setProductStartTime(String productStartTime) {
		this.productStartTime = productStartTime;
	}
	public String getProductEndTime() {
		return productEndTime;
	}
	public void setProductEndTime(String productEndTime) {
		this.productEndTime = productEndTime;
	}
	
}

然后在mapper中定义接口；

// 读取Excel文件之后的插入方法
	public int insertData(@Param("user")MarketUser user);

xml文件中写SQL：

<!-- 将Excel文件数据插入到数据库 -->
	<insert id="insertData">
	  INSERT INTO LOSS_CUST_WARNING(
		    CIS,                       
			CUST_NAME,                        
			sal_balance, 
			tellPhone,
			idCard,               
			belong_orgn, 
			capitalOutTime,
			capitalOutOrgn,
			capitalInOrgn,
			product_start_time,
			product_end_time)
		VALUES(
			#{user.cis, jdbcType=VARCHAR},
			#{user.custName, jdbcType=VARCHAR},
			#{user.salBalance, jdbcType=VARCHAR},
			#{user.tellPhone, jdbcType=VARCHAR},
			#{user.idCard, jdbcType=VARCHAR},
			#{user.belongOrgn, jdbcType=VARCHAR},
			#{user.capitalOutTime,jdbcType=VARCHAR},
			#{user.capitalOutOrgn, jdbcType=VARCHAR},
			#{user.capitalInOrgn, jdbcType=VARCHAR},
			#{user.productStartTime,jdbcType=VARCHAR},
			#{user.productEndTime,jdbcType=VARCHAR}
		)
	</insert>

service中的业务逻辑：

@SuppressWarnings("null")
	public int ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response){
		MultipartHttpServletRequest multiparRequest = (MultipartHttpServletRequest) request;
		
		int  resultNum = 0;
		MultipartFile file = multiparRequest.getFile("upfile");
		// 文件为空
		if (file.isEmpty()) {
			try {
				throw new Exception("文件不存在！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		InputStream in = null;
		try {
			in = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		List<List<Object>> listOb = null;
		try {
			listOb = new ExcelUtil().getBankListByExcel(in, file.getOriginalFilename());
		} catch (Exception e) {
			e.printStackTrace();
		}
		MarketUser user = null;
		for (int i = 0; i < listOb.size(); i++) {
			List<Object> lo = listOb.get(i);
			user = new MarketUser();
			user.setCis(String.valueOf(lo.get(0)));  // 表格的第一列
			user.setCustName(String.valueOf(lo.get(1)));
			user.setSalBalance(String.valueOf(lo.get(2)));
			user.setTellPhone(String.valueOf(lo.get(3)));
			user.setIdCard(String.valueOf(lo.get(4)));
			user.setBelongOrgn(String.valueOf(lo.get(5)));
			user.setCapitalOutTime(String.valueOf(lo.get(6)));
			user.setCapitalOutOrgn(String.valueOf(lo.get(7)));
			user.setCapitalInOrgn(String.valueOf(lo.get(8)));
			user.setProductStartTime(String.valueOf(lo.get(9)));
			user.setProductEndTime(String.valueOf(lo.get(10)));
			
			// 插入列的数据
			int result = custMarketListMapper.insertData(user);
			resultNum += result;
		}
		
		return resultNum;
	}

controller中的方法：

@RequestMapping(value="configPage")
	@ResponseBody
	public Map<String, Object> configPage(HttpServletRequest request,HttpServletResponse response){
		Map<String, Object> map = new HashMap<String, Object>();
		// 插入Excel数据返回的插入条数
		int resultNum  =  custMarketListService.ajaxUploadExcel(request, response);
		if (resultNum > 0) {
			map.put("resInfo", 0);
		}else {
			map.put("resInfo", 1);
		}
		
		return map;
	}
	

到这儿，后台逻辑就写完了，下面是前端了：
jsp中：

<form enctype="multipart/form-data" id="uploadExcelForm" action="${pageContext.request.contextPath}/custMarketList/configPage" method="post">
		<div class="form-group">
			<label for="cluster_name">上传文件:</label>
			<input id="upfile" class="form-control" type="file" name="upfile" accept=".xls,xlsx" placeholder="上传Excel"/>
			<button type="reset" style="display: none"></button>
		</div>
</form>

定义button：

<button type="button" class="btn btn-primary"  id="saveClusterBtn" title="上传Excel文件">上传</button>

js中的写法：

$( '#saveClusterBtn' ).on( 'click' , upLoadExcel );

// 格式验证
	function checkData(){
		var fileDir = $("#upfile").val();
		var suffix = fileDir.substring(fileDir.lastIndexOf("."));
		if(fileDir == ""){
			bootbox.alert("请选择需要导入的Excel文件！");
			return false;
		}
		if(".xls" != suffix && ".xlsx" != suffix){
			bootbox.alert("请选择Excel格式的文件导入！");
			return false;
		}
		
		return true;
	}

// 上传营销清单 Excel 文件
	function upLoadExcel(){
		var isSumbit = checkData();
		if(isSumbit){
			$('#uploadExcelForm').ajaxSubmit({
				   type:"POST",
				   dataType:"text",
				   url:this.uploaderUrl,//"${pageContext.request.contextPath}/custMarketList/configPage",
				   enctype:"multipart/form-data",
				   success : function(responseText) {
					   // 返回值中去掉  <pre style="word-wrap: break-word; white-space: pre-wrap;">{"resInfo":0}</pre>
					     responseText = $.parseJSON(responseText.replace(/<.*?>/ig,""));
						 if(responseText.resInfo == 0){
							$("#cluster_name").val("");
							bootbox.alert("上传成功");
						}else{
						    $("#cluster_name").val("");
							bootbox.alert("上传失败,请重新上传！");
						} 
					},
					error:function(){
						$("#cluster_name").val("");
						bootbox.alert("系统错误，请联系管理员！");
					}
			   });
		}
		
	}