多个表格在不同下文件夹下面汇总到同一个表不同工作簿的方法 """ #主文件夹 #sheet1name #name1.xlsx #name2.xlsx #name3.xlsx #sheet2name #name1.xlsx #name2.xlsx #name3.xlsx #workbook sheet1name sheet2name 这个工具的作用就是 将和sheetname名字相同的1个文件夹中的所有的excel文件合并成一个pandas件，并将数据写入到对应的sheet中。 """ import os import xlwings as xw import pandas as pd path = f"{os.path.split(os.path.realpath(__file__))[0]}/" def readsheet_to_sheet(workbook, sheetname): '''读取sheetname对应的文件夹中的所有excel文件，并将数据合并到一个pandas数据框中''' folder_path = path + sheetname all_df = pd.DataFrame() for filename in os.listdir(folder_path): if filename.endswith('.xlsx'): file_path = os.path.join(folder_path, filename) print(f"Reading sheets from {file_path}:") with xw.Book(file_path) as wb: for sheet in wb.sheets: # 读取工作表数据并跳过第一行 df = sheet.used_range.options(pd.DataFrame, index=False, header=1).value # 打印工作表名称和数据 print(f"\nSheet: {sheet.name}") print(df) all_df = pd.concat([all_df,df],ignore_index=True) workbook.sheets[sheetname].range("A2").options(header = False , index = False).value = all_df def main(book_name): ''' 打开excel文件''' wb1 = xw.books(book_name) ########## #这里做了切片, 因为我前面2个工作表是不需要收集的 shop_list = [x for x in wb1.sheet_names][3:] for i in shop_list: readsheet_to_sheet(workbook=wb1,sheetname=i) if __name__ == "__main__": main("file_name.xlsx")

记录一下python 写入钉钉在线表格的过程 {timeline}{timeline-item color="#19be6b"} 2024年11月17日更新{/timeline-item}{/timeline}成为开发者自己创建一个企业， 或者让你所在的公司给予你的账号一个权限 钉钉开发者后台登录创建一个应用如图，这里只需要Client ID (原 AppKey 和 SuiteKey)Client Secret (原 AppSecret 和 SuiteSecret)这两个东西 获取 access_token 然后还需要一个userid 提供的operatorId：表示待办事项的操作者的ID，是一个唯一的标识符。这个可以在钉钉提供的API接口直接获取到， 目前来看是不会变化的后面就是源码了import requests import json import pandas as pd import datetime class Ding(): """ 钉钉表格推送数据 文档信息 https://open.dingtalk.com/document/orgapp/update-cell-properties """ def __init__(self,appkey,appsecret,operatorid,sheetid): #获取access_token access_token = requests.get(f"https://oapi.dingtalk.com/gettoken?"\ f"appkey={appkey}&appsecret={appsecret}",\ timeout=5).json()["access_token"] #设置基础的请求头 self.default_settings = { 'headers':{'Host':'api.dingtalk.com', 'x-acs-dingtalk-access-token':access_token, 'Content-Type':'application/json'}, 'verify': False, 'timeout': 20} self.primary_domain = "https://api.dingtalk.com/v1.0/doc/workbooks/" self.operatorid = operatorid self.sheetid= sheetid def get(self,sheetname,get_range): """ 单独获取单元格内容 """ url = f"{self.primary_domain}{self.sheetid}"\ f"/sheets/{sheetname}/ranges/{get_range}"\ f"?operatorId={self.operatorid}" res = requests.get(url,**self.default_settings) return res.json()["displayValues"] def put(self,sheetname,wt_range,content): """ 单独写入单元格内容 """ url = f"{self.primary_domain}{self.sheetid}"\ f"/sheets/{sheetname}/ranges/{wt_range}"\ f"?operatorId={self.operatorid}" json1 = {"values" : [content]} res = requests.put(url,data=json.dumps(json1),**self.default_settings) if res.status_code != 200: print("钉钉表格推送失败") print(res.text) def clear(self,sheetname,wt_range): """ 清空单元格全部 """ url = f"{self.primary_domain}{self.sheetid}"\ f"/sheets/{sheetname}/ranges/{wt_range}/clear"\ f"?operatorId={self.operatorid}" res = requests.post(url,**self.default_settings) if res.status_code != 200: print("钉钉表格推送失败") print(res.text) def cleardata(self,sheetname,wt_range): """ 清空单元格数据 """ url = f"{self.primary_domain}{self.sheetid}"\ f"/sheets/{sheetname}/ranges/{wt_range}/clearData"\ f"?operatorId={self.operatorid}" res = requests.post(url,**self.default_settings) if res.status_code != 200: print("钉钉表格推送失败") print(res.text) def put_df_to_ding(self,sheetname:str,df_data:pd.DataFrame,wt_range:str,is_mater_head=False): """ 写入dataframe数据到 钉钉表格中 """ letters = list(string.ascii_uppercase) + [letters1 + letters2 \ for letters1 in string.ascii_uppercase\ for letters2 in string.ascii_uppercase] # 写入表格前面的字母 wt_range_latter = ''.join([char for char in wt_range if char.isalpha()]) # 写入表格后面的数字 wt_range_numberr = int(''.join([char for char in wt_range if char.isdigit()])) # 写入表格的宽度 df_width = df_data.shape[1] # 写入表格的起始和结束位置的字母 left_letter = wt_range_latter right_letter = letters[letters.index(left_letter)+df_width-1] def meterhead(df_data:pd.DataFrame): """ 写入表头 """ url = f"{self.primary_domain}{self.sheetid}"\ f"/sheets/{sheetname}/ranges/"\ f"{left_letter}{wt_range_numberr}:{right_letter}{wt_range_numberr}"\ f"?operatorId={self.operatorid}" input_data = {"values" : [df_data.columns.tolist()]} res = requests.put(url,data=json.dumps(input_data),**self.default_settings) if res.status_code != 200: print("钉钉表格推送失败") print(res.text) def put_data(df_data:pd.DataFrame,start_index,end_index): """ 写入表格数据, """ #如果需要写入表头 就需要往下移动一格 就将offset设置为1 offset = 1 if is_mater_head else 0 url = f"{self.primary_domain}{self.sheetid}"\ f"/sheets/{sheetname}/ranges/"\ f"{left_letter}{wt_range_numberr+start_index+offset}:"\ f"{right_letter}{wt_range_numberr+end_index+offset}"\ f"?operatorId={self.operatorid}" input_data = {"values" :\ df_data.astype("str").iloc[start_index:end_index+1].values.tolist()} res = requests.put(url,data=json.dumps(input_data),**self.default_settings) if res.status_code != 200: print("钉钉表格推送失败") print(res.text) def cycle(df:pd.DataFrame): df_height = df.shape[0] width_per_cycle = 1000 num_segments = df_height // width_per_cycle for i in range(num_segments): start = i * width_per_cycle end = (i + 1) * width_per_cycle-1 put_data(df,start,end) if df_height % width_per_cycle != 0: start = num_segments * width_per_cycle end = df_height-1 put_data(df,start,end) if is_mater_head: meterhead(df_data) cycle(df_data) if __name__ == "__main__": DING = Ding(appkey = "appkey", appsecret ="appsecret", operatorid = "operatorid", sheetid= "表格ID",) df_test = pd.DataFrame({"A": [x for x in range(1,1002)], "B": [x for x in range(1,1002)]}) DING.put_df_to_ding(sheetname="Sheet1",df_data=df_test,wt_range="A15",is_mater_head=False) DING.cleardata(sheetname="Sheet1",wt_range="A15:C15")

将请求头转化为key:value的形式 浏览器请求头复制转化为key:value的形式用vscode的正则替换ctrl+H 后 ctrl+R源匹配为 (.*):(.*)$ 如果带换行 (.*):\n(.*)$ 替换匹配为 '$1':'$2',

python 拆分同一个表格中的不同选项 将同选项不同数据差分到不同的表格中

用excel &python 检查 网店库存和erp 库存 #导入包 from datetime import date import json import requests import urllib from urllib import request import pandas as pd url = 'http://cqzs.3cerp.com/pages/stock/searchGoodsExtendStock.htm' cookies = {'3cu':'？？？？'} headers = { "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36 SE 2.X MetaSr 1.0"} x = requests.get(url=url,headers=headers,cookies=cookies,verify=False) dict = x.json() df2 = pd.DataFrame(dict['data']) y = 0 L1 = ["有货且上架:"] L2 = ["有货没上架:"] L3 = ["无货且上架:"] L4 = ["无货没上架:"] while y < 4: df1 = pd.read_excel(r"G:\ZM\sku对应表.xlsx") #print(df1) sku = str(df1.iloc[y,0]) one = df1.iloc[y,1] try: two = df1.iloc[y,2] except: two = 1 try: three = df1.iloc[y,3] except: three = 1 try: four = df1.iloc[y,4] except: four = 1 url = f'https://c0.3.cn/stocks?area=4_50952_50969_0&type=getstocks&skuIds={sku}' headers = { "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36 SE 2.X MetaSr 1.0"} x = requests.get(url=url,headers=headers,verify=False) #print(x) dict = x.json() #print(dict) sku_stock = dict[sku]['StockStateName'] print('网店库存',sku_stock) print(one) print(two) print(three) a = df2.loc[df2['b_c_name']==f'{one}','n_stock'].values[0] try: b = df2.loc[df2['b_c_name']==f'{two}','n_stock'].values[0] except: b = 1 try: c = df2.loc[df2['b_c_name']==f'{three}','n_stock'].values[0] except: c = 1 try: d = df2.loc[df2['b_c_name']==f'{four}','n_stock'].values[0] except: d = 1 print('库存数',a,b,c) y += 1 if a > 0 and b > 0 and c > 0 and d > 0 : print("库存有货") if sku_stock != '无货': print("有货且上架") L1.append(sku) else: print("有货没上架") L2.append(sku) else: print("库存没有") if sku_stock != '无货': print('无货但上架') L3.append(sku) else: print('无货没上架') L4.append(sku) Lall = L1 + L2 + L3 + L4 print(Lall) def ding(): headers={'Content-Type': 'application/json'} robot_url = "https://oapi.dingtalk.com/robot/send?access_token=？？？" data1 ={ "at": { "atMobiles":[ "180xxxxxx" ], "atUserIds":[ "user123" ], "isAtAll": False }, "text": { "content":f"{Lall}" }, "msgtype":"text" } robot= requests.post(url = robot_url,data=json.dumps(data1),headers=headers) print(Lall) ding()...