动态从excel取出数据，并封装动态javabean存入数据库

用户表

public class Userinfo implements Serializable {private static final long serialVersionUID = 1L;@Expose private Integer id;@Expose private String userCode;//用户编号@Expose private String loginName;//登录名@Expose private String loginPassWord;//密码@Expose private String userName;//用户名@Expose private String jobNumber;//工号@Expose private String sex;//性别:{F===>女；M===>男}@Expose private String phone;//手机号@Expose private String email;//邮箱@Expose private String address;//住址@Expose private String noteDiy;//个性签名@Expose private Date created;//创建时间@Expose private Integer loginCount = 0;//登录次数@Expose private boolean isDelete;//删除标志public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUserCode() {return userCode;}public void setUserCode(String userCode) {this.userCode = userCode;}public String getLoginName() {return loginName;}public void setLoginName(String loginName) {this.loginName = loginName;}public String getLoginPassWord() {return loginPassWord;}public void setLoginPassWord(String loginPassWord) {this.loginPassWord = loginPassWord;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getJobNumber() {return jobNumber;}public void setJobNumber(String jobNumber) {this.jobNumber = jobNumber;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public String getNoteDiy() {return noteDiy;}public void setNoteDiy(String noteDiy) {this.noteDiy = noteDiy;}public Date getCreated() {return created;}public void setCreated(Date created) {this.created = created;}public Date getLastLoginDate() {return lastLoginDate;}public void setLastLoginDate(Date lastLoginDate) {this.lastLoginDate = lastLoginDate;}public Integer getLoginCount() {return loginCount;}public void setLoginCount(Integer loginCount) {this.loginCount = loginCount;}public boolean getIsDelete() {return isDelete;}public void setIsDelete(boolean isDelete) {this.isDelete = isDelete;}}

摄像头编码表

public class CameraCode {@Expose private Integer id;//id@Expose private String cameraCode;//公安局摄像头编码@Expose private String cameraDesc;//公安局摄像头编码描述@Expose private String camerPhoto;//缩略图@Expose private String name;//摄像头编码录入人@Expose private Date createDate;//摄像头编码录入时间@Expose private boolean useing;//是否启用public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public Date getCreateDate() {return createDate;}public void setCreateDate(Date date) {this.createDate = date;}public boolean isUseing() {return useing;}public void setUseing(boolean useing) {this.useing = useing;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getCameraCode() {return cameraCode;}public void setCameraCode(String cameraCode) {this.cameraCode = cameraCode;}public String getCameraDesc() {return cameraDesc;}public void setCameraDesc(String cameraDesc) {this.cameraDesc = cameraDesc;}public String getCamerPhoto() {return camerPhoto;}public void setCamerPhoto(String camerPhoto) {this.camerPhoto = camerPhoto;}}

excel导入action

public class ExcelAction extends BaseAction {/** *  */private static final long serialVersionUID = 1L;public static final String RELACE=".0";public static final String SPACE="";private String fileName;private String suffix;private UserInfoService userInfoService;private CameraCodeService cameraCodeService;private String uploadName;public String getUploadName() {return uploadName;}public void setUploadName(String uploadName) {this.uploadName = uploadName.trim();}public CameraCodeService getCameraCodeService() {return cameraCodeService;}public void setCameraCodeService(CameraCodeService cameraCodeService) {this.cameraCodeService = cameraCodeService;}public UserInfoService getUserInfoService() {return userInfoService;}public void setUserInfoService(UserInfoService userInfoService) {this.userInfoService = userInfoService;}public String getFileName() {return fileName;}public void setFileName(String fileName) {this.fileName = fileName.trim();}public String upload(){  getResponse().setContentType("text/html; charset=utf-8");  String message = null;  if(fileName==null || "".equalsIgnoreCase(fileName)){  message = "上传文件有误";  }else if(uploadName==null || "".equalsIgnoreCase(uploadName)){  message = "上传文件类型不能为空";  }else{  File file = getUploadFile();    //这里确定传入excel是哪个实体类，并获取class文件  if("userInfo".equalsIgnoreCase(uploadName)){  Class<Userinfo> beanClass = Userinfo.class;  message = readExcel(beanClass,file);  }else if("cameraCode".equalsIgnoreCase(uploadName)){  Class<CameraCode> beanClass = CameraCode.class;  message = readExcel(beanClass,file);  }else{  message = "传入数据失败";  }  }  writeText(message);  return NONE;  }private File getUploadFile() {MultiPartRequestWrapper wrapper = (MultiPartRequestWrapper) getRequest();     File file = wrapper.getFiles("fileName")[0];     fileName = wrapper.getFileNames("fileName")[0];    suffix = fileName.substring(fileName.lastIndexOf(".")+1, fileName.length());  int maxPostSize = 1000 * 1024 * 1024;  if(file.length() > maxPostSize){      String temStr = "上传文件大小超过限制。";      writeText(temStr);  }  return file;}private <T> String  readExcel(Class<T> beanClass,File file) {try {InputStream in = new FileInputStream(file);boolean flag = false;if("xls".equals(suffix)){HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);for (int numPage = 0; numPage < hssfWorkbook.getNumberOfSheets(); numPage++) {HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numPage);if(hssfSheet!=null){HSSFRow row = hssfSheet.getRow(0);if(row!=null){Field[] declaredFields = beanClass.getDeclaredFields();Method[] methods = beanClass.getMethods();for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {HSSFRow hssfRow = hssfSheet.getRow(rowNum);if (hssfRow != null) {T newInstance = beanClass.newInstance();for (int i = 0; i < hssfRow.getLastCellNum(); i++) {String valueName = getValue(row.getCell(i));String value = getValue(hssfRow.getCell(i));for (Field field : declaredFields) {if(valueName.equalsIgnoreCase(field.getName())){String methodName = "set" + valueName.substring(0, 1).toUpperCase() + valueName.substring(1);String typeName = field.getType().getName();try {if("int".equalsIgnoreCase(typeName)){System.out.println("int");Method method = beanClass.getMethod(methodName, int.class);method.invoke(newInstance, new int[]{Integer.parseInt(value)});}else if("java.lang.Integer".equalsIgnoreCase(typeName)){System.out.println("Integer");Method method = beanClass.getMethod(methodName, Integer.class);method.invoke(newInstance, new Integer[]{Integer.valueOf(value)});}else if("boolean".equalsIgnoreCase(typeName)){System.out.println("boolean");Method method = beanClass.getMethod(methodName, boolean.class);method.invoke(newInstance, new Boolean[]{Boolean.parseBoolean(value)});}else if("java.lang.Boolean".equalsIgnoreCase(typeName)){System.out.println("Boolean");Method method = beanClass.getMethod(methodName, Boolean.class);method.invoke(newInstance, new Boolean[]{Boolean.valueOf(value)});}else if("double".equalsIgnoreCase(typeName)){System.out.println("double");Method method = beanClass.getMethod(methodName, double.class);method.invoke(newInstance, new double[]{Double.parseDouble(value)});}else if("java.lang.Double".equalsIgnoreCase(typeName)){System.out.println("Double");Method method = beanClass.getMethod(methodName, Double.class);method.invoke(newInstance, new Double[]{Double.valueOf(value)});}else if("float".equalsIgnoreCase(typeName)){System.out.println("float");Method method = beanClass.getMethod(methodName, float.class);method.invoke(newInstance, new float[]{Float.parseFloat(value)});}else if("java.lang.Float".equalsIgnoreCase(typeName)){System.out.println("Float");Method method = beanClass.getMethod(methodName, Float.class);method.invoke(newInstance, new Float[]{Float.valueOf(value)});}else{System.out.println("String");Method method = beanClass.getMethod(methodName, String.class);if("男".equalsIgnoreCase(value)){value = "M";}else if("女".equalsIgnoreCase(value)){value = "F";}method.invoke(newInstance, new String[]{value});}} catch (NoSuchMethodException e) {System.out.println(e);return "导入失败";} catch (SecurityException e) {System.out.println(e);return "导入失败";} catch (IllegalArgumentException e) {System.out.println(e);return "导入失败";} catch (InvocationTargetException e) {System.out.println(e);return "导入失败";}break;}}}if(newInstance instanceof Userinfo){Userinfo userinfo = (Userinfo) newInstance;userinfo.setCreated(new Date());userInfoService.addUserInfo(userinfo);}else if(newInstance instanceof CameraCode){CameraCode cameraCode = (CameraCode) newInstance;cameraCode.setCreateDate(new Date());cameraCodeService.addCameraCode(cameraCode);}}}}}}return "导入成功";}else if("xlsx".equals(suffix)){XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);if(xssfWorkbook!=null){for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);if (xssfSheet != null) {XSSFRow row = xssfSheet.getRow(0);if(row!=null){Field[] declaredFields = beanClass.getDeclaredFields();Method[] methods = beanClass.getMethods();for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {XSSFRow xssfRow = xssfSheet.getRow(rowNum);if(xssfRow!=null){T newInstance = beanClass.newInstance();for (int i = 0; i < xssfRow.getLastCellNum(); i++) {String valueName = getValue(row.getCell(i));String value = getValue(xssfRow.getCell(i));for (Field field : declaredFields) {if(valueName.equalsIgnoreCase(field.getName())){String methodName = "set" + valueName.substring(0, 1).toUpperCase() + valueName.substring(1);String typeName = field.getType().getName();try {if("int".equalsIgnoreCase(typeName)){System.out.println("int");Method method = beanClass.getMethod(methodName, int.class);method.invoke(newInstance, new int[]{Integer.parseInt(value)});}else if("java.lang.Integer".equalsIgnoreCase(typeName)){System.out.println("Integer");Method method = beanClass.getMethod(methodName, Integer.class);method.invoke(newInstance, new Integer[]{Integer.valueOf(value)});}else if("boolean".equalsIgnoreCase(typeName)){System.out.println("boolean");Method method = beanClass.getMethod(methodName, boolean.class);method.invoke(newInstance, new Boolean[]{Boolean.parseBoolean(value)});}else if("java.lang.Boolean".equalsIgnoreCase(typeName)){System.out.println("Boolean");Method method = beanClass.getMethod(methodName, Boolean.class);method.invoke(newInstance, new Boolean[]{Boolean.valueOf(value)});}else if("double".equalsIgnoreCase(typeName)){System.out.println("double");Method method = beanClass.getMethod(methodName, double.class);method.invoke(newInstance, new double[]{Double.parseDouble(value)});}else if("java.lang.Double".equalsIgnoreCase(typeName)){System.out.println("Double");Method method = beanClass.getMethod(methodName, Double.class);method.invoke(newInstance, new Double[]{Double.valueOf(value)});}else if("float".equalsIgnoreCase(typeName)){System.out.println("float");Method method = beanClass.getMethod(methodName, float.class);method.invoke(newInstance, new float[]{Float.parseFloat(value)});}else if("java.lang.Float".equalsIgnoreCase(typeName)){System.out.println("Float");Method method = beanClass.getMethod(methodName, Float.class);method.invoke(newInstance, new Float[]{Float.valueOf(value)});}else{System.out.println("String");Method method = beanClass.getMethod(methodName, String.class);if("男".equalsIgnoreCase(value)){value = "M";}else if("女".equalsIgnoreCase(value)){value = "F";}method.invoke(newInstance, new String[]{value});}} catch (NoSuchMethodException e) {System.out.println(e);return "导入失败";} catch (SecurityException e) {System.out.println(e);return "导入失败";} catch (IllegalArgumentException e) {System.out.println(e);return "导入失败";} catch (InvocationTargetException e) {System.out.println(e);return "导入失败";}break;}}}//这里判断实例化class是哪个实体类，这里可以设置一些特定不变，创建时间，之类的字段//这步主要是确定调用什么service来操作保存数据if(newInstance instanceof Userinfo){Userinfo userinfo = (Userinfo) newInstance;userinfo.setCreated(new Date());userInfoService.addUserInfo(userinfo);}else if(newInstance instanceof CameraCode){CameraCode cameraCode = (CameraCode) newInstance;cameraCode.setCreateDate(new Date());cameraCodeService.addCameraCode(cameraCode);}}}}}}}return "导入成功";}} catch (FileNotFoundException e) {System.out.println(e);;return "文件未找到";} catch (IOException e) {System.out.println(e);return "读取或写入失败";} catch (InstantiationException e) {System.out.println(e);return "实例失败";} catch (IllegalAccessException e) {System.out.println(e);return "非法";}return "导入失败";} }

</pre><p></p><pre>

注意：需要先引入poi相关jar包，

下方是我的excel文件格式

userinfo.xls

userinfo.xlsx

camercode.xls

camercode.xlsx