作者 | 李秋键
责编 | 晋兆雨
头图 | CSDN付费下载自视觉中国

在很多的公司项目中，常常有很多对office项目的比较机械化的操作，在这里就可以借助python实现对office的合理排版。而这里我们就将借助海尔公司的出货表爬取对应图片信息，并重新排版成为更加合理的Excel布局。

而今天我们这个项目是来自于实际生活中真实存在的处理事件。海尔在国外的员工常常要处理一些进出货的表格统计，但是由于国外人很多不大精通汉字，故常常要通过给出的汉字在网上搜索图片，然后复制到表格中以方便国外人能看懂是什么货物，并加上拼音有助于理解。相对以往而言，由于货物清单任务量很大，常常需要大量时间人力而且容易出错，故我们这里设计了个程序使得这一项任务完全可以由电脑自动完成，不仅速度极快，而且不需要浪费人力和精力，提高了生产效率。

实验前的准备

其中海尔给出的货物清单的Excel在4.xlsx中，数据如下：

代码总体框架

整体的程序框架分为两个重要部分。一个部分是用来爬取数据，另一个用来排版成美观的Excel。如下详解：

1 爬取数据并保存

如download.py程序可见。

首先我们流程是从Excel读取数据、然后借助百度图库搜索图片，并爬取保存。

2 Excel排版和拼音注释

如xls.py可见。根据下载下来的图片和文字及其拼音保存排版。

第三方库介绍

1. Xlrd库：用来读取和保存Excel表格，更方便读取数据
2. Pypinyin库：用来把汉字转为拼音，这里我们需要将它准成有音素的拼音
3. Xlsxwriter库：用来读取和保存Excel表格，其可以更好地保存格式
4. Requests库：Requests 是用Python语言编写，基于 urllib，采用Apache2 Licensed 开源协议的
5. HTTP 库。它比 urllib 更加方便，可以节约我们大量的工作，完全满足 HTTP 测试需求。
6. Re库：正则表达式匹配
7. Pillow库：读取图片
8. Urllib库：用来网络爬虫处理
9. Socket库：数据包处理
10. Openpyx库l:openpyxl是一款比较综合的工具，不仅能够同时读取和修改Excel文档，而且可以对Excel文件内单元格进行详细设置，包括单元格样式等内容，甚至还支持图表插入、打印设置等内容，使用openpyxl可以读写xltm, xltx, xlsm, xlsx等类型的文件，且可以处理数据量较大的Excel文件，跨平台处理大量数据是其它模块没法相比的。因此，openpyxl成为处理Excel复杂问题的首选库函数。在使用openpyxl前先要掌握三个对象，即：Workbook(工作簿)、Worksheet（工作表）和Cell（单元格，存储具体的数据对象）三个对象。
11. Time库：用来每次爬取数据的适当延迟，以防止网站封掉ip。
12. Os模块：用来本地文件和文件夹的读取和生成等等。

代码

在download程序中，首先是根据给出的Excel表读取数据，代码如下：

首先是设置编码格式和导入所要使用到的库：

#encoding=utf-8
importxlrd
frompypinyin import lazy_pinyin,pinyin
importxlsxwriter
importrequests
import os
import re
from PILimport Image
importtime
fromurllib import request
fromurllib import error
importrandom
importsocket

接着设立文件名变量方便保存和读取。设立爬虫包的延迟时间为20s。

#改文件名的地方
excel_address="4.xlsx"
socket.setdefaulttimeout(20)

为了防止反爬的问题，设立请求头

header ={'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML,like Gecko) Chrome/63.0.3239.132 Safari/537.36'}

接着使用xlrd库读取Excel表格，按照列对象读取数据

workbook= xlrd.open_workbook(excel_address)
sheet =workbook.sheet_by_name("Sheet1")
col0 =sheet.col_values(0) # 获取第1列内容,序号那一列
col1 =sheet.col_values(1)# 获取第2列内容,物资名称那一列
col2=sheet.col_values(2)#获取第3列内容,型号规格那一列
col3=sheet.col_values(3)
col4=sheet.col_values(4)
col5=sheet.col_values(5)

使用lazy_pinyin函数将汉字转为拼音，同时保留了音素并保存：

for i in col1:
    i=lazy_pinyin(i)
    txt=""
    for w in i:
        txt+=w+" "
    yin.append(txt)
print(yin)

根据百度图片搜索图片并爬取保存。其中为了防止网

页延迟等问题，通过多次尝试爬取合适的图片：

numm=[]
for i in range(len(col0)):
    print("完成了"+str((i/len(col0))*100)+"%

判断读取到的数据是否是数字，因为其中有些数据是

空格等等多余的无效数据：

  if isinstance(col0[i],float):
        numm.append(i)
        t=col1[i]+col2[i]
        print(t)
        url = 'http://image.baidu.com/search/flip?tn=baiduimage&ie=utf-8&word=' + t + '&ct=201326592&v=flip'
        result = requests.get(url, headers=header)
        pic_url = re.findall('"objURL":"(.*?)",', result.text, re.S)
        num=1
        for each in range(len(pic_url)):
            print('正在下载第' + str(num) + '张图片，图片地址:' + str(pic_url[each]))
            try:
                pic = requests.get(pic_url[each], timeout=2,headers=header)

保存爬取下来的图片至对应的文件夹中，文件夹名为

对应行的序号。然后设置爬取下来的图片尺寸为300

并保存：

   if not os.path.exists("image/"+str(i)):
                    os.makedirs("image/"+str(i))
                dir = "image/"+str(i)+"/" + str(num) + '.jpg'
                fp = open(dir, 'wb')
                fp.write(pic.content)
                fp.close()
                try:
                    img = Image.open("image/" +str(i)+"/" + str(num) + '.jpg')
                    img.resize((300, 300)).save("image/" +str(i)+"/" + str(num) + '.jpg')
                except:
                    pass
                num += 1
                if num == 5:
                    print("下一个。")
                    result.close()
                    break
            except :
                print('【换资源中。。。】')
                continue

每个货物名称只需要下载爬取四个图片即可：

  if num == 5:
                print("下一个。")
                result.close()
                time.sleep(random.randint(0,3))
                break

其中download.py运行效果如下：

爬取下来的保存部分图片如下：

接着使用xls.py进行排版。因为这里我们使用xlrd排版

时发现保存的Excel格式都失效了，故需要借助

openpyxl库进行原有格式的保存和处理，即保存为

新的Excel为8.xlsx。

import os
import xlrd
from pypinyin import lazy_pinyin,pinyin
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
#改文件名的地方
excel_address="4.xlsx"
wb = load_workbook(excel_address)
wb.save("8.xlsx")
workbook = xlrd.open_workbook(excel_address)
sheet1 = workbook.sheet_by_name("Sheet1")
wb = load_workbook('8.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
sht = wb.worksheets[0]
col0 = sheet1.col_values(0) #获取第1列内容,序号那一列
col1 = sheet1.col_values(1)#获取第2列内容,物资名称那一列
col2=sheet1.col_values(2)#获取第3列内容,型号规格那一列
col3=sheet1.col_values(3)
col4=sheet1.col_values(4)
col5=sheet1.col_values(5)

根据Excel表格单元填充进拼音：

for i in range(len(col0)):
    sht.row_dimensions[i].height = 150.0
    if isinstance(col0[i], float):
        sheet["G"+str(i+1)].value=yin[i]

接着根据已经保存下来的爬取到的图片依次按照布局

顺序进行填充，同时还要设定一定的图片尺寸，以保

证布局的美观和合理：

for i in os.listdir("image"):
    for j in os.listdir("image/"+i):
        tw="image/"+i+"/"+j
        label=j.split(".")
        label=label[0]

根据label对象的数据判断对应序号，根据序号判断对

应图片存放的文件夹位置，然后读取图片并复制到

Excel表格中：

if int(label)==1:
try:
img_address_2 = “image/” +i+"/"+ str(label) + ‘.jpg’

使用openpyxl中的image函数读取图片

 img = Image(img_address_2)

设置图片的长宽为200，统一尺寸以方便布局

     img.width = 200.0
        img.height = 200.0

通过单元格添加图片即可达到插入图片的效果

sht.add_image(img, ‘J’+str(int(i)+1))

如果没有合理的找到对应图片就跳过：

            except:
                pass
        if int(label)==2:
            try:
                img_address_2 = "image/" +i+"/"+ str(label) + '.jpg'
                img = Image(img_address_2)
                img.width = 200.0
                img.height = 200.0
                sht.add_image(img, 'M'+str(int(i)+1))
            except:
                pass
        if int(label)==3:
            try:
                img_address_2 = "image/" +i+"/"+ str(label) + '.jpg'
                img = Image(img_address_2)
                img.width = 200.0
                img.height = 200.0
                sht.add_image(img, 'P'+str(int(i)+1))
            except:
                pass
        if int(label)==4:
            try:
                img_address_2 = "image/" +i+"/"+ str(label) + '.jpg'
                img = Image(img_address_2)
                img.width = 200.0
                img.height = 200.0
                sht.add_image(img, 'S'+str(int(i)+1))
            except:
                pass
wb.save("dels.xlsx")

最终得到的排版程序如下可见：

由此可见通过python的office操作和网络搜索自动爬取排版可以极大地节省人力和时间。

作者介绍：
李秋键，CSDN 博客专家，CSDN达人课作者。硕士在读于中国矿业大学，开发有taptap安卓武侠游戏一部，vip视频解析，文意转换工具，写作机器人等项目，发表论文若干，多次高数竞赛获奖等等。

源码地址：

链接：https://pan.baidu.com/s/1Rtl3zoQRhKeRstgnl1firw
提取码：wk2z