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();
}
}
}