代码语言：javascript复制
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor
import requests
def download(url,raw_path):
file = url.split("/")[-1]
file_name = f'{raw_path}/{file}'
if os.path.exists(file_name):
print(f"已存在{file_name}")
return
headers = {
"Accept": "text/html,application/xhtml xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
"Accept-Language": "zh-CN,zh;q=0.9",
"Cache-Control": "no-cache",
"Connection": "keep-alive",
"Pragma": "no-cache",
"Sec-Fetch-Dest": "document",
"Sec-Fetch-Mode": "navigate",
"Sec-Fetch-Site": "none",
"Sec-Fetch-User": "?1",
"Upgrade-Insecure-Requests": "1",
"User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36",
"sec-ch-ua": "".Not/A)Brand";v="99", "Google Chrome";v="103", "Chromium";v="103"",
"sec-ch-ua-mobile": "?0",
"sec-ch-ua-platform": ""macOS""
}
res = requests.get(url,headers=headers,verify=False,)
with open(file_name,"wb") as fw:
fw.write(res.content)
def item_list_to_excel(row_list):
#数据列表默认第一行为图片
if not row_list:
return None
wb = Workbook()
ws = wb.create_sheet("Sheet",0) # 根据文件夹名称创建sheet
raw_path =os.path.dirname(os.path.dirname(__file__)) "/images"
if not os.path.exists(raw_path):
os.makedirs(raw_path)
pool = ThreadPoolExecutor(20)
t_list = [ pool.submit(download,data["商品图片"],raw_path) for data in row_list ]
for t in t_list:
t.result()
# 设置第一行的字段
column = 1
for k in row_list[0].keys():
ws.cell(row=1, column=column, value=k)
column = 1
m = 0
for data in row_list:
row_data = list(data.values())
row_count =len(row_data)
#填写除图片字段的其他字段
for index in range(1,row_count):
ws.cell(row=m 1 1, column=index 1, value=row_data[index])
#插入图片
file = row_data[0].split("/")[-1]
image_path = f'{raw_path}/{file}'
img = Image(image_path)
ws.row_dimensions[m 2].height = 180 #高是榜为单位
ws.column_dimensions['A'].width = 50 #宽是字符为大卫
_from = AnchorMarker(0, 50000, m 1, 50000) # 创建锚标记对象,设置图片所占的row
to = AnchorMarker(1, -50000, m 1 1, -50000) # 创建锚标记对象,设置图片所占的row 从而确认了图片位置
img.anchor = TwoCellAnchor('twoCell', _from, to) # 将锚标记对象设置图片对象的锚属性,图形就具备了所在位置
ws.add_image(img) # 添加图片
m =1
file_name = f'export_distributor_{datetime.datetime.now().strftime("%Y%m%d_%H%M")}.xlsx'
wb.save(file_name)
wb.close()
print(f"保存文件")
return file_name
