利用反射导入Excel文件【一个实体类文件、一个导入文件】

PatrolGps.java

package com.entity;

import javax.persistence.*;
import com.util.ExcelAnnotation;

@Entity
@Table(name = "Patrol_Gps", catalog = "", schema = "MMS")
public class PatrolGps {

	@Id
	@SequenceGenerator(name = "SEQ_PATROL_GPS", sequenceName = "SEQ_PATROL_GPS_ID", allocationSize = 1)
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_PATROL_GPS")
	@Basic(optional = false)
	@Column(name = "GPS_ID", nullable = false, precision = 32, scale = 0)
	private Long gspId;

	@Column(name = "DEPART_NAME_ONE", length = 50)
	@ExcelAnnotation(exportName = "一级部门")
	private String departNameOne;

	@Column(name = "DEPART_NAME_TWO", length = 50)
	@ExcelAnnotation(exportName = "二级部门")
	private String departNameTwo;

	@Column(name = "DEPART_NAME_THREE", length = 50)
	@ExcelAnnotation(exportName = "三级部门")
	private String departNameThree;

	@Column(name = "DEPART_NAME_FOUR", length = 50)
	@ExcelAnnotation(exportName = "四级部门")
	private String departNameFour;

	@Column(name = "DEPART_NAME_FIVE", length = 50)
	@ExcelAnnotation(exportName = "五级部门")
	private String departNameFive;

	@Column(name = "DEPART_NAME_SIX", length = 50)
	@ExcelAnnotation(exportName = "六级部门")
	private String departNameSix;

	@Column(name = "SPECIALTY", length = 50)
	@ExcelAnnotation(exportName = "专业")
	private String specialty;

	@Column(name = "STAFF_NAME", length = 50)
	@ExcelAnnotation(exportName = "人员姓名")
	private String staffName;

	@Column(name = "BEGIN_TIME", length = 50)
	@ExcelAnnotation(exportName = "开始时间")
	private String beginTime;

	@Column(name = "END_TIME", length = 50)
	@ExcelAnnotation(exportName = "结束时间")
	private String endTime;

	@Column(name = "PROJECT_DETAIL_NUM", length = 11)
	@ExcelAnnotation(exportName = "计划明细数")
	private String projectDetailNum;

	@Column(name = "QUALIFIED_DETAIL_NUM", length = 11)
	@ExcelAnnotation(exportName = "合格明细数")
	private String qualifiedDetailNum;

	@Column(name = "CHECK_RATE", length = 50)
	@ExcelAnnotation(exportName = "巡检率")
	private String checkRate;

	@Column(name = "PLAN_EQUIP_NUM", length = 11)
	@ExcelAnnotation(exportName = "计划涉及设施个数")
	private String planEquipNum;

	@Column(name = "ACTUAL_EQUIP_NUM", length = 11)
	@ExcelAnnotation(exportName = "实际到位设施数")
	private String actualEquipNum;

	@Column(name = "PLAN_COVER_RATE", length = 50)
	@ExcelAnnotation(exportName = "计划覆盖率")
	private String planCoverRate;

	public Long getGspId() {
		return gspId;
	}

	public void setGspId(Long gspId) {
		this.gspId = gspId;
	}

	public String getDepartNameOne() {
		return departNameOne;
	}

	public void setDepartNameOne(String departNameOne) {
		this.departNameOne = departNameOne;
	}

	public String getDepartNameTwo() {
		return departNameTwo;
	}

	public void setDepartNameTwo(String departNameTwo) {
		this.departNameTwo = departNameTwo;
	}

	public String getDepartNameThree() {
		return departNameThree;
	}

	public void setDepartNameThree(String departNameThree) {
		this.departNameThree = departNameThree;
	}

	public String getDepartNameFour() {
		return departNameFour;
	}

	public void setDepartNameFour(String departNameFour) {
		this.departNameFour = departNameFour;
	}

	public String getDepartNameFive() {
		return departNameFive;
	}

	public void setDepartNameFive(String departNameFive) {
		this.departNameFive = departNameFive;
	}

	public String getDepartNameSix() {
		return departNameSix;
	}

	public void setDepartNameSix(String departNameSix) {
		this.departNameSix = departNameSix;
	}

	public String getSpecialty() {
		return specialty;
	}

	public void setSpecialty(String specialty) {
		this.specialty = specialty;
	}

	public String getStaffName() {
		return staffName;
	}

	public void setStaffName(String staffName) {
		this.staffName = staffName;
	}

	public String getBeginTime() {
		return beginTime;
	}

	public void setBeginTime(String beginTime) {
		this.beginTime = beginTime;
	}

	public String getEndTime() {
		return endTime;
	}

	public void setEndTime(String endTime) {
		this.endTime = endTime;
	}

	public String getProjectDetailNum() {
		return projectDetailNum;
	}

	public void setProjectDetailNum(String projectDetailNum) {
		this.projectDetailNum = projectDetailNum;
	}

	public String getQualifiedDetailNum() {
		return qualifiedDetailNum;
	}

	public void setQualifiedDetailNum(String qualifiedDetailNum) {
		this.qualifiedDetailNum = qualifiedDetailNum;
	}

	public String getCheckRate() {
		return checkRate;
	}

	public void setCheckRate(String checkRate) {
		this.checkRate = checkRate;
	}

	public String getPlanEquipNum() {
		return planEquipNum;
	}

	public void setPlanEquipNum(String planEquipNum) {
		this.planEquipNum = planEquipNum;
	}

	public String getActualEquipNum() {
		return actualEquipNum;
	}

	public void setActualEquipNum(String actualEquipNum) {
		this.actualEquipNum = actualEquipNum;
	}

	public String getPlanCoverRate() {
		return planCoverRate;
	}

	public void setPlanCoverRate(String planCoverRate) {
		this.planCoverRate = planCoverRate;
	}

}

 

TestImportExcel.java

 

package com.test;

import com.util.ExcelAnnotation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * User: CYG
 * Date: 12-3-5
 * Time: 4:39pm
 */
@SuppressWarnings("unchecked")
public class TestImportExcel extends HibernateDaoSupport {

	static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	@SuppressWarnings( { "unused", "static-access" })
	public Collection impExcel(String className, File file, String... pattern)
			throws Exception {
		Collection patrolGpsList = new ArrayList();// 解析后每条PatrolGps数据
		Class clazz = Class.forName(className);// 实例化类
		int exceptionNum = 0;

		try {
			/**
			 * 类反射得到调用方法
			 */
			Field filed[] = clazz.getDeclaredFields();
			// 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
			Map fieldMap = new HashMap();
			// 循环读取所有字段
			for (int i = 1; i < filed.length; i++) {
				Field f = filed[i];
				// 得到单个字段上的Annotation
				ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
				// 如果标识了Annotationd的话
				if (exa != null) {
					// 构造设置了Annotation的字段的Setter方法
					String fieldName = f.getName();
					String setMethodName = "set"
							+ fieldName.substring(0, 1).toUpperCase()
							+ fieldName.substring(1);
					// 构造调用的method，
					Method setMethod = clazz.getMethod(setMethodName,
							new Class[] { f.getType() });
					// 将这个method以Annotaion的名字为key来存入。
					fieldMap.put(exa.exportName(), setMethod);// 注释的名称对应字段的set方法
					fieldMap.put(exa.exportName() + "_exa", exa);// 注释的名称拼接_exa对应字段的注释
				}
			}
			/**
			 * excel的解析开始
			 */
			// 将传入的File构造为FileInputStream;
			FileInputStream in = new FileInputStream(file);
			// 得到工作表
			HSSFWorkbook book = new HSSFWorkbook(in);
			// 得到第一页
			HSSFSheet sheet = book.getSheetAt(0);
			// 得到第一面的所有行
			Iterator<Row> allRows = sheet.rowIterator();
			/**
			 * 标题解析
			 */
			// 得到第一行，也就是标题行
			Row title = allRows.next();
			// 得到第一行的所有列
			Iterator<Cell> cellTitle = title.cellIterator();
			// 将标题的文字内容放入到一个map中。
			Map<Integer, String> titleMap = new HashMap<Integer, String>();
			int i = 0;
			while (cellTitle.hasNext()) {
				Cell cell = cellTitle.next();
				String value = cell.getStringCellValue();
				titleMap.put(i, value);
				i = i + 1;
			}
			Map<Integer, String> firstTitleMap = new HashMap<Integer, String>();
			firstTitleMap.put(0, "一级部门");
			firstTitleMap.put(1, "二级部门");
			firstTitleMap.put(2, "三级部门");
			firstTitleMap.put(3, "四级部门");
			firstTitleMap.put(4, "五级部门");
			firstTitleMap.put(5, "六级部门");
			// 判断标题栏是否为空
			if (!"部门名称".equals(titleMap.get(0))) {// 第一格是部门名称，实体类里面是不存在这个！
				exceptionNum = 3;
				throw new Exception();
			}
			for (int j = 1; j < titleMap.size(); j++) {// 其余格都在实体类里面
				if (null == fieldMap.get(titleMap.get(j))) {
					exceptionNum = 3;
					throw new Exception();
				}
			}
			/**
			 * 解析内容行
			 */
			// 用来格式化日期的DateFormat
			if (pattern.length >= 1) {
				sdf = new SimpleDateFormat(pattern[0]);
			}
			int j = 0;// 行数
			int k = 0;// 列数
			try {
				while (allRows.hasNext()) {
					// 标题下的第一行
					Row nextRow = allRows.next();
					// 得到传入类的实例
					Object objClass = clazz.newInstance();
					// 遍历这一行的第一列
					Cell firstCell = nextRow.getCell(0);
					String[] firstTitle = firstCell.getStringCellValue().split(
							"\\\\");
					int m = 0;// 拆分第一列后的列数
					for (String str : firstTitleMap.values()) {
						Method setMethod = (Method) fieldMap.get(str);
						ExcelAnnotation annotation = (ExcelAnnotation) fieldMap
								.get(str + "_exa");
						Type[] setterTypes = setMethod
								.getGenericParameterTypes();
						String parType = setterTypes[0].toString();
						setMethod.invoke(objClass, firstTitle[m]);
						m++;
						if (m == firstTitle.length) {
							break;
						}
					}

					// 遍历这一行的其余列,每行开始初始化列数
					for (k = 1; k < titleMap.size(); k++) {
						// begin
						// 列取值
						Cell cell = nextRow.getCell(k);
						// 这里得到此列对应的标题
						String titleString = titleMap.get(k).toString();
						// 如果这一列的标题和类中的某一列的Annotation相同，那么就调用此类的set方法进行赋值
						if (fieldMap.containsKey(titleString)) {
							Method setMethod = (Method) fieldMap
									.get(titleString);
							ExcelAnnotation annotation = (ExcelAnnotation) fieldMap
									.get(titleString + "_exa");
							// 得到setter方法的参数
							Type[] setterTypes = setMethod
									.getGenericParameterTypes();
							// setter方法只有一个参数
							String parType = setterTypes[0].toString();
							// 取得参数的类型保存到一个字符串里面
							String typeString = "";
							if (cell != null) {
								if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
									if ("开始时间".equals(titleMap.get(k))
											|| "结束时间".equals(titleMap.get(k))) {
										typeString = sdf.format(cell
												.getDateCellValue());
									} else {
										typeString = String.valueOf(cell
												.getNumericCellValue());
									}
								} else if (cell.getCellType() == cell.CELL_TYPE_STRING) {
									typeString = String.valueOf(cell
											.getStringCellValue());
								}
							}
							setMethod.invoke(objClass, typeString);
						}
						// end
					}
					patrolGpsList.add(objClass);// 将这个类保存到List里面
				}
			} catch (Exception e) {
				e.printStackTrace();
				if (exceptionNum == 0) {
					throw new Exception("第" + (j + 1) + "行" + (k + 1)
							+ "列出错！请检查！");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			if (exceptionNum == 3) {
				throw new Exception("第一行标题栏不正确！");
			}
			throw new Exception("导入出错，请检查Excel格式");
		}
		return patrolGpsList;// 返回解析成功后List内容
	}

	public void save(String className, File File) throws Exception {
		try {
			Long before = System.currentTimeMillis();
			List obj = (ArrayList) impExcel(className, File);
			Long after = System.currentTimeMillis();
			System.out.println("此次操作共耗时：" + (after - before) + "毫秒");
			if (null != obj)
				for (int i = 0; i < obj.size(); i++) {
					getHibernateTemplate().saveOrUpdate(obj.get(i));
				}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	public static void main(String[] args) {
		TestImportExcel tie = new TestImportExcel();
		// try {
		// File file = new File("D:\\excel.xls");
		// Collection<PatrolGps> gpsList = tie.impExcel("com.entity.PatrolGps",
		// file);
		// for (PatrolGps patrolGps : gpsList) {
		// System.out.println(patrolGps.getDepartNameOne());
		// System.out.println(patrolGps.getDepartNameTwo());
		// System.out.println(patrolGps.getDepartNameThree());
		// System.out.println(patrolGps.getDepartNameFour());
		// System.out.println(patrolGps.getDepartNameFive());
		// System.out.println(patrolGps.getDepartNameSix());
		// }
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		try {
			tie.save("com.entity.PatrolGps", new File("D:\\excel.xls"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}