用Python手撕一个批量填充数据到excel表格的工具，解放双手！
共 11475字，需浏览 23分钟
·
2021-08-02 09:46
Hi~大家好！
今天这篇文章是根据批量填充数据的进阶版。基础版本就一段很简单的代码。虽然简单，但如果这个模板或者数据发生变化，还是要改来改去的，所以本文就在基础版本上进行改进，只需要动动鼠标就可以填充大量数据到Excel工作表中。
文末附完整源码、示例文件和工具获取方式！
GUI界面设计
在开始设计GUI界面时，要明确我们需要实现什么功能，可以先设计出图纸，再动手去写代码！本文根据需求，最后得出以下GUI界面图纸：
通过 打开文件按钮选择数据文件或者在输入框中输入数据文件文件路径，但只支持csv、xlsx、xls格式的文件，并把数据文件中的列标题传入选择或输入数据列标题框中。通过 打开模板按钮选择模板文件或者在输入框中输入模板文件的路径，只支持xlsx和xls格式的文件，并把模板表格中的空白单元格坐标传入选择或输入单元格坐标框中。通过 选择或输入数据列标题框选择要填充的数据列。通过 选择或输入单元格坐标框选择各个数据列填充的位置。通过 继续按钮把数据列标题和单元格坐标存储入列表中。通过 开始填充按钮选择保存路径和输入文件名称，最后开始填充数据。通过 信息展示框展示操作信息。当数据列标题和单元格坐标选择错误时，可以通过 删除元素按钮删除列表中的错误数据。通过 退出程序按钮直接结束工具的运行
根据图纸和基本功能思路最后得出以下代码：
# 主题设置
sg.theme('BrownBlue')
# 布局设置
layout = [
# 选择数据文件框和按钮 file_types 后面跟的是支持的文件格式，传入的是元组，元组中只包含一个元素时，需要在元素后面添加逗号
[sg.Text('请选择文件:', font=("微软雅黑", 12)),
sg.InputText(key='please_select_file', size=(78, 1), font=("微软雅黑", 10), enable_events=True),
sg.FileBrowse('打开文件', file_types=(("Text Files", "*.csv*"), ("Text Files", "*.xls*")), font=("微软雅黑", 12))],
# 选择模板框和按钮 xls包括xlsx格式
[sg.Text('请选择模板:', font=("微软雅黑", 12)),
sg.InputText(key='template', size=(78, 1), font=("微软雅黑", 10), enable_events=True),
sg.FileBrowse('打开模板', file_types=(("Text Files", "*.xls*"),), font=("微软雅黑", 12))],
[sg.Text('请选择或输入数据列标题:', font=("微软雅黑", 12)),
sg.Combo(values='', tooltip='请选择或输入数据列:', font=("微软雅黑", 10), auto_size_text=True,
size=(15, 10), key='value'),
sg.Text('请选择或输入单元格坐标:', font=("微软雅黑", 12)),
sg.Combo(values='', tooltip='请选择或输入单元格坐标:', font=("微软雅黑", 10), auto_size_text=True,
size=(15, 10), key='keys'),
sg.Button('继续', font=("微软雅黑", 12)),
sg.Button('开始填充', font=("微软雅黑", 12))],
[sg.Text('信息展示:', justification='center')],
[sg.Output(size=(100, 10), font=("微软雅黑", 10))],
[sg.Text('', font=("微软雅黑", 12), size=(0, 0)), sg.Button('删除元素', font=("微软雅黑", 12)),
sg.Text('', font=("微软雅黑", 12), size=(62, 0)), sg.Button('退出程序', font=("微软雅黑", 12))]
]
# 创建窗口
window = sg.Window('数据填充工具', layout, font=("微软雅黑", 12), default_element_size=(80, 1))
# 事件循环
while True:
# 退出按钮
event, values = window.read()
if event in (None, '退出程序'):
break
window.close()
界面效果：
事件循环设置
打开文件按钮只要实现的是传入数据文件，然后获取数据文件的标题行并传入对应的框中：
if event == 'please_select_file':
fileName = values['please_select_file']
if os.path.exists(fileName):
# 因为pandas读取文件因格式而异，所以需要判断
if fileName.split('.')[-1] == 'csv':
df = pd.read_csv(fileName, encoding='utf-8')
# 获取标题行
keys = df.columns.to_list()
# 传入相应的的框
window["value"].Update(values=keys, font=("微软雅黑", 10), size=(15, 8))
elif fileName.split('.')[-1] == 'xls' or 'xlsx':
df = pd.read_excel(fileName, encoding='utf-8')
keys = df.columns.to_list()
window["value"].Update(values=keys, font=("微软雅黑", 10), size=(15, 8))
else:
print('文件格式不正确，请重新选择文件！')
sg.popup('文件格式不正确，请重新选择文件！')
else:
print('文件不存在，请重新选择文件！')
sg.popup('文件不存在，请重新选择文件！')
打开模板按钮只要实现的是传入模板文件，以及获取模板表格中的空白表格坐标；其中，之所以使用openpyxl打开模板文件，是因为后面是openpyxl进行填充数据的。而列表推导式中，第一个for循环取出单元格对象，第二个循环把对象内容转换为字符串格式，通过正则表达式取出单元格坐标，最后通过if判断单元格内容是否为None，是的话就传入列表中,需要注意的是:如果是合并单元格的，会被分开计算为单个单元格。
if event == 'template':
fileName = values['template']
if os.path.exists(fileName):
wb = openpyxl.load_workbook(fileName)
sheets = wb.sheetnames # 获取全部sheet
ws = wb[sheets[0]] # 默认获取第一个工作表
data_row = []
# 列表推导式，获取模板表格中的空白表格坐标
data = [data_row.append(i) for row in ws.rows for i in
re.findall("<.*? '.*?'.([A-Z]+\d+)>", str("{}".format(row))) if ws[str(i)].value == None]
window["keys"].Update(values=data_row, font=("微软雅黑", 10), size=(15, 8))
else:
print('文件不存在，请重新选择文件')
sg.popup('文件不存在，请重新选择文件')
继续选择按钮只要实现的是：获取用户在选择或输入数据列标题框和选择或输入单元格坐标框一次次输入的内容，存储到valuelist和keyslist列表中：
if event == '继续':
if values['value'] and values['keys']:
a = values['value']
b = values['keys']
valuelist.append(a)
keyslist.append(b)
print(f'选择: {a}:{b} 完毕，请继续；或者点击开始进行数据填充！')
else:
print('数据列标题或者单元格坐标未选择！')
sg.popup('数据列标题或单元格坐标未选择！')
判断两个列表中的内容是否存在，存在就把数据传入Datainput函数中，files是一个保存路径弹窗，先选择路径，然后在输入文件名称，最后开始填充：
if event == '开始填充':
if len(keyslist) and len(valuelist) != 0:
files = sg.popup_get_folder('请选择存储路径和输入文件名称：')
Datainput(files, valuelist, keyslist)
else:
print('数据列标题或者单元格坐标未选择！')
sg.popup('数据列标题或单元格坐标未选择！')删除元素按钮只要用python自带函数remove来删除列表中的元素，为了防止元素不存在而导致报错，这里加一个条件判断：
if event == '删除元素':
if values['value'] or values['keys']:
a = values['value']
b = values['keys']
if a in valuelist:
valuelist.remove(a)
print('删除成功！')
else:
print('表格列标题不存在!')
sg.popup('表格列标题不存在!')
if b in keyslist:
keyslist.remove(b)
print('删除成功！')
else:
print('单元格坐标不存在!')
sg.popup('单元格坐标不存在!')数据填充函数
Datainput函数接收通过开始填充按钮传入的列标题和单元格坐标,并开始填充数据:
def Datainput(files, key, value):
for p in range(len(df[key[0]])): # 计算excel工作表其中一列数据的数目，然后进行遍历这个数值
sheet = wb.copy_worksheet(ws) # 复制原有工作表
sheet.title = str(df[key[0]][p]) # 设置工作表名称
for i in range(len(key)): # 计算excel工作表中标题数目
sheet[value[i]].value = df[key[i]][p] # 写入数据
print('正在填充，请稍等！')
# 弹窗进度条
sg.one_line_progress_meter('正在填充，请稍等！', p + 1, 100, orientation='h',
bar_color=('#F47264', '#FFFFFF'))
wb.save(files)
print('已完成...')
sg.popup('已完成！') # 弹窗
打包运行
打包可以通过pyinstaller库，安装只需要pip命令即可！安装后在命令行窗口cd到文件所在的文件目录中，最后用下面命令进行打包。
pyinstaller -F -w 名称.py
打包时可能会报错：
报错源于一个hook-sqlalchemy.py文件，一个简单的解决方法是找到它直接回收删除它（最后暂未发现删除它对打包后的exe文件有什么影响），等打包完成后在放回去即可：
最终效果展示。没录到鼠标，后面选择完单元格坐标后，是点击了继续，选择完成后是点击了开始填充；如果你选择错误标题和单元格坐标，可以点击删除元素按钮删除；打开表格时有点卡，后面出现的两个弹窗直接点击“是”和“关闭”即可：
结语
把一个简单的脚本制作成一个可运行的工具，代码量变多了，但用起来方便了很多，只要是能节省时间，解放双手（虽然还要动手），避免重复性、机器式操作。
以上便是本文全部内容，代码在测试过程中暂未发现什么bug，可正常运行。如果你感兴趣的话，点个赞和在看支持一下呗。
完整源码、示例文件和工具可在公众号后台发送填充数据获取！
3、谷歌程序员少输一个“&”，差点让全球Chrome笔记本变砖
今天因为您的点赞和在看，让我元气满满！