连接数据库类
package com.lianrui.it;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 封装数据库连接操作
*
* @author chendongj
*
*/
public class DBUtil {
private String driverClassName;
private String url;
private String userName;
private String password;
/**
* 取得数据库连接
*
* @return
*/
public Connection getConnection() {
Connection conn = null;
try {
Class.forName(driverClassName);
conn = DriverManager.getConnection(url, userName, password);
System.out.println("连接数据库成功！");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭数据库连接
*
* @param conn
*/
public void closeConn(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭PreparedStatement
*
* @param pstmt
*/
public void closePreparedStatement(PreparedStatement pstmt) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭ResultSet
*
* @param rs
*/
public void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static void main(String[] args) {
DBUtil db = new DBUtil();
db.setDriverClassName("com.mysql.jdbc.Driver");
db.setUrl("jdbc:mysql://localhsot:3306/eeee?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true");
db.setUserName("yonghuming");
db.setPassword("mima");
db.getConnection();
}
}
工具类
package com.lianrui.it;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
public class Util {
public static final String dtShort = "yyyyMM";
/**
*
* 获取统计月份
* @return
*/
public static String getDate(String month) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/m/dd");
if (month != null && month != "") {
try {
Date date = sdf.parse(month);
;
DateFormat df = new SimpleDateFormat(dtShort);
return df.format(date);
} catch (ParseException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 字符串转整型
*
* @param ss
* @return
*/
public static int string2Int(String ss) {
if (null == ss) {
return 0;
}
return Integer.parseInt(ss);
}
/**
* 整形转字符串
*
* @param integer
* @return
*/
public static String int2String(int integer) {
return String.valueOf(integer);
}
/**
* 判断是否周末
*
* @param dataStr
* @return
*/
public static int isWeekend(String dataStr) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/m/d");
if (dataStr != null && dataStr != "") {
try {
Date bdate = sdf.parse(dataStr);
Calendar cal = Calendar.getInstance();
cal.setTime(bdate);
if (cal.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY) {
return 1; // 周六
} else if (cal.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) {
return 2; // 周日
}
} catch (ParseException e) {
e.printStackTrace();
}
}
return -1;
}
/**
* 判断迟到10分钟以内
*
* @param time
* @return
*/
public static boolean tenMi(String time) {
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
try {
if (time != null && time != "") {
Date shijitime = sdf.parse(time);
String shangban = "08:45";
Date shangbanTime = sdf.parse(shangban);
if (shijitime.getTime() > shangbanTime.getTime()
&& shijitime.getTime() - shangbanTime.getTime()
return true; // 10分钟以内
} else {
return false;
}
}
} catch (ParseException e) {
e.printStackTrace();
}
return false;
}
/**
* 判断迟到(补休)
*
* @param time
* @return
*/
public static String chidao(String time) {
try {
if (time != null && time != "") {
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
Date shijitime = sdf.parse(time);
String shangban = "08:45";
Date shangbanTime = sdf.parse(shangban);
if (shijitime.getTime() > shangbanTime.getTime()
&& shijitime.getTime() - shangbanTime.getTime() > 0
&& shijitime.getTime() - shangbanTime.getTime() <= 3600000) {
return "1"; // 迟到
} else if (shijitime.getTime() - shangbanTime.getTime() > 3600000
&& shijitime.getTime() - shangbanTime.getTime() <= 7200000) {
return "2";
} else if (shijitime.getTime() - shangbanTime.getTime() > 7200000
&& shijitime.getTime() - shangbanTime.getTime() <= 17100000) {
return "4";
} else if (shijitime.getTime() - shangbanTime.getTime() > 17100000) {
return "8";
}
}
} catch (ParseException e) {
e.printStackTrace();
}
return "";
}
public static String jiaban(String time) {
try {
if (time != null && time != "") {
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
Date shijitime = sdf.parse(time);
String xiawu = "18:00";
Date xiabanTime = sdf.parse(xiawu);
if (shijitime.getTime() > xiabanTime.getTime()
&& shijitime.getTime() - xiabanTime.getTime() > 0
&& shijitime.getTime() - xiabanTime.getTime()
return ""; // 正常下班
} else if (shijitime.getTime() > xiabanTime.getTime()
&& shijitime.getTime() - xiabanTime.getTime() >= 7200000
&& shijitime.getTime() - xiabanTime.getTime()
return "1"; // 加班1小时
} else if (shijitime.getTime() > xiabanTime.getTime()
&& shijitime.getTime() - xiabanTime.getTime() >= 10800000
&& shijitime.getTime() - xiabanTime.getTime()
return "2"; // 加班2小时
} else if (shijitime.getTime() > xiabanTime.getTime()
&& shijitime.getTime() - xiabanTime.getTime() >= 14400000
&& shijitime.getTime() - xiabanTime.getTime()
return "4"; // 加班4小时
} else if (shijitime.getTime() - xiabanTime.getTime() <= -43200000) {
return "8"; // 加班2小时
} else if (shijitime.getTime()
&& shijitime.getTime() - xiabanTime.getTime() > -43200000) {
return "早退"; // 早退
}
}
} catch (ParseException e) {
e.printStackTrace();
}
return ""; // 其他
}
/**
* 周末加班
*
* @return
*/
public static String zmjb(String startTime, String endTime) {
try {
if (startTime != null && startTime != "" && endTime != null
&& endTime != "") {
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
Date shijitime = sdf.parse(endTime);
Date shangbanTime = sdf.parse(startTime);
long hour = (shijitime.getTime() - shangbanTime.getTime())
/ (60000 * 60);
return String.valueOf(hour);
}
} catch (ParseException e) {
e.printStackTrace();
}
return ""; // 其他
}
public static void main(String[] args) {
// Util.tenMi("8:54");
}
}
bean
package com.lianrui.it;
public class TxlBean {
public String noNum; // 序号
public String memberNum; // 登记号
public String name; // 姓名
public String dateTime; // 登记日期
public String startTime; // 签到时间
public String endTime; // 签退时间
public String bumen; // 部门
public String getNoNum() {
return noNum;
}
public void setNoNum(String noNum) {
this.noNum = noNum;
}
public String getMemberNum() {
return memberNum;
}
public void setMemberNum(String memberNum) {
this.memberNum = memberNum;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDateTime() {
return dateTime;
}
public void setDateTime(String dateTime) {
this.dateTime = dateTime;
}
public String getStartTime() {
return startTime;
}
public void setStartTime(String startTime) {
this.startTime = startTime;
}
public String getEndTime() {
return endTime;
}
public void setEndTime(String endTime) {
this.endTime = endTime;
}
public String getBumen() {
return bumen;
}
public void setBumen(String bumen) {
this.bumen = bumen;
}
}
业务实现
package com.lianrui.it;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* @author chendongj
*
*/
public class ReadExcelFile {
private static String[] dengjihao = { "11255", "11224", "11240", "11236",
"11164", "11175", "11162", "11194", "11190", "11212", "11191",
"11213" }; //员工有变动在这里修改登记
public static void main(String[] args) {
System.out.println("start...........");
ReadExcelFile obj = new ReadExcelFile();
List list = obj.readExcel("E:/kaoqin.xls"); // 读取的文件路径，每次导入需要修改
DBUtil db = new DBUtil();
// 连接数据库
db.setDriverClassName("com.mysql.jdbc.Driver");
db.setUrl("jdbc:mysql://localhost:3306/ee?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true");
db.setUserName("用户名");
db.setPassword("密码");
Connection ct = db.getConnection();
Statement st = null;
try {
st = ct.createStatement();
String noNum = ""; // 序号
String memberNum = ""; // 登记号
String name = ""; // 姓名
String dateTime = ""; // 登记日期
String startTime = ""; // 签到时间
String endTime = ""; // 签退时间
String bumen = ""; // 部门
String jiaban = ""; // 加班时间(小时数)
String buxiu = ""; // 补休
String beizhu = ""; // 备注
String createTime = "";
String sql = "";
int num = 0; // 迟到次数(10分钟以内)
int weekendNum = 0;
for (int i = 0; i
noNum = list.get(i).noNum;
memberNum = list.get(i).memberNum;
name = list.get(i).name;
dateTime = list.get(i).dateTime;
startTime = list.get(i).startTime;
endTime = list.get(i).endTime;
bumen = list.get(i).bumen;
createTime = Util.getDate(dateTime); // 导入月份
weekendNum = Util.isWeekend(dateTime);
for (int j = 0; j
if (memberNum.equals(dengjihao[j])) {
if ((startTime != null && startTime != "")
|| (endTime != null && endTime != "")) {
if (weekendNum == 1 || weekendNum == 2) {
beizhu = "周末加班";
}
jiaban = Util.jiaban(endTime); // 加班情况
}
if ((startTime == null || startTime == "")
&& (endTime == null || endTime == "")) {
jiaban = "";
buxiu = "";
beizhu = "无打卡记录";
if (weekendNum == 1) {
beizhu = "周六";
} else if (weekendNum == 2) {
beizhu = "周日";
}
} else if((startTime == null || startTime == "") &&((endTime != null || endTime != ""))){
buxiu = "";
beizhu = "早上无打卡记录";
}else if((startTime != null || startTime != "") &&((endTime == null || endTime == ""))){
beizhu = "下午无打卡记录";
}else {
beizhu = "";
if (weekendNum == 1 || weekendNum == 2) {
beizhu = "周末加班";
jiaban = Util.zmjb(startTime, endTime);
}
}
if (Util.chidao(startTime) != null
|| Util.chidao(startTime) != "") { // 已迟到
if (Util.tenMi(startTime)) {// 10分钟以内
num++;
if (num <= 3) {
beizhu = "10分钟以内";
buxiu = "";
} else {//超过3次
buxiu = Util.chidao(startTime);
beizhu = "";
}
} else {//超过10分钟
buxiu = Util.chidao(startTime);
}
} else {// 没迟到
buxiu = "";
beizhu = "";
}
sql = "insert into es_kaoqin"
+ "(no_num,member_num,name,date_time,start_time,end_time,bumen,create_time,jiaban,buxiu,beizhu) values('"
+ noNum
+ "','"
+ memberNum
+ "','"
+ name
+ "','"
+ dateTime
+ "','"
+ startTime
+ "','"
+ endTime
+ "','"
+ bumen
+ "','"
+ createTime
+ "','"
+ jiaban
+ "','"
+ buxiu
+ "','"
+ beizhu + "')";
st.executeUpdate(sql);
}
num = 0;// 重置0
}
}
System.out.println("导入成功！");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
st.close();
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
db.closeConn(ct);
System.out.println("end.............");
}
}
/**
* 读取Excel数据
*
* @param filepath
* @return
*/
public List readExcel(String filepath) {
File file = new File(filepath);
List txlList = new ArrayList();
try {
InputStream is = new FileInputStream(file.getAbsolutePath());
Workbook wb = Workbook.getWorkbook(is);
int sheet_size = wb.getNumberOfSheets();
for (int index = 0; index
Sheet sheet = wb.getSheet(index);
for (int i = 1; i
TxlBean txlbean = new TxlBean();
txlbean.setNoNum(sheet.getCell(0, i).getContents());
txlbean.setMemberNum(sheet.getCell(1, i).getContents());
txlbean.setName(sheet.getCell(2, i).getContents());
txlbean.setDateTime(sheet.getCell(3, i).getContents());
txlbean.setStartTime(sheet.getCell(4, i).getContents());
txlbean.setEndTime(sheet.getCell(5, i).getContents());
txlbean.setBumen(sheet.getCell(6, i).getContents());
txlList.add(txlbean);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return txlList;
}
}
462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
