Java POI 导入Excel

今天说下Java Web中常见的Excel的导入，一种是POI，一种是jxl。我们来用POI操作一下，写一个Java 导入Excel的实例。前提是导入POI的相关jar包。

我们呢定义一个常见的Java bean，包括一些属性和get/set方法。

package org.topcheer.biz.sys.model;

public class BcapBmsSignIMP {
	
	private String actiontype;
	
	private String actiondesc;
	
	private String strdecode;

	private String branchcode;

	private String clientcode;

	private String clientname;

	private String contractdate;

	private String contract;

	private String contractamount;

	private String maturity;

	private String lincame;

	private String lincamethod;

	private String aginraup;

	private String aginralo;

	private String aginraloinpay;

	private String remark;

	private String businessid;

	private String orgcode;

	public String getStrdecode() {
		return strdecode;
	}

	public void setStrdecode(String strdecode) {
		this.strdecode = strdecode;
	}

	public String getActiontype() {
		return actiontype;
	}

	public void setActiontype(String actiontype) {
		this.actiontype = actiontype;
	}

	public String getActiondesc() {
		return actiondesc;
	}

	public void setActiondesc(String actiondesc) {
		this.actiondesc = actiondesc;
	}

	public String getBranchcode() {
		return branchcode;
	}

	public void setBranchcode(String branchcode) {
		this.branchcode = branchcode;
	}

	public String getClientcode() {
		return clientcode;
	}

	public void setClientcode(String clientcode) {
		this.clientcode = clientcode;
	}

	public String getClientname() {
		return clientname;
	}

	public void setClientname(String clientname) {
		this.clientname = clientname;
	}

	public String getContractdate() {
		return contractdate;
	}

	public void setContractdate(String contractdate) {
		this.contractdate = contractdate;
	}

	public String getContract() {
		return contract;
	}

	public void setContract(String contract) {
		this.contract = contract;
	}

	public String getContractamount() {
		return contractamount;
	}

	public void setContractamount(String contractamount) {
		this.contractamount = contractamount;
	}

	public String getMaturity() {
		return maturity;
	}

	public void setMaturity(String maturity) {
		this.maturity = maturity;
	}

	public String getLincame() {
		return lincame;
	}

	public void setLincame(String lincame) {
		this.lincame = lincame;
	}

	public String getLincamethod() {
		return lincamethod;
	}

	public void setLincamethod(String lincamethod) {
		this.lincamethod = lincamethod;
	}

	public String getAginraup() {
		return aginraup;
	}

	public void setAginraup(String aginraup) {
		this.aginraup = aginraup;
	}

	public String getAginralo() {
		return aginralo;
	}

	public void setAginralo(String aginralo) {
		this.aginralo = aginralo;
	}

	public String getAginraloinpay() {
		return aginraloinpay;
	}

	public void setAginraloinpay(String aginraloinpay) {
		this.aginraloinpay = aginraloinpay;
	}

	public String getRemark() {
		return remark;
	}

	public void setRemark(String remark) {
		this.remark = remark;
	}

	public String getBusinessid() {
		return businessid;
	}

	public void setBusinessid(String businessid) {
		this.businessid = businessid;
	}

	public String getOrgcode() {
		return orgcode;
	}

	public void setOrgcode(String orgcode) {
		this.orgcode = orgcode;
	}

	
	

}

这个是要导入的数据类型，Excel中的一行对应一个BcapBmsSignIMP对象。

下面在我们实际Action需要的地方调用这个方法:ImportDataFromExcel即可。参数:vo:就是一个BcapBmsSignIMP对象的实例

is是页面传过来的Excel转化的输入流，后面的excelFilename是excel的名称。

/**
	 * 判断excel文件后缀名,生成不同的Workbook
	 * @param is
	 * @param excelFileName
	 * @return
	 * @throws IOException
	 */
	public  Workbook createWorkBook(InputStream is,String excelFileName) throws IOException {
		
		if(excelFileName.endsWith(".xls")){
			return new HSSFWorkbook(is);
		}else if(excelFileName.endsWith(".xlsx")){
	    	return new XSSFWorkbook(is);
	    }
		return null;
	}
	
	/**
	 * 根据sheet索引号获取对应的sheet
	 * 
	 * @param workBook
	 * @param sheetIndex
	 * @return
	 */
	public  Sheet getSheet(Workbook workBook,int sheetIndex){
		return workBook.getSheetAt(0);
	}
	

	/**
	 * 将sheet中的数据保存到list中,
	 * 1、使用此方法时 vo的属性个数必须和excel文件每行数据的列数相同且一一对应,vo属性的所有类型都为String
	 * 2、在action调用此方法是 需声明 
	 * private File excelFile;上传的文件
	 * private String excelFileFileName;保存原始的文件名
	 * 两个属性
	 * 3、页面的file控件 name需对应File的名称
	 * @param vo javaBean
	 * @param is  输入流 
	 * @param excelFileName 要导入的excel名称
	 */
	public List<Object> importDataFromExcel(Object vo,InputStream is,String excelFileName){
	  List<Object> list = new ArrayList<Object>();
	  try{
		//1、创建工作簿
		Workbook workBook = this.createWorkBook(is, excelFileName);
		 //2、创建工作表sheet
		Sheet sheet = this.getSheet(workBook, 0);
		 //3、获取sheet中数据行数
		int rows = sheet.getPhysicalNumberOfRows();
	    int cells = sheet.getRow(0).getPhysicalNumberOfCells();//获取表头的单元格个数
		//利用反射得到该对象的所有属性
		Field[] fields = vo.getClass().getDeclaredFields();
		
		for(int i=1;i< rows ; i++){//第一行为标题行，从第二行开始取数据
		   Row row = sheet.getRow(i);
		   int index =0;
		 //利用反射，根据javabean属性的先后顺序，动态调用setXxx()方法给属性赋值
		   while(index < cells){
			   Cell cell = row.getCell(index);
			   if(null == cell){
				   cell = row.createCell(index);
			   }
			   cell.setCellType(Cell.CELL_TYPE_STRING);
			   String value =  null == cell.getStringCellValue()?"":cell.getStringCellValue();
			   
			   Field field = fields[index];
			   String fieldName = field.getName();
			   String methodName = "set"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
			   Method setMethod  = vo.getClass().getMethod(methodName,new Class[]{String.class} );
			   setMethod.invoke(vo, new Object[]{value}); 
			   index++;
		   }
		   
		   if(isHasValues(vo)){//对象属性有值
			   list.add(vo);
			   vo = vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新创建一个vo对象
		   }
		}
	  }catch(Exception e){
		  e.printStackTrace();
		  log.error(e);
	  }finally{
		try {
			is.close();//关闭输入流
		} catch (IOException e) {
			log.error(e);
		}
	  }
	  
	  return list;
	}