python实现表格批量url替换成图片
- 在网上找了一圈,看着大家写的都挺好,就是拿过来在自己的电脑上运行报错,然后经过了一定的加工,跑通整个代码。至于没成功的,我们可以在excel中重新整理,数量如果大于10个我们就再排量跑一圈,如果少了就自己手动一下,我们主打的就是批量,给大家节约时间。当然大佬自己搂代码。
from csv import excel
import io
import requests
import time
import re
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
def img_resize(width, height):
if width > height:
height = height*200//width
width = 200
else:
width = width*200//height
height = 200
return width, height
def getRowsClosNum(self):
rows = self.max_row
columns = self.max_column
return rows,columns
def get_row_value(self, row):
columns = self.max_column
row_data = []
for i in range(1, columns + 1):
cell_value = self.cell(row=row, column=i).value
row_data.append(cell_value)
return row_data
def urlpoint(data):
NumTip = []
if data == []:
return -1
for i in range(len(data)):
strname =str(data[i])
pattern = re.compile(r'(.png|.img|.gif|.jpg|.jpeg)$')
m = pattern.search(strname)
if m == None:
continue
else:
NumTip.append(i)
if NumTip == []:
return -1
else:
return NumTip
def TurnURLPictures():
ExcleSheet=0
start = time.time()
textname ='请求图片 - 副本下载.xlsx'
dataname ='请求图片 - 副本.xlsx'
wb = load_workbook(dataname)
Sheetnames =wb.sheetnames
ws = wb[Sheetnames[ExcleSheet]]
cData = get_row_value(ws,1)
Crows,Ccolumns= getRowsClosNum(ws)
urlnum =urlpoint(cData)
if urlnum == -1:
print ('Its second line does not have a URL')
return -1
Cnum ={1:"A",2:"B",3:"C",4:"D",5:"E",6:"F",7:"G",8:"H",9:"I",10:"J",11:"K",12:"L",13:"M",14:"N","15":"O",16:"P",17:"Q",18:"R",19:"S",20:"T",21:"U",22:"V",23:"W",24:"X",25:"Y"}
GroupNum = len(urlnum)
for i in range(GroupNum):
subscript = urlnum[i]+1
name =str(Cnum[subscript])+'2:'+str(Cnum[subscript])+str(Crows)
ws.column_dimensions[Cnum[subscript]].width = 50
for cell, in ws[name]:
try:
url = cell.value
if url == None:
continue
pattern = re.compile(r'^(https://|http://)')
m = pattern.match(url)
if m == None:
url ='https://image.baidu.com/' +url
cell.hyperlink = None
cell.value = ""
ws.row_dimensions[cell.row].height = 200
res = requests.get(url)
img = Image(io.BytesIO(res.content))
width, height = img_resize(img.width, img.height)
img.width = width
img.height = height
endnum =str(Cnum[subscript]) + str(cell.row)
print('成功添加到第:',endnum,'列')
ws.add_image(img, endnum)
except:
pass
start2 = time.time()
wb.save(textname)
print('Running time: %s 毫秒' % (start2*1000-start*1000))
return Crows/60
if __name__=="__main__":
TurnURLPictures()
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/acddf634262f4fbb880762c78ed6b72a.png)