8、gucci_write_to_excel
<p>欢迎使用ShowDoc！</p>
<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', 'men_all_scarves']
women_all = ['women_all_shoes', 'women_all_bags', 'women_all_belts', 'women_all_clothes', 'women_all_wallets', 'women_all_scarves']
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
download_type_number[download_type] = row_number
# 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)
write_home_page(book)
book.close()
print(excel_name + ': 写入EXCEL成功')
return None</code></pre>
<pre><code class="language-python">men_all, women_all = get_all_list()</code></pre>
<pre><code class="language-python">download_type_number =\
{
'men_all_shoes':0,
'men_all_bags':0,
'men_all_belts':0,
'men_all_clothes':0,
'men_all_wallets':0,
'men_all_shoes':0,
'men_all_bags':0,
'men_all_belts':0,
'men_all_clothes':0,
'men_all_wallets':0
}</code></pre>
<pre><code class="language-python">def write_home_page(book):
keys = []
for k in download_type_number.keys():
keys.append(k)
sheet = book.add_worksheet('Home')
# 设置sheet表单元格列宽
sheet.set_column("A:A", 20) # 种类
sheet.set_column("B:B", 8.5) # 商品名称
# 设定整个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.set_row(0, 22) # 设置第一行的高度为22
# 插入第一行
sheet.write(0, 0, '种类', cell_format)
sheet.write(0, 1, '个数', cell_format)
row_number = 1
all_number = 0
for i in range(0,len(download_type_number)):
sheet.set_row(row_number, 22) # 设置第row_number行的高度为22
sheet.write(row_number, 0, keys[i], cell_format)
all_number = all_number + download_type_number[keys[i]] - 1
sheet.write(row_number, 1, download_type_number[keys[i]] - 1, cell_format) # product['productCode']
row_number = row_number + 1
sheet.write(row_number, 0, '总计', cell_format)
sheet.write(row_number, 1, all_number, cell_format)
# book.close()
print('HomePage' + ': 写入EXCEL成功')
return book</code></pre>
<pre><code class="language-python">it_write_excel_to_path(men_all, women_all, 'it_excel-20.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_scarves
men_all_scarves: 写入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成功
men_all_scarves
women_all_scarves: 写入EXCEL成功
HomePage: 写入EXCEL成功
it_excel-20.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
download_type_number[download_type] = row_number
# 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)
write_home_page(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-20.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_scarves
men_all_scarves: 写入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成功
men_all_scarves
women_all_scarves: 写入EXCEL成功
HomePage: 写入EXCEL成功
zh_excel-20.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
download_type_number[download_type] = row_number
# 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)
write_home_page(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-20.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_scarves
men_all_scarves: 写入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成功
men_all_scarves
women_all_scarves: 写入EXCEL成功
HomePage: 写入EXCEL成功
kaola_excel-20.xlsx: 写入EXCEL成功</code></pre>