实例1：用Python自动批量生成Excel档物料标签

Four兄 2019-08-25

展开全文

作为一个生产型企业，其产品种类繁多。按照客户要求，每种产品在出货时都需要在包装上贴上如下信息标签，以便识别。

标签上需要包含6种信息，原始资料内容如下：

我们注意到原始信息中的品牌，有些是大写，有些是小写，而标签要求全部大写，因此在填入之前需要先做处理。公司的常规做法是充分发挥人海战术，安排一个小组的人一哄而上，先手动将小写字母替换为大写，然后开始一系列的'复制'加'粘贴'。通常完成一个物料的标签信息需要35秒，100个物料就需要约1小时。可怜那些操作人员，除了手酸，脖子硬，眼睛疼，再加上键盘上的“ctrl，C，V”三个键像光头一样光滑外，还容易出错。如果原始信息有误，部分标签还得小心翼翼、如履薄冰地重新复制粘贴来修正。有时候，操作人员一走神，复制或者粘贴错了，还会被客户投诉，继而被老板训斥，整个心情都不好了。

通常一个订单有上百种物料，每天需要处理约30个订单，需要4个人全职处理这些信息。使用Python, 只需要很少量代码即可轻松搞定，运行只需几秒钟时间。只要原始信息没问题，Python会保质保量完成任务。

首先，我们需要将空白标签的格式复制足够多个。为方便打印，本例是在A4纸上每排放3个标签。也可让Python去自动填写标签第一列的信息，但涉及格式操作，还不如在Excel内直接设置方便。

以上操作完成后，就可以开始后续的信息读取和填写了，步骤如下：

从“info”工作表读取所有物料的信息
转换字母为大写，存储好信息
将处理好的信息逐个写入到“label”工作表
保存工作表

下面我们需要将自然语言翻译成计算机能理解的语言,以便它按我们的指令执行：

# 从“info”工作表读取并处理信息，存入列表from openpyxl import load_workbookdef get_info(path): wb = load_workbook(path) ws = wb['info'] info=[]for row in range(2, ws.max_row+1): brand = ws['A' + str(row)].valueif brand: # 判断“品牌”是否为空值，若为空值则不进行小写字母的转换# 若无这行，当遇到“品牌”为空值时，程序会报错，因为空值无法进行大小写转换 brand=brand.upper() #将“品牌”中的小写字母全部转换成大写字母 typ = ws['B' + str(row)].value # 获取单元格中的数据 pn = ws['C' + str(row)].value lotno = ws['D' + str(row)].value date = ws['E' + str(row)].valueif date: # 判断“日期时间”是否为空值，若为空值则不进行日期获取# 若无这行，当遇到“日期时间”为空值时，程序会报错，因为空值无法进行日期获取 date=date.date() #只获取日期时间中的日期，比如2019-2-20，不需要具体时间 quantity = str(ws['F' + str(row)].value)+' pcs' # 在数量后面加上“pcs”字样 data={'brand':brand,'typ':typ,'pn':pn,'lotno':lotno,'date':date,'quantity':quantity } info.append(data)return info

%%time# %%time 用于查看程序运行所需时间path='data\label_info.xlsx'info=get_info(path)print('finished')

>>finished

>>Wall time: 79 ms

我们首先导入openpyxl模块中的load_workbook模块。openpyxl是一个可读取Excel的第三方库。load_workbook()可打开一个现有的Excel文件（或叫工作簿）。

此处我们先定义一个函数，取名为get_info,该函数包含一个参数path,用来传入所需要打开的Excel文件的路径。定义函数是为了方便后续反复地调用，因为我们后续需要处理的文件可能在不同的路径，我们只需传入不同的文件路径即可获取该文件的信息，而不用每次都去函数内部做改动。其中def就是定义（Define）的意思。函数就是高等数学中的f(x),x就是它的参数，我们传入x,经函数一运算，就可以得到我们想要的结果。比如f(x)=x*2,那假如将x=2传入该函数，结果就是f(2)=2*2=4。以上get_info(path)函数可以理解为，传入“一个Excel文件的路径”，就可以得到“这个Excel内指定的信息”。我们甚至可以设想奶牛就是一个函数：奶牛(草)=牛奶,只要给这个奶牛函数传入“草”，就可以得到我们要的结果--“牛奶”。

我们使用load_workbook（path）读取文件，并存在变量wb里面。我们可以想象变量就是容器，用来储存各种数据，这里的wb就是这个容器的名字。可以通过type()函数查看这个容器的类型。我们可以看到这是一个openpyxl.workbook.workbook.Workbook类型的数据，可以理解为它装下了整个Excel文件的内容。

In [3]:wb = load_workbook('data\label_info.xlsx')type(wb)Out[3]:openpyxl.workbook.workbook.Workbook

但是我们并不需要整个Excel文件的内容，只需要其中一个名叫“info”的工作表的内容，因此我们再定义一个变量（容器）ws从容器wb中将工作表“info”中的数据取过来并放进去：ws = wb['info']。ws的类型是openpyxl.worksheet.worksheet.Worksheet，可以理解为它装下了工作表“info”中的内容。

In [4]:ws=wb['info']type(ws)Out[4]:openpyxl.worksheet.worksheet.Worksheet

然后我们建立了一个空列表info来存储从ws中按每种物料的信息取出来的数据。然后从第二行开始（第一行是列名）通过for循环来逐个查看并获取每行的数据，存进一个字典data，再将data放入info列表中。其中ws.max_row可获得这个工作表的行数。比如本例的“info”工作表有71行数据，那这个ws.max_row就是71。由于从range函数中取值时，是不包含末尾那个数的，所以此处需要ws.max_row+1=72，才能最多取到第71行的数据。

In [5]:ws.max_rowOut[5]:71

In [6]:for i in range(2,5):# 从2-5范围内逐个取值，但不包含5print(i)234

数据提取完成后，我们就可以检查我们的数据是否有问题。len(info)可查看info列表中有多少个数据，此处是70个数据，正好与Excel中的数据行数相同。再看info列表中的第一个数据info[0]（注意列表的索引是从0开始，0就表示第一个数据，以此类推）：

{'brand': 'ABC1',
 'typ': '11X11-XX761X8',
 'pn': '011-0076108',
 'lotno': 'Q19H023994901',
 'date': datetime.date(2019, 2, 20),
 'quantity': '300 pcs'}

字典里的键对应的是“品牌、型号、物料编号、批次号、生成日期、数量”，值正好是Excel表中第2行的数据。

In [7]:info[0]Out[7]:{'brand': 'ABC1','typ': '11X11-XX761X8','pn': '011-0076108','lotno': 'Q19H023994901','date': datetime.date(2019, 2, 20),'quantity': '300 pcs'}

在label_info_nan.xlsx故意放了3行含有空值的数据，可以看到最后一个数据的“品牌”和“生产日期”是“None”，即为空值。

In [8]:info_nan=get_info('data\label_info_nan.xlsx')len(info_nan)info_nan[-1]Out[8]:91Out[8]:{'brand': 'ABC91','typ': 'C1HQERFFX1D','pn': '212-05012072','lotno': 'H0345345410','date': datetime.date(2018, 11, 8),'quantity': '200 pcs'}

此时，我们成功获取了所有产品的数据，并存进了列表容器info中。下一步就需要将其中的信息逐个提取出来，并写入到标签工作表里去了。

# 将处理好的信息逐个写入到“label”工作表并保存def write_info(path,info): wb = load_workbook(path) ws = wb['label'] k=0for i in range(2,9,3): #列遍历for j in range(1,round(len(info)*7/3),7): #行遍历

if k < len(info): #当数据条数不是3的整数倍时，计数器k会超出列表info的范围，后续代码只有在k < len(info)的情况下执行

ws.cell(row=j, column=i).value = info[k]['brand'] ws.cell(row=j+1, column=i).value = info[k]['typ'] ws.cell(row=j+2, column=i).value = info[k]['pn'] ws.cell(row=j+3, column=i).value = info[k]['lotno'] ws.cell(row=j+4, column=i).value = info[k]['date'] ws.cell(row=j+5, column=i).value = info[k]['quantity'] k+=1 #k是列表info的索引，此处加一以便获取下一条数据 wb.save(path) #保存Excel文件

%%timepath='data\label_info.xlsx'write_info(path,info) #调用write_info函数，将数据写入并保存print('Finished!')----------------------Finished!Wall time: 557 ms

此处我们定义了第二个函数write_info，它包含两个参数，path及info,即需操作的文件及装着信息的容器。写成函数，方便后续的反复调用。这个函数的功能是将容器里的信息写入到需要操作的文件，并保存，不需要返回什么值，因此没有return语句。

我们的目的是将label_info.xlsx工作簿中的info中的信息填写到label中去，所以此处还是在label_info.xlsx中操作。先用load_workbook打开Excel表，然后用wb这个容器来存储其所有信息。然后用ws容器存储label这张工作表里的信息。我们定义了一个变量k,并设定它的初始值为0。这是为了给后续遍历列表info提供索引，比如info[0]就是指info里面的第一条数据，也就是第一个物料的所有信息。然后是for循环遍历所有列。为方便打印，我们的标签是3个一行，每个标签之间空一行一列。而我们只给第二、五、八列填写数据，因此这里的range(2,9,3)指的是从2列开始，8列结束，以3为步长，列的取值将为2,5,8，正是我们需要填写数据的列号。

随后我们进行行遍历，由于每个标签有6个信息，加上1个空行，因此每个标签总共占用了7行。range(1,round(len(info)*7/3),7)指从第一行开始，以步长为7取值，直到最后一行,round用于四舍五入以保证为整数，此处取的值都是每个标签的第一行。假设共有6个标签需要填写，那么需要的行数为6*7/3=14，range(1,14,7)可取到的值为1，8行。

因为数据可能不是3的整数倍，此时计数器k可能会超出列表Info的范围导致程序报错而终止，所以加上if语句，后续代码只有在k < len(info)的情况下才执行。然后就是写入数据了。以第一个标签为例，我们在第一行第二列写入第一个物料的“品牌”信息，第二行第二列写入第一个物料的“型号”信息，一次类推。每写完一个物料的标签信息，计数器k需要加上一个1。因为k是列表info的索引，我们写好了info[0]里面的信息后，就需要接着写info[1]的信息了。信息全部写完后，就使用wb.save保存Excel文件。所保存的文件可指定新的路径，新的文件名，此处我们还是使用原文件名保存，避免产生太多Excel文件。

%%time语句可计算并显示运行这个程序所花费的时间，我们可以看到只用了不到短短0.2秒（Wall time: 137 ms）就完成了，真真实实的千倍效率提升。最终写好的标签如图所示。

以上是处理单个订单的标签，对于多个订单的标签，我们可以用类似的方法一次完成。我们只需要将所有订单的Excel文件放入一个文件夹，然后就可以批量操作。此时，需要引入os模块。该模块可自动获取文件路径及文件名。我们定义一个函数file_name，包含一个参数file_dir,该参数用于传入Excel文件所在的路径。随后定义一个列表names用于存储后续获取的Excel文件路径。os.listdir将返回指定的文件夹包含的文件及文件夹的名字的列表。我们只需要处理Excel文件，因此加入一个条件判断，只将带有“.xlsx”后缀的文件放入names列表。由于os.listdir只获取了文件名，但我们在写入数据时需要文件的完整路径，所以需要把文件所在的路径加在文件名前面，即file_dir+file。最后返回names列表，获得所有Excel文件的完整路径。然后就可以调用之前写好的读取信息及写入信息的函数，逐个将names列表中的路径传入函数，完成信息的读取和写入。

In [11]:import osdef file_name(file_dir): names=[]for file in os.listdir(file_dir):if '.xlsx' in file: # 只处理该路径下的Excel文件 names.append(file_dir+file)return namesfile_dir='data/'pathess=file_name(file_dir)pathessOut[11]:['data/label_info - 1.xlsx','data/label_info - 2.xlsx','data/label_info - 3.xlsx','data/label_info - 4.xlsx','data/label_info - 5.xlsx','data/label_info.xlsx','data/label_info_nan.xlsx']In [12]:%%timefor path in pathess: info=get_info(path) write_info(path,info) print('Finished!') Finished!Wall time: 1.93 s

因为不同的客户，我们的文件夹可能还包含子文件夹，那要一次获取母文件夹中的所有Excel文件，需要应用到os.walk函数。os.walk可遍历一个目录内各个子目录和子文件。它先遍历当前目录，返回三个值，分别是目录的路径，目录下子目录的名字，文件的名字。再遍历子目录，同样返回子目录的路径，子目录下的子目录的名字，子目录内的文件的名字。若还有子目录，则继续遍历，直到所有目录被遍历。因此需要三个变量root, dirs, files去接收它的返回值。

我们在data/路径下建了2个子目录，a和b，可以看到os.walk先遍历data/目录，再data/a,data/b，并找出所有目录下的文件。

In [13]:
file_dir='data/'for root, dirs, files in os.walk(file_dir):    print(root,dirs,files,sep='\n******************\n')data/******************['a', 'b']******************['label_info - 1.xlsx', 'label_info - 2.xlsx', 'label_info - 3.xlsx', 'label_info - 4.xlsx', 'label_info - 5.xlsx', 'label_info.xlsx', 'label_info_nan.xlsx']data/a******************[]******************['abc.docx', 'label_info - 6.xlsx', 'label_info - 7.xlsx']data/b******************[]******************['new.txt']In [14]:
pathss=[] # 文件夹内所有文件（包括子目录）file_dir='data/'for root, dirs, files in os.walk(file_dir):    path = [os.path.join(root, name) for name in files]    pathss.extend(path)pathssOut[14]:['data/label_info - 1.xlsx','data/label_info - 2.xlsx','data/label_info - 3.xlsx','data/label_info - 4.xlsx','data/label_info - 5.xlsx','data/label_info.xlsx','data/label_info_nan.xlsx','data/a\\abc.docx','data/a\\label_info - 6.xlsx','data/a\\label_info - 7.xlsx','data/b\\new.txt']

由于我们需要获得文件的完整路径，但以上files只是文件的名字，所以需要使用os.path.join函数将文件所在路径与文件名连起来，如data/b需要跟new.txt连起来,变成'data/b\\new.txt'，其中'\'是'\'的转义字符。另外上述程序用到了列表解析式:

path = [os.path.join(root, name) for name in files]

这可以让程序变得非常简洁，也节省运算时间。通过看下面这个小例子就可以知道其原理：

a=[i for i in range(10)]
a
>>[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

如果文件夹内还有除Excel之外的文件，需要将其从文件路径列表中剔除，然后再传入信息读取和写入的函数。

In [15]:
result=[]for i in pathss:if '.xlsx' in i:        result.append(i)resultOut[15]:
['data/label_info - 1.xlsx','data/label_info - 2.xlsx','data/label_info - 3.xlsx','data/label_info - 4.xlsx','data/label_info - 5.xlsx','data/label_info.xlsx','data/label_info_nan.xlsx','data/a\\label_info - 6.xlsx','data/a\\label_info - 7.xlsx']