用户表
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