python对钉钉考勤旷工的数据填入excel

1.先获取钉钉接口

app_key = 'ttt'
app_secret = 'ttt'
def getToken():
    url = 'https://oapi.dingtalk.com/gettoken?appkey='+app_key+'&appsecret='+app_secret
    response = requests.get(url=url)
    result = response.json()
    errmsg = result['errmsg']
    print('获取密钥是否成功：',errmsg)
    try:
        access_token = result['access_token']
    except Exception as e:
        print(e)
        access_token = ''
    return access_token
access_token = getToken()

2.获取人员信息

def get_dp(token):
    """
    获取部门列表
    :param token:
    :return:
    """
    url = "https://oapi.dingtalk.com/department/list?access_token=" + token
    dp = json.loads(requests.get(url).text)
    return dp['department']

def get_users(token, dept_id):
    """
    获取部门用户userid列表
    :param token:
    :param dept_id:
    :return:
    """
    url = "https://oapi.dingtalk.com/user/getDeptMember?access_token={0}&deptId={1}".format(token, dept_id)
    user_list = json.loads(requests.get(url).text)
    return user_list['userIds']

def get_user_info(token, user_id):
    """
    根据userid获取用户详情
    :param token:
    :param userId:
    :return:
    """
    url = "https://oapi.dingtalk.com/user/get?access_token={0}&userid={1}".format(token, user_id)
    info = json.loads(requests.get(url).text)
    return info

3.获取自己所需的考勤字段，在输出打印的json中可以查看带字段id

def get_report(token):
    """
    获取钉钉考勤字段
    :param token:
    :return:
    """
    url = "https://oapi.dingtalk.com/topapi/attendance/getattcolumns?access_token=" + token
    dp = json.loads(requests.get(url).text)
    return dp
report=get_report(access_token)
#print(report)

4.根据以上获得json，并处理，填入表格

if __name__ == '__main__':

    #新建excel表格

    work_book = xlwt.Workbook(encoding='utf-8')
    sheet = work_book.add_sheet('sheet')


    #获取当前时间戳的前一天
    kk=(datetime.date.today()-datetime.timedelta(days=1)).strftime('%y-%m-%d')

    kkfrom="20"+kk+" 07:45:00"
    kkto="20"+kk+" 20:30:00"
    print(kkfrom)
    """"
    下面获取到钉钉员工编号和旷工天数，填入上面的表格
    """

    dp = get_dp(access_token)
    i=0

    for d in dp:
        users = get_users(access_token, d['id'])
        #print(users)

        for u in users:
          payload = {

                     "column_id_list":"95976184",
                     "from_date":kkfrom,
                     "to_date":kkto,
                     "userid":u

            }
          #根据userid获取用户名
          x=get_user_info(access_token,u)



          #根据userid获取当天打卡情况
          _data = json.dumps(payload)
          response = requests.post(url=url, data=_data)

          #处理json获得需要的值
          xx = handle.json_chuli2(x)
          h=handle.json_chuli(response.json())




          #操控表格输入相应信息
          sheet.write(i, 0, xx)
          sheet.write(i, 1, h)
          time.sleep(0.5)
          i = i + 1
          work_book.save("nihao.xls")  # 保存文件

5.json处理

#!/usr/bin/env python
# coding: utf-8

# In[8]:


import pickle
import json


def json_chuli(jj):

 #with open(r+jj,"rb") as f:
    #content = f.read()

    # In[12]:

    mydict = jj # 加载Json，存储在mydict

    # In[14]:

    myRes = mydict['result']  # 获取result元素

    # In[22]:

    tmp = myRes['column_vals']
    res = tmp[0]['column_vals']  # 获取column_vals元素

    # In[24]:

    res[0]['value']
    return res[0]['value']


def json_chuli2(hh):
   mydict = hh

   myRes=mydict['name']


   return myRes

6.钉钉机器人创建后就获得数据进行相应发送

# -*- coding: utf-8 -*-


import json
import requests
import xlrd
import os
import datetime


def sendmessage(message):     #钉钉机器人进行发送
    url = 'https://oapi.dingtalk.com/robot/send?access_token=webhook'  # 这里填写你自定义机器人的webhook地址
    HEADERS = {
        "Content-Type": "application/json ;charset=utf-8"
    }
    String_textMsg = {
        "msgtype": "text",
        "text": {"content": message},
        "at": {
            "atMobiles": [
                ""  # 如果需要@某人，这里写他的手机号
            ],
            "isAtAll": 0  # 如果需要@所有人，这里写1
        }
    }
    String_textMsg = json.dumps(String_textMsg)
    res = requests.post(url, data=String_textMsg, headers=HEADERS)
    #print(res.text)


class ExcelData():
    # 初始化方法
    def __init__(self, data_path, sheetname):
        # 定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容（方式①）
        self.table = self.data.sheet_by_name(self.sheetname)
        # 根据工作表的索引获取工作表的内容（方式②）
        # self.table = self.data.sheet_by_name(0)
        # 获取第一行所有内容,如果括号中1就是第二行，这点跟列表索引类似
        self.keys = self.table.row_values(0)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows
        # 获取工作表的有效列数
        self.colNum = self.table.ncols

    # 定义一个读取excel表的方法
    def readExcel(self):
        # 定义一个空列表
        datas = []

        for i in range( self.rowNum):
            # 定义一个空字典
            sheet_data = []
            c_cell = self.table.cell_value(i, 0)
            a_cell = self.table.cell_value(i, 1)
            if a_cell=="1":
                a_cell=c_cell
                datas.append(a_cell)
        # 返回从excel中获取到的数据：以列表存字典的形式返回


        # 去重复值
        new_li1 = list(set(datas))
        # 头部插入时间戳
        kk = (datetime.date.today() - datetime.timedelta(days=1)).strftime('%y-%m-%d')
        d = "20" + kk + "旷工名单"
        new_li1.insert(0,d)
        #list转换为字典
        z=[]
        for x in new_li1:
            z.append(x)
        print(z)
        return z


if __name__ == "__main__":
    data_path = "nihao.xls" #获取表格名字
    sheetname = "sheet"   #获取工资表名
    get_data = ExcelData(data_path, sheetname)  #调用初始函数
    datas = get_data.readExcel()    #调用读取函数


if __name__ == '__main__':
    if datas == ['旷工名单']:
        print("1")
    else:

        sendmessage(datas)