釘釘應(yīng)用開發(fā)-Python操作釘釘文檔

一： 服務(wù)端SDK下載

服務(wù)端SDK下載 - 釘釘開放平臺 (dingtalk.com)

pip3 install alibabacloud_dingtalk

二：釘釘開放平臺

開發(fā)者后臺 (dingtalk.com)

基礎(chǔ)概念 - 釘釘開放平臺 (dingtalk.com)

2.1：創(chuàng)建應(yīng)用

2.2：獲取應(yīng)用基本信息

2.3：權(quán)限申請，獲取讀寫權(quán)限

批量申請所需權(quán)限

申請查詢用戶詳情權(quán)限，否則后面不能根據(jù)用戶UserID查詢用戶UnionID的信息。

2.4：創(chuàng)建文檔，獲取workbookID

在文檔的右上角，左鍵三個點的按鈕，在彈出的框中選擇文檔信息。

獲取表格ID

2.5：獲取開發(fā)者的UserID

進入【釘釘管理后臺】 成員管理 (dingtalk.com)

在詳細信息中，這里的員工UserID就是需要的信息。

2.6：獲取AccessToken

獲取Acess Token API: API Explorer (dingtalk.com)

根據(jù)前面獲取到的appKey和appSecret信息，作為API的入?yún)�，調(diào)用后返回的accessToken就是需要的token。有效期7200秒。

2.7：獲取開發(fā)者的UnionID

根據(jù)UserID,獲取開發(fā)者的UnionID(OperatorID)。

根據(jù)UserID獲取UnionID的釘釘API接口： API Explorer (dingtalk.com)

把前面獲取到的AccessToken和UserID信息作為入?yún)�傳入，獲取用戶UnionID。

2.8：所有基本信息

• AppID
• AgentID
• ClientID（原AppKey和SuiteKey）
• ClientSecret（原AppSecret和SuiteSecret）
• WorkBookID
• UserID
• UnionID(原OperatorID)
• AccessToken

相關(guān)API調(diào)用頁面：

API Explorer (dingtalk.com)

獲取Acess Token

API Explorer (dingtalk.com)

獲取UserID:

成員管理 (dingtalk.com)

根據(jù)UserID查詢unionid

API Explorer (dingtalk.com)

三：生成代碼demo

3.1：安裝python模塊

pip install alibabacloud_dingtalk

3.2：開發(fā)通用模塊代碼

根據(jù)官方提供的Demo，按照不同功能調(diào)整成自己的代碼。

代碼結(jié)構(gòu)如下：

• conf目錄下settings.py是配置文件（配置日志,系統(tǒng)變量等）
• data目錄下token.json是緩存token的文件。
• utils目錄下是通用工具模塊
  • conndb.py是連接數(shù)據(jù)庫的模塊。
  • decrypt.py是加密解密模塊。
  • pyding_workbook.py是調(diào)用釘釘文檔API接口,操作釘釘文檔的模塊。
  • AYOGI目錄下，（比如rpt_luckbean_daily.py）是業(yè)務(wù)相關(guān)的代碼。

• 代碼路徑： ./utils/pyding_workbook.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @CreateDate   : 2024/6/7 下午10:41
# @Author       : DBArtist
# @Email        : [email protected]
# @Project      : my-python-scripts
# @ScriptFile   : pyding_workbook.py
# @Describe     :

# pip install alibabacloud_dingtalk
import os
import sys
import json
from datetime import datetime,timedelta
from typing import List
from alibabacloud_dingtalk.doc_1_0.client import Client as dingtalkdoc_1_0Client
from alibabacloud_dingtalk.doc_1_0 import models as dingtalkdoc__1__0_models
from alibabacloud_tea_util import models as util_models
from alibabacloud_tea_openapi import models as open_api_models
from alibabacloud_tea_util.client import Client as UtilClient
from alibabacloud_dingtalk.oauth2_1_0.client import Client as dingtalkoauth2_1_0Client
from alibabacloud_dingtalk.oauth2_1_0 import models as dingtalkoauth_2__1__0_models
from conf.settings import *

class MyWorkbook():
    def __init__(self,access_token,workbook_id=WORKBOOK_ID,operator_id=OPERATOR_ID):
        self.access_token = access_token
        self.workbook_id = workbook_id
        self.operator_id = operator_id
        self.client = self.create_client()

    @staticmethod
    def create_client() -> dingtalkdoc_1_0Client:
        """
        使用 Token 初始化賬號Client
        @return: Client
        @throws Exception
        """
        config = open_api_models.Config()
        config.protocol = 'https'
        config.region_id = 'central'
        return dingtalkdoc_1_0Client(config)

    def get_all_sheets(self):
        """
        獲取所有的sheets
        :return:
        """
        get_all_sheets_headers = dingtalkdoc__1__0_models.GetAllSheetsHeaders()
        get_all_sheets_headers.x_acs_dingtalk_access_token = self.access_token
        get_all_sheets_request = dingtalkdoc__1__0_models.GetAllSheetsRequest(
            operator_id=self.operator_id
        )
        try:
            ret = self.client.get_all_sheets_with_options(self.workbook_id, get_all_sheets_request, get_all_sheets_headers, util_models.RuntimeOptions())
            wb_sheets_list = ret.body.value
            wb_sheets_list_format = []
            for wb_sheet in wb_sheets_list:
                wb_sheets_list_format.append([wb_sheet.id, wb_sheet.name])

            return wb_sheets_list_format
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] get_all_sheets err: {err =}')
                pass

    def check_sheet_exist(self, sheet_name):
        """
        檢測某個sheet_name是否存在
        :param sheet_name:
        :return:
        """
        all_sheets_list = self.get_all_sheets()
        for wb_sheet in all_sheets_list:
            if sheet_name in wb_sheet:
                return True
        return False

    def create_sheet(self,sheet_name):
        """
        創(chuàng)建sheet
        :param sheet_name:
        :return:
        """
        if self.check_sheet_exist(sheet_name):
            LOGGER.info(f'[pyding_workbook] create sheet fail, {sheet_name} is already exist.')
            return None

        create_sheet_headers = dingtalkdoc__1__0_models.CreateSheetHeaders()
        create_sheet_headers.x_acs_dingtalk_access_token = self.access_token
        create_sheet_request = dingtalkdoc__1__0_models.CreateSheetRequest(
                operator_id=self.operator_id,
                name=sheet_name
            )
        try:
            sheet_obj = self.client.create_sheet_with_options(self.workbook_id, create_sheet_request, create_sheet_headers, util_models.RuntimeOptions())

            return sheet_obj
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] create_sheet err: {err =}')
                pass

    def get_sheetid_by_name(self,sheet_name):
        """
        根據(jù)sheet名稱查找對應(yīng)的sheet_id
        :param sheet_name:
        :return:
        """
        all_sheets = self.get_all_sheets()
        for wb_sheet in all_sheets:
            if sheet_name in wb_sheet[1]:
                return wb_sheet[0]
        return False

    def get_sheet(self,sheet_id=None, sheet_name=None):
        """
        獲取sheet
        :return:
        :param self:
        :return:
        """
        if sheet_id is None and sheet_name is None:
            LOGGER.info(f'[pyding_workbook] get sheet fail, sheet_id or sheet_name must atleast be specified.')
            return None
        if sheet_id is None and sheet_name is not None:
            sheet_id = self.get_sheetid_by_name(sheet_name)
            if not sheet_id:
                LOGGER.info(f'[pyding_workbook] get sheet fail, sheet_name:"{sheet_name}" is not exist.')
                return None

        get_sheet_headers = dingtalkdoc__1__0_models.GetSheetHeaders()
        get_sheet_headers.x_acs_dingtalk_access_token = self.access_token
        get_sheet_request = dingtalkdoc__1__0_models.GetSheetRequest(
            operator_id=self.operator_id
        )

        try:
            sheet_obj = self.client.get_sheet_with_options(self.workbook_id, sheet_id,
                        get_sheet_request, get_sheet_headers, util_models.RuntimeOptions())

            return sheet_obj
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] get_sheet err: {err =}')
                pass

    def delete_sheet(self,sheet_id=None,sheet_name=None):
        """
        刪除sheet
        :return:
        :param self:
        :return:
        """
        if sheet_id is None and sheet_name is None:
            LOGGER.info(f'[pyding_workbook] delete sheet fail, sheet_id or sheet_name must atleast be specified.')
            return None
        if sheet_id is None and sheet_name is not None:
            sheet_id = self.get_sheetid_by_name(sheet_name)
            if not sheet_id:
                LOGGER.info(f'[pyding_workbook] delete sheet fail, sheet_name:"{sheet_name}" is not exist.')
                return None

        delete_sheet_headers = dingtalkdoc__1__0_models.DeleteSheetHeaders()
        delete_sheet_headers.x_acs_dingtalk_access_token = self.access_token
        delete_sheet_request = dingtalkdoc__1__0_models.DeleteSheetRequest(
            operator_id=self.operator_id,
        )
        try:
            ret = self.client.delete_sheet_with_options(self.workbook_id, sheet_id, delete_sheet_request, delete_sheet_headers,
                                               util_models.RuntimeOptions())
            return ret
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] delete_sheet err: {err =}')
                pass

    def get_range(self,range_address,sheet_id=None,sheet_name=None):
        """
        獲取單元格信息
        :param range_address:
        :param sheet_id:
        :param sheet_name:
        :return:
        """
        if sheet_id is None and sheet_name is None:
            LOGGER.info(f'[pyding_workbook] get range fail, sheet_id or sheet_name must atleast be specified.')
            return None
        if sheet_id is None and sheet_name is not None:
            sheet_id = self.get_sheetid_by_name(sheet_name)
            if not sheet_id:
                LOGGER.info(f'[pyding_workbook] get range fail, sheet_name:"{sheet_name}" is not exist.')
                return None

        get_range_headers = dingtalkdoc__1__0_models.GetRangeHeaders()
        get_range_headers.x_acs_dingtalk_access_token = self.access_token
        get_range_request = dingtalkdoc__1__0_models.GetRangeRequest(
            operator_id=self.operator_id
        )
        try:
            ret = self.client.get_range_with_options(self.workbook_id, sheet_id, range_address,
                            get_range_request, get_range_headers,util_models.RuntimeOptions())
            return ret
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] get_range err: {err =}')
                pass

    def clear_range_all(self,range_address,sheet_id=None,sheet_name=None):
        """
        清除區(qū)域內(nèi)所有內(nèi)容（包括數(shù)據(jù)，格式等。。。）
        :return:
        """
        if sheet_id is None and sheet_name is None:
            LOGGER.info(f'[pyding_workbook] clear range all fail, sheet_id or sheet_name must atleast be specified.')
            return None
        if sheet_id is None and sheet_name is not None:
            sheet_id = self.get_sheetid_by_name(sheet_name)
            if not sheet_id:
                LOGGER.info(f'[pyding_workbook] clear range all fail, sheet_name:"{sheet_name}" is not exist.')
                return None

        clear_headers = dingtalkdoc__1__0_models.ClearHeaders()
        clear_headers.x_acs_dingtalk_access_token = self.access_token
        clear_request = dingtalkdoc__1__0_models.ClearRequest(
            operator_id=self.operator_id
        )
        try:
            cra_obj = self.client.clear_with_options(self.workbook_id, sheet_id, range_address,
                      clear_request, clear_headers, util_models.RuntimeOptions())
            return cra_obj
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] clear_range_all err: {err =}')
                pass

    def clear_range_data(self,range_address,sheet_id=None,sheet_name=None):
        """
        清除單元格所有數(shù)據(jù)（格式等其他保留）
        :param range_address:
        :param sheet_id:
        :param sheet_name:
        :return:
        """
        if sheet_id is None and sheet_name is None:
            LOGGER.info(f'[pyding_workbook] clear range data fail, sheet_id or sheet_name must atleast be specified.')
            return None
        if sheet_id is None and sheet_name is not None:
            sheet_id = self.get_sheetid_by_name(sheet_name)
            if not sheet_id:
                LOGGER.info(f'[pyding_workbook] clear range data faile, sheet_name:"{sheet_name}" is not exist.')
                return None

        clear_data_headers = dingtalkdoc__1__0_models.ClearDataHeaders()
        clear_data_headers.x_acs_dingtalk_access_token = self.access_token
        clear_data_request = dingtalkdoc__1__0_models.ClearDataRequest(
            operator_id=self.operator_id
        )
        try:
            crd_obj = self.client.clear_data_with_options(self.workbook_id, sheet_id, range_address,
                       clear_data_request, clear_data_headers,util_models.RuntimeOptions())
            return crd_obj
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] clear_range_data err: {err =}')
                pass

    def update_range(self,range_address,range_values,sheet_id=None,sheet_name=None,**kwargs):
        """
        更新單元格信息，包括單元格中的值、背景色、超鏈接等。
        :param range_address:
        :param sheet_id:
        :param sheet_name:
        :return:
        """
        if sheet_id is None and sheet_name is None:
            LOGGER.info(f'[pyding_workbook] update range fail, sheet_id or sheet_name must atleast be specified.')
            return None
        if sheet_id is None and sheet_name is not None:
            sheet_id = self.get_sheetid_by_name(sheet_name)
            if not sheet_id:
                LOGGER.info(f'[pyding_workbook] update range fail, sheet_name:"{sheet_name}" is not exist.')
                return None

        update_range_headers = dingtalkdoc__1__0_models.UpdateRangeHeaders()
        update_range_headers.x_acs_dingtalk_access_token = self.access_token
        update_range_request = dingtalkdoc__1__0_models.UpdateRangeRequest(
            operator_id=self.operator_id,
            values=range_values,
            **kwargs
        )
        try:
            # print(self.workbook_id, sheet_id, range_address,update_range_request, update_range_headers)
            upt_ret = self.client.update_range_with_options(self.workbook_id, sheet_id, range_address,
                        update_range_request, update_range_headers, util_models.RuntimeOptions())

            return upt_ret
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] update_range err: {err =}')
                pass


class MyToken:
    def __init__(self):
        pass

    @staticmethod
    def create_client() -> dingtalkoauth2_1_0Client:
        """
        使用 Token 初始化賬號Client
        @return: Client
        @throws Exception
        """
        config = open_api_models.Config()
        config.protocol = 'https'
        config.region_id = 'central'
        return dingtalkoauth2_1_0Client(config)

    @staticmethod
    def create_token(app_key=APP_KEY,app_secret=APP_SECRET) -> None:
        """
        創(chuàng)建token
        :param app_key:
        :param app_secret:
        :return:
        """
        client = MyToken.create_client()
        get_access_token_request = dingtalkoauth_2__1__0_models.GetAccessTokenRequest(
            app_key=app_key,
            app_secret=app_secret
        )
        try:
            ret_token_obj = client.get_access_token(get_access_token_request)
            return ret_token_obj
        except Exception as err:
            if not UtilClient.empty(err.code) and not UtilClient.empty(err.message):
                # err 中含有 code 和 message 屬性，可幫助開發(fā)定位問題
                ERROR_LOGGER.error(f'[pyding_workbook] create_token err: {__qualname__} {err=}')
                pass

    @staticmethod
    def get_token(file_path,flush=False):
        """
        獲取token
        1：先從本地文件讀取，如果沒有本地文件，則執(zhí)行創(chuàng)建token，獲取并存儲在本地。
        2：如果本地文件讀取到數(shù)據(jù)，再判斷是否過期，如果過期，則執(zhí)行創(chuàng)建token,獲取并存儲在本地。
        3：如果本地文件讀取到數(shù)據(jù)，沒有過期，則直接返回本地文件中存儲的token信息
        :param save_path:
        :return:
        """

        ## 本地文件不存在或者需要強制刷新，則獲取再存儲到本地
        if not os.path.isfile(file_path) or flush:
            LOGGER.info(f"[pyding_workbook] {file_path} is not exists, begin to create new token file.")
            # 創(chuàng)建獲取token : 76eb8496841b3155850e126ad13a8a46
            token_obj = MyToken.create_token(app_key=APP_KEY,app_secret=APP_SECRET)
            access_token = token_obj.body.access_token
            expire_in = token_obj.body.expire_in
            expire_at = datetime.now() + timedelta(seconds=expire_in)
            expire_at_str = expire_at.isoformat()

            tokenData = {
                'access_token': access_token,
                'expire_in': expire_in,
                'expire_at': expire_at_str
            }

            MyToken.save_token(file_path,tokenData)
            return access_token

        # 本地文件存在，則讀取本地json文件
        data = MyToken.read_token(file_path)
        access_token_local=data['access_token']
        expire_at_str_local=data['expire_at']
        expire_at_local = datetime.fromisoformat(expire_at_str_local)

        if expire_at_local < datetime.now():
            # 創(chuàng)建獲取token : 76eb8496841b3155850e126ad13a8a46
            token_obj = MyToken.create_token(app_key=APP_KEY,app_secret=APP_SECRET)
            access_token_new = token_obj.body.access_token
            expire_in_new = token_obj.body.expire_in
            expire_at_new = datetime.now() + timedelta(seconds=expire_in_new)
            expire_at_new_str = expire_at_new.isoformat()

            tokenData = {
                'access_token': access_token_new,
                'expire_in': expire_in_new,
                'expire_at': expire_at_new_str
            }

            MyToken.save_token(file_path,tokenData)
            LOGGER.info(f"[pyding_workbook] Token expired, regenerate.")
            return access_token_new
        else:
            return access_token_local

    @staticmethod
    def save_token(file_path,token_data):
        # 將字典數(shù)據(jù)寫入到JSON文件中
        with open(file_path, 'w', encoding='utf-8') as file:
            # 使用json.dump()寫入數(shù)據(jù)，indent參數(shù)用于美化輸出，表示縮進的空格數(shù)
            json.dump(token_data, file, indent=4, ensure_ascii=False)
        LOGGER.info(f"[pyding_workbook] Token data save to {file_path}")
        return token_data['access_token']

    @staticmethod
    def read_token(file_path):
        with open(file_path, 'r', encoding='utf-8') as file:
            data = json.load(file)
        LOGGER.info(f"[pyding_workbook] Token data read from {file_path}")
        return data

def create_range_address(row_len, col_len, start_row=2,start_col="A"):
    """
    根據(jù)根據(jù)傳入的起始值，及數(shù)據(jù)行，列數(shù)。獲取單元格區(qū)間
    比如: 從A2開始，3行4列的數(shù)據(jù)，單元格區(qū)間是A2:D4
    :param row_len:
    :param col_len:
    :param start_row:
    :param start_col:
    :return:
    """
    if row_len <= 0 or col_len <= 0:
        return None

    if not isinstance(start_col, str) or not (1 <= len(start_col) <= 2) or not start_col.isalpha() or not start_col.isupper():
        raise ValueError("起始列名必須是1或2位大寫字母。")

    if start_row < 2:
        raise ValueError("起始行數(shù)必須是大約等于2。")

    end_col = get_end_column_name(start_col,col_len - 1)
    end_row = start_row + row_len - 1

    range_address = f"{start_col}{start_row}:{end_col}{end_row}"
    return range_address

def get_end_column_name(start_col, num_cols):
    """
    # 將列名轉(zhuǎn)換為數(shù)字，每個字母對應(yīng)26的冪次。
    # 舉例傳入（A，3），表示從A列開始往后3列，結(jié)束列就是C。
    :param start_col:
    :param num_cols:
    :return:
    """
    def col_to_num(col):
        return sum((26 ** i) * (ord(char) - ord('A') + 1) for i, char in enumerate(reversed(col)))

    # 將數(shù)字轉(zhuǎn)換回列名
    def num_to_col(num):
        col_name = ''
        while num > 0:
            num, rem = divmod(num - 1, 26)
            col_name = chr(rem + ord('A')) + col_name
        return col_name

    # 計算起始列的數(shù)字表示
    start_num = col_to_num(start_col)

    # 計算結(jié)束列的數(shù)字表示
    end_num = start_num + num_cols

    # 將結(jié)束列的數(shù)字表示轉(zhuǎn)換回列名
    return num_to_col(end_num)


def get_last_column_name(total_columns, start_column='A'):
    """
    根據(jù)傳入的列數(shù)量，起始列名，獲取最后一列的列名。
    比如：傳入2，最后一列列名是B;
         傳入12，最后一列列名是L;
    默認都是從A列開始計算。
    :param total_columns:  總列數(shù)
    :param start_column:  開始列名或列序號
    :return:
    """
    if total_columns < 1:
        raise ValueError("Invalid column number")

    def column_index(column_name):
        """Convert a column name to a 1-based index."""
        if isinstance(column_name, str):
            result = 0
            for i, char in enumerate(reversed(column_name.upper())):
                result += (ord(char) - 64) * (26 ** i)
            return result
        elif isinstance(column_name, int) and 1 <= column_name <= 702:
            # Excel columns can go up to 'XFD'
            # Excel 2007 及以后版本支持的最大列數(shù)（16,384 列）
            return column_name
        else:
            raise ValueError("start_column must be a column letter (A-XFD) or a positive integer between 1 and 702")

    start_index = column_index(start_column)
    adjusted_columns = total_columns + start_index - 1  # 計算基于起始列的總列索引

    column_name = ""
    while adjusted_columns > 0:
        adjusted_columns -= 1  # 轉(zhuǎn)換為從0開始的索引
        remainder = adjusted_columns % 26
        column_name = chr(65 + remainder) + column_name
        adjusted_columns //= 26

    return column_name


if __name__ == '__main__':
    pass
    # ## 1: 獲取access_token
    # access_token = MyToken.get_token('./data/token.json')
    # # print(f'{access_token =}')
    #
    # ## 生成實例對象
    # mysample = MyWorkbook(access_token,WORKBOOK_ID,OPERATOR_ID)
    #
    # ## 2: 獲取所有sheets列表
    # all_sheets = mysample.get_all_sheets()
    # print(f'{all_sheets = }')
    #
    # # for wb_sheet in all_sheets:
    # #     wb_sheet_id = wb_sheet[0]
    # #     wb_sheet_name = wb_sheet[1]
    # #     print(f'[{wb_sheet_id =} ,{wb_sheet_name=}]')
    # #
    # #     ## 3: 刪除每個sheets(必須保留最少一個sheet，不能全部都刪除完)
    # #     mysample.delete_sheet(wb_sheet_id)
    #
    # # 3: 新創(chuàng)建sheet
    # sheet_name = 'new_sheet2'
    # cs_obj = mysample.create_sheet(sheet_name)
    # if not cs_obj:
    #     print('create sheet fail.')
    # else:
    #     print(f'{cs_obj.body.id =},{cs_obj.body.name =},{cs_obj.body.visibility =}')
    #
    # # 4: 刪除指定sheet
    # ds_obj = mysample.delete_sheet(None, 'new_sheet2')
    # if not ds_obj:
    #     print('delete sheet fail.')
    # else:
    #     print(f'delete sheet status: {ds_obj.body}')
    #
    # # 5: 查詢指定sheet
    # gs_obj = mysample.get_sheet(None,'new_sheet1')
    # if not gs_obj:
    #     print('get sheet fail.')
    # else:
    #     print(f'get sheet msg: {gs_obj.body}')
    #
    #
    # # 6: 查詢區(qū)域內(nèi)容
    # ret = mysample.get_range("A1:B3", None,'new_sheet1')
    # if not ret:
    #     print('get range fail.')
    # else:
    #     # print(f'{ret.body}')
    #     print(f'{ret.body.values}')
    #     # print(f'{ret.body.background_colors}')
    #     # print(f'{ret.body.display_values}')
    #     # print(f'{ret.body.font_sizes}')
    #     # print(f'{ret.body.font_weights}')
    #     # print(f'{ret.body.formulas}')
    #     # print(f'{ret.body.horizontal_alignments}')
    #     # print(f'{ret.body.vertical_alignments}')
    #
    # # 7: 清除區(qū)域內(nèi)所有數(shù)據(jù)
    # range_address = "A2:B3"
    # sheet_id = None
    # sheet_name = 'new_sheet1'
    # ret = mysample.clear_range_data(range_address,sheet_id,sheet_name)
    # if not ret:
    #     print(f'clear range data fail.')
    # else:
    #     print(f'clear range data success: {ret}')
    #
    # # 8: 清除區(qū)域內(nèi)所有數(shù)據(jù)+格式
    # range_address = "A2:B3"
    # sheet_id = None
    # sheet_name = 'new_sheet1'
    # ret = mysample.clear_range_all(range_address,sheet_id,sheet_name)
    # if not ret:
    #     print(f'clear range all fail.')
    # else:
    #     print(f'clear range all success: {ret}')
    #
    #
    # # 9: 更新單元格內(nèi)容
    # range_address = "A1:B3"
    # range_values = [["1.21", "3.31"], ["3.43", "4.54"], ["5.65", "6.02"]]
    # sheet_id = None
    # sheet_name = 'new_sheet1'
    # ret = mysample.update_range(range_address, range_values, sheet_id, sheet_name,number_format="#,##0.00")
    # if not ret:
    #     print(f'update range fail.')
    # else:
    #     print(f'update range success: {ret.body.a_1notation}')
    #
    # # 10: 再次查詢區(qū)域內(nèi)容
    # ret = mysample.get_range(range_address,sheet_id, sheet_name)
    # if not ret:
    #     print('get range fail.')
    # else:
    #     # print(f'{ret.body}')
    #     print(f'get range success: {ret.body.values}')

說明：pyding_workbook.py就是調(diào)用釘釘API的接口核心代碼。
其它代碼就根據(jù)業(yè)務(wù)調(diào)用相關(guān)API接口即可。

settings.py文件主要配置變量：

APP_KEY = "xxx"
APP_SECRET = "xxx"
WORKBOOK_ID = "xxx"
OPERATOR_ID = "xxx"  ## unionid

token.json格式如下

{
    "access_token": "xxx",
    "expire_in": 7200,
    "expire_at": "2024-07-04T16:40:25.944534"
}

數(shù)字格式：

單元格的值，根據(jù)Range地址范圍傳參，格式為二維數(shù)組。

詳情請參考如下示例：

Range地址范圍有幾行，該參數(shù)二維數(shù)組內(nèi)就有幾個元素；Range地址范圍內(nèi)有幾列，該參數(shù)二維數(shù)組每個元素內(nèi)就有幾個值。

示例1 ：Range地址為A1:B3，范圍內(nèi)是一個三行兩列的表格，該參數(shù)值格式如下： { "values": [ ["1", "2"], ["3", "4"], ["5", "6"] ] } 示例2 ：Range地址為A1:C3，范圍內(nèi)是一個三行三列的表格，該參數(shù)值格式如下： { "values": [ ["1","2","3"], ["4","5","6"], ["7","8","9"] ] }

背景色，顏色的16進制值，根據(jù)Range地址范圍傳參，格式為二維數(shù)組。詳情請參考如下示例：

Range地址范圍有幾行，該參數(shù)二維數(shù)組內(nèi)就有幾個元素；Range地址范圍內(nèi)有幾列，該參數(shù)二維數(shù)組每個元素內(nèi)就有幾個值。

示例1 ：Range地址為A1:B3，范圍內(nèi)是一個三行兩列的表格，該參數(shù)值格式如下： { "backgroundColors": [ ["#ff0000", "#00ff00"], ["#f0f0f0", "#0000ff"], ["#f0f0f0", "#0000ff"] ] }

示例2 ：Range地址為A1:C3，范圍內(nèi)是一個三行三列的表格，該參數(shù)值格式如下： { "backgroundColors": [ ["#ff0000","#ff0000","#ff0000"], ["#ff0000","#ff0000","#ff0000"], ["#ff0000","#ff0000","#ff0000"] ] }

超鏈接，根據(jù)Range地址范圍傳參，格式為二維數(shù)組。詳情請參考如下示例：

Range地址范圍有幾行，該參數(shù)二維數(shù)組內(nèi)就有幾個元素；

Range地址范圍內(nèi)有幾列，該參數(shù)二維數(shù)組每個元素內(nèi)就有幾個值。

示例1 ：Range地址為A1:B3，范圍內(nèi)是一個三行兩列的表格，該參數(shù)值格式如下：

{ "hyperlinks": [ [ { "type": "path", "link": "https://www.dingtalk.com", "text": "test" }, { "type": "sheet", "link": "Sheet2", "text": "測試" } ], [ { "type": "range", "link": "Sheet2!A4", "text": "test" }, { "type": "path", "link": "https://www.dingtalk.com", "text": "測試" } ], [ { "type": "range", "link": "Sheet2!A4", "text": "2" }, { "type": "sheet", "link": "Sheet2", "text": "測試" } ] ] }

示例2 ：Range地址為A1:C3，范圍內(nèi)是一個三行三列的表格，該參數(shù)值格式如下：

{ "hyperlinks": [ [ { "type": "path", "link": "https://www.dingtalk.com", "text": "test" }, { "type": "sheet", "link": "Sheet2", "text": "測試" },{ "type": "path", "link": "https://www.dingtalk.com", "text": "test" } ], [ { "type": "range", "link": "Sheet2!A4", "text": "test" }, { "type": "path", "link": "https://www.dingtalk.com", "text": "測試" },{ "type": "path", "link": "https://www.dingtalk.com", "text": "test" } ], [ { "type": "range", "link": "Sheet2!A4", "text": "2" }, { "type": "sheet", "link": "Sheet2", "text": "測試" },{ "type": "path", "link": "https://www.dingtalk.com", "text": "test" } ] ] }

更新清除區(qū)域格式

更新單元格區(qū)域 - 釘釘開放平臺 (dingtalk.com)
清除單元格區(qū)域內(nèi)所有內(nèi)容 - 釘釘開放平臺 (dingtalk.com)