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、创建工作表sheetSheet 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;}