关于python3导出excel图片链接转图片且图片内嵌表格内实现
2023-06-13 09:13:36 时间
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
相关文章
- python3基础:操作mysql数据库
- Python3脚本编程常用.md
- python3安装后没有pip_解决Centos7安装python3后pip工具无法使用「建议收藏」
- Python3中的“加和”函数
- Selenium4+Python3系列(十三) - 与docker中的jenkins持续集成
- Java的字节(byte)数组与Python3中的字节类型负值问题
- 宝塔教程:基于centos7环境设置python3的一些技巧分享
- Python3使用qrcode生成二维码详解编程语言
- python3 开发面试题(去重保持原来的顺序)6.2详解编程语言
- 数据库快速实现Excel导入MySQL数据库(excel导入mysql)
- MySQL查询结果导出至Excel:一步到位(mysql查询结果导出excel)
- python3 selenium模块Chrome设置代理ip的实现详解编程语言
- Excel数据快速转移至MySQL(excel转mysql)
- 快速完成:从Excel导入Oracle数据库(excel导入oracle数据库)
- 轻松实现Excel导入MySQL数据库(excel导入mysql数据库)
- MySQL快速导入Excel数据(mysql导入excel数据)
- 使用Excel快速读取MySQL数据(excel读取mysql)
- Python3 实现淘女郎照片爬虫
- Linux 卸载Python3:步骤与注意事项(linux卸载python3)
- Oracle与Excel联合提升数据分析挖掘能力(oracle与excel)
- php读取EXCEL文件phpexcelreader读取excel文件