feishu_lib.py 9.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261
  1. # -*- coding: utf-8 -*-
  2. # @Author: wangkun
  3. # @Time: 2022/5/6
  4. import json
  5. import requests
  6. import urllib3
  7. from main.common import Common
  8. proxies = {"http": None, "https": None}
  9. class Feishu:
  10. """
  11. 编辑飞书云文档
  12. """
  13. feishu_url = "https://w42nne6hzg.feishu.cn/sheets/shtcngRPoDYAi24x52j2nDuHMih?"
  14. spreadsheetToken = "shtcngRPoDYAi24x52j2nDuHMih"
  15. # 获取飞书api token
  16. @classmethod
  17. def get_token(cls):
  18. """
  19. 获取飞书api token
  20. :return:
  21. """
  22. url = "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal/"
  23. post_data = {"app_id": "cli_a13ad2afa438d00b", # 这里账号密码是发布应用的后台账号及密码
  24. "app_secret": "4tK9LY9VbiQlY5umhE42dclBFo6t4p5O"}
  25. urllib3.disable_warnings()
  26. response = requests.post(url=url, data=post_data, proxies=proxies, verify=False)
  27. tenant_access_token = response.json()["tenant_access_token"]
  28. return tenant_access_token
  29. # 获取表格元数据
  30. @classmethod
  31. def get_metainfo(cls):
  32. """
  33. 获取表格元数据
  34. :return:
  35. """
  36. url = "https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/" + cls.spreadsheetToken + "/metainfo"
  37. headers = {
  38. "Authorization": "Bearer " + cls.get_token(),
  39. "Content-Type": "application/json; charset=utf-8"
  40. }
  41. params = {
  42. "extFields": "protectedRange", # 额外返回的字段,extFields=protectedRange时返回保护行列信息
  43. "user_id_type": "open_id" # 返回的用户id类型,可选open_id,union_id
  44. }
  45. urllib3.disable_warnings()
  46. r = requests.get(url=url, headers=headers, params=params, proxies=proxies, verify=False)
  47. response = json.loads(r.content.decode("utf8"))
  48. return response
  49. # 读取工作表中所有数据
  50. @classmethod
  51. def get_values_batch(cls, sheetid):
  52. """
  53. 读取工作表中所有数据
  54. :param sheetid: 哪张表
  55. :return: 所有数据
  56. """
  57. url = "https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/" + cls.spreadsheetToken + "/values_batch_get"
  58. headers = {
  59. "Authorization": "Bearer " + cls.get_token(),
  60. "Content-Type": "application/json; charset=utf-8"
  61. }
  62. params = {
  63. # 多个查询范围 如 url?ranges=range1,range2 ,其中 range 包含 sheetId 与单元格范围两部分
  64. "ranges": sheetid,
  65. # valueRenderOption=ToString 可返回纯文本的值(数值类型除外);
  66. # valueRenderOption=FormattedValue 计算并格式化单元格;
  67. # valueRenderOption=Formula单元格中含有公式时返回公式本身;
  68. # valueRenderOption=UnformattedValue计算但不对单元格进行格式化
  69. "valueRenderOption": "ToString",
  70. # dateTimeRenderOption=FormattedString 计算并将时间日期按照其格式进行格式化,但不会对数字进行格式化,返回格式化后的字符串。
  71. "dateTimeRenderOption": "",
  72. # 返回的用户id类型,可选open_id,union_id
  73. "user_id_type": "open_id"
  74. }
  75. urllib3.disable_warnings()
  76. r = requests.get(url=url, headers=headers, params=params, proxies=proxies, verify=False)
  77. response = json.loads(r.content.decode("utf8"))
  78. values = response["data"]["valueRanges"][0]["values"]
  79. return values
  80. # 看一看+工作表,插入首行
  81. @classmethod
  82. def insert_columns(cls, sheetid):
  83. """
  84. 插入行或列
  85. :return:插入首行
  86. """
  87. url = "https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/"\
  88. + cls.spreadsheetToken + "/insert_dimension_range"
  89. headers = {
  90. "Authorization": "Bearer " + cls.get_token(),
  91. "Content-Type": "application/json; charset=utf-8"
  92. }
  93. body = {
  94. "dimension": {
  95. "sheetId": sheetid,
  96. "majorDimension": "ROWS", # 默认 ROWS ,可选 ROWS、COLUMNS
  97. "startIndex": 1, # 开始的位置
  98. "endIndex": 2 # 结束的位置
  99. },
  100. "inheritStyle": "AFTER" # BEFORE 或 AFTER,不填为不继承 style
  101. }
  102. urllib3.disable_warnings()
  103. r = requests.post(url=url, headers=headers, json=body, proxies=proxies, verify=False)
  104. Common.logger().info(r.json()["msg"])
  105. # 看一看+工作表,首行写入数据
  106. @classmethod
  107. def update_values(cls, sheetid, a1, b1, c1, d1, e1, f1, g1, h1, i1, j1, k1, l1, m1, n1, o1):
  108. """
  109. 写入数据
  110. :param sheetid: 哪张工作表
  111. :param a1: 单元格
  112. :param b1: 单元格
  113. :param c1: 单元格
  114. :param d1: 单元格
  115. :param e1: 单元格
  116. :param f1: 单元格
  117. :param g1: 单元格
  118. :param h1: 单元格
  119. :param i1: 单元格
  120. :param j1: 单元格
  121. :param k1: 单元格
  122. :param l1: 单元格
  123. :param m1: 单元格
  124. :param n1: 单元格
  125. :param o1: 单元格
  126. :return:
  127. """
  128. url = "https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/" + cls.spreadsheetToken + "/values_batch_update"
  129. headers = {
  130. "Authorization": "Bearer " + cls.get_token(),
  131. "Content-Type": "application/json; charset=utf-8"
  132. }
  133. body = {
  134. "valueRanges": [
  135. {
  136. "range": sheetid + "!A2:O2",
  137. "values": [
  138. [a1, b1, c1, d1, e1, f1, g1, h1, i1, j1, k1, l1, m1, n1, o1]
  139. ]
  140. },
  141. ],
  142. }
  143. urllib3.disable_warnings()
  144. r = requests.post(url=url, headers=headers, json=body, proxies=proxies, verify=False)
  145. Common.logger().info(r.json()["msg"])
  146. # 读取单元格数据
  147. @classmethod
  148. def get_range_value(cls, sheetid, cell):
  149. """
  150. 读取单元格内容
  151. :param sheetid: 哪张工作表
  152. :param cell: 哪个单元格
  153. :return: 单元格内容
  154. """
  155. url = "https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/" \
  156. + cls.spreadsheetToken + "/values/" + sheetid + "!" + cell
  157. headers = {
  158. "Authorization": "Bearer " + cls.get_token(),
  159. "Content-Type": "application/json; charset=utf-8"
  160. }
  161. params = {
  162. # valueRenderOption=ToString 可返回纯文本的值(数值类型除外);
  163. # valueRenderOption=FormattedValue 计算并格式化单元格;
  164. # valueRenderOption=Formula单元格中含有公式时返回公式本身;
  165. # valueRenderOption=UnformattedValue计算但不对单元格进行格式化。
  166. "valueRenderOption": "ToString",
  167. # dateTimeRenderOption=FormattedString 计算并对时间日期按照其格式进行格式化,但不会对数字进行格式化,返回格式化后的字符串。
  168. "dateTimeRenderOption": "",
  169. # 返回的用户id类型,可选open_id,union_id
  170. "user_id_type": "open_id"
  171. }
  172. urllib3.disable_warnings()
  173. r = requests.get(url=url, headers=headers, params=params, proxies=proxies, verify=False)
  174. return r.json()["data"]["valueRange"]["values"][0]
  175. # 删除行或列,可选 ROWS、COLUMNS
  176. @classmethod
  177. def dimension_range(cls, sheetid, major_dimension, startindex, endindex):
  178. """
  179. 删除行或列
  180. :param sheetid:工作表
  181. :param major_dimension:默认 ROWS ,可选 ROWS、COLUMNS
  182. :param startindex:开始的位置
  183. :param endindex:结束的位置
  184. :return:
  185. """
  186. url = "https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/" + cls.spreadsheetToken + "/dimension_range"
  187. headers = {
  188. "Authorization": "Bearer " + cls.get_token(),
  189. "Content-Type": "application/json; charset=utf-8"
  190. }
  191. body = {
  192. "dimension": {
  193. "sheetId": sheetid,
  194. "majorDimension": major_dimension,
  195. "startIndex": startindex,
  196. "endIndex": endindex
  197. }
  198. }
  199. urllib3.disable_warnings()
  200. r = requests.delete(url=url, headers=headers, json=body, proxies=proxies, verify=False)
  201. Common.logger().info(r.json()["msg"])
  202. if __name__ == "__main__":
  203. feishu = Feishu()
  204. # # 获取飞书api token
  205. # feishu.get_token()
  206. # # 获取表格元数据
  207. # feishu.get_metainfo()
  208. # 读取工作表中所有数据
  209. # print(feishu.get_values_batch("SdCHOM"))
  210. # print(len(feishu.get_values_batch("SdCHOM")))
  211. # for i in range(len(feishu.get_values_batch("SdCHOM"))):
  212. # videoid = feishu.get_values_batch("SdCHOM")[i][1]
  213. # if videoid == "b3":
  214. # # 删除行或列,可选 ROWS、COLUMNS
  215. # feishu.dimension_range("SdCHOM", "ROWS", i+1, i+1)
  216. # print(videoid)
  217. # # 看一看+工作表,插入首行
  218. # print(feishu.insert_columns("SdCHOM"))
  219. #
  220. # # 看一看+工作表,首行写入数据
  221. # print(feishu.update_values("SdCHOM", "a1", "b1", "c1", "d1", "e1", "f1", "g1",
  222. # "h1", "i1", "j1", "k1", "l1", "m1", "n1", "o1"))
  223. # # 查询单元格内容
  224. # print(feishu.get_range_value("SdCHOM", "B8:C8"))
  225. #
  226. # # 删除行或列,可选 ROWS、COLUMNS
  227. # feishu.dimension_range("SdCHOM", "ROWS")
  228. pass