python查询POS后台获取指定时间和状态的订单存入到excel表格中
#!/usr/bin/env python# coding=utf-8# 查询pos后台指定时间和状态的订单import requestsimport refrom lxml import etreefrom openpyxl import Workbookimport time,osfrom requests.packages import urllib3from date...
·
#!/usr/bin/env python
# coding=utf-8
# 查询pos后台指定时间和状态的订单
import requests
import re
from lxml import etree
from openpyxl import Workbook
import time,os
from requests.packages import urllib3
from datetime import datetime
class POS_crawl(object):
def __init__(self, username, password):
# 初始化headers实例
self.headers = {"User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:56.0) Gecko/20100101 Firefox/56.0",
"Referer": "https://pos.XXXX.com/login.html"
}
# 核实界面
self.post_url = "https://pos.XXXXX.com/j_spring_security_check"
# session会话对象
self.session = requests.session()
self.username = username
self.password = password
self.data = ['序号','订单号','卖家','客户','订单金额','下单时间','订单来源','订单状态','签收时间']
self.wb = Workbook()
# 去掉警告
urllib3.disable_warnings()
# 提取表单登录信息(可以和模拟登陆合并成一个)
def get_login_info(self):
data = {
"j_username": self.username,
"j_password": self.password
}
return data
# 模拟登录
def login(self):
data = self.get_login_info()
headers = {
'User-Agent': 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0);',
"Referer": self.post_url,
"X-Requested-With": "XMLHttpRequest",
}
try:
login_page = self.session.post(self.post_url, data=data, headers=headers,verify=False)
# print(login_page.text)
if "loginerror" in login_page.text:
print("登录失败,错误的手机号码或密码!")
if "</span>首页" in login_page.text:
print("欢迎您'%s',成功登陆POS管理系统!"%self.username)
self.order_list()
except Exception as e:
print(e)
# 处理订单
def order_list(self):
# 获取当天日期
file_name_date = time.strftime("%Y-%m-%d", time.localtime())
# 日期格式正则规则
patt = re.compile(r"\d{4}-\d{1,2}-\d{1,2}")
headers = {
"User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:56.0) Gecko/20100101 Firefox/56.0",
"Referer": "https://pos.XXXX.com/order/tolist.html",
}
for state in ['100','40']:
while True:
print("\n" + "=" * 75)
print("开始日期如果不输入则为2015年最早日期,假设为2015-1-1,结束日期如果不输入则为当天日期。")
print("注意:已完成订单中,如果开始和结束日期都不输入,则把签收日期为空的也会筛选出来。")
print("=" * 75 + "\n")
if state == "100":
# self.wb.sheetnames显示sheet表名字,防止输错日期循环的时候多次创建ws
if '已完成订单列表'not in self.wb.sheetnames:
ws = self.wb.active
ws.title = '已完成订单列表' # 修改第一个sheet表名为销售企业数量列表
start_date = input("请输入已完成签收的开始日期(格式:2018-11-30):")
end_date = input("请输入已完成签收的结束日期(格式:2018-11-30):")
ws.column_dimensions['I'].width = 19
else:
if '配送中订单列表'not in self.wb.sheetnames:
ws = self.wb.create_sheet('配送中订单列表',index=1) # 插入新的工作表,放在1位置
start_date = input("请输入配送中的下单开始日期(格式:2018-11-30):")
end_date = input("请输入配送中的下单结束日期(格式:2018-11-30):")
re_start_date = patt.findall(start_date)
re_end_date = patt.findall(end_date)
if start_date !='':
if len(re_start_date)!=0:
# 结束日期可以不输入
if end_date =='':
break
else:
if len(re_end_date) == 0:
print("ERROR:结束日期格式输入有误,请重新输入!")
continue
else:
break
else:
if end_date != '':
if len(re_end_date) == 0:
print("ERROR:开始和结束日期格式输入有误,请重新输入!")
continue
else:
print("ERROR:开始日期格式输入有误,请重新输入!")
continue
else:
# re_end_date = patt.findall(end_date)
if end_date != '':
if len(re_end_date) == 0:
print("ERROR:结束日期格式输入有误,请重新输入!")
continue
else:
break
else:
break
# 先写入表头数据
for head in range(1, len(self.data) + 1):
_ = ws.cell(row=1, column=head, value=self.data[head - 1])
# 我们将查询日期的区间写在第一行第10列
if end_date == '':
end_date = file_name_date
if start_date == '':
start_date = '2015-1-1'
if state == "100":
select_date = "查询签收日期:"+start_date+"至"+end_date
ws.cell(row=1, column=10, value=select_date)
else:
select_date = "查询下单日期:" + start_date + "至" + end_date
ws.cell(row=1,column=9,value=select_date)
# 冻结首行
ws.freeze_panes = 'A2'
ws.column_dimensions['A'].width = 6
ws.column_dimensions['B'].width = 10
ws.column_dimensions['C'].width = 45
ws.column_dimensions['D'].width = 33
ws.column_dimensions['E'].width = 10
ws.column_dimensions['F'].width = 19
ws.column_dimensions['G'].width = 12
self.process_product(start_date,end_date,state,ws,headers)
self.wb.save('订单数量列表'+file_name_date+'.xlsx')
address = os.path.abspath('订单数量列表'+file_name_date+'.xlsx')
print("数据处理完毕,表格已经保存到该路径下:%s"%address)
self.session.post('https://pos.XXXX.com/j_spring_security_logout',headers=headers,verify=False)
print("退出后台系统....")
# 获取数据
def process_product(self,start_date,end_date,state,ws,headers):
formdata = {
# "d-5481-p": "1",
"to.buyer": "",
"to.endDate": end_date,
"to.id": "",
# 订单状态,-1代表全部
"to.orderSource": "-1",
"to.seller": "",
"to.startDate": start_date,
# 已完成是100,配送中是40
"to.status": state,
}
time.sleep(1)
# headers = {
# "User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:56.0) Gecko/20100101 Firefox/56.0",
# "Referer": "https://pos.XXXXX.com/order/tolist.html",
# }
get_page = self.session.post("https://pos.XXXXX.com/order/tolist.html", data=formdata, headers=headers,verify=False)
# 利用xpath规则提取查询结果信息
get_page_html = etree.HTML(get_page.text)
# 获取查询结果的数据,计算有多少页,是一个列表
page_list = get_page_html.xpath('//*[@id="signForm"]/span[1]/text()')
# with open("557.html", "w", encoding="utf-8")as f:
# f.write(get_page.text)
if len(page_list) ==0:
print("抱歉,没有查询到相应条件的商品!")
else:
# 如果只有一条数据会显示One
if page_list[0].split(" ")[0] != "One":
total = int(page_list[0].split(" ")[0].replace(",",""))
else:
total = 1
if total % 20 == 0:
end_page = total // 20
print('共获取到%d项订单数据,合计%d页' % (total, end_page))
else:
end_page = total // 20 + 1
print('共获取到%d项订单数据,合计%d页' % (total, end_page))
for page in range(1,end_page+1):
product_list = []
order_list_url = "https://pos.XXXX.com/order/tolist.html?d-5481-p="+str(page)+"&to.status=" + state + "&to.orderSource=-1&to.buyer=&to.startDate=" + start_date + "&to.id=&to.seller=&to.endDate=" + end_date
order_list = self.session.post(order_list_url,headers=headers, verify=False)
get_order_html = etree.HTML(order_list.text)
# 获取序号
number_ = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[1]/text()')
number_result = []
[number_result.append(int(number)) for number in number_]
# 获取订单号
order_number_ = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[2]/a/text()')
order_number_result = []
[order_number_result.append(int(order_number)) for order_number in order_number_]
# 获取卖家
order_company_ = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[3]/a/text()')
order_company_result = []
[order_company_result.append(order_company.replace("\n", "").replace("\t", "").replace(" ", "").strip()) for
order_company in order_company_]
# 获取客户
order_client_ = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[4]/text()')
order_client_result =[]
[order_client_result.append(order_client.replace("\n", "").replace("\t", "")) for order_client in
order_client_]
# 获取订单金额
order_money_ = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[5]/text()')
order_money_result = []
[order_money_result.append(float(order_money)) for order_money in order_money_]
# 获取下单时间,只有这个首尾没有空格
order_time = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[6]/text()')
# 订单来源
order_source_ = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[10]/text()')
order_source_result = []
[order_source_result.append(order_source.replace("\n", "").replace("\t", "")) for order_source in
order_source_]
# 订单状态
order_state_ = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[11]/text()')
order_state_result = []
[order_state_result.append(order_state.replace("\n", "").replace("\t", "").replace(" ","")) for order_state in
order_state_]
product_list.append(number_result)
product_list.append(order_number_result)
product_list.append(order_company_result)
product_list.append(order_client_result)
product_list.append(order_money_result)
product_list.append(order_time)
product_list.append(order_source_result)
product_list.append(order_state_result)
# 存储签收时间,首尾无空格
sign_time_result = []
# 这样获取的是xpath对象,没加/text是因为这样也能获取到空的值
sign_time_element = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[7]')
[sign_time_result.append(sign_time.text) for sign_time in sign_time_element]
product_list.append(sign_time_result)
# 已完成才有签收时间
# if state == '100':
# sign_time = get_order_html.xpath('//*[@id="to"]/tbody/tr/td[7]/text()')
# product_list.append(sign_time)
# else:
# sign_time = ("*"*(len(number)-1)).split("*")
# product_list.append(sign_time)
row_start = [int(k) for k in number_][0]+1
row_end = [int(k) for k in number_][-1]+1
print("第%d页数据写入表格中...."%page)
for col in range(1, len(product_list)+1):
for row in range(row_start, row_end+1):
# print("写入第%d行第%d列数据..." % (row, col))
_ = ws.cell(row=row, column=col, value=(product_list[col - 1][row-2-(page-1)*20]))
print("此项数据处理完成。")
if __name__ == "__main__":
username = input("请输入登录账号:")
password = input("请输入登录密码:")
az = POS_crawl(username, password)
az.login()
input("请按回车键退出程序。。")
更多推荐
所有评论(0)