通过python读取excel表格内容url批量下载

Updated on in 人生苦短,我用Python with 56 views

现有表格内容如下:
image.png
BT 列有全部图片 url 地址:

表格中一个单元格中有一个 url 或者多个 url 或者空白,解决不识别情况,str 转 list 解决

解决方法:

import xlrd
import requests
import random
import string
import os
import time
path = 'test.xls'
date = time.strftime('%Y-%m-%d',time.localtime())
workbook = xlrd.open_workbook(path)
# print(workbook.sheet_names())
Data_sheet = workbook.sheets()[0]
# print(Data_sheet.name)
rowNum = Data_sheet.nrows #行数
colNum = Data_sheet.ncols #列数
'''
提取单元格所有内容
'''
list = []
for i in range(rowNum):
    rowlist = []
    for j in range(colNum):
        rowlist.append(Data_sheet.cell_value(i,j))
        list.append(rowlist)
'''
提取对应行列的内容
'''
# print(Data_sheet.cell_value(0,43))   #列名称
# print(Data_sheet.ncols)  #有效列数
name_col = '图片地址'
url_list = []
for b in range(Data_sheet.ncols):
    if (Data_sheet.cell_value(0,b)) == name_col:
        for col in range(colNum):
            url_data = Data_sheet.cell_value(col,b)
            url_data2 = url_data.replace(';','\n')
            url_list.append(url_data2)
            # print(url_data2)
del url_list[0]
with open('./url.txt','w',encoding='utf-8') as f:
    for url in url_list:
        f.write(str(url))
        # f.write(',')
with open('./url.txt','r',encoding='utf-8') as d:
    file = d.read()
    file_list = file.split('\n')
del file_list[-1]

headers = {
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36'
}
name = 'pictures'
os.mkdir(name)
os.chdir(name)
for url_1 in file_list:
    name_1 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
    res = requests.get(url_1,headers=headers)
    print(('%s下载完毕')%(name_1))
    with open(name_1 + '.jpg','wb') as p:
        p.write(res.content)

1.2 异常捕获

#!/usr/bin/python
# -*- coding: utf-8 -*-
# @Time  : 2019/10/15 21:36
# @Author : cuijianzhe
# @File  : biaoge.py
# @Software: PyCharm
import xlrd
import requests
import random
import string
import os
import time
path = 'test.xls'
date = time.strftime('%Y-%m-%d',time.localtime())
workbook = xlrd.open_workbook(path)
# print(workbook.sheet_names())
Data_sheet = workbook.sheets()[0]
# print(Data_sheet.name)
rowNum = Data_sheet.nrows #行数
colNum = Data_sheet.ncols #列数
'''
提取单元格所有内容
'''
list = []
for i in range(rowNum):
    rowlist = []
    for j in range(colNum):
        rowlist.append(Data_sheet.cell_value(i,j))
        list.append(rowlist)

'''
提取对应行列的内容
'''
#print(Data_sheet.cell_value(0,43))   #列名称
#print(Data_sheet.ncols)  #有效列数
name_col = '图片地址'
url_list = []
try:
    for b in range(Data_sheet.ncols):
        if (Data_sheet.cell_value(0,b)) == name_col:
            for col in range(colNum):
                url_data = Data_sheet.cell_value(col,b)
                url_data2 = url_data.replace(';','\n')
                url_list.append(url_data2)
except:
    pass

del url_list[0]
with open('./url.txt','w',encoding='utf-8') as f:
    for url in url_list:
        f.write(str(url))
        # f.write(',')
with open('./url.txt','r',encoding='utf-8') as d:
    file = d.read()
    file_list = file.split('\n')
del file_list[-1]

headers = {
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36'
}
name = 'pictures'
os.mkdir(name)
os.chdir(name)
for url_1 in file_list:
    name_1 = ''.join(random.sample(string.ascii_letters + string.digits, 8))
   #name_1 = url_1.replace('/','')
    res = requests.get(url_1,headers=headers)
    print(('%s下载完毕')%(name_1))
    with open(name_1 + '.jpg','wb') as p:
        p.write(res.content)

如图:
image.png

打包 exe 文件

pip install pyinstaller 
  • pyinstaller 的使用
pyinstaller -F biaoge.py
  • 找到文件
    image.png

参考:
pyinstaller官方文档:
xlrd官方文档:
https://www.cnblogs.com/insane-Mr-Li/p/9092619.html

搭讪你就破功了,老弟!
Responses