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)