锟斤拷锟斤拷位锟矫ｏ拷锟斤拷页 > 锟斤拷锟斤拷锟教筹拷 > 锟教筹拷 > 锟斤拷锟斤拷应锟矫匡拷锟斤拷-Python锟斤拷锟斤拷锟斤拷锟斤拷锟侥碉拷锟斤拷excel锟芥）
锟斤拷锟斤拷应锟矫匡拷锟斤拷-Python锟斤拷锟斤拷锟斤拷锟斤拷锟侥碉拷 一锟斤拷 锟斤拷锟斤拷锟絊DK锟斤拷锟斤拷 锟斤拷锟斤拷锟絊DK锟斤拷锟斤拷 - 锟斤拷锟斤拷锟斤拷锟斤拷平台 (dingtalk.com) pip3 install alibabacloud_dingtalk 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷平台 锟斤拷锟斤拷锟竭猴拷台 (dingtalk.com) 锟斤拷锟斤拷锟斤拷锟斤拷 - 锟斤拷锟斤拷锟斤拷锟斤拷平台 (dingt
锟斤拷锟斤拷锟絊DK锟斤拷锟斤拷 - 锟斤拷锟斤拷锟斤拷锟斤拷平台 (dingtalk.com)
pip3 install alibabacloud_dingtalk
锟斤拷锟斤拷锟竭猴拷台 (dingtalk.com)
锟斤拷锟斤拷锟斤拷锟斤拷 - 锟斤拷锟斤拷锟斤拷锟斤拷平台 (dingtalk.com)
锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷权锟斤拷
锟斤拷锟斤拷锟窖拷没锟斤拷锟斤拷锟饺拷蓿锟斤拷锟斤拷锟斤拷锟芥不锟杰革拷锟斤拷锟矫伙拷UserID锟斤拷询锟矫伙拷UnionID锟斤拷锟斤拷息锟斤拷
锟斤拷锟侥碉拷锟斤拷锟斤拷锟较角ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥帮拷钮锟斤拷锟节碉拷锟斤拷锟侥匡拷锟斤拷选锟斤拷锟侥碉拷锟斤拷息锟斤拷
锟斤拷取锟斤拷锟斤拷ID
锟斤拷锟诫【锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷台锟斤拷 锟斤拷员锟斤拷锟斤拷 (dingtalk.com)
锟斤拷锟斤拷细锟斤拷息锟叫ｏ拷锟斤拷锟斤拷锟皆憋拷锟経serID锟斤拷锟斤拷锟斤拷要锟斤拷锟斤拷息锟斤拷
锟斤拷取Acess Token API: API Explorer (dingtalk.com)
锟斤拷锟斤拷前锟斤拷锟饺★拷锟斤拷锟絘ppKey锟斤拷appSecret锟斤拷息锟斤拷锟斤拷为API锟斤拷锟斤拷危锟斤拷锟斤拷煤蠓祷氐锟絘ccessToken锟斤拷锟斤拷锟斤拷要锟斤拷token锟斤拷锟斤拷效锟斤拷7200锟诫。
锟斤拷锟斤拷UserID,锟斤拷取锟斤拷锟斤拷锟竭碉拷UnionID(OperatorID)锟斤拷
锟斤拷锟斤拷UserID锟斤拷取UnionID锟侥讹拷锟斤拷API锟接口ｏ拷 API Explorer (dingtalk.com)
锟斤拷前锟斤拷锟饺★拷锟斤拷锟紸ccessToken锟斤拷UserID锟斤拷息锟斤拷为锟斤拷未锟斤拷耄拷锟饺★拷没锟経nionID锟斤拷
锟斤拷锟紸PI锟斤拷锟斤拷页锟芥：
API Explorer (dingtalk.com)
锟斤拷取Acess Token
API Explorer (dingtalk.com)
锟斤拷取UserID:
锟斤拷员锟斤拷锟斤拷 (dingtalk.com)
锟斤拷锟斤拷UserID锟斤拷询unionid
API Explorer (dingtalk.com)
pip install alibabacloud_dingtalk
锟斤拷锟捷官凤拷锟结供锟斤拷Demo锟斤拷锟斤拷锟秸诧拷同锟斤拷锟杰碉拷锟斤拷锟斤拷锟皆硷拷锟侥达拷锟诫。
锟斤拷锟斤拷峁癸拷锟斤拷拢锟�
./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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
ERROR_LOGGER.error(f'[pyding_workbook] get_all_sheets err: {err =}')
pass
def check_sheet_exist(self, sheet_name):
"""
锟斤拷锟侥筹拷锟絪heet_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):
"""
锟斤拷锟斤拷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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
ERROR_LOGGER.error(f'[pyding_workbook] create_sheet err: {err =}')
pass
def get_sheetid_by_name(self,sheet_name):
"""
锟斤拷锟斤拷sheet锟斤拷锟狡诧拷锟揭讹拷应锟斤拷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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
ERROR_LOGGER.error(f'[pyding_workbook] get_range err: {err =}')
pass
def clear_range_all(self,range_address,sheet_id=None,sheet_name=None):
"""
锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷荩锟斤拷锟斤拷锟斤拷锟斤拷荩锟斤拷锟绞斤拷取锟斤拷锟斤拷锟斤拷锟�
: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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
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):
"""
锟斤拷锟斤拷锟皆拷锟斤拷锟斤拷锟斤拷锟斤拷荩锟斤拷锟绞斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�
: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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
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:
"""
锟斤拷锟斤拷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 锟斤拷锟皆ｏ拷锟缴帮拷锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷
ERROR_LOGGER.error(f'[pyding_workbook] create_token err: {__qualname__} {err=}')
pass
@staticmethod
def get_token(file_path,flush=False):
"""
锟斤拷取token
1锟斤拷锟饺从憋拷锟斤拷锟侥硷拷锟斤拷取锟斤拷锟斤拷锟矫伙拷斜锟斤拷锟斤拷募锟斤拷锟斤拷锟街达拷写锟斤拷锟絫oken锟斤拷锟斤拷取锟斤拷锟芥储锟节憋拷锟截★拷
2锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷募锟斤拷锟饺★拷锟斤拷锟斤拷荩锟斤拷锟斤拷卸锟斤拷欠锟斤拷锟节ｏ拷锟斤拷锟斤拷锟斤拷冢锟斤拷锟街达拷写锟斤拷锟絫oken,锟斤拷取锟斤拷锟芥储锟节憋拷锟截★拷
3锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷募锟斤拷锟饺★拷锟斤拷锟斤拷荩锟矫伙拷泄锟斤拷冢锟斤拷锟街憋拷臃锟斤拷乇锟斤拷锟斤拷募锟斤拷写娲拷锟絫oken锟斤拷息
: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.")
# 锟斤拷锟斤拷锟斤拷取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
# 锟斤拷锟斤拷锟侥硷拷锟斤拷锟节ｏ拷锟斤拷锟饺★拷锟斤拷锟絡son锟侥硷拷
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():
# 锟斤拷锟斤拷锟斤拷取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):
# 锟斤拷锟街碉拷锟斤拷锟斤拷写锟诫到JSON锟侥硷拷锟斤拷
with open(file_path, 'w', encoding='utf-8') as file:
# 使锟斤拷json.dump()写锟斤拷锟斤拷锟捷ｏ拷indent锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞撅拷锟斤拷锟斤拷目崭锟斤拷锟�
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"):
"""
锟斤拷锟捷革拷锟捷达拷锟斤拷锟斤拷锟绞贾碉拷锟斤拷锟斤拷锟斤拷锟斤拷校锟斤拷锟斤拷锟斤拷锟斤拷锟饺★拷锟皆拷锟斤拷锟斤拷锟�
锟斤拷锟斤拷: 锟斤拷A2锟斤拷始锟斤拷3锟斤拷4锟叫碉拷锟斤拷锟捷ｏ拷锟斤拷元锟斤拷锟斤拷锟斤拷锟斤拷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("锟斤拷始锟斤拷锟斤拷锟斤拷锟斤拷锟角达拷约锟斤拷锟斤拷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):
"""
# 锟斤拷锟斤拷锟斤拷转锟斤拷为锟斤拷锟街ｏ拷每锟斤拷锟斤拷母锟斤拷应26锟斤拷锟捷次★拷
# 锟斤拷锟斤拷锟斤拷锟诫（A锟斤拷3锟斤拷锟斤拷锟斤拷示锟斤拷A锟叫匡拷始锟斤拷锟斤拷3锟叫ｏ拷锟斤拷锟斤拷锟叫撅拷锟斤拷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)))
# 锟斤拷锟斤拷锟斤拷转锟斤拷锟斤拷锟斤拷锟斤拷
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
# 锟斤拷锟斤拷锟斤拷始锟叫碉拷锟斤拷锟街憋拷示
start_num = col_to_num(start_col)
# 锟斤拷锟斤拷锟斤拷锟斤拷械锟斤拷锟斤拷直锟绞�
end_num = start_num + num_cols
# 锟斤拷锟斤拷锟斤拷锟叫碉拷锟斤拷锟街憋拷示转锟斤拷锟斤拷锟斤拷锟斤拷
return num_to_col(end_num)
def get_last_column_name(total_columns, start_column='A'):
"""
锟斤拷锟捷达拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞硷拷锟斤拷锟斤拷锟斤拷锟饺★拷锟斤拷一锟叫碉拷锟斤拷锟斤拷锟斤拷
锟斤拷锟界：锟斤拷锟斤拷2锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟紹;
锟斤拷锟斤拷12锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟絃;
默锟较讹拷锟角达拷A锟叫匡拷始锟斤拷锟姐。
:param total_columns: 锟斤拷锟斤拷锟斤拷
: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 锟斤拷锟皆猴拷姹局э拷值锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷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 # 转锟斤拷为锟斤拷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: 锟铰达拷锟斤拷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: 锟斤拷询锟斤拷锟斤拷锟斤拷锟斤拷
# 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: 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�
# 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: 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�+锟斤拷式
# 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: 锟斤拷锟铰碉拷元锟斤拷锟斤拷锟斤拷
# 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: 锟劫次诧拷询锟斤拷锟斤拷锟斤拷锟斤拷
# 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锟斤拷锟角碉拷锟矫讹拷锟斤拷API锟侥接口猴拷锟侥达拷锟诫。
锟斤拷锟斤拷锟斤拷锟斤拷透锟斤拷锟揭碉拷锟斤拷锟斤拷锟斤拷锟紸PI锟接口硷拷锟缴★拷
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"
}
锟斤拷锟街革拷式锟斤拷
| 锟斤拷锟斤拷 | 锟斤拷锟街革拷式 | 示锟斤拷 |
|---|---|---|
| 锟斤拷锟斤拷 | "General" | |
| 锟侥憋拷 | "@" | |
| 锟斤拷锟斤拷 | "#,##0" | 1,234 |
| 锟斤拷锟街ｏ拷小锟斤拷锟姐） | "#,##0.00" | 1,234.56 |
| 锟劫凤拷锟斤拷 | "0%" | 12% |
| 锟劫凤拷锟斤拷锟斤拷小锟斤拷锟姐） | "0.00%" | 12.34% |
| 锟斤拷学锟斤拷锟斤拷 | "0.00E+00" | 1.01E+03 |
| 锟斤拷锟斤拷锟� | "?#,##0" | ?1,234 |
| 锟斤拷锟斤拷遥锟叫★拷锟斤拷悖� | ?#,##0.00" | ?1,234.56 |
| 锟斤拷元 | "$#,##0" | $1,234 |
| 锟斤拷元锟斤拷小锟斤拷锟姐） | "$#,##0.00" | $1,234.56 |
| 锟斤拷锟斤拷 | "yyyy/m/d" | 2022/1/1 |
| 锟斤拷锟节ｏ拷锟斤拷锟侥ｏ拷 | "yyyy锟斤拷m锟斤拷d锟斤拷" | 2022锟斤拷1锟斤拷1锟斤拷 |
| 锟斤拷锟节ｏ拷锟斤拷锟斤拷锟斤拷锟铰ｏ拷 | "yyyy锟斤拷m锟斤拷" | 2022锟斤拷1锟斤拷 |
| 时锟斤拷 | "hh:mm:ss" | 00:00:00 |
| 锟斤拷锟斤拷时锟斤拷 | "yyyy/m/d hh:mm:ss" | 2022/1/1 00:00:00 |
锟斤拷元锟斤拷锟街碉拷锟斤拷锟斤拷锟絉ange锟斤拷址锟斤拷围锟斤拷锟轿ｏ拷锟斤拷式为锟斤拷维锟斤拷锟介。
锟斤拷锟斤拷锟斤拷慰锟斤拷锟斤拷锟绞撅拷锟斤拷锟�
Range锟斤拷址锟斤拷围锟叫硷拷锟叫ｏ拷锟矫诧拷锟斤拷锟斤拷维锟斤拷锟斤拷锟节撅拷锟叫硷拷锟斤拷元锟截ｏ拷Range锟斤拷址锟斤拷围锟斤拷锟叫硷拷锟叫ｏ拷锟矫诧拷锟斤拷锟斤拷维锟斤拷锟斤拷每锟斤拷元锟斤拷锟节撅拷锟叫硷拷锟斤拷值锟斤拷
示锟斤拷1 锟斤拷Range锟斤拷址为A1:B3锟斤拷锟斤拷围锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟叫的憋拷锟今，该诧拷锟斤拷值锟斤拷式锟斤拷锟铰ｏ拷
{ "values": [ ["1", "2"], ["3", "4"], ["5", "6"] ] }示锟斤拷2 锟斤拷Range锟斤拷址为A1:C3锟斤拷锟斤拷围锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟叫的憋拷锟今，该诧拷锟斤拷值锟斤拷式锟斤拷锟铰ｏ拷{ "values": [ ["1","2","3"], ["4","5","6"], ["7","8","9"] ] }
锟斤拷锟斤拷色锟斤拷锟斤拷色锟斤拷16锟斤拷锟斤拷值锟斤拷锟斤拷锟斤拷Range锟斤拷址锟斤拷围锟斤拷锟轿ｏ拷锟斤拷式为锟斤拷维锟斤拷锟介。锟斤拷锟斤拷锟斤拷慰锟斤拷锟斤拷锟绞撅拷锟斤拷锟�
Range锟斤拷址锟斤拷围锟叫硷拷锟叫ｏ拷锟矫诧拷锟斤拷锟斤拷维锟斤拷锟斤拷锟节撅拷锟叫硷拷锟斤拷元锟截ｏ拷Range锟斤拷址锟斤拷围锟斤拷锟叫硷拷锟叫ｏ拷锟矫诧拷锟斤拷锟斤拷维锟斤拷锟斤拷每锟斤拷元锟斤拷锟节撅拷锟叫硷拷锟斤拷值锟斤拷
示锟斤拷1 锟斤拷Range锟斤拷址为A1:B3锟斤拷锟斤拷围锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟叫的憋拷锟今，该诧拷锟斤拷值锟斤拷式锟斤拷锟铰ｏ拷
{ "backgroundColors": [ ["#ff0000", "#00ff00"], ["#f0f0f0", "#0000ff"], ["#f0f0f0", "#0000ff"] ] }示锟斤拷2 锟斤拷Range锟斤拷址为A1:C3锟斤拷锟斤拷围锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟叫的憋拷锟今，该诧拷锟斤拷值锟斤拷式锟斤拷锟铰ｏ拷
{ "backgroundColors": [ ["#ff0000","#ff0000","#ff0000"], ["#ff0000","#ff0000","#ff0000"], ["#ff0000","#ff0000","#ff0000"] ] }
锟斤拷锟斤拷锟接ｏ拷锟斤拷锟斤拷Range锟斤拷址锟斤拷围锟斤拷锟轿ｏ拷锟斤拷式为锟斤拷维锟斤拷锟介。锟斤拷锟斤拷锟斤拷慰锟斤拷锟斤拷锟绞撅拷锟斤拷锟�
Range锟斤拷址锟斤拷围锟叫硷拷锟叫ｏ拷锟矫诧拷锟斤拷锟斤拷维锟斤拷锟斤拷锟节撅拷锟叫硷拷锟斤拷元锟截ｏ拷
Range锟斤拷址锟斤拷围锟斤拷锟叫硷拷锟叫ｏ拷锟矫诧拷锟斤拷锟斤拷维锟斤拷锟斤拷每锟斤拷元锟斤拷锟节撅拷锟叫硷拷锟斤拷值锟斤拷
示锟斤拷1 锟斤拷Range锟斤拷址为A1:B3锟斤拷锟斤拷围锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟叫的憋拷锟今，该诧拷锟斤拷值锟斤拷式锟斤拷锟铰ｏ拷
{ "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锟斤拷锟斤拷围锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟叫的憋拷锟今，该诧拷锟斤拷值锟斤拷式锟斤拷锟铰ｏ拷
{ "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" } ] ] }
锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷式
锟斤拷锟铰碉拷元锟斤拷锟斤拷锟斤拷 - 锟斤拷锟斤拷锟斤拷锟斤拷平台 (dingtalk.com)
锟斤拷锟斤拷锟皆拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟� - 锟斤拷锟斤拷锟斤拷锟斤拷平台 (dingtalk.com)
全锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷ERA5锟斤拷锟斤拷锟截凤拷锟斤拷
锟侥讹拷锟斤拷锟斤拷学习锟斤拷锟斤拷锟斤拷锟界构锟斤拷锟斤拷锟铰ｏ拷
锟侥讹拷锟斤拷为Mate品锟斤拷盛锟戒：HarmonyOS NEXT锟接筹拷锟斤拷锟斤拷戏锟斤拷锟杰得碉拷锟斤拷锟斤拷头锟�
锟侥讹拷实锟街讹拷锟襟集猴拷锟斤拷DataTable锟斤拷锟洁互转锟斤拷
锟侥讹拷锟斤拷锟斤拷NEXT元锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟窖匡拷锟斤拷锟较硷拷锟斤拷品
锟侥讹拷锟结巢锟斤拷锟叫★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡硷拷锟截闭凤拷锟斤拷
锟侥讹拷锟斤拷锟斤拷锟紸rcMap锟斤拷锟斤拷锟叫斤拷锟斤拷栅锟斤拷图锟斤拷锟截诧拷锟斤拷锟斤拷锟斤拷
锟侥讹拷锟斤拷讯锟斤拷锟斤拷锟竭客凤拷锟斤拷营锟斤拷系统锟斤拷锟斤拷
锟侥讹拷锟斤拷锟节猴拷锟斤拷NEXT锟斤拷血锟斤拷锟脚达拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
锟侥讹拷5. Spring Cloud OpenFeign 锟斤拷锟斤拷式 WebService 锟酵伙拷锟剿的筹拷锟斤拷细使锟斤拷
锟侥讹拷Java锟斤拷锟斤拷模式锟斤拷锟斤拷态锟斤拷锟斤拷锟酵讹拷态锟斤拷锟斤拷锟侥对比凤拷锟斤拷
锟侥讹拷Win11锟绞记憋拷锟斤拷锟皆讹拷锟斤拷锟斤拷应锟矫碉拷锟斤拷色锟斤拷锟斤拷示锟斤拷锟斤拷
锟侥讹拷
锟斤拷锟节猴拷锟斤拷锟斤拷锟斤拷系锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�锟斤拷锟斤拷站锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡硷拷锟斤拷锟斤拷
频锟斤拷 锟斤拷锟斤拷频锟斤拷锟斤拷锟斤拷锟斤拷专锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷卓锟斤拷锟斤拷锟�锟斤拷app锟斤拷锟斤拷
锟狡硷拷 锟斤拷原锟斤拷锟斤拷锟斤拷锟斤拷锟铰帮拷锟斤拷锟斤拷锟斤拷锟杰匡拷锟斤拷锟斤拷锟截诧拷锟斤拷
锟斤拷锟斤拷 锟斤拷锟斤拷色锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷锟斤拷锟�锟斤拷锟斤拷战锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
扫锟斤拷维锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街伙拷锟芥本锟斤拷
扫锟斤拷维锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷微锟脚癸拷锟节号ｏ拷
锟斤拷站锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟较达拷锟斤拷锟斤拷锟斤拷锟街凤拷锟斤拷陌锟饺拷锟斤拷敕拷始锟�[email protected]
锟斤拷ICP锟斤拷2022002427锟斤拷-10 锟芥公锟斤拷锟斤拷锟斤拷锟斤拷43070202000427锟斤拷© 2013~2025 haote.com 锟斤拷锟斤拷锟斤拷
