ZKTeco考勤数据集成至Google Sheet或在线服务器实战教程

from pyzk.zk import ZK

from pyzk.misc import get_connection

# 替换为你的考勤机IP地址和端口

DEVICE_IP = 192.168.1.201

DEVICE_PORT = 4370

def get_attendance_logs():

    conn = None

    try:

        conn = get_connection(DEVICE_IP, DEVICE_PORT)

        zk = ZK(conn)

        zk.disable_device() # 禁用设备，防止操作冲突

        # 获取所有考勤记录

        attendances = zk.get_attendance()

        attendance_list = []

        for att in attendances:

            attendance_list.append({

                uid: att.uid,

                user_id: att.user_id,

                timestamp: att.timestamp.strftime(%Y-%m-%d %H:%M:%S),

                status: att.status,

                punch: att.punch

            })

        zk.enable_device() # 重新启用设备

        return attendance_list

    except Exception as e:

        print(f"Error connecting to ZKTeco device: {e}")

        return []

    finally:

        if conn:

            conn.disconnect()

if __name__ == __main__:

    logs = get_attendance_logs()

    if logs:

        print(f"Fetched {len(logs)} attendance records.")

        # 在这里可以将logs数据存储到数据库，或通过Web框架暴露为API

        # 例如，使用Flask或Django创建一个简单的API接口

    else:

        print("No attendance logs fetched.")

<?php

header(Content-Type: application/json);

// 数据库连接配置

$servername = "localhost";

$username = "your_db_user";

$password = "your_db_password";

$dbname = "attendance_db";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {

    die(json_encode([error => Database connection failed: . $conn->connect_error]));

}

$sql = "SELECT user_id, timestamp, status FROM attendance_logs ORDER BY timestamp DESC";

$result = $conn->query($sql);

$attendance_data = [];

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {

        $attendance_data[] = $row;

    }

}

$conn->close();

echo json_encode($attendance_data);

?>

/**

 * 从外部API获取ZKTeco考勤数据并写入Google Sheet。

 */

function importZKTecoAttendance() {

  // 替换为你的服务器端API URL

  var apiUrl = http://your-server.com/api/get_attendance.php;

  try {

    // 使用UrlFetchApp服务调用API

    var response = UrlFetchApp.fetch(apiUrl);

    var content = response.getContentText();

    var json = JSON.parse(content); // 解析JSON字符串

    // 获取当前活动的电子表格和工作表

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

    // 清除旧数据（可选，根据需求决定是否清空）

    // sheet.clearContents();

    // 设置表头

    var headers = [User ID, Timestamp, Status]; // 确保与API返回的JSON键名一致

    sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight("bold");

    // 准备要写入的数据

    var data = [];

    for (var i = 0; i < json.length; i++) {

      var record = json[i];

      data.push([

        record.user_id,

        record.timestamp,

        record.status

      ]);

    }

    // 将数据写入工作表

    if (data.length > 0) {

      // 从第二行开始写入数据

      sheet.getRange(2, 1, data.length, headers.length).setValues(data);

      Logger.log(Successfully imported + data.length + records.);

    } else {

      Logger.log(No data received from API.);

    }

  } catch (e) {

    Logger.log(Error importing attendance data: + e.toString());

    SpreadsheetApp.getUi().alert(数据导入失败, 发生错误: + e.toString(), SpreadsheetApp.getUi().ButtonSet.OK);

  }

}