用python的openpyxl库实现对excel工作表的自动化操作
用python的openpyxl库读取excel工作表，批量建立工作表，批量修改工作表标题，批量设置单元格样式，批量调整打印设置。
文章目录
一、提出问题
因工作需要需制订公交线路站台的调查方案，现有公交线路站点的具体信息。目标需求是制作一个excel工作簿，工作簿约包含120个工作表，每个工作表包含要调查公交线路站点信息情况，同时，每个工作表以该线路的名称来命名以方便查询。如下图所示。
该工作簿约包含120个工作表，如果重复性地进行新建、复制、粘贴、查找、复制、粘贴，太无聊，太浪费时间了，如何才能快速方便的完成这项工作，解决机械性的重复操作问题，提高工作效率呢？
二、分析问题
1.公交线路站点信息表
提供的公交线路站点信息如下表所示。
表 datachengdu_station.xlsx
提供的线路站点信息表如图所示，从中可以看出，表头主要包含线路名称、经度、纬度、站点名称和站序5个字段，线路包含上行和下行两个方向，站序升序排列。
2.调查线路站点信息表
分析调查线路信息表可知，每张调查表都具有相同的头部和尾部结构，只是中间部分不同，需要把具体需要调查的线路站点信息写入中间部分，同时,对工作表的标题进行修改。
三、解决问题
1.openpyxl库
好在前段时间看了《Python编程快速上手 让繁琐工作自动化》一书，里面讲到了如何用python处理excel表格。本文主要用到openpyxl，一个用于读取和写入Excel 2010 xlsx/xlsm文件的python第三方库。Python本身是不能对excel进行读写的，但是借助于openpyxl就可以非常方便地对excel进行自动化操作。
2.程序的编写思路
程序的思路和代码顺序并没有直接关联，以下主要说明程序的思路。
首先，读取调查方案excel表中的线路名称，调查方案表如下图所示，并保存为一个列表；
其次，新建表格，修改表格标题，写入表头信息；对于调查方案中的每个线路名称，遍历线路站点表，找到对应线路的站点名称和站序，由于线路站点信息表是分上行和下行的，调查方案表格中只需要上行即可，分析线路站点表可知，站序是递增的，但当线路由上下变为下行时，站序会重新从1开始，这时加入一个判断，当站序不再递增时，停止写入，如此重复遍历判断，对新表逐一添加一行，写入一行，直至站点遍历结束；判断新表的最大行长度，在新表末尾写入表尾的内容、合并单元格、添加公式、设置填充颜色、调整行高列宽、设置字号加粗、设置边框、调整对齐方式、设置纸张大小横向打印、设置水平居中打印，重复打印标题，直至调查方案中的每条线路遍历结束。
最后，保存工作簿。
表 需调查线路汇总新方案.xlsx
3.运行结果
运行程序后，得到如下结果。
表 sample.xlsx
四、完整代码
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill, Border, Side, Font, Alignment
# 新建工作表
wb1 = Workbook()
# 加载工作表
wb2 = load_workbook(r'C:\Users\Desktop\pythonProject\需调查线路汇总新方案.xlsx')
wb3 = load_workbook(r'C:\Users\Desktop\pythonProject\datachengdu_station.xlsx')
# 获取活动单元表
wb2s = wb2.active
title = []
a0 = wb2s.max_row
for i in range(2, a0 + 1):
t0 = wb2s.cell(row=i, column=1).value
t1 = str(t0)
title.append(t1)
wb3s = wb3.active
a = wb3s.max_row
# 批量创建工作表，并写入模板表格中的数据
for i in title:
# 创建表格
ws = wb1.create_sheet(i)
# 添加表头
ws.append(['序号', '站台名', '类型', '站牌问题', '', '', '', '', '', '', '站台问题'])
ws.append(['', '', '', '破损', '有污渍', '有牛皮廯', '信息不清晰', '信息缺失', '无警示标志', '无投诉电话', '乘车秩序混乱', '地面有垃圾', '共享单车乱停放'])
# 合并单元格
ws.merge_cells('A1:A2')
ws.merge_cells('B1:B2')
ws.merge_cells('C1:C2')
ws.merge_cells('D1:J1')
ws.merge_cells('k1:M1')
# 调整标头样式
# 写入线路站点信息
m = 1
for j in range(1, a + 1):
b = wb3s.cell(j, 1).value
if b == i:
d = wb3s.cell(j, 6).value
c1 = wb3s.cell(j, 7).value
c2 = wb3s.cell(j + 1, 7).value
# print(c1, d)
ws.insert_rows(j + 3) # 插入一行
ws.cell(m + 2, 1).value = c1
ws.cell(m + 2, 2).value = d
m = m + 1
if int(c2) != int(c1) + 1:
break
# 计算现表长度
b = ws.max_row
# print(b)
# 添加表底
ws.append(['标准分值', '', '', 20, 10, 10, 10, 20, 10, 5, 10, 5, '不计入得分'])
ws.append(['问题笔数'])
ws.append(['分项得分'])
ws.append(['折算得分（百分制）'])
ws.append(['合计得分'])
# 合并单元格
# ws.merge_cells('A1:A2')
ws.merge_cells(start_row=b + 1, start_column=1, end_row=b + 1, end_column=3)
ws.merge_cells(start_row=b + 2, start_column=1, end_row=b + 2, end_column=3)
ws.merge_cells(start_row=b + 3, start_column=1, end_row=b + 3, end_column=3)
ws.merge_cells(start_row=b + 4, start_column=1, end_row=b + 4, end_column=3)
ws.merge_cells(start_row=b + 5, start_column=1, end_row=b + 5, end_column=3)
# 添加公式
for n in range(4, 13):
le = get_column_letter(n)
formula1 = "=SUM(" + le + "3:" + le + str(b) + ")"
formula2 = "=" + le + str(b + 1) + "*(1-" + le + str(b + 2) + "/$A" + str(b) + ")"
# print(formula2)
ws.cell(b + 2, n, formula1)
ws.cell(b + 3, n, formula2)
le = get_column_letter(n)
formula3 = "=D" + str(b + 3) + "/D" + str(b + 1) + "*100"
formula4 = "=SUM(E" + str(b + 3) + ":G" + str(b + 3) + ")/SUM(E" + str(b + 1) + ":G" + str(b + 1) + ")" + "*100"
formula5 = "=SUM(H" + str(b + 3) + ":I" + str(b + 3) + ")/SUM(H" + str(b + 1) + ":I" + str(b + 1) + ")" + "*100"
formula6 = "=SUM(D" + str(b + 4) + "*0.02+E" + str(b + 4) + "*0.03+H" + str(b + 4) + "*0.03)/0.08"
ws.cell(b + 4, 4, formula3)
ws.cell(b + 4, 5, formula4)
ws.cell(b + 4, 8, formula5)
ws.cell(b + 5, 4, formula6)
# 合并单元格
ws.merge_cells('E' + str(b + 4) + ':G' + str(b + 4))
ws.merge_cells('H' + str(b + 4) + ':I' + str(b + 4))
ws.merge_cells('D' + str(b + 5) + ':M' + str(b + 5))
# 设置单元格样式
# 设置填充颜色
orange_fill = PatternFill(fill_type='solid', fgColor="ffc000")
green_fill = PatternFill(fill_type='solid', fgColor="92d050")
blue_fill = PatternFill(fill_type='solid', fgColor="00b0f0")
yellow_fill = PatternFill(fill_type='solid', fgColor="ffff00")
for o in range(4, 13):
ws.cell(row=b + 3, column=o).fill = orange_fill
ws.cell(row=b + 4, column=4).fill = orange_fill
ws.cell(row=b + 4, column=5).fill = green_fill
ws.cell(row=b + 4, column=8).fill = blue_fill
ws.cell(row=b + 5, column=1).fill = yellow_fill
ws.cell(row=b + 5, column=4).fill = yellow_fill
# 设置字号，加粗等
font = Font(size=11, bold=True)
for y in range(1, 14):
for x in range(1, 3):
ws.cell(x, y).font = font
for z in range(b + 1, b + 6):
ws.cell(z, y).font = font
# 设置边框
thin = Side(border_style="thin", color="000000")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
for x in range(1, b + 6):
for y in range(1, 14):
ws.cell(x, y).border = border
# 设置对其方式
for x in range(1, b + 6):
for y in range(1, 14):
ws.cell(x, y).alignment = Alignment(horizontal='center', vertical='center', wrapText=True)
for x in range(3, b + 1):
ws.cell(x, 2).alignment = Alignment(horizontal='right', vertical='center')
# 设置行高列宽
ws.row_dimensions[1].height = 15
ws.row_dimensions[2].height = 30
ws.column_dimensions['B'].width = 22
ws.column_dimensions['M'].width = 11
# 设置A4纸横向
ws.set_printer_settings(paper_size=9, orientation='landscape')
# 设置水平居中打印，重复打印标题
ws.print_options.horizontalCentered = True
ws.print_titles_cols = 'A:M'
ws.print_title_rows = '1:2'
# 保存文件
wb1.save("sample.xlsx")
总结
利用python openpyxl库，对excel进行自动化操作，相对于一张表格接着一张表格的进行新建、复制、粘贴、查找、复制、粘贴，行机械性地进行重复操作，工作效率得到了极大地提高，几秒钟工作簿就做好了，成就感满满。