美文网首页
[Python] 自动化办公 无模板批量生成Word请假条

[Python] 自动化办公 无模板批量生成Word请假条

作者: 半为花间酒 | 来源:发表于2021-01-16 21:44 被阅读0次

    转载请注明:陈熹 chenx6542@foxmail.com (简书号:半为花间酒)
    若公众号内转载请联系公众号:早起Python

    这篇文章能学到的主要内容:

    1. openpyxl 读取 Excel 文件
    2. python-docx 写入 Word 文件
    3. python-docx 各类样式的设计和调整

    练习数据:https://pan.baidu.com/s/1DauDEcu2QEfJd3xV5I4ADw 提取码:ftzn

    本案例同样适用于邀请函、证明等各类文书的制作

    需求描述

    你是公司的底层小虾米,前段时间收集了公司各个部门的请假信息汇总表如下:

    现在你需要根据表格中每个人的信息依次生成各自的请假条如下:

    需求的特殊性在于没有现成的模板,因此需要在代码中同时完成模板的制作和文字、段落样式设计

    (比较复杂的文字版面更建议直接设计好模板,以及确定好程序识别位置的定位符,详细内容可以参考之前的教程:批量生成合同 https://www.jianshu.com/p/3ee47f594d81

    逻辑分析

    整个需求的实现逻辑很简单,主要分为以下 2 步:

    1. 获取 Excel 文件中每一行的信息,提取 5 个参数;
    2. 结合获取的参数设计请假条样式并输出

    复杂的地方在于用代码输出请假条的过程,包括加粗、字号、下划线等等

    代码实现

    需求中的请假信息汇总表为 Leave.xlsx,已放链接供练习下载
    首先读取请假信息表,尝试获取除表头外实际信息的 5 个参数:

    from openpyxl import load_workbook
    
    path = r'C:\xxx' # 路径为Excel 文件所在的位置,可按实际情况更改
    workbook = load_workbook(path + r'\Leave.xlsx')
    sheet = workbook.active
    
    n = 0
    for row in sheet.rows:
        if n:
            for cell in row:
                print(cell.value)
        n += 1
    

    通过 for row in sheet.rowsfor cell in row 就可以迭代 Excel 中有数据的每个单元格了,循环体中加上对 n 的判断是为了跳过表头
    如果让输出更加直观可以稍微修改上面的代码:

    n = 0
    for row in sheet.rows:
        if n:
            for cell in row:
                print(cell.value, end=', ')
            print('')
        n += 1
    

    信息已经获取到了,但我们也发现申请日期是 datetime 形式,因此我们需要利用 datetime 库获取其中的日期成分,也可以转化为字符串后利用空格切片:

    n = 0
    for row in sheet.rows:
        if n:
            name = row[0].value
            department = row[1].value
            reason = row[2].value
            days = row[3].value
            date = str(row[4].value).split()[0]
            print(name, department, reason, days, date)
        n += 1
    

    提取到信息后就可以在循环体内建立个“请假条函数”,即把每条记录的 5 个变量传入函数,在函数中生成完整请假条并保存,即修改成如下形式:

    def request_for_leave(name, department, reason, days, date):
        pass
    
    n = 0
    for row in sheet.rows:
        if n:
            name = row[0].value
            department = row[1].value
            reason = row[2].value
            days = row[3].value
            date = str(row[4].value).split()[0]
            request_for_leave(name, department, reason, days, date)
        n += 1
    

    生成请假条中,首先导入依赖的库 python-docx 以及跟样式设置有关的相应方法:

    # 读取 Word 文件
    from docx import Document
    # 文件涉及段落样式修改
    from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
    # 文件涉及文字样式修改:颜色修改、字号调整
    from docx.shared import RGBColor, Pt
    # 设置中文字体
    from docx.oxml.ns import qn
    

    接下来就是依次添加各部分的内容和样式,因为全文的字体均为楷体,可以在函数体的最末尾一并修改。首先实例化文件后添加“请假条”:

    def request_for_leave(name, department, reason, days, date):
        doc = Document()
        heading_1 = '请 假 条'
        paragraph_1 = doc.add_heading(heading_1, level=1)
        # 居中对齐
        paragraph_1.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        # 标题要打,单独修改较大字号
        for run in paragraph_1.runs:
            run.font.size = Pt(17)
    

    如果对上面的代码单独输出会发现标题颜色是蓝色,这是以 .add_heading() 添加标题默认的颜色,最后也可以统一修改
    对于“尊敬的领导:”这一行基本同理,但不需要修改字号:

        greeting_word = '尊敬的领导:'
        paragraph_2 = doc.add_paragraph(greeting_word)
    

    接下来是核心请假条的正文了,从需求中的样式上可以看出,整句话中有一些文字块是固定的,包括 “本人” “、所在部门” “,由于” “需请假” “天。”,而几个文字块之间的信息是根据不同人的情况而不同,并且需要添加下划线。简单的逻辑就是将参数对应的文字块添加好下划线之后,和固有不变的变量进行拼接,就可以形成完整的段落了:

        word_1 = "    本人"
        word_2 = ",所在部门"
        word_3 = ",由于"
        word_4 = ",需请假"
        word_5 = "天。"
    
        paragraph_3 = doc.add_paragraph()
        paragraph_3.add_run(word_1)
        paragraph_3.add_run(name).underline = True
        paragraph_3.add_run(word_2)
        paragraph_3.add_run(department).underline = True
        paragraph_3.add_run(word_3)
        paragraph_3.add_run(reason).underline = True
        paragraph_3.add_run(word_4)
        paragraph_3.add_run(str(days)).underline = True
        paragraph_3.add_run(word_5)
        # 设置下划线
        paragraph_3.paragraph_format.line_spacing = 1.5
    

    代码中 .underline = True 就是对参数对应的文字块添加下划线
    接下来的申请人和日期填写就和上面基本类似,比较大的区别在于添加了一行新的命令 paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT, 这行命令能够将这两个段落右对齐

        word_6 = '申请人:'
        paragraph_4 = doc.add_paragraph()
        paragraph_4.add_run(word_6)
        paragraph_4.add_run(name).underline = True
        paragraph_4.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT
    
        word_7 = '日期:'
        sign_date = "{}年{}月{}日".format(date.split('-')[0], date.split('-')[1], date.split('-')[2])
        paragraph_5 = doc.add_paragraph()
        paragraph_5.add_run(word_7)
        paragraph_5.add_run(sign_date).underline = True
        paragraph_5.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT
    

    最后就是统一修改字体为楷体,中文字体修改比较麻烦,不像英文字体只需要指定如 run.font.name = 'Arial' 就可以,需要额外的几行代码。最后记得保存:

        for paragraph in doc.paragraphs:
            for run in paragraph.runs:
                # 统一修改颜色
                run.font.color.rgb = RGBColor(0, 0, 0)
                run.font.name = '楷体'
                r = run._element.rPr.rFonts
                r.set(qn('w:eastAsia'), '楷体')
    
        doc.save(path + "\{}-请假条.docx".format(name))
    

    运行上述代码即可针对每个人产生相应的请假条:

    最后附上完整代码:

    from openpyxl import load_workbook
    from docx import Document
    from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
    from docx.shared import RGBColor, Pt
    from docx.oxml.ns import qn
    
    path = r'C:\xxx' # 路径记得修改
    workbook = load_workbook(path + r'\Leave.xlsx')
    sheet = workbook.active
    
    def request_for_leave(name, department, reason, days, date):
        doc = Document()
        heading_1 = '请 假 条'
        paragraph_1 = doc.add_heading(heading_1, level=1)
        paragraph_1.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        for run in paragraph_1.runs:
            run.font.size = Pt(17)
    
        greeting_word = '尊敬的领导:'
        paragraph_2 = doc.add_paragraph(greeting_word)
    
        word_1 = "    本人"
        word_2 = ",所在部门"
        word_3 = ",由于"
        word_4 = ",需请假"
        word_5 = "天。"
    
        paragraph_3 = doc.add_paragraph()
        paragraph_3.add_run(word_1)
        paragraph_3.add_run(name).underline = True
        paragraph_3.add_run(word_2)
        paragraph_3.add_run(department).underline = True
        paragraph_3.add_run(word_3)
        paragraph_3.add_run(reason).underline = True
        paragraph_3.add_run(word_4)
        paragraph_3.add_run(str(days)).underline = True
        paragraph_3.add_run(word_5)
        paragraph_3.paragraph_format.line_spacing = 1.5
    
        word_6 = '申请人:'
        paragraph_4 = doc.add_paragraph()
        paragraph_4.add_run(word_6)
        paragraph_4.add_run(name).underline = True
        paragraph_4.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT
    
        word_7 = '日期:'
        sign_date = "{}年{}月{}日".format(date.split('-')[0], date.split('-')[1], date.split('-')[2])
        paragraph_5 = doc.add_paragraph()
        paragraph_5.add_run(word_7)
        paragraph_5.add_run(sign_date).underline = True
        paragraph_5.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT
    
        for paragraph in doc.paragraphs:
            for run in paragraph.runs:
                run.font.color.rgb = RGBColor(0, 0, 0)
                run.font.name = '楷体'
                r = run._element.rPr.rFonts
                r.set(qn('w:eastAsia'), '楷体')
    
        doc.save(path + "\{}-请假条.docx".format(name))
    
    n = 0
    for row in sheet.rows:
        if n:
            name = row[0].value
            department = row[1].value
            reason = row[2].value
            days = row[3].value
            date = str(row[4].value).split()[0]
            request_for_leave(name, department, reason, days, date)
        n += 1
    

    相关文章

      网友评论

          本文标题:[Python] 自动化办公 无模板批量生成Word请假条

          本文链接:https://www.haomeiwen.com/subject/oyfnaktx.html