专栏首页Python与Excel之交用Python手撕一个批量填充数据到excel表格的工具,解放双手!

用Python手撕一个批量填充数据到excel表格的工具,解放双手!

Hi~大家好!

今天这篇文章是根据批量填充数据的进阶版。基础版本就一段很简单的代码。虽然简单,但如果这个模板或者数据发生变化,还是要改来改去的,所以本文就在基础版本上进行改进,只需要动动鼠标就可以填充大量数据到Excel工作表中。

GUI界面设计

GUI是用PySimpleGUI库创建的,安装命令直接用pip命令安装即可!

在开始设计GUI界面时,要明确我们需要实现什么功能,可以先设计出图纸,再动手去写代码!本文根据需求,最后得出以下GUI界面图纸:

GUI界面中按钮和框的一些功能:

  • 通过打开文件按钮选择数据文件或者在输入框中输入数据文件文件路径,但只支持csvxlsxxls格式的文件,并把数据文件中的列标题传入选择或输入数据列标题框中。
  • 通过打开模板按钮选择模板文件或者在输入框中输入模板文件的路径,只支持xlsxxls格式的文件,并把模板表格中的空白单元格坐标传入选择或输入单元格坐标框中。
  • 通过选择或输入数据列标题框选择要填充的数据列。
  • 通过选择或输入单元格坐标框选择各个数据列填充的位置。
  • 通过继续按钮把数据列标题和单元格坐标存储入列表中。
  • 通过开始填充按钮选择保存路径和输入文件名称,最后开始填充数据。
  • 通过信息展示框展示操作信息。
  • 当数据列标题和单元格坐标选择错误时,可以通过删除元素按钮删除列表中的错误数据。
  • 通过退出程序按钮直接结束工具的运行

根据图纸和基本功能思路最后得出以下代码:

# 主题设置
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('文件不存在,请重新选择文件')

继续选择按钮只要实现的是:获取用户在选择或输入数据列标题框选择或输入单元格坐标框一次次输入的内容,存储到valuelistkeyslist列表中:

 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,可正常运行。如果你感兴趣的话,点个赞和在看支持一下呗。

本文分享自微信公众号 - Python与Excel之交(Yi-Python-Excel),作者:锋小刀

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-06-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 别人还在一个一个的填表格,而我已经用python写了个批量填充数据的自动化脚本,让它处理了上百份表格

    在工作中,我们经常同word、excel、ppt打交道,而excel用的应该是最多的。不知道大家有没有一填就是几百上千份表格的经历,那种感觉就像个机器人一样做着...

    Python与Excel之交
  • Excel表格中最经典的36个小技巧,全在这儿了

    技巧1、单元格内强制换行 技巧2、锁定标题行 技巧3、打印标题行 技巧4、查找重复值 技巧5、删除重复值 技巧6、快速输入对号√ 技巧7、万元显示 技巧8、隐藏...

    统计学家
  • Python办公自动化|从Excel到Word

    在前几天的文章中我们讲解了如何从Word表格中提取指定数据并按照格式保存到Excel中,今天我们将再次以一位读者提出的真实需求来讲解如何使用Python从Exc...

    刘早起
  • 太秀了!用 pandas 搞定 24 张 Excel 报表

    大家的关注点主要是如何循环遍历表格、如何用Pandas批量处理,当然,还有在996的压迫下如何提效(来挤出更多摸鱼时间)。

    Python数据科学
  • [PBI催化剂]国际水准,中国首款重量级PowerBIDeskTop外部工具问世

    SQLBI的工具,有兴趣的可了解下,需要点英文阅读能力:https://www.sqlbi.com/tools/analyze-in-excel-for-pow...

    Excel催化剂
  • [PowerBI]中国首款重量级PowerBIDeskTop外部工具问世

    过往的PowerBIDeskTop,它是一个独立的软件,不像Excel那样可以有二次开发的接口,但7月份更新PowerBIDeskTop后,已经开放了外部工具,...

    Excel催化剂
  • Excel,大多数人只会使用1%的功能

    最近,孩子在万门大学上学习了Excel,拿了几道题考考我。我自认为Excel功底还不错,从Office 2000一直用到现在的Office 365,窗口冻结、区...

    申龙斌
  • 数据地图系列1|热力地图(手工DIY)

    今天要跟大家分享数据地图第一讲——热力地图(手工DIY)! 昨天的推送已经跟大家分享过如何获取、导入矢量地图素材,今天教大家怎么编辑矢量素材,进而制作出一幅数据...

    数据小磨坊
  • 精美炫酷数据分析地图——简单几步轻松学会

    本篇文章开始教大家如何使用矢量素材在Excel、PPT中自定义精美的数据分析图表。 使用矢量素材制作数据分析报告其实并不难,最常见的就是形式就是使用矢量地图制作...

    数据小磨坊
  • Python+Excel数据分析实战:军事体能考核成绩评定(一)项目概况

    Excel(Microsoft office)是现在最常用的办公软件,主要涉及电子表格制作、数据处理、报表输出展示以及更高端的还有金融建模等;我们知道,在需要批...

    张国平
  • 懂Excel轻松入门Python数据分析包pandas(30):

    Excel 的表格是以灵活为主,也因此造就各种数据灾难现场。今天来看看怎么填补有意义的空白单元格,并且对应的 pandas 方法。

    咋咋
  • Python+Excel数据分析实战:军事体能考核成绩评定(一)项目概况

    Excel(Microsoft office)是现在最常用的办公软件,主要涉及电子表格制作、数据处理、报表输出展示以及更高端的还有金融建模等;我们知道,在需要批...

    张国平
  • Python办公自动化| word 表格转excel

    之前写过一篇 Python办公自动化 | 批量word报告生成工具 ,有小伙伴提出了逆向需求,即:从批量word中获取内容并写入excel,需求背景是汇总一些材...

    披头
  • 数据地图系列9|excel(VBA)数据地图!

    今天要跟大家分享的是数据地图系列的第九篇——excel(VBA)数据地图! 关于VBA在excel中的应用非常广泛,本篇仅仅是给出示例代码,不会对基础操作做太过...

    数据小磨坊
  • 商业数据分析从入门到入职(2)Excel基础

    Excel不仅仅只是存放数据的工具,其功能特别强大,甚至可以做出美观的仪表盘,如下:

    cutercorley
  • Python办公自动化|批量提取Excel数据

    今天我们来讲解一个比较简单的案例,使用openpyxl从Excel中提取指定的数据并生成新的文件,之后进一步批量自动化实现这个功能,通过本例可以学到的知识点:

    刘早起
  • 吐槽下Excel的十大不规范使用问题

    太多的人觉得每个月一个表格存放数据,一年12个月,一个工作薄文件里放12个工作表,然后还有大量的插件批量生成工作表,批量重命名工作表、工作表排序等一系列的功能来...

    Excel催化剂
  • 个人永久性免费-Excel催化剂功能第33波-报表形式数据结构转标准数据源

    原文在简书上发表,再同步到Excel催化剂微信公众号或其他平台上,文章后续有修改和更新将在简书上操作, 其他平台不作同步修改更新,因此建议阅读其他出处的文章时,...

    Excel催化剂
  • 个人永久性免费-Excel催化剂功能第52波-相同内容批量合并单元格,取消合并单元格并填充内容

    Excel催化剂已正式在千聊上发布视频,如查阅文章有理解障碍,不妨查看下视频,视频不定期更新,内容丰富,干货满满,有术亦有道!

    Excel催化剂

扫码关注云+社区

领取腾讯云代金券