廢話不多說，直接開講！
昨天接到任務：通過上傳excel表格資料，對資料進行500一組分組分割到excel表格進行匯出！說實在的，這個問題并不常見！但是感覺可以實作，于是乎今天寫博客記錄下！
好了，廢話不多說，直接看完整代碼！
# coding=utf-8
import math
import random
import time
from tkinter import filedialog, messagebox, ttk
import tkinter.messagebox
import requests
import xlrd
from tkinter.ttk import Label
from tkinter import *
import urllib3
import xlwt
from xlwt import Workbook
import threading
import tkinter as tk
urllib3.disable_warnings()
def main():
def selectExcelfile():
sfname = filedialog.askopenfilename(title='選擇Excel檔案', filetypes=[('Excel', '*.xlsx'), ('All Files', '*')])
# print(sfname)
# 傳遞引數，目前引數固定值，可以設定以下動態引數
sku = e_1.get()
num = e_2.get()
iscode = comboxlist.get()
# print(sku,num,iscode)
doProcess(sfname)
text1.insert(INSERT, sfname)
def shop_goodid(id):
url = 'https://item.jd.com/{}.html'.format(id)
headers = {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36'}
ss = requests.get(url, headers=headers).text
skuid = re.findall('colorSize:(.*?],)', ss)[0]
start = time.time()
#
time.sleep(0.1)
# 考慮性能放棄下面回圈
dd = re.findall('"skuId":(\d+),', skuid)
num = 0
for ids in dd:
num+=1
file = Workbook(encoding='utf-8')
# 指定file以utf-8的格式打開
table = file.add_sheet('data')
# 指定打開的檔案名
# 字典資料
datatime = time.strftime("%Y-%m-%d%H-%M-%S", time.localtime())
table.write(0, 0, 'sku資料')
table.write(0, 1, 'spu資料')
table.write(num, 0, ids)
table.write(num, 1, id)
print(ids,id)
# file.save('sku資料' + datatime + '.xlsx')
end = time.time() - start
# print(end)
def good_id(goodid):
try:
url = 'https://club.jd.com/comment/productCommentSummaries.action?referenceIds={}&callback=jQuery1982868&_=1592352931024'.format(
goodid)
headers = {
'Host': 'club.jd.com',
'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.12 Safari/537.36',
# 'cookie': 'shshshfpa=d1cf61f9-0c3c-a4e9-7480-e08635b84d37-1607913649; shshshfpb=yYxp3xqZQQ%20AKGOa2zG66jQ%3D%3D; unpl=V2_ZzNtbUNeFxx3DUIHcx0IUWIDEQ4SU0ccJgxAAH1LWgZmURtZclRCFnUUR1RnGV4UZwIZXEJcRxRFCEdkexhdBGYAE1pKVXMlRQtGZHopXAJmCxdVQlZFE3wMTlR8G1gFZAsUW3JnRBV8OHZUeRtUDW4HGltBVS1URQ1HUX4cXwNkBiJccldKHHYKTlVzH1s1LG0TEEJQQh1wAEZVfR9VAW8DFV9GV0Adcw52VUsa; __jdv=76161171|ajdo.ubfgho.com|t_1000020693_|tuiguang|08e8345b95ee412ba558b57e7c720c85|1610503315749; shshshfp=d2b0cf0825e71c58c4ea5772d72a8e0e; areaId=12; user-key=0ecd7836-6752-4f2a-b407-21aa747837b7; ipLoc-djd=12-978-2927-51582.3076064641; ipLocation=%u6c5f%u82cf; cn=0; logintype=wx; npin=jd_4af2b33dd6a59; TrackID=1ZdTkJ7YGTF7AvxQ8lOf9vPzhGHiFpiMxv3f4yCkxsEP3_2p1Q_g3ZVuQqsyfcYz_eDTnyQnYurQ7t9C-j-_F7iuKLcmwasMN-h3jkNA5YYI; pinId=LxgJESmodokf_AhbrnKClw; pin=jinshiyuan1123; unick=jinshiyuan1123; _tp=qTmSS0pjZ%2BpSltacjzri4w%3D%3D; _pst=jinshiyuan1123; 3AB9D23F7A4B3C9B=77ANC4CUAEAS5WLYFJ3LFTSZSIPEYNE4Y3ZS62WMGYYW42YQ7BLCPOH5UY2SNI2H4H5D7UTCIRHWPYPEBZIQGHO2KM; __jda=122270672.16110518206731258489497.1611051821.1611285111.1611305375.6; jwotest_product=99; __jdu=1608511737771113412215'
}
con = requests.get(url, headers=headers, verify=False)
# texts = re.findall('"CommentsCount":\[.*?]}',ss.text)
# print(ss.text)
CommentCount = re.findall('"CommentCount":\d+', con.text)
# time.sleep(0.1)
json = CommentCount[0].split(':')[1]
return json
except Exception:
return '暫無'
pass
def write_excel(res,s_r):
update_time = time.strftime('%Y%m%d', time.localtime(time.time()))
book = xlwt.Workbook() # 創建一個excel
sheet = book.add_sheet("采購入庫單商品匯入模板")
title = ['CLPS事業部商品編碼', '外部店鋪商品編碼','商品數量（個）','代貼條碼(是/否)','單價(全球購采購單必填)','質檢比例（大件且開通質檢服務）0-100','是否序列號入庫（是/否）','商家包裝規格編碼','包裝單位']
i = 0
for header in title:
sheet.write(0, i, header)
i += 1
# 寫入資料
num = 0
for row in res:
num+=1
sheet.write(num, 1, row)
sheet.write(num, 2, 6)
sheet.write(num, 3, '否')
# row+=1
# print(row)
book.save('采購入庫單商品匯入模板{}{}.xlsx'.format(update_time,s_r))
print("匯出成功！")
def write_excel_two(res,s_r):
update_time = time.strftime('%Y%m%d', time.localtime(time.time()))
book = xlwt.Workbook() # 創建一個excel
sheet = book.add_sheet("POP店鋪商品編號")
title = ['POP店鋪商品編號（SKU編碼）', '商家商品標識','商品條碼']
i = 0
for header in title:
sheet.write(0, i, header)
i += 1
# 寫入資料
num = 0
for row in res:
num+=1
sheet.write(num, 0, row)
sheet.write(num, 1, row)
sheet.write(num, 2,row)
# row+=1
# print(row)
book.save('POP店鋪商品編號{}{}.xlsx'.format(update_time,s_r))
print("匯出成功！")
def write_excel_three(res,s_r):
update_time = time.strftime('%Y%m%d', time.localtime(time.time()))
book = xlwt.Workbook() # 創建一個excel
sheet = book.add_sheet("事業部商品編碼")
title = ['事業部商品編碼（若此列不為空，以此編碼獲取的商品為準）', '事業部編碼（事業部商品編碼為空時必填）','商家商品編號（事業部商品編碼為空時必填）','長(mm)（必填，大于0）','寬(mm)（必填，大于0）','高(mm)（必填，大于0）','凈重(kg)','毛重(kg)（必填，大于0）']
i = 0
for header in title:
sheet.write(0, i, header)
i += 1
# 寫入資料
num = 0
for row in res:
num+=1
sheet.write(num, 2, row)
sheet.write(num, 3, 10)
sheet.write(num, 4, 10)
sheet.write(num, 5, 10)
sheet.write(num, 7, 0.01)
# row+=1
# print(row)
book.save('事業部商品編碼{}{}.xlsx'.format(update_time,s_r))
print("匯出成功！")
'''
統計總資料
'''
def group(totals=None):
print(totals)
# totals = 1601
s_r = math.ceil(totals / 500)
print(s_r)
arr_n = []
nums = 0
for n in range(1, s_r):
nums += 1
t_da = nums * 500
arr_n.append(t_da)
X = [x for x in range(1, totals)]
print(arr_n)
a = nums
while a > 0:
r = random.choice(arr_n)
# print(arr_n)
# print(r)
if X[r] != 0 and X[r - 1] != 0 and X[r + 1] != 0:
# print(r)
X.insert(r, 0)
a -= 1
z = X.index(0)
nn = 0
while z:
nn += 1
t = tuple(X[:z])
print(nn, X[:z])
X = X[z + 1:]
try:
z = X.index(0)
except ValueError:
print(s_r, X)
break
def doProcess(sfname=None):
fname = sfname
bk = xlrd.open_workbook(fname)
shxrange = range(bk.nsheets)
sh = bk.sheet_by_name("Sheet1")
# 列數
da = sh.nrows
ncols = sh.ncols
rows = sh.row_values(0)
list1 = []
# 統計總資料，分組切割
totals = da
s_r = math.ceil(totals / 500)
print(s_r)
arr_n = []
nums = 0
for n in range(1, s_r):
nums += 1
t_da = nums * 500
arr_n.append(t_da)
X = [x for x in range(1, totals)]
a = nums
while a > 0:
r = random.choice(arr_n)
if X[r] != 0 and X[r - 1] != 0 and X[r + 1] != 0:
# print(r)
X.insert(r, 0)
a -= 1
z = X.index(0)
nn = 0
while z:
nn += 1
t = tuple(X[:z])
for ii in X[:z]:
rowss = sh.row_values(ii)
goodid = int(rowss[0])
skuids = goodid
list1.append(skuids)
t1 = threading.Thread(target=write_excel, args=(list1, nn))
t2 = threading.Thread(target=write_excel_two, args=(list1,nn))
t3 = threading.Thread(target=write_excel_three, args=(list1,nn))
t1.setDaemon(True)
t1.start()
t2.setDaemon(True)
t2.start()
t3.setDaemon(True)
t3.start()
# print(nn, X[:z])
X = X[z + 1:]
try:
z = X.index(0)
except ValueError:
for ii in X:
rowss = sh.row_values(ii)
goodid = int(rowss[0])
skuids = goodid
list1.append(skuids)
t1 = threading.Thread(target=write_excel, args=(list1,s_r))
t2 = threading.Thread(target=write_excel_two, args=(list1,s_r))
t3 = threading.Thread(target=write_excel_three, args=(list1,s_r))
t1.setDaemon(True)
t1.start()
t2.setDaemon(True)
t2.start()
t3.setDaemon(True)
t3.start()
break
tkinter.messagebox.showinfo('提示', '處理Excel檔案的添加成功，')
def log2():
#
# 判斷編輯框的內容
# messagebox.showinfo(title="提示",message="取消登錄！")
if messagebox.askokcancel("確定", "取消操作！！"):
root.destroy()
def log1():
#
sku = e_1.get()
num = e_2.get()
iscode = comboxlist.get()
print(sku, num,iscode)
def go(*args): # 處理事件，*args表示可變引數
print(comboxlist.get()) # 列印選中的值
# 初始化
root = Tk()
# 設定表單標題
root.title('Excel 資料上傳')
# 設定視窗大小和位置
root.geometry('500x300+570+200')
f_1 = tk.Frame(root)
f_1.place(x=100, y=50)
# 標簽1
l_1 = tk.Label(f_1, text="SKU：")
l_1.pack()
# 標簽2
l_2 = tk.Label(f_1, text="數量：")
l_2.pack()
l_21 = tk.Label(f_1, text="代貼條碼：")
l_21.pack()
f_2 = tk.Frame(root)
f_2.place(x=170, y=50)
f_3 = tk.Frame(root)
f_3.place(x=320, y=90)
# 編輯框1
e_1 = tk.Entry(f_2, width=20)
e_1.pack()
# f_5 = tk.Frame(root)
# f_5.place(x=180, y=180)
# 編輯框2,隱藏輸入的內容
e_2 = tk.Entry(f_2, width=20)
e_2.pack()
f_0 = tk.Frame(root)
f_0.place(x=80, y=20)
label1 = tk.Label(f_0, text='請選擇檔案:')
label1.pack()
# label1 = tk.Frame(root)
# label1.place(x=470, y=50)
# text1 = Entry(root, bg='red', width=20)
# text1.pack()
text1 = tk.Frame(root)
# text1.place(x=70, y=20)
button1 = Button(root, text='瀏覽', width=8, command=lambda: thread_it(selectExcelfile))
e_3 = tk.Entry(text1, width=20)
e_3.pack()
label1.pack()
text1.pack()
button1.pack()
# label1.place(x=30, y=30)
text1.place(x=170, y=15)
button1.place(x=350, y=13)
f_3 = tk.Frame(root)
f_3.place(x=190, y=190)
# 創建登陸按鈕
button = tk.Button(f_3, text="執行",width = 10, height = 1, command=log1, bg="green")
button.pack()
f_4 = tk.Frame(root)
f_4.place(x=290, y=190)
# 創建取消按鈕
button = tk.Button(f_4, text="取消",width = 10, height = 1, command=log2, bg="red")
button.pack()
comvalue = tk.Frame(root) # 表單自帶的文本，新建一個值
f_5 = tk.Frame(root) # 表單自帶的文本，新建一個值
f_5.place(x=160,y=100)
comboxlist = ttk.Combobox(f_5, textvariable=comvalue) # 初始化
comboxlist["values"] = ("是", "否")
comboxlist.current(0) # 選擇第一個
comboxlist.bind("<<ComboboxSelected>>", go) # 系結事件,(下拉串列框被選中時，系結go()函式)
e_5 = tk.Entry(comboxlist, width=10)
comboxlist.pack()
# e_5.pack()
root.mainloop()
def thread_it(func, *args):
'''將函式打包進執行緒'''
# 創建
t = threading.Thread(target=func, args=args)
# 守護 !!!
t.setDaemon(True)
# 啟動
t.start()
# 阻塞--卡死界面！
# t.join()
if __name__ == "__main__":
main()
模板
看下結果：
看下分組分割部分代碼：
s_r = math.ceil(totals / 500)
print(s_r)
arr_n = []
nums = 0
for n in range(1, s_r):
nums += 1
t_da = nums * 500
arr_n.append(t_da)
X = [x for x in range(1, totals)]
print(arr_n)
a = nums
while a > 0:
r = random.choice(arr_n)
# print(arr_n)
# print(r)
if X[r] != 0 and X[r - 1] != 0 and X[r + 1] != 0:
# print(r)
X.insert(r, 0)
a -= 1
z = X.index(0)
nn = 0
while z:
nn += 1
t = tuple(X[:z])
print(nn, X[:z])
X = X[z + 1:]
try:
z = X.index(0)
except ValueError:
print(s_r, X)
break
歡迎下方留言！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/266676.html
標籤：python
上一篇：中年大叔學Python第7課------零基礎爬蟲爬取F1圖片
下一篇：Python批量獲取基金資料