wps宏编辑器案例1-自定义函数使用

某公司考情和请假系统是2套独立的系统，人资在月底做考勤统计的时候需要把考勤系统导出的考勤信息表和OA请假流程导出的请假信息表进行合并，得出人员真实的考勤情况。比如员工“忠达”在考勤系统全显示缺勤，实际上请假系统里有4天请假，也就是说考勤系统应该显示4天是请假而不是缺勤。
之前都是人资专员手动肉眼比较，看考勤信息表中缺勤的记录里面，在请假信息里是否有，有则纠正考勤信息表的缺勤，由原来的缺勤改成请假，这个过程比较低效，而且容易眼花出错。为此，利用wps的JS宏编辑器编写了一个自定义函数getWorkLeaveInfo，我们只要在原考勤信息表中增加两列分别调用getWorkLeaveInfo即可，高效且不会出错，具体实现如下。

1、考勤信息表展示

主要有：考勤人员姓名，部门，上班考勤时间、状态，下班考勤时间、状态，考勤日期信息，月底从考勤系统导出。

2、请假信息表

主要由：申请人姓名、部门，申请日期，请假原因，请假类型，请假开始、结束日期、时长等信息，月底从OA系统导出。

3、自定义函数getWorkLeaveInfo编写

1）函数功能需求

如图1黄色高亮背景区域列所示，在考勤信息表增加2列：上班状态2、下班状态2，要求：

• 当日上班状态缺勤的情况下，去比对请假信息表里该天是否有请假，有则返回请假类型，没有则返回缺勤；
• 当日下班状态缺勤的情况下，去比对请假信息表里该天是否有请假，有则返回请假类型，没有则返回缺勤；

2）具体代码

function getWorkLeaveInfo(empName, kqDate, kqBanci, kqStatus) {
	// 获取请假、出差信息
	// empName: 姓名, kqDate：考勤日期, kqBanci：1-上午，2-下午
	// 08:00--13:00, 13:00--18:00
	kqStatus = kqStatus ? kqStatus.Value2 : "缺勤";
	if (!empName || !kqDate || !kqBanci) return kqStatus;
	let sheetQJ = Application.Worksheets.Item("请假信息");
	let amStart = "08:00", amEnd = "13:00", pmStart = "13:00", pmEnd = "18:00";
	kqDate = kqDate.Value2.replace(/^\s|\s$/g,"");		// 去除空格
	let strKqStart = kqDate + " " + (kqBanci == 1 ? amStart : pmStart);
	let strKqEnd = kqDate + " " + (kqBanci == 1 ? amEnd : pmEnd);
	let dateKqStart = Date.parse(strKqStart.replace(/-/g, '/'));
	let dateKqEnd = Date.parse(strKqEnd.replace(/-/g, '/'));
	if (kqStatus == "正常上班" || kqStatus == "正常下班") return kqStatus;	

	for (let i = 1; i < 2000; i++) {
		if (! sheetQJ.Cells.Item(i, 1).Value2) break;
		if (sheetQJ.Cells.Item(i, 1).Value2 == empName) {	// 找人
			let strStart = sheetQJ.Cells.Item(i, 6).Value2 + " " + sheetQJ.Cells.Item(i, 7).Value2;
			let strEnd = sheetQJ.Cells.Item(i, 8).Value2 + " " + sheetQJ.Cells.Item(i, 9).Value2;
			let dateStart = Date.parse(strStart.replace(/-/g, '/'));
			let dateEnd = Date.parse(strEnd.replace(/-/g, '/'));
			if (dateKqStart >= dateStart && dateKqEnd <= dateEnd) {
				return sheetQJ.Cells.Item(i, 5).Value2;
			}
		}
	}
	return kqStatus;
}

3）代码说明

• 参数说明：

  • empName 人员姓名，此处理想情况应使用工号
  • kqDate 考勤日期
  • kqBanci 1-上午（上班），2-下午(下班)
  • kqStatus，考勤系统中显示的考勤状态

• 简单逻辑

  • kqStatus为正常上班或正常下班，则直接返回
  • 自上而下根据姓名比对，且考勤日期+上下班时间，要在请假开始、结束时间段内
  • 考勤信息表默认2000行，当读到空行比对结束，可修改行数

• 实现方式，比对的方式还可以通过range读取所有有效单元格，然后循环单元格来实现，具体见下个案例。

4、自定义函数使用

单元格中引用自定义函数getWorkLeaveInfo即可，如下图所示。

5、案例代码获取

请到gitee获取，https://gitee.com/zongtong2046/jsexcel