springmvc上传并解析excel文件

1.从jsp页面提交上传excel文件

<script type="text/javascript">
jQuery(document).ready(function(){
	jQuery("#templetId").click(function(){
		var btn = $(this);
        btn.button('loading');
        setTimeout(function () {btn.button('reset')}, 2000);
		var url="/cancelInsurance/gettemplet.do";
		$("#getTemplet").attr("action",url); 
		$("#getTemplet").submit();
	});
});
<form:form id="getTemplet" action=""></form:form>
<form:form id="cancelInfo" action="cancelInsuranceEdit.do"  method="post" enctype="multipart/form-data">
		<div class="control-group">
			<div class="controls">
				<label class="control-label">选择退保文件:</label>
					<input type="file" name="cancelFiles" id="cancelFiles" accept=".xls,.xlsx,application/vnd.ms-excel" class="required"/> 
					<button class="btn btn-info" data-loading-text="loading..." id="templetId" type="button">点击下载退保模板</button>
			</div>
		</div><br/><br/><br/>
		<div>
			<input id="btnSubmit" class="btn btn-primary btn-large" type="submit" data-loading-text="loading..." value="确认退保" style="margin-left: 200px;width: 200px;"/>&nbsp;
		</div>
	</form:form>

其中，点击获取模板访问/cancelInsurance/gettemplet.do，获取模板的java代码如下

@RequestMapping(value = "gettemplet", method = RequestMethod.POST)
	public void getTemplate(HttpServletRequest request,
			RedirectAttributes redirectAttributes, HttpServletResponse response) {
		OutputStream out = null;
		InputStream is = null;
		try {
			is = new BufferedInputStream(this.getClass().getResourceAsStream(
					"/templates/jdCancelInsurance.xls"));//找到项目中存储的.xls文件
			ExcelUtil.setExcelResponse(response,"批量退保模板");
			Map beans = Maps.newHashMap();
			beans.put("loginName", UserUtils.getUser().getLoginName());
			Workbook workbook = WriteExcel.write(is, beans);
			// 将内容写入输出流并把缓存的内容全部发出去
			out = response.getOutputStream();
			workbook.write(out);
			out.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
				}
			}
		}
	}

2.解析.xls文件（controller）,java代码如下

ReadExcels readExcel=new ReadExcels();
//解析excel，获取客户信息集合。
List<CancleInsuranceInfo> customerList = readExcel.getExcelInfo(filename ,multipartFile);

3.工具类，java代码如下

public class ReadExcels {
    //总行数
    private int totalRows = 0;  
    //总条数
    private int totalCells = 0; 
    //错误信息接收器
    private String errorMsg;
    //构造方法
    public ReadExcels(){}
    //获取总行数
    public int getTotalRows()  { return totalRows;} 
    //获取总列数
    public int getTotalCells() {  return totalCells;} 
    //获取错误信息
    public String getErrorInfo() { return 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;
  }
public List<CancleInsuranceInfo> getExcelInfo(String fileName,MultipartFile multipartFile){
       //初始化退保信息的集合    
       List<CancleInsuranceInfo> customerList=new ArrayList<CancleInsuranceInfo>();
       //初始化输入流
       InputStream is = null;  
       try{
          //验证文件名是否合格
          if(!validateExcel(fileName)){
              return null;
          }
          //根据文件名判断文件是2003版本还是2007版本
          boolean isxls = true; 
          if(WDWUtil.isxlsx(fileName)){
              isxls = false;  
          }
          //根据新建的文件实例化输入流
          is = multipartFile.getInputStream();
          //根据excel里面的内容读取客户信息
          customerList = getExcelInfo(is, isxls); 
          is.close();
      }catch(Exception e){
          e.printStackTrace();
      } finally{
          if(is !=null)
          {
              try{
                  is.close();
              }catch(IOException e){
                  is = null;    
                  e.printStackTrace();  
              }
          }
      }
      return customerList;
  }
  /**
   * 根据excel里面的内容读退保信息
   * @param isxls excel是xls文件还是xlsx文件
   * @return
   * @throws IOException
   */
  public  List<CancleInsuranceInfo> getExcelInfo(InputStream is,boolean isxls){
       List<CancleInsuranceInfo> cancleInsuranceInfo=null;
       try{
           /** 根据版本选择创建Workbook的方式 */
           Workbook wb = null;
           //当excel是xls时
           if(isxls){
               wb = new HSSFWorkbook(is);
           }
           else{//当excel是xlsx文件时
               wb = new XSSFWorkbook(is); 
           }
           //读取Excel里面退保信息
           cancleInsuranceInfo=readExcelValue(wb);
       }
       catch (IOException e)  {  
           e.printStackTrace();  
       }  
       return cancleInsuranceInfo;
  }
  /**
   * 读取Excel里面的信息
   * @param wb
   * @return
   */
  private List<CancleInsuranceInfo> 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<CancleInsuranceInfo> cancleInsuranceInfos=new ArrayList<CancleInsuranceInfo>();
       CancleInsuranceInfo cancleInsuranceInfo;            
      //循环Excel行数,从第二行开始。标题不进行统计
       for(int r=1;r<totalRows;r++){
           Row row = sheet.getRow(r);
           if (row == null) continue;
           cancleInsuranceInfo = new CancleInsuranceInfo();
           
           //循环取出Excel的列
           for(int c = 0; c <this.totalCells; c++){    
               Cell cell = row.getCell(c);
               cell.setCellType(Cell.CELL_TYPE_STRING);
               if (null != cell){
                   if(c==0){//取出第一列
                	   cancleInsuranceInfo.setServerNumber(cell.getStringCellValue());
                   }else if(c==4){//取出第五列
                	   cancleInsuranceInfo.setBackCode(cell.getStringCellValue());
                	   break;
                   }
               }
           }
           //添加退保信息
           cancleInsuranceInfos.add(cancleInsuranceInfo);
       }
       return cancleInsuranceInfos;
  }
}