1、重新修改格式
<pre><code class="language-python">from bs4 import BeautifulSoup
import urllib.request
# import pandas as pd
import ssl
import time
import random
import xlsxwriter
import re
import json
import os
import pickle
import socket
import sys</code></pre>
<pre><code class="language-python">def get_all_list():
men_all = ['men_all_shoes', 'men_all_bags', 'men_all_belts', 'men_all_clothes', 'men_all_wallets']
women_all = ['women_all_shoes', 'women_all_bags', 'women_all_belts', 'women_all_clothes', 'women_all_wallets']
return men_all, women_all</code></pre>
<pre><code class="language-python">kaola_list = pickle.load(open('./gucci/' + 'women_all_shoes' + '/kaola_list.txt', 'rb'))</code></pre>
<pre><code class="language-python"></code></pre>
<pre><code class="language-python">def it_write_excel(download_type, book):
# '''
# 读取变量
it_goods_list = pickle.load(open('./gucci/' + download_type + '/it_goods_list.txt', 'rb'))
sheet = book.add_worksheet(download_type)
# 设置sheet表单元格列宽
sheet.set_column("A:A", 5) # 欧洲
sheet.set_column("B:B", 80.88) # 商品名称
sheet.set_column("C:C", 10.5) # 正面图
sheet.set_column("D:D", 19.38) # 货号
sheet.set_column("E:E", 12) # 欧洲零售价
sheet.set_column("F:F", 82) # 商品链接
# 设定整个sheet表的单元格的格式
property = {
'font_size': 11, # 字体大小
'bold': False, # 是否加粗
'align': 'center', # 水平对齐方式 left
'valign': 'vcenter', # 垂直对齐方式
'font_name': u'微软雅黑',
'text_wrap': False, # 是否自动换行
}
cell_format = book.add_format(property)
# 设置sheet表单元格行高
sheet.set_row(0, 22) # 设置第一行的高度为22
# 在向单元格中写入内容时，加上单元格样式
# 插入第一行
sheet.write(0, 0, '欧洲', cell_format)
sheet.write(0, 1, '商品名称', cell_format)
sheet.write(0, 2, '正面图', cell_format)
sheet.write(0, 3, '货号', cell_format)
sheet.write(0, 4, '欧洲零售价', cell_format)
sheet.write(0, 5, '商品链接', cell_format)
img_format = {'x_offset': 4, # 左右移动
'y_offset': 0,
'x_scale': 0.2, # 缩放比例
'y_scale': 0.19}
# 插入爬取it_goods_list信息
row_number = 1
for product in it_goods_list:
sheet.set_row(row_number, 52) # 设置第row_number行的高度为52
sheet.write(row_number, 1, product['product_title'], cell_format)
sheet.insert_image(row_number, 2, product['url_path'], img_format)
sheet.write(row_number, 3, product['productCode'], cell_format) # product['productCode']
sheet.write(row_number, 4, '€' + str(product['product_prices']), cell_format)
sheet.write(row_number, 5, product['product_url'], cell_format)
row_number = row_number + 1
# book.close()
print(download_type + ': 写入EXCEL成功')
return book</code></pre>
<pre><code class="language-python">def it_write_excel_to_path(men_all, women_all, excel_name):
book = xlsxwriter.Workbook(excel_name)
for i in range(0, len(men_all)):
print(men_all[i])
book = it_write_excel(men_all[i], book)
for i in range(0, len(women_all)):
print(men_all[i])
book = it_write_excel(women_all[i], book)
book.close()
print(excel_name + ': 写入EXCEL成功')
return None</code></pre>
<pre><code class="language-python">def get_all_list():
men_all = ['men_all_shoes', 'men_all_bags', 'men_all_belts', 'men_all_clothes', 'men_all_wallets']
women_all = ['women_all_shoes', 'women_all_bags', 'women_all_belts', 'women_all_clothes', 'women_all_wallets']
return men_all, women_all</code></pre>
<pre><code class="language-python">men_all, women_all = get_all_list()</code></pre>
<pre><code class="language-python">it_write_excel_to_path(men_all, women_all, 'it_excel.xlsx')</code></pre>
<pre><code>men_all_shoes
men_all_shoes: 写入EXCEL成功
men_all_bags
men_all_bags: 写入EXCEL成功
men_all_belts
men_all_belts: 写入EXCEL成功
men_all_clothes
men_all_clothes: 写入EXCEL成功
men_all_wallets
men_all_wallets: 写入EXCEL成功
men_all_shoes
women_all_shoes: 写入EXCEL成功
men_all_bags
women_all_bags: 写入EXCEL成功
men_all_belts
women_all_belts: 写入EXCEL成功
men_all_clothes
women_all_clothes: 写入EXCEL成功
men_all_wallets
women_all_wallets: 写入EXCEL成功
it_excel.xlsx: 写入EXCEL成功</code></pre>
<pre><code class="language-python">len(kaola_list)</code></pre>
<pre><code>15</code></pre>
<pre><code class="language-python">all_number = 0
for i in range(0,5):
kaola_list = pickle.load(open('./gucci/' + men_all[i] + '/kaola_list.txt', 'rb'))
all_number = all_number + len(kaola_list)
print(men_all[i]+ ' ' + str(len(kaola_list)))
for i in range(0,5):
kaola_list = pickle.load(open('./gucci/' + women_all[i] + '/kaola_list.txt', 'rb'))
all_number = all_number + len(kaola_list)
print(women_all[i]+ ' ' +str(len(kaola_list)))
print(all_number)</code></pre>
<pre><code>men_all_shoes 91
men_all_bags 105
men_all_belts 91
men_all_clothes 65
men_all_wallets 68
women_all_shoes 15
women_all_bags 148
women_all_belts 24
women_all_clothes 17
women_all_wallets 109
733</code></pre>
<pre><code class="language-python">def zh_write_excel(download_type, book):
# '''
# 读取变量
zh_goods_list = pickle.load(open('./gucci/' + download_type + '/zh_goods_list.txt', 'rb'))
sheet = book.add_worksheet(download_type)
# 设置sheet表单元格列宽
sheet.set_column("A:A", 5) # 中国
sheet.set_column("B:B", 55) # 商品名称
sheet.set_column("C:C", 10.5) # 正面图
sheet.set_column("D:D", 19.38) # 货号
sheet.set_column("E:E", 8.25) # 欧洲零售价
sheet.set_column("F:F", 106) # 商品链接
# 设定整个sheet表的单元格的格式
property = {
'font_size': 11, # 字体大小
'bold': False, # 是否加粗
'align': 'center', # 水平对齐方式 left
'valign': 'vcenter', # 垂直对齐方式
'font_name': u'微软雅黑',
'text_wrap': False, # 是否自动换行
}
cell_format = book.add_format(property)
# 设置sheet表单元格行高
sheet.set_row(0, 22) # 设置第一行的高度为22
# 在向单元格中写入内容时，加上单元格样式
# 插入第一行
sheet.write(0, 0, '中国', cell_format)
sheet.write(0, 1, '商品名称', cell_format)
sheet.write(0, 2, '正面图', cell_format)
sheet.write(0, 3, '货号', cell_format)
sheet.write(0, 4, '中国零售价', cell_format)
sheet.write(0, 5, '商品链接', cell_format)
gucci_zh_img_format = {'x_offset': 4, # 左右移动
'y_offset': 0,
'x_scale': 0.3, # 缩放比例
'y_scale': 0.29}
img_format = {'x_offset': 4, # 左右移动
'y_offset': 0,
'x_scale': 0.2, # 缩放比例
'y_scale': 0.19}
# 插入爬取zh_goods_list信息
row_number = 1
for product in zh_goods_list:
sheet.set_row(row_number, 52) # 设置第row_number行的高度为52
sheet.write(row_number, 1, product['product_title'], cell_format)
sheet.insert_image(row_number, 2, product['url_path'], gucci_zh_img_format)
sheet.write(row_number, 3, product['productCode'], cell_format) # product['productCode']
sheet.write(row_number, 4, '¥' + str(product['product_prices']), cell_format)
sheet.write(row_number, 5, product['product_url'], cell_format)
row_number = row_number + 1
# book.close()
print(download_type + ': 写入EXCEL成功')
return book
</code></pre>
<pre><code class="language-python">def zh_write_excel_to_path(men_all, women_all, excel_name):
book = xlsxwriter.Workbook(excel_name)
for i in range(0, len(men_all)):
print(men_all[i])
book = zh_write_excel(men_all[i], book)
for i in range(0, len(women_all)):
print(men_all[i])
book = zh_write_excel(women_all[i], book)
book.close()
print(excel_name + ': 写入EXCEL成功')
return None</code></pre>
<pre><code class="language-python">zh_write_excel_to_path(men_all, women_all, 'zh_excel.xlsx')</code></pre>
<pre><code>men_all_shoes
men_all_shoes: 写入EXCEL成功
men_all_bags
men_all_bags: 写入EXCEL成功
men_all_belts
men_all_belts: 写入EXCEL成功
men_all_clothes
men_all_clothes: 写入EXCEL成功
men_all_wallets
men_all_wallets: 写入EXCEL成功
men_all_shoes
women_all_shoes: 写入EXCEL成功
men_all_bags
women_all_bags: 写入EXCEL成功
men_all_belts
women_all_belts: 写入EXCEL成功
men_all_clothes
women_all_clothes: 写入EXCEL成功
men_all_wallets
women_all_wallets: 写入EXCEL成功
zh_excel.xlsx: 写入EXCEL成功</code></pre>
<pre><code class="language-python">all_number = 0
for i in range(0,5):
kaola_list = pickle.load(open('./gucci/' + men_all[i] + '/zh_goods_list.txt', 'rb'))
all_number = all_number + len(kaola_list)
print(men_all[i]+ ' ' + str(len(kaola_list)))
for i in range(0,5):
kaola_list = pickle.load(open('./gucci/' + women_all[i] + '/zh_goods_list.txt', 'rb'))
all_number = all_number + len(kaola_list)
print(women_all[i]+ ' ' +str(len(kaola_list)))
print(all_number)</code></pre>
<pre><code>men_all_shoes 106
men_all_bags 107
men_all_belts 36
men_all_clothes 217
men_all_wallets 93
women_all_shoes 139
women_all_bags 202
women_all_belts 37
women_all_clothes 166
women_all_wallets 124
1227</code></pre>
<pre><code class="language-python">def kaola_write_excel(download_type, book):
# '''
# 读取变量
kaola_list = pickle.load(open('./gucci/' + download_type + '/kaola_list.txt', 'rb'))
sheet = book.add_worksheet(download_type)
# 设置sheet表单元格列宽
sheet.set_column("A:A", 5) # 考拉
sheet.set_column("B:B", 89) # 商品名称
sheet.set_column("C:C", 11.5) # 正面图
sheet.set_column("D:D", 50.38) # 商品链接
sheet.set_column("E:E", 14.25) # 到手价
sheet.set_column("F:F", 25.75) # 所有售价
sheet.set_column("G:G", 25.75) # 备选颜色
sheet.set_column("H:H", 28.75) # 销售商家
# 设定整个sheet表的单元格的格式
property = {
'font_size': 11, # 字体大小
'bold': False, # 是否加粗
'align': 'center', # 水平对齐方式 left
'valign': 'vcenter', # 垂直对齐方式
'font_name': u'微软雅黑',
'text_wrap': False, # 是否自动换行
}
cell_format = book.add_format(property)
# 设置sheet表单元格行高
sheet.set_row(0, 22) # 设置第一行的高度为22
# 在向单元格中写入内容时，加上单元格样式
# 插入第一行
sheet.write(0, 0, '考拉', cell_format)
sheet.write(0, 1, '商品名称', cell_format)
sheet.write(0, 2, '正面图', cell_format)
sheet.write(0, 3, '商品链接', cell_format)
sheet.write(0, 4, '到手价', cell_format)
sheet.write(0, 5, '所有售价', cell_format)
sheet.write(0, 6, '备选颜色', cell_format)
sheet.write(0, 7, '销售商家', cell_format)
gucci_zh_img_format = {'x_offset': 4, # 左右移动
'y_offset': 0,
'x_scale': 0.3, # 缩放比例
'y_scale': 0.29}
img_format = {'x_offset': 4, # 左右移动
'y_offset': 0,
'x_scale': 0.2, # 缩放比例
'y_scale': 0.19}
# 插入爬取kaola_list信息
row_number = 1
for product in kaola_list:
sheet.set_row(row_number, 52) # 设置第row_number行的高度为52
sheet.write(row_number, 1, product['product_title'], cell_format)
sheet.insert_image(row_number, 2, product['url_path'], img_format)
sheet.write(row_number, 3, product['product_url'], cell_format)
sheet.write(row_number, 4, '¥' + str(product['product_prices']), cell_format)
sheet.write(row_number, 5, product['product_all_prices'], cell_format)
sheet.write(row_number, 6, product['product_other_color'], cell_format)
sheet.write(row_number, 7, product['product_sales_name'], cell_format)
row_number = row_number + 1
# book.close()
print(download_type + ': 写入EXCEL成功')
return book
</code></pre>
<pre><code class="language-python">def kaola_write_excel_to_path(men_all, women_all, excel_name):
book = xlsxwriter.Workbook(excel_name)
for i in range(0, len(men_all)):
print(men_all[i])
book = kaola_write_excel(men_all[i], book)
for i in range(0, len(women_all)):
print(men_all[i])
book = kaola_write_excel(women_all[i], book)
book.close()
print(excel_name + ': 写入EXCEL成功')
return None</code></pre>
<pre><code class="language-python">kaola_write_excel_to_path(men_all, women_all, 'kaola_excel.xlsx')</code></pre>
<pre><code>men_all_shoes
men_all_shoes: 写入EXCEL成功
men_all_bags
men_all_bags: 写入EXCEL成功
men_all_belts
men_all_belts: 写入EXCEL成功
men_all_clothes
men_all_clothes: 写入EXCEL成功
men_all_wallets
men_all_wallets: 写入EXCEL成功
men_all_shoes
women_all_shoes: 写入EXCEL成功
men_all_bags
women_all_bags: 写入EXCEL成功
men_all_belts
women_all_belts: 写入EXCEL成功
men_all_clothes
women_all_clothes: 写入EXCEL成功
men_all_wallets
women_all_wallets: 写入EXCEL成功
kaola_excel.xlsx: 写入EXCEL成功</code></pre>
<pre><code class="language-python">kaola_list</code></pre>
<pre><code>[{'product_all_prices': ' ¥3490 7折 ¥4990 ',
'product_img_url': 'http://pop.nosdn.127.net/1667d0a6-cb11-4d88-8e19-1fa348663720?imageView&thumbnail=262x262&quality=90',
'product_other_color': '粉色边427110 KU230 8973,红色拼色-427110 K0L80 8960,',
'product_prices': 3490,
'product_sales_name': '美欧汇奢海外奢侈品专营店',
'product_title': '【碎花经典】GUCCI古驰 女士新款GG高级人造革印花捆绳鞋底套穿式休闲鞋',
'product_url': 'https://goods.kaola.com/product/2787032.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/12.jpg'},
{'product_all_prices': ' ¥3770 7.6折 ¥4990 ',
'product_img_url': 'http://pop.nosdn.127.net/ad532a39-5c57-4903-93f2-57af12eb6bfe?imageView&thumbnail=262x262&quality=90',
'product_other_color': '棕色505917 9N010 8460,',
'product_prices': 3770,
'product_sales_name': '美欧汇奢海外奢侈品专营店',
'product_title': 'GUCCI 古驰 女士皮革边饰金色蜜蜂印花涂层帆布麻底平底鞋',
'product_url': 'https://goods.kaola.com/product/2787690.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/8.jpg'},
{'product_all_prices': ' ¥3799 7.6折 ¥4980 ',
'product_img_url': 'http://pop.nosdn.127.net/ae2b2f7f-3103-4557-b1d6-2cd41825ae80?imageView&thumbnail=262x262&quality=90',
'product_other_color': '黑色454703 CWC10 1000,',
'product_prices': 3799,
'product_sales_name': '美欧汇奢海外奢侈品专营店',
'product_title': '【经典GG印花】GUCCI古驰 女鞋压花真皮草编鞋牛皮休闲平底鞋',
'product_url': 'https://goods.kaola.com/product/2787627.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/6.jpg'},
{'product_all_prices': ' ¥4399 7.6折 ¥5800 ',
'product_img_url': 'http://pop.nosdn.127.net/b925f891-7f3d-48ff-aff5-d313a8f4916f?imageView&thumbnail=262x262&quality=90',
'product_other_color': '白色,黑色,粉色,暗橙色,',
'product_prices': 4399,
'product_sales_name': '顶秀奢侈品专营店',
'product_title': '【国内现货，支持7天退换】GUCCI 古驰女士羊皮粉色马衔扣乐福鞋英伦女鞋',
'product_url': 'https://goods.kaola.com/product/2302846.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/2.jpg'},
{'product_all_prices': ' ¥4590 7.8折 ¥5900 ',
'product_img_url': 'http://pop.nosdn.127.net/6e4ba303-d546-42ec-9f63-09be0ccf73d5?imageView&thumbnail=262x262&quality=90',
'product_other_color': '红色,黑色,白/花,',
'product_prices': 4590,
'product_sales_name': '欧瑟奢侈品旗舰店',
'product_title': 'GUCCI古驰古奇 新品女款凉鞋453436 H5QH0 8465',
'product_url': 'https://goods.kaola.com/product/2544312.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/11.jpg'},
{'product_all_prices': ' ¥4639 7.1折 ¥6500 ',
'product_img_url': 'http://pop.nosdn.127.net/c0961380-bc2d-41d4-ba44-90bd62e9fd48?imageView&thumbnail=262x262&quality=90',
'product_other_color': '红色,黑色,',
'product_prices': 4639,
'product_sales_name': '第五时尚海外专营店',
'product_title': '【火拼底价】【爆款】【臻品】GUCCI 古驰 女款经典皮革软跟马衔扣船鞋(皮质柔软) 欧码 盒装',
'product_url': 'https://goods.kaola.com/product/2827524.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/14.jpg'},
{'product_all_prices': ' ¥5199 会员价 ¥4399 ',
'product_img_url': 'http://haitao.nosdn1.127.net/1bnfc5c0081_800_800.jpg?imageView&thumbnail=262x262&quality=90',
'product_other_color': '黑色,',
'product_prices': 5199,
'product_sales_name': '网易考拉自营',
'product_title': '【领券立减720】GUCCI 古驰 女士鞋 404069-BLM00-1000',
'product_url': 'https://goods.kaola.com/product/1666946.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/1.jpg'},
{'product_all_prices': ' ¥6399 6.8折 ¥9400 ',
'product_img_url': 'http://pop.nosdn.127.net/219f3413-0ae3-4305-8824-2d121cff494b?imageView&thumbnail=262x262&quality=90',
'product_other_color': '黑色1061,',
'product_prices': 6399,
'product_sales_name': '顶秀奢侈品专营店',
'product_title': 'GUCCI 古驰 黑色牛皮时尚女士粗跟鞋 QCJ',
'product_url': 'https://goods.kaola.com/product/2629210.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/4.jpg'},
{'product_all_prices': ' ¥6699 7.1折 ¥9400 ',
'product_img_url': 'http://haitao.nosdn2.127.net/c8eef436ef11449b91921acfb945a5f71510214441632j9s6ooz511383.jpg?imageView&thumbnail=262x262&quality=90',
'product_other_color': '黑色,',
'product_prices': 6699,
'product_sales_name': '网易考拉自营',
'product_title': 'GUCCI 古驰 女士经典双G方头时尚珍珠镶嵌高跟皮鞋',
'product_url': 'https://goods.kaola.com/product/1823062.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/7.jpg'},
{'product_all_prices': ' ¥6799 7.2折 ¥9400 ',
'product_img_url': 'http://pop.nosdn.127.net/8a99dd62-6630-4a95-b6d3-63130d4e1c92?imageView&thumbnail=262x262&quality=90',
'product_other_color': '米白色,黑色,',
'product_prices': 6799,
'product_sales_name': '顶秀奢侈品专营店',
'product_title': '【国内现货，支持7天退换】GUCCI 古驰 女士米白色牛皮尖头竹节高跟鞋凉鞋',
'product_url': 'https://goods.kaola.com/product/2108222.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/5.jpg'},
{'product_all_prices': ' ¥6899 7.5折 ¥9200 ',
'product_img_url': 'http://haitao.nos.netease.com/d3fdee175a1e44b995f718b0442408d01541055246108jny8i61211746.jpg?imageView&thumbnail=262x262&quality=90',
'product_other_color': '棕色,',
'product_prices': 6899,
'product_sales_name': '网易考拉自营',
'product_title': 'GUCCI 古驰 女士双G标志绒面流苏中跟及踝靴',
'product_url': 'https://goods.kaola.com/product/2662772.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/3.jpg'},
{'product_all_prices': ' ¥6999 7.6折 ¥9200 ',
'product_img_url': 'http://haitao.nosdn1.127.net/3d9199b09a804cf9b598b4a4bd3bdca81523879072773jg2695o310156.jpg?imageView&thumbnail=262x262&quality=90',
'product_other_color': '黑色,',
'product_prices': 6999,
'product_sales_name': '网易考拉自营',
'product_title': '【领券立减960】【张俪同款】GUCCI 古驰 女士皮革织带细节珍珠装饰方头中跟皮鞋乐福鞋',
'product_url': 'https://goods.kaola.com/product/2086447.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/0.jpg'},
{'product_all_prices': ' ¥7690 8.6折 ¥8900 ',
'product_img_url': 'http://pop.nosdn.127.net/a4ce3bd3-fc40-4ebb-939e-0dde0044c985?imageView&thumbnail=262x262&quality=90',
'product_other_color': '粉红色,黑色,',
'product_prices': 7690,
'product_sales_name': '欧瑟奢侈品旗舰店',
'product_title': 'GUCCI古驰 新品女款古驰高跟鞋452766 C9DN0',
'product_url': 'https://goods.kaola.com/product/2541726.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/10.jpg'},
{'product_all_prices': ' ¥8878 9.4折 ¥9400 ',
'product_img_url': 'http://pop.nosdn.127.net/7e16beba-07f0-4cc9-88a1-eaaee24efcd1?imageView&thumbnail=262x262&quality=90',
'product_other_color': '黑色,',
'product_prices': 8878,
'product_sales_name': 'COSCIA时尚精品百货',
'product_title': 'GUCCI 古驰女士双G织带羊皮革粗跟高跟鞋 425943CQXM0',
'product_url': 'https://goods.kaola.com/product/2245454.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/13.jpg'},
{'product_all_prices': ' ¥8990 9.2折 ¥9750 ',
'product_img_url': 'http://pop.nosdn.127.net/d36d5770-b243-4635-b460-5b5e1bdf3de5?imageView&thumbnail=262x262&quality=90',
'product_other_color': '粉色,黑色,',
'product_prices': 8990,
'product_sales_name': '欧瑟奢侈品旗舰店',
'product_title': 'GUCCI古驰 新款凉鞋古奇酷奇女鞋樱桃鞋平底鞋453496 C9DN0 1170',
'product_url': 'https://goods.kaola.com/product/2544378.html',
'url_path': './gucci/women_all_shoes/kaola_zh_/9.jpg'}]</code></pre>
<pre><code class="language-python">kaola_list = pickle.load(open('./gucci/' + 'women_all_shoes' + '/kaola_list.txt', 'rb'))</code></pre>
<pre><code class="language-python">x = "I like to program in Python"
print (x.count("i"))</code></pre>
<pre><code>2</code></pre>
<pre><code class="language-python">x = "粉色,黑色,"
print (x.count(","))</code></pre>
<pre><code>2</code></pre>
<pre><code class="language-python">def kaola_write_excel(download_type, book):
# '''
# 读取变量
kaola_list = pickle.load(open('./gucci/' + download_type + '/kaola_list.txt', 'rb'))
sheet = book.add_worksheet(download_type)
# 设置sheet表单元格列宽
sheet.set_column("A:A", 5) # 考拉
sheet.set_column("B:B", 89) # 商品名称
sheet.set_column("C:C", 11.5) # 正面图
sheet.set_column("D:D", 50.38) # 商品链接
sheet.set_column("E:E", 14.25) # 到手价
sheet.set_column("F:F", 25.75) # 所有售价
sheet.set_column("G:G", 25.75) # 备选颜色
sheet.set_column("H:H", 28.75) # 颜色数量
sheet.set_column("I:I", 28.75) # 销售商家
# 设定整个sheet表的单元格的格式
property = {
'font_size': 11, # 字体大小
'bold': False, # 是否加粗
'align': 'center', # 水平对齐方式 left
'valign': 'vcenter', # 垂直对齐方式
'font_name': u'微软雅黑',
'text_wrap': False, # 是否自动换行
}
cell_format = book.add_format(property)
# 设置sheet表单元格行高
sheet.set_row(0, 22) # 设置第一行的高度为22
# 在向单元格中写入内容时，加上单元格样式
# 插入第一行
sheet.write(0, 0, '考拉', cell_format)
sheet.write(0, 1, '商品名称', cell_format)
sheet.write(0, 2, '正面图', cell_format)
sheet.write(0, 3, '商品链接', cell_format)
sheet.write(0, 4, '到手价', cell_format)
sheet.write(0, 5, '所有售价', cell_format)
sheet.write(0, 6, '备选颜色', cell_format)
sheet.write(0, 7, '颜色数量', cell_format)
sheet.write(0, 8, '销售商家', cell_format)
gucci_zh_img_format = {'x_offset': 4, # 左右移动
'y_offset': 0,
'x_scale': 0.3, # 缩放比例
'y_scale': 0.29}
img_format = {'x_offset': 4, # 左右移动
'y_offset': 0,
'x_scale': 0.2, # 缩放比例
'y_scale': 0.19}
# 插入爬取kaola_list信息
row_number = 1
for product in kaola_list:
sheet.set_row(row_number, 52) # 设置第row_number行的高度为52
sheet.write(row_number, 1, product['product_title'], cell_format)
sheet.insert_image(row_number, 2, product['url_path'], img_format)
sheet.write(row_number, 3, product['product_url'], cell_format)
sheet.write(row_number, 4, '¥' + str(product['product_prices']), cell_format)
sheet.write(row_number, 5, product['product_all_prices'], cell_format)
sheet.write(row_number, 6, product['product_other_color'], cell_format)
# color_number=1
if(product['product_other_color'].count(',')==0):
color_number = 1
else:
color_number = product['product_other_color'].count(',')
sheet.write(row_number, 7, color_number, cell_format)
sheet.write(row_number, 8, product['product_sales_name'], cell_format)
row_number = row_number + 1
# book.close()
print(download_type + ': 写入EXCEL成功')
return book</code></pre>