需求分析
我們公司考勤使用指紋打卡琅束，出入公司也需要指紋打卡開門，這樣每個(gè)人每天打卡記錄全在考勤系統(tǒng)里面算谈，這個(gè)考勤系統(tǒng)竟然還不能自動(dòng)識(shí)別員工每天的出勤情況涩禀，只是把每次打卡記錄統(tǒng)計(jì)出來。然后月底時(shí)由人事然眼、財(cái)務(wù)篩選做績效考核和工資核算艾船，先來看看五月份考勤系統(tǒng)統(tǒng)計(jì)出來的原始數(shù)據(jù)：
看到這個(gè)表，我也是醉了高每。公司幾十人員工統(tǒng)計(jì)出來4828條記錄屿岂，這樣的系統(tǒng)要它何用。現(xiàn)在我用POI寫個(gè)程序自動(dòng)篩選出來并導(dǎo)出Excel文件鲸匿。
設(shè)計(jì)思路
1.找到每個(gè)員工每天的第一條打開記錄和最后一條打卡記錄爷怀，去除中間重復(fù)數(shù)據(jù)。
2.統(tǒng)計(jì)到每天只有一次打卡的記錄為考勤異常带欢。
3.統(tǒng)計(jì)第一次打卡在早上九點(diǎn)半之后的記錄為遲到运授。
4.統(tǒng)計(jì)上班不滿8小時(shí)的記錄為早退烤惊。
5.統(tǒng)計(jì)周六、周日加班日期.
6.統(tǒng)計(jì)正常出勤天數(shù)吁朦。
效果預(yù)覽
代碼實(shí)現(xiàn)
1.把考勤系統(tǒng)導(dǎo)出的考勤表放到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);// 讀取現(xiàn)有的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);
??}
??/**
?? * * 獲取指定日期是星期幾 參數(shù)為null時(shí)表示獲取當(dāng)前日期是星期幾
?? *
?? * @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();// 創(chuàng)建Excel文件(Workbook)
????HSSFSheet sheet = workbook.createSheet("Sheet1");// 創(chuàng)建工作表(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:\\統(tǒng)計(jì)考勤表.xls";// 文件路徑
????FileOutputStream out;
????try {
??????out = new FileOutputStream(filePath);
??????workbook.write(out);// 保存Excel文件
??????out.close();// 關(guān)閉文件流
??????System.out.println("OK!");
????} catch (Exception e) {
??????// TODO Auto-generated catch block
??????e.printStackTrace();
????}
??}
}
