// 2003excel导入
@SuppressWarnings("all")
//@Transactional(propagation=Propagation.REQUIRED)
public int upLoadSubject(String fileName) {
//System.err.println("提示信息");
int flag = 1;
String uploadPath = SysConfig.basepath + SysConfig.excelfile + "/";
try {
//获取Excel文件对象
Workbook book = Workbook.getWorkbook(new File(uploadPath + fileName));
//读取第一个sheet
Sheet sheet = book.getSheet(0);
// int columns=sheet.getColumns();
//初始化时间格式
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat sdfsfm = new SimpleDateFormat("HH:mm:ss");
Etm etm =null;
int column = 0;
int rows = sheet.getRows();
//从EXCEL第4行开始循环,第4行才是数据
for (int i = 3; i < rows; i++) {
if (!"".equals(sheet.getCell(3, i).getContents())&& null != sheet.getCell(3, i).getContents()) {
//实例化对象
etm = new Etm();
//自动产生ID
UUIDGenerator uu = new UUIDGenerator();
//ID
etm.setID(uu.generate().toString());
//导入时间
etm.setDRSJ(sdf.format(new Date()));
//标题----单位
String biaoti=sheet.getCell(0, 0).getContents();
if(-1!=biaoti.indexOf("运管")){
biaoti="运管";
}else if (-1!=biaoti.indexOf("京")){
biaoti="京局";
}else if (-1!=biaoti.indexOf("一")){
biaoti="一局";
}else if (-1!=biaoti.indexOf("三")){
biaoti="三局";
}else if (-1!=biaoti.indexOf("四")){
biaoti="四局";
}else if (-1!=biaoti.indexOf("十五")){
biaoti="十五局";
}else if (-1!=biaoti.indexOf("天津")){
biaoti="天津地铁";
}
//System.out.println("标题单位::"+biaoti);
etm.setDW(biaoti);
//第二行--文档时间,时间必须用DateCell,不能使用getContents()
String dateWdsj="";
Cell cell=sheet.getCell(0, 1);
try{
DateCell dateCell=(DateCell)cell;
Date datetime=dateCell.getDate();
TimeZone zone=TimeZone.getTimeZone("GMT");//使用GMT时间,不然就会多8个小时
sdf.setTimeZone(zone);
dateWdsj=sdf.format(datetime);
etm.setWDSJ(dateWdsj);//文档里的时间
//System.out.println("2:"+dateWdsj);
}catch(ParseException e){
System.out.println("导入时间有错误:"+dateWdsj);
dateWdsj=cell.getContents();
e.printStackTrace();
//etm.setWDSJ(null);
}
//第4行B列-线别
etm.setXB(sheet.getCell(1, i).getContents());
//机车种类
etm.setJCZL(sheet.getCell(2, i).getContents());
//司机工号
etm.setSJGH(sheet.getCell(3, i).getContents());
//司机姓名
etm.setSJXM(sheet.getCell(4, i).getContents());
//副司机工号
etm.setFSJGH(sheet.getCell(5, i).getContents());
//副司机姓名
etm.setFSJXM(sheet.getCell(6, i).getContents());
//学员工号
etm.setXYGH(sheet.getCell(7, i).getContents());
//学员姓名
etm.setXYXM(sheet.getCell(8, i).getContents());
//备注-备注有时间,用备注时间,备注没有时间,用文档日期时间,备注列只有时间字段
if(""== sheet.getCell(9, i).getContents()){
etm.setBZ(dateWdsj);//备注为空用文档自带时间
}else{
DateCell dc=(DateCell)sheet.getCell(9, i);
Date date=dc.getDate();
TimeZone zonee=TimeZone.getTimeZone("GMT");//使用GMT时间,不然就会使用12小时制度
sdfsfm.setTimeZone(zonee);
String sDate=sdfsfm.format(date);
String dateBz=dateWdsj.substring(0,10)+" "+sDate;
etm.setBZ(dateBz);
}
this.getSqlMapClientTemplate().insert("save", etm);
} else {
}
}
book.close();
} catch (Exception e) {
flag = 0;
e.printStackTrace();
} finally {
File file = new File(uploadPath + fileName);
if (file.exists()) {
file.delete();
}
}
return flag;
}
Java 使用jxl导入Excel后数据类型处理
Java-Excel导入代码
最新推荐文章于 2024-07-19 10:47:34 发布