POI之考勤统计
需求分析
我们公司考勤使用指纹打卡，出入公司也需要指纹打卡开门，这样每个人每天打卡记录全在考勤系统里面，这个考勤系统竟然还不能自动识别员工每天的出勤情况，只是把每次打卡记录统计出来。然后月底时由人事、财务筛选做绩效考核和工资核算，先来看看五月份考勤系统统计出来的原始数据：
看到这个表，我也是醉了。公司几十人员工统计出来4828条记录，这样的系统要它何用。现在我用POI写个程序自动筛选出来并导出Excel文件。
设计思路
1.找到每个员工每天的第一条打开记录和最后一条打卡记录，去除中间重复数据。
2.统计到每天只有一次打卡的记录为考勤异常。
3.统计第一次打卡在早上九点半之后的记录为迟到。
4.统计上班不满8小时的记录为早退。
5.统计周六、周日加班日期.
6.统计正常出勤天数。
效果预览
代码实现
1.把考勤系统导出的考勤表放到D:\liuwuchang文件夹里。
2.新建OnePeople.java
package com.zhanyun.cc;
public class OnePeople {
String id;
String name;
String date;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
@Override
public String toString() {
return "OnePeople [id=" + id + ", name=" + name + ", date=" + date + "]";
}
}
3.新建ManyPeople.java
package com.zhanyun.cc;
import java.util.ArrayList;
public class ManyPeople {
String id;String name;
ArrayListonePeopleList;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public ArrayListgetOnePeopleList() {
return onePeopleList;
}
public void setOnePeopleList(ArrayList onePeopleList) {
this.onePeopleList = onePeopleList;
}
@Override
public String toString() {
return "ManyPeople [id=" + id + ", name=" + name + ", onePeopleList=" + onePeopleList + "]";
}
}
4.新建Result.java
package com.zhanyun.cc;
public class Result {
String id;
String name;
String normalDay;
String lateDay;
String leaveEarly;
String weekDay;
String error;
public Result() {
// TODO Auto-generated constructor stub
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getNormalDay() {
return normalDay;
}
public void setNormalDay(String normalDay) {
this.normalDay = normalDay;
}
public String getLateDay() {
return lateDay;
}
public void setLateDay(String lateDay) {
this.lateDay = lateDay;
}
public String getLeaveEarly() {
return leaveEarly;
}
public void setLeaveEarly(String leaveEarly) {
this.leaveEarly = leaveEarly;
}
public String getWeekDay() {
return weekDay;
}
public void setWeekDay(String weekDay) {
this.weekDay = weekDay;
}
public String getError() {
return error;
}
public void setError(String error) {
this.error = error;
}
@Override
public String toString() {
return "Result [id=" + id + ", name=" + name + ", normalDay=" + normalDay + ", lateDay=" + lateDay
+ ", leaveEarly=" + leaveEarly + ", weekDay=" + weekDay + ", error=" + error + "]";
}
}
5.新建MainActivity.java
package com.zhanyun.cc;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class MainActivity {
String filePath = "D://liuwuchang/考勤表.xls";
InputStream stream;
HSSFWorkbook workbook;
HSSFSheet sheet;
ArrayList resultList;
public MainActivity() {
try {
stream = new FileInputStream(filePath);
workbook = new HSSFWorkbook(stream);// 读取现有的Excel
sheet = workbook.getSheet("Sheet1");// 得到指定名称的Sheet
resultList = new ArrayList<>();
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* 读取所有人的考勤记录
*/
public ArrayList getManyPeopleList() {
ArrayList manyPeopleList = new ArrayList<>();
ArrayList onePeopleList = null;
ManyPeople manyPeople = null;
String id = null;
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int i = firstRowNum; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
if (id != row.getCell(0).getStringCellValue()) {
id = row.getCell(0).getStringCellValue();
onePeopleList = new ArrayList<>();
manyPeople = new ManyPeople();
manyPeople.setId(row.getCell(0).getStringCellValue());
manyPeople.setName(row.getCell(1).getStringCellValue());
manyPeople.setOnePeopleList(onePeopleList);
manyPeopleList.add(manyPeople);
}
OnePeople onePeople = new OnePeople();
onePeople.setId(row.getCell(0).getStringCellValue());
onePeople.setName(row.getCell(1).getStringCellValue());
onePeople.setDate(row.getCell(2).getStringCellValue());
onePeopleList.add(onePeople);
}
return manyPeopleList;
}
/*
* 读取所有人有效的考勤记录
*/
public void getValidManyPeopleList(String id,String name,ArrayList list) {
Result result = new Result();
int normalDay=0;
String lateDay="";
String leaveEarly="";
String weekDay="";
String error="";
ArrayList firstOnePeopleList = new ArrayList<>();
ArrayList lastOnePeopleList = new ArrayList<>();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm");
Calendar calendar = Calendar.getInstance();
int firstDay = 0;
int lastDay = 0;
for (int i = 0; i < list.size(); i++) {
try {
Date date = formatter.parse(list.get(i).getDate());
calendar.setTime(date);
if (firstDay != calendar.get(Calendar.DAY_OF_MONTH)) {
firstDay = calendar.get(Calendar.DAY_OF_MONTH);
firstOnePeopleList.add(date);
}
// System.out.println(calendar.get(Calendar.DAY_OF_MONTH));
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
for (int i = list.size() - 1; i >= 0; i--) {
try {
Date date = formatter.parse(list.get(i).getDate());
calendar.setTime(date);
if (lastDay != calendar.get(Calendar.DAY_OF_MONTH)) {
lastDay = calendar.get(Calendar.DAY_OF_MONTH);
lastOnePeopleList.add(date);
}
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
ArrayList arrayList = new ArrayList<>();
for (int i = lastOnePeopleList.size() - 1; i >= 0; i--) {
arrayList.add(lastOnePeopleList.get(i));
}
lastOnePeopleList = arrayList;
int len = firstOnePeopleList.size();
for (int i = 0; i < len; i++) {
calendar.setTime(firstOnePeopleList.get(i));
int month=calendar.get(Calendar.MONTH)+1;
int day=calendar.get(Calendar.DAY_OF_MONTH);
String week = getWeekOfDate(firstOnePeopleList.get(i));
long firstTime = firstOnePeopleList.get(i).getTime();
long lastTime = lastOnePeopleList.get(i).getTime();
int firstHours = firstOnePeopleList.get(i).getHours();
int firstMinutes = firstOnePeopleList.get(i).getMinutes();
int lastHours = lastOnePeopleList.get(i).getHours();
int lastMinutes = lastOnePeopleList.get(i).getMinutes();
if (firstTime != lastTime) {
if (week != "星期日" && week != "星期六") {
if (firstHours < 9 || (firstHours == 9 && firstMinutes <= 30)) {
long diff = (lastTime - firstTime) / (1000 * 60);
if (diff >= (9 * 60) && (lastHours > 17 || (lastHours == 17 && lastMinutes >= 30))) {
// System.out.println("正常上班");
normalDay++;
} else {
// System.out.println("早退");
leaveEarly=leaveEarly+month+"月"+day+"日"+lastHours+":"+lastMinutes+" ";
}
} else {
// System.out.println("迟到");
lateDay=lateDay+month+"月"+day+"日"+firstHours+":"+firstMinutes+" ";
}
} else {
// System.out.println("周末加班");
weekDay=weekDay+month+"月"+day+"日 ";
}
} else {
// System.out.println(calendar.get(Calendar.DAY_OF_MONTH));
// System.out.println("考勤异常");
error=error+month+"月"+day+"日 "+firstHours+":"+firstMinutes+" ";
}
}
result.setId(id);
result.setName(name);
result.setNormalDay(normalDay+"");
result.setLateDay(lateDay);
result.setLeaveEarly(leaveEarly);
result.setWeekDay(weekDay);
result.setError(error);
resultList.add(result);
}
/**
* * 获取指定日期是星期几 参数为null时表示获取当前日期是星期几
*
* @param date
* @return
*/
public String getWeekOfDate(Date date) {
String[] weekOfDays = { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
Calendar calendar = Calendar.getInstance();
if (date != null) {
calendar.setTime(date);
}
int w = calendar.get(Calendar.DAY_OF_WEEK) - 1;
if (w < 0) {
w = 0;
}
return weekOfDays[w];
}
public static void main(String[] args) {
MainActivity mainActivity = new MainActivity();
ArrayList manyPeopleList = mainActivity.getManyPeopleList();
HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
HSSFSheet sheet = workbook.createSheet("Sheet1");// 创建工作表(Sheet)
for (int i = 0; i < manyPeopleList.size(); i++) {
mainActivity.getValidManyPeopleList(manyPeopleList.get(i).getId(),
manyPeopleList.get(i).getName(),
manyPeopleList.get(i).getOnePeopleList());
}
for (int i = 0; i < mainActivity.resultList.size(); i++) {
HSSFRow row= sheet.createRow(i);
HSSFCell cell_0 = row.createCell(0);
HSSFCell cell_1 = row.createCell(1);
HSSFCell cell_2 = row.createCell(2);
HSSFCell cell_3 = row.createCell(3);
HSSFCell cell_4 = row.createCell(4);
HSSFCell cell_5 = row.createCell(5);
HSSFCell cell_6 = row.createCell(6);
cell_0.setCellValue(mainActivity.resultList.get(i).getId());
cell_1.setCellValue(mainActivity.resultList.get(i).getName());
cell_2.setCellValue(mainActivity.resultList.get(i).getNormalDay());
cell_3.setCellValue(mainActivity.resultList.get(i).getLateDay());
cell_4.setCellValue(mainActivity.resultList.get(i).getLeaveEarly());
cell_5.setCellValue(mainActivity.resultList.get(i).getWeekDay());
cell_6.setCellValue(mainActivity.resultList.get(i).getError());
System.out.println(mainActivity.resultList.get(i).toString());
}
String filePath = "D:\\统计考勤表.xls";// 文件路径
FileOutputStream out;
try {
out = new FileOutputStream(filePath);
workbook.write(out);// 保存Excel文件
out.close();// 关闭文件流
System.out.println("OK!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
