Python+Excel+VBA实现批量自助生成名牌

需求

这次是hrbp小姐姐需要找我批量生成他们新部门的员工名牌.如果让设计一个个去改名字,几个人倒还好,几十个人可以忍受,几百上千号人的话,设计小姐姐就要崩溃.所以需要我写一个自助生成名牌的程序,需求我列了一下

1. 可以自己输入:名字,岗位,部门
2. 一键生成所有输入信息的名牌(也就是图片)

像之前一样,设计小姐姐会提供给我

1. 名牌背景图
2. 字体&范本

在Excel制作名牌

方法与这篇一样👇

Excel做数据海报

你们要学会举一反三

批量导出图片

1. 全VBA

一开始想纯粹在VBA中实现,发现有个ChartObject对象可以提取Excel中的图片,然鹅…

这个方法总是报错,每次循环加上1秒的等待都不行.代码如下

Sub Dan()
    Dim i%, iRow%, t!
    Dim Shp As Shape, Img As Shape, Cht As ChartObject
    t = Timer
    With ActiveSheet
        iRow = .Cells(.Rows.Count, 1).End(3).Row '常规探底
        For i = 2 To iRow
            For Each Shp In .Shapes
            	If Shp.Name <> "Pic" And Shp.Name <> "PicOne" Then Shp.Delete
        	Next '开始前遍历对象,删除多余内容
        	
        	'填入文本框引用信息
            .Cells(2, 5).Value = .Cells(i, 1).Value
            .Cells(2, 6).Value = .Cells(i, 2).Value
            .Cells(2, 7).Value = .Cells(i, 3).Value
            
            Set Shp = .Shapes(1)
            Shp.CopyPicture '将更新完信息的组合形状粘贴为图
            .Paste
            Application.Wait Now + TimeValue("00:00:01") '由于图片经常出现加载不到剪切板,故加一秒等待
            Application.CutCopyMode = 0
            For Each Shp In .Shapes
                If Shp.Name <> "Pic" And Shp.Name <> "PicOne" Then
                    Set Img = Shp
                    Img.Copy
                    Exit For
                End If
            Next
            Application.Wait Now + TimeValue("00:00:01")
            Set Cht = .ChartObjects.Add(0, 0, Img.Width, Img.Height) '关键步骤:增加ChartObject对象
            Cht.Chart.Paste '将复制的Img粘贴进ChartObject
            Cht.Chart.Export ThisWorkbook.Path & "\" & .Cells(2, 8).Value & ".jpg" '导出为图片
            Cht.Delete '删除对象,为下一次循环准备
        Next
    End With
    MsgBox Format(Timer - t, "0.00s")
End Sub

这份代码的成功率极低,5次有1次可以跑完,而且还不完整,所以我换了一种需要操作的方法,从模型v1升级到模型v2

2. VBA+压缩包

这种方法是

1. 首先使用VBA在Excel中批量生成图片(也就是v1版本的CopyPicture)
2. 其次利用网上的从压缩包提取图片方法,批量提取图片,步骤请参照百度经验

第一步的代码如下

Sub Dan()
    Dim i%, iRow%, t!
    Dim Shp As Shape, Img As Shape, Cht As ChartObject
    t = Timer
    With ActiveSheet
        iRow = .Cells(.Rows.Count, 1).End(3).Row 
        For Each Shp In .Shapes
           	If Shp.Name <> "Pic" And Shp.Name <> "PicOne" Then Shp.Delete
       	Next         
        For i = 2 To iRow
            .Cells(2, 5).Value = .Cells(i, 1).Value
            .Cells(2, 6).Value = .Cells(i, 2).Value
            .Cells(2, 7).Value = .Cells(i, 3).Value
            Set Shp = .Shapes(1)
            Shp.CopyPicture 
            .Paste
        Next
    End With
    MsgBox Format(Timer - t, "0.00s")
End Sub

有没有发现实际上就是v1版本的代码稍作修改
然后就是按照百度经验所说找到media文件夹,批量解压图片.由于我是形状组合,格式不是直接的jpg,所以我用美图看看批量转换图片格式完成.步骤稍微有点复杂,所以我升级到了v3

3. Python+Excel

由于经常提取Excel里面的图片,会用到pillow模块,这次就直接用这些代码拼凑出在Excel更新组合形状并导出图片了

1. 用pywin32模块实现数据更新
2. 用pillow模块导出图片
3. 循环1,2

代码如下

from win32com.client import Dispatch
from PIL import ImageGrab as ig 
from time import sleep
import sys
import os

#1 参数
name_excel = '一键生成图片v3.xlsx' # 自助写名字和其他信息的表格
list_img = ['a','b','c'] #每个名字需要生成3张不同风格的名牌,需要提前在Excel命名好组合形状名字,一次操作即可

#2 自定义函数
def frozen_dir(): #打包exe专用的相对路径函数
    if hasattr(sys,'frozen'):
        return os.path.dirname(sys.executable)
    return os.path.dirname(__file__)

def folder_isexist(uDir): 
    #如果存在文件夹,则返回原路径
    #如果不存在文件夹,则创建该目录并返回
    if os.path.exists(uDir):
        return uDir
    else:
        os.mkdir(uDir)
        return uDir

def main(uExcel,uFolder,uImg):
    strow = 2 #从第2行开始循环
    uFolder = folder_isexist(uFolder)
    app = Dispatch('excel.application')
    app.visible = 1
    wkb = app.workbooks.open(uExcel)
    sht = wkb.sheets[0]
    while True:
        if sht.cells(strow,1).value is None: #单元格内容为空的时候即循环完毕(用len会报错) 
            break #退出循环
        else:
            sht.cells(2,5).value = sht.cells(strow,1).value
            sht.cells(2,6).value = sht.cells(strow,2).value
            sht.cells(2,7).value = sht.cells(strow,3).value
            for imgname in uImg: #多图片
                try_cnt = 0 #错误尝试次数限制
                while True:
                    try:
                        sht.shapes(imgname).copy() #复制形状
                        sleep(1) #剪切板经常捕捉不到,所以要延迟1秒
                        path_img = '{0}\\{1}_{2}.jpg'.format(uFolder,imgname,sht.cells(strow,1).value)
                        img = ig.grabclipboard() #从剪切板获取图片
                        img.save(path_img) #保存图片
                        print("::::::::::::::::::::Saved:::::::::::::::::::%s" % path_img)
                        break #没有报错则退出循环
                    except Exception as e:
                        print(e)
                        try_cnt += 1
                        if try_cnt > 3: #尝试三次则退出
                            print('Time Out')
                            break
            strow += 1
    wkb.Save()
    wkb.Close(0)
    pass

#3 执行
path_excel = '{0}\\{1}'.format(frozen_dir(),name_excel)
folder_save = '{0}\\pic'.format(frozen_dir())
main(path_excel,folder_save,list_img)

Excel里面需要一些提前操作

1. 固定文字位置,文本框链接到单元格(见上面👆海报推文)
2. 定义好组合名称(本例为a,b,c)
   
   运行Python代码就会自动在当前文件夹下批量生成名牌图片,最后pic文件夹下效果如图所示(实际有2个名字x3张海报)
   

打包

由于小姐姐电脑上不会提前给你安装Python,作为直男的我肯定是不可能手把手教小姐姐安装Python的,所以我决定打包让她啥也不用安装就可以直接运行.这个要用到pyinstaller模块

具体可以先参考站内博客(后续我再写一份自己的)

python 打包exe文件

这样双击exe就可以运行当前目录下的Excel文件,并在当前目录下创建一个Pic文件夹,导出所有图片,如上