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

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);
  }
}