def write2Excel(jsondata, title): # 根据不同的商圈地点写入数据，每个商圈地点占用excel 的一个sheet
fileName = excelName()
isexit = "false"
if (os.path.exists(fileName)):
wb = load_workbook(fileName)
isexit = "true"
else:
wb = Workbook()
if (wb.__contains__(title)):
ws = wb[title]
ws.append([])
else:
ws = wb.create_sheet(title)
ws.column_dimensions["A"].width = 10.0
ws.column_dimensions["B"].width = 40.0
ws.column_dimensions["C"].width = 60.0
ws.column_dimensions["D"].width = 10.0
ws.column_dimensions["E"].width = 18.0
ws.column_dimensions["F"].width = 10.0
ws.append(["ID", "店名", "地址", "距离", "人均消费", "月销售额"])
for i in range(len(jsondata)):
row = jsondata[i]
# print(type(row))
if ("average_cost" not in row.keys()):
row["average_cost"] = "无人均消费数据"
ws.append([
row["id"], row["name"], row["address"], row["distance"],
row["average_cost"], row["recent_order_num"]
])
wb.save(fileName)
def write2Excel(jsondata, title):
fileName = excelName(title)
if (os.path.exists(fileName)):
wb = load_workbook(fileName)
else:
wb = Workbook()
if (wb.__contains__(title)):
ws = wb[title]
# ws.append([])
else:
ws = wb.create_sheet(title)
ws.column_dimensions["A"].width = 15.0
ws.column_dimensions["B"].width = 35.0
ws.column_dimensions["C"].width = 15.0
ws.column_dimensions["D"].width = 15.0
ws.column_dimensions["E"].width = 15.0
ws.column_dimensions["F"].width = 15.0
ws.column_dimensions["G"].width = 10.0
ws.column_dimensions["G"].width = 10.0
ws.append(["店铺ID", "店铺名称", "起送价", "配送费", "人均消费", "平均送达速度", "评分", "距离"])
for i in range(len(jsondata)):
row = jsondata[i]
print(row)
ws.append([
row["mtWmPoiId"], row["shopName"], row["minPriceTip"],
row["shippingFeeTip"], row["averagePriceTip"],
row["deliveryTimeTip"], row["wmPoiScore"] / 10, row["distance"]
])
wb.save(fileName)
def write2Excel(jsondata, title):
fileName = excelName(title)
if (os.path.exists(fileName)):
wb = load_workbook(fileName)
else:
wb = Workbook()
if (wb.__contains__(title)):
ws = wb[title]
# ws.append([])
else:
ws = wb.create_sheet(title)
ws.column_dimensions["A"].width = 25.0
ws.column_dimensions["B"].width = 10.0
ws.column_dimensions["C"].width = 10.0
ws.column_dimensions["D"].width = 15.0
ws.column_dimensions["E"].width = 15.0
ws.column_dimensions["F"].width = 10.0
ws.column_dimensions["G"].width = 15.0
ws.column_dimensions["H"].width = 10.0
ws.column_dimensions["I"].width = 10.0
ws.column_dimensions["J"].width = 10.0
ws.column_dimensions["K"].width = 10.0
ws.append([
"店铺名称", "菜品分类数", "SKU数量", "配送服务", "营业时间", "类别", "菜品名称", "月销售",
"点赞", "产品标格", "实付价格"
])
for menus in (jsondata["categoryList"]):
name = menus['categoryName']
if name == '热销':
food = menus['spuList']
for i in range(len(food)):
foods = food[i]
if i == 0:
count = 0
for i in range(1, len(jsondata["categoryList"])):
count = count + len(
jsondata["categoryList"][i]["spuList"])
ws.append([
jsondata["shopInfo"]["shopName"],
len(jsondata["categoryList"]) - 1, count,
jsondata["shopInfo"]["deliveryMsg"],
jsondata["shopInfo"]["shippingTime"], name,
foods["spuName"], foods["sellStatus"],
foods["praiseNum"], foods["originPrice"],
foods["currentPrice"]
])
else:
ws.append([
"", "", "", "", "", name, foods["spuName"],
foods["sellStatus"], foods["praiseNum"],
foods["originPrice"], foods["currentPrice"]
])
wb.save(fileName)
def write2Excel(jsondata, title): # 根据不同的商圈地点写入数据，每个商圈地点占用excel 的一个sheet
fileName = excelName(title)
if (os.path.exists(fileName)):
wb = load_workbook(fileName)
else:
wb = Workbook()
if (wb.__contains__(title)):
ws = wb[title]
# ws.append([])
else:
ws = wb.create_sheet(title)
ws.column_dimensions["A"].width = 10.0
ws.column_dimensions["B"].width = 10.0
ws.column_dimensions["C"].width = 10.0
ws.column_dimensions["D"].width = 30.0
ws.column_dimensions["E"].width = 10.0
ws.column_dimensions["F"].width = 10.0
ws.column_dimensions["G"].width = 10.0
ws.column_dimensions["H"].width = 10.0
ws.column_dimensions["I"].width = 20.0
ws.column_dimensions["J"].width = 10.0
ws.column_dimensions["K"].width = 10.0
ws.column_dimensions["L"].width = 10.0
ws.column_dimensions["M"].width = 30.0
ws.column_dimensions["N"].width = 10.0
ws.column_dimensions["O"].width = 40.0
ws.column_dimensions["P"].width = 10.0
ws.append([
"店铺ID", "纬度", "经度", "店铺名称", "月销量", "起送价", "配送费", "平均送达速度", "营业时长",
"评分", "评价数", "活动数量", "满减活动", "是否新开店", "店铺地址", "距离"
])
for i in range(len(jsondata)):
attribute = ""
row = jsondata[i]['restaurant']
# print(type(row))
# print(row)
for activitie in row["activities"]:
if "type" in activitie and activitie['type'] is 102:
attribute = attribute + activitie["tips"]
pass
ws.append([
row["id"], row["latitude"], row["longitude"], row["name"],
row["recent_order_num"], row["float_minimum_order_amount"],
row["float_delivery_fee"], row["order_lead_time"],
str(row["opening_hours"]), row["rating"], row["rating_count"],
len(row["activities"]),
str(attribute), row["is_new"], row["address"], row["distance"]
])
wb.save(fileName)
def write2Excel(jsondata, title):
fileName = excelName(title)
if (os.path.exists(fileName)):
wb = load_workbook(fileName)
else:
wb = Workbook()
if (wb.__contains__(title)):
ws = wb[title]
# ws.append([])
else:
ws = wb.create_sheet(title)
ws.column_dimensions["A"].width = 30.0
ws.column_dimensions["B"].width = 10.0
ws.column_dimensions["C"].width = 10.0
ws.column_dimensions["D"].width = 10.0
ws.column_dimensions["E"].width = 30.0
ws.column_dimensions["F"].width = 10.0
ws.column_dimensions["G"].width = 10.0
ws.column_dimensions["H"].width = 10.0
ws.append(["店铺名称", "菜品分类数", "SKU", "类别", "菜品名称", "月销售", "好评率", "价格"])
for menus in (jsondata["menu"]):
name = menus['name']
if name == '热销':
food = menus['foods']
for i in range(len(food)):
foods = food[i]
if i == 0:
count = 0
for i in range(2, len(jsondata["menu"])):
count = count + len(jsondata["menu"][i]["foods"])
ws.append([
jsondata["rst"]["name"],
len(jsondata["menu"]) - 2, count, "热销", foods["name"],
foods["month_sales"],
str(foods["satisfy_rate"]) + "%",
foods["specfoods"][0]["price"]
])
else:
ws.append([
jsondata["rst"]["name"], "", "", "热销", foods["name"],
foods["month_sales"],
str(foods["satisfy_rate"]) + "%",
foods["specfoods"][0]["price"]
])
wb.save(fileName)
