用python怎么实现办公自动化【批量生成出货清单】

---

👨‍💻个人主页：@开发者-曼亿点

👨‍💻 hallo 欢迎 点赞👍 收藏⭐ 留言📝 加关注✅!

👨‍💻 本文由 曼亿点 原创

👨‍💻 收录于专栏：机器学习

⭐🅰⭐

—

---

---

⭐前言⭐

   工作中经常需要将一个Excel工作表中的数据按一定的规则分类填写到多个相同格式的工作表中，如将出货记录按出货日期分类生成多张出货清单。本任务将带领大家编写Python程序，利用xlwings库操作Excel工作表，批量生成出货清单。

---

🎶 一、xlwings库简介

---

  Python提供了多种库操作Excel，如XlsxWriter、xlrd、xlwt、xlutils、openpyxl和xlwings等，其功能对比如表1所示。

  从表1可以看出，xlwings库的功能最齐全，它不仅能读、写和修改两种格式的工作簿（xls和xlsx），还能批量处理多个工作簿。此外，xlwings库还可以与pandas库结合使用，实现更强大的数据输入和分析功能。但使用xlwings库时系统必须安装Excel软件。
  xlwings库的逻辑结构如图1所示。其中，App（即应用）表示Excel程序，Book表示工作簿，Sheet表示工作表，Range表示区域，既可以是一个单元格，也可以是单元格区域。从图1可以看出，使用xlwings库操作Excel时，可以同时启动多个App，一个App可以打开多个Book，一个Book可以打开多个Sheet，一个Sheet可以对多个Range进行操作。

   使用xlwings库操作Excel的基本步骤按照xlwings库的逻辑（App→Book→Sheet→Range）操作即可，最后关闭Book和退出App。

【温馨提示】：xlwings库不是Python内置的标准库，使用之前需要安装。

---

🎶 二、读写Excel工作表

---

1．读取Excel工作表

读取Excel工作表，首先须启动Excel程序，可以用下面代码实现。

import xlwings #导入xlwings库
app = xlwings.App(visible=False, add_book=False)#启动Excel程序

  其中，app表示App对象；visible表示Excel程序窗口的可见性，如果为True，则显示Excel程序窗口，否则隐藏Excel程序窗口（即后台运行），缺省时为True；add_book表示启动Excel程序后是否新建工作簿，如果为True，则新建一个工作簿，否则不新建工作簿，缺省时为True。

启动Excel程序后，须打开工作簿，可以用下面代码实现。

workbook = app.books.open(bookname)

  其中，workbook表示Book对象，bookname表示要打开的工作簿名，即Excel文件名。打开工作簿后，须打开工作表，可以用下面两种方法实现。

worksheet = workbook.sheets[index]
worksheet = workbook.sheets[sheetname]

  其中，worksheet表示Sheet对象；index表示工作表在工作簿中的索引，按工作表打开的顺序从后往前排序，最后一个打开的工作表索引为0；sheetname表示工作表名。
  打开工作表后，就可以使用range()方法读取指定区域内的数据了。例如，读取单元格A1中的数据，可以用下面代码实现。

worksheet.range(‘A1’).value

  如果想要读取单元格区域的数据，如A1到A10一列的数据、A1到G1一行的数据、A1到G10的10行7列的数据，可以用下面代码实现。

#读取A1到A10一列的数据
value1 = worksheet.range(‘A1:A10’).value
#读取A1到G1一行的数据
value2 = worksheet.range(‘A1:G1’).value
#读取A1到G10的10行7列的数据
value3 = worksheet.range(‘A1:G10’).value

  其中，value1和value2为一维列表，value2为二维列表。
  如果不能确定工作表中具体区域的范围，可以使用expand()方法读取指定区域后所有的数据。例如，读取整个工作表的数据，可以用下面代码实现。

worksheet.range(‘A1’).expand().value

  读取数据完成后，应通过Book对象调用close()方法关闭工作簿，以及App对象调用quit()方法退出Excel程序。

【温馨提示】：

S还可以直接使用单元格名读取区域的数据，上述读取数据的操作也可以用下面的代码实现。
worksheet[‘A1’].value
worksheet[‘A1:A10’].value
worksheet[‘A1:G1’].value
worksheet[‘A1:G10’].value

【例1】 读取成绩表。
【问题分析】本例读取并输出成绩表中序号为3、序号为1～5和所有学生的成绩，以及所有学生的姓名，“初三(1)班成绩表.xlsx”的内容如图11-2所示。从图11-2可以看出，序号为3的学生成绩的区域为第4行，即A4～J4；序号为1～5的学生成绩的区域为第2行到第6行，即A2～J6；所有学生成绩的区域为第1行后的整个表格，即A2后的所有区域；所有学生姓名的区域为第3列，即C2～C11。

【参考代码】

import xlwings									#导入xlwings库
app = xlwings.App(visible=False, add_book=False)#启动Excel程序
#打开工作簿
book = app.books.open('初三成绩表\\初三(1)班成绩表.xlsx')
sheet = book.sheets['sheet1']				#打开工作表
print('序号为3的学生成绩：\n', sheet.range('A4:J4').value)
print('序号为1～5的学生成绩：\n', sheet.range('A2:J6').value)
print('所有学生的成绩：\n', sheet.range('A2').expand().value)
print('所有学生的姓名：\n', sheet.range('C2:C11').value)
book.close()						#关闭工作簿
app.quit()						#退出Excel程序

【运行结果】 程序运行结果如图所示。

2．写入Excel工作表

将数据写入Excel工作表很简单，只需将数据赋值给对应的区域，然后通过Book对象调用save()方法保存工作簿即可。例如，将列表[1,2,3,4,5]添加到工作表worksheet中的第一行，可以用下面代码实现。

worksheet.range(‘A1’).value = [1,2,3,4,5]
workbook.save()

此外，还可以用add()方法新建工作簿和工作表写入数据，可以用下面代码实现。

workbook = app.books.add() #新建工作簿
worksheet = workbook.sheets.add(sheetname) #新建工作表
workbook.save(bookname) #命名并保存工作簿

  这里需要注意的是，使用add()方法新建工作簿时无法命名，须在使用save()方法保存工作簿时命名。此外，新建工作簿时会默认新建一个“Sheet1”工作表。

【例2】 新建文件并写入成绩表。
【问题分析】首先新建工作簿，并在其中新建工作表“初三(1)班”；然后读取“初三(1)班成绩表.xlsx”的内容，将其写入“初三(1)班”工作表中；接着将新建工作簿命名为“成绩表.xlsx”并保存，最后关闭工作簿和退出Excel程序。
【参考分析】

import xlwings					#导入xlwings库
app = xlwings.App(visible=False, add_book=False)#启动Excel程序
book1 = app.books.add()				#新建工作簿book1
sheet1 = book1.sheets.add('初三(1)班')		#新建工作表sheet1
#打开工作簿book2
book2 = app.books.open('初三成绩表\\初三(1)班成绩表.xlsx')
sheet2 = book2.sheets['sheet1']			#打开工作表sheet2
#读取sheet2中的内容，并赋值给value_list
value_list = sheet2.range('A1').expand().value
#将value_list写入sheet1
sheet1.range('A1').value = value_list
book1.save('成绩表.xlsx')				#命名并保存book1
book1.close()					#关闭book1
book2.close()					#关闭book2
app.quit()				           #退出Excel程序

【运行结果】 运行程序，将会在当前文件夹中生成“成绩表.xlsx”文件，其内容如图所示。

---

🎶三、批量处理数据

---

  利用Python可以更高效地对多个Excel工作簿进行批量操作，如批量替换单元格数据、批量计算行或列数据的和等。批量处理数据时，可以先将工作簿中的所有数据提取出来进行处理，然后再将结果写入工作表，避免频繁读取，降低效率。
  【例 3】 批量计算每个班级的成绩表中每个学生的总成绩，并写入成绩表中。
  【问题分析】 “初三成绩表”文件夹中只包含3个文件，分别为“初三(1)班成绩表.xlsx”“初三(2)班成绩表.xlsx”和“初三(3)班成绩表.xlsx”（内容见图11-2）。想要计算每个工作簿中每个学生总成绩，须首先获取该文件夹中的文件列表；然后遍历文件列表，判断文件扩展名是否不为“.xlsx”或文件名是否以“~$”开头，如果是，则结束本次循环，否则读取该文件中的成绩，返回二维成绩列表，计算每个一维数据的和（即总成绩），添加到列表中；最后将成绩列表写入该文件。

【温馨提示】：因为Excel程序在打开一个工作簿时会生成一个文件名以“~$”开头的临时文件，如果Excel程序非正常退出，该临时文件会在文件夹中保留下来，所以须判断文件名是否以“$”开头，避免处理此类临时文件。

【参考代码】

import xlwings					#导入xlwings库
import os					#导入os库
app = xlwings.App(visible=False, add_book=False)#启动Excel程序
path = os.chdir('初三成绩表')			#设置当前工作路径
folder = os.listdir(path)			#获取当前工作路径下所有文件列表
for file in folder:					#遍历文件列表#如果文件扩展名不为“.xlsx”或文件名以“~$”开头if ('.xlsx' not in file) or (file.startswith('~$')):continue					#结束本次循环book = app.books.open(file)			#打开工作簿sheet = book.sheets['Sheet1']			#打开工作表sheet.range('K1').value = '总分'			#在单元格K1写入“总分”#获取单元格A2后所有数据，并返回二维成绩列表score_list = sheet.range('A2').expand().valuefor index, score in enumerate(score_list):	#遍历二维成绩列表total = sum(score[3:])				#计算总成绩#将总成绩添加到每个学生一维成绩列表中score_list[index].append(total)sheet.range('A2').value = score_list	#将二维成绩列表写入工作表book.save()					#保存工作簿book.close()					#关闭工作簿app.quit()					#退出Excel程序

【运行结果】 运行程序，“初三成绩表”文件夹中的3个文件都会添加一列，即总分。其中，“初三(1)班成绩表.xlsx”的内容如图所示。

---

🎶 四、合并多个Excel工作表

---

  Python可以将多个工作簿中的数据快速合并为一个工作表。
【例 4】 合并班级成绩表，并按总成绩降序排名。
【问题分析】“ 本例题将“初三成绩表”文件夹中的3个文件“初三(1)班成绩表.xlsx”“初三(2)班成绩表.xlsx”和“初三(3)班成绩表.xlsx”合并，并按总成绩排名保存到新建的“年级排名表.xlsx”的“初三排名表”工作表中。
  首先获取“初三成绩表”文件夹中的文件列表；接着遍历文件列表，判断文件扩展名是否不为“.xlsx”或文件名是否以“~$”开头，如果是，则结束本次循环，否则读取文件第一行表头header，以及该文件中的成绩，并将返回的二维成绩列表添加到年级成绩列表all_list中；然后对all_list按总成绩进行降序排序，并循环在每个学生成绩中添加年级排名；再新建工作簿和工作表，将header、all_list和序号列表写入新建的工作表；最后保存和关闭工作簿，以及退出Excel程序。

【参考代码】

import xlwings					#导入xlwings库
import os					#导入os库
app = xlwings.App(visible=False, add_book=False)#启动Excel程序
path = os.chdir('初三成绩表')			#设置当前工作目录
folder = os.listdir(path)			#获取当前工作目录下所有文件列表
all_list = []					#定义年级成绩列表
for file in folder:					#遍历文件列表#如果文件扩展名不为“.xlsx”或文件名以“~$”开头if ('.xlsx' not in file) or (file.startswith('~$')):continue				                 #结束本次循环book = app.books.open(file)			  #打开工作簿booksheet = book.sheets['Sheet1']			  #打开工作表sheetheader = sheet.range('A1:K1').value		   #获取表头all_list[i].append(i+1)
book_new = app.books.add()		#新建工作簿book_new
sheet_new = book_new.sheets.add('初三排名表')#新建工作表sheet_new
header.append('年级排名')			#在表头中添加“年级排名”
#将表头写入sheet_new
sheet_new.range('A1').value = header
#将年级成绩列表写入sheet_new
sheet_new.range('A2').value = all_list
#生成1～len(all_list)+1的数字列表
num_list = list(range(1, len(all_list) + 1))
#将数字列表作为序号列写入sheet_new
sheet_new.range('A2').options(transpose=True).value=num_list
book_new.save('年级排名表.xlsx')		#保存book_new
book_new.close()				#关闭book_new
book.close()				#关闭book
app.quit()				#退出Excel程序

【运行结果】 运行程序，将会在“初三成绩表”文件夹中创建“年级排名表.xlsx”文件，其内容如图所示。

---

🎶 五、拆分Excel工作表

---

  Python还可以将一个工作表按一定的规则快速拆分为多个工作表。
【例 5】 拆分年级排名表，按班级保存每个班学生成绩。
【问题分析】 本例题将“初三成绩表”文件夹中“年级排名表.xlsx”按班级拆分为3个文件，分别为“初三(1)班排名表.xlsx”“初三(2)班排名表.xlsx”和“初三(3)班排名表.xlsx”。
  首先打开“年级排名表.xlsx”中的“初三排名表”工作表，获取表头header和二维年级成绩列表score_list；接着遍历score_list，根据学号（“202101”开头的为一班学生，“202102”开头的为二班学生，“202103”开头的为三班学生）按班级将学生成绩添加到二维班级成绩列表class_list中；然后循环3次，在循环中新建工作簿和工作表，并将header、class_list中对应的元素和序号列表写入新建的工作表，并命名、保存和关闭新建的工作簿；最后关闭“年级排名表.xlsx”工作簿，以及退出Excel程序。

【参考代码】

import xlwings					#导入xlwings库
import os					#导入os库
app = xlwings.App(visible=False, add_book=False)#启动Excel程序
path = os.chdir('初三成绩表')			#设置当前工作目录
folder = os.listdir(path)			              #获取当前工作目录下所有文件列表
book = app.books.open('年级排名表.xlsx')		#打开工作簿book
sheet = book.sheets['初三排名表']			#打开工作表sheet
header = sheet.range('A1:L1').value			#获取表头
#获取二维年级成绩列表score_list
score_list = sheet.range('A2').expand().value
class_list = [[], [], []]		                            #定义二维班级成绩列表class_list
#遍历score_list，根据学号按班级将学生成绩添加到class_list中
for score in score_list:num = str(score[1])if '202101' in num:class_list[0].append(score)elif '202102' in num:class_list[1].append(score)elif '202103' in num:class_list[2].append(score)
for i in range(3):						#循环3次book_new = app.books.add()				#新建工作簿book_new#打开默认新建的工作表sheet_newsheet_new = book_new.sheets['Sheet1']#将表头写入sheet_newsheet_new.range('A1').value = header#将class_list的第i个元素写入sheet_newsheet_new.range('A2').value = class_list[i]num_list = list(range(1, len(class_list[i]) + 1))#将数据列表作为序号列写入sheet_newsheet_new.range('A2').options(transpose=True).value = num_list#定义新建的工作簿名filename = '初三({})班排名表.xlsx'.format(i + 1)book_new.save(filename)				#命名并保存book_newbook_new.close()					#关闭book_new
book.close()						#关闭book
app.quit()						#退出Excel程序

【运行结果】 运行程序，将会在“初三成绩表”文件夹中创建3个文件，分别为“初三(1)班排名表.xlsx”“初三(2)班排名表.xlsx”和“初三(3)班排名表.xlsx”。其中，“初三(1)班排名表.xlsx”的内容如图所示。

  本任务根据出货清单模板，将水果出货记录按日期分类生成多个水果出货清单。
  完成本任务，须首先打开“水果出货记录.xlsx”工作簿book中的“Sheet1”工作表sheet，读取出货信息，并保存到列表info_list中，遍历info_list，将信息保存到以出货日期为键，其他信息的列表（包括计算的金额）为值的字典data中；然后打开“水果出货清单.xlsx”工作簿book_day中的“模板”工作表sheet_sample，遍历data的键，使用copy()方法复制sheet_sample并以出货日期命名赋给sheet_new，再循环将data中以出货日期为键的值写入sheet_new相应的区域；最后保存并关闭book_day，关闭book，以及退出Excel程序。

结束语🥇

以上就是机器学习
持续更新机器学习教程，欢迎大家订阅系列专栏🔥机器学习
你们的支持就是曼亿点创作的动力💖💖💖