1、背景
行政部的同事每次都要根据excel里面的信息,制作word版的劳动合同,然后他们有一天找到了我,问我可不可以帮忙。
首次签合同人员.xlsx
需要获取该表的【姓名】【证件号码】【岗位】【到职日期】
![首次签合同人员.xlsx](http://www.wd1x.com/picture/allimg/210727/15043V610-0.jpg)
模板:简易劳动合同.docx
原来的word模板如下
![在这里插入图片描述](http://www.wd1x.com/picture/allimg/210727/15043UZ0-1.jpg)
因为用python的docxtpl批量修改就要把需要替换的地方用2个花括号+变量名。例如:{{name}},表示这个变量名是name.修改后word模板如下:
![在这里插入图片描述](http://www.wd1x.com/picture/allimg/210727/15043R0X-2.jpg)
2、python代码
from docxtpl import DocxTemplate
import xlrd
import os
import win32api,win32con,win32print,win32gui
import sys
from dateutil.relativedelta import relativedelta
import time
import shutil
def printer_loading(filename):
open(filename, "r")
"""
打开文件,这个只有open,没有close,所以会打开很多窗口。
如果用with open我的电脑会出现“word还有对话框正在打开,不能强制关闭”的问题。
如果不执行这一句,下面的打印命令发送了没反应???不知道是不是我的电脑有问题
"""
print("打开:",filename)
time.sleep(3)
win32api.ShellExecute(
0,
"print",
filename,
'/d:"%s"' % win32print.GetDefaultPrinter(),
".",
0
)
def close_word():
"""
关闭多余的word窗口???大概是这个功能
主要是我的电脑如果不关闭窗口,打开了15个窗口它就会出现问题,文字消失了!!!!
我的微信、鼠标右键菜单等.....文字都消失了,不知道为啥
"""
while True:
time.sleep(0.5)
hwnd = win32gui.FindWindow("OpusApp", None)
if hwnd == 0:
break
win32gui.PostMessage(hwnd, win32con.WM_NULL, 0, 0)
def message_exit(msg,exit=True):
win32api.MessageBox(0, msg, "提示",win32con.MB_OK)
if exit:
win32api.MessageBox(0, "退出程序", "提示",win32con.MB_OK)
sys.exit()
def main():
try:
current_path = os.getcwd()
excel_path = os.path.join(current_path,"首次签合同人员.xlsx")
doc_path = os.path.join(current_path,"模板:简易劳动合同.docx")
result_dir = os.path.join(current_path,"结果")
if not os.path.exists(excel_path):
message_exit("没有找到excel工作簿
"+excel_path,exit=True)
if not os.path.exists(doc_path):
message_exit("没有找到word文件
"+doc_path,exit=True)
if os.path.exists(result_dir):
message_exit("请先手动删除结果文件夹
程序退出" + result_dir, exit=True)
os.makedirs(result_dir)
excel_content = []
workbook = xlrd.open_workbook(excel_path)
if not "首次简易合同人员" in workbook.sheet_names():
message_exit("没有找到excel工作簿中的【首次简易合同人员】工作表,请把sheet的名称改为“首次简易合同人员”" , exit=True)
data = workbook.sheet_by_name("首次简易合同人员")
data_row = data.nrows
for i in range(data_row):
if i == 0:
continue
time1 = xlrd.xldate_as_datetime(data.cell(i, 7).value,0)
year1 = time1.strftime("%Y-%m-%d")
year2 = (time1 + relativedelta(years=1)).strftime("%Y-%m-%d")
y1 = year1.split("-")[0]
m1 = year1.split("-")[1]
d1 = year1.split("-")[2]
y2 = year2.split("-")[0]
m2 = year2.split("-")[1]
d2 = year2.split("-")[2]
content = {'name': data.cell(i,1).value,
'idcard': data.cell(i,4).value,
'y1':y1,
'm1':m1,
'd1':d1,
'y2': y2,
'm2': m2,
'd2': d2,
'work': data.cell(i,6).value
}
if i < 10:
n="0" + str(i)
else:
n=str(i)
excel_content.append([content,n])
for context,n in excel_content:
doc = DocxTemplate(doc_path)
doc.render(context)
doc.save(os.path.join(result_dir ,n+context["name"] + context["idcard"] + ".docx"))
is_print = win32api.MessageBox(0, "word已生成
是否现在打印????
打印的话可能会卡,请耐心", "是否打印", win32con.MB_YESNO)
if is_print == 6:
is_printer = win32api.MessageBox(0, "打印机是"+win32print.GetDefaultPrinter()+"
如果不是这个打印机,请把你想要打印的打印机设置为默认打印机!!!", "默认打印机", win32con.MB_YESNO)
if is_printer == 6:
n=0
files_dir_list = os.listdir(os.path.join(os.getcwd(),"结果"))
time_start = time.time()
for files in files_dir_list:
print("="*30)
n = n+1
if n !=1:
print("正在打印:第",n,"个文件,共",len(excel_content),"个文件
剩余时间:"+str(round((time.time()-time_start)/60/n*(len(excel_content)-n),2)) + "分钟")
if n % 5 == 0:
close_word()
printer_loading(os.path.join(result_dir,files))
close_word()
message_exit("请等待打印机加载好了再点击!!!!!!!
要不然会有没打印完的文件!!!!!!!", exit=False)
elif is_printer == 7:
pass
pass
elif is_print == 7:
pass
message_exit("完成!",exit=True)
except Exception as err:
message_exit("发生错误:
{}
请拍照联系开发人员".format(err),exit=True)
if __name__ == '__main__':
main()
问题
我用printer_loading(),如果命令发送太快不加控制的话,我电脑上就会有很多word窗口。
问题不严重就是漏打。
问题如果很严重,那就会微信等软件、鼠标右键菜单、word、弹窗大部分文字都消失。
3、word vba
![在这里插入图片描述](http://www.wd1x.com/picture/allimg/210727/15043V019-3.jpg)
1) 根据模板批量生成文件
程序/函数 |
作用 |
FileFolderExists() |
函数:判断文件或文件夹是否存在 |
read_excel() |
程序:读取excel数据,放到公共变量里 |
replace(old_text, new_text) |
程序:用录制宏录制的【替换】操作,变量:旧文字、新文字 |
make_contract() |
主程序:根据模板批量生成文件 |
Public excel_content(1 To 1000, 1 To 8)
Public current_path As String
Public Function FileFolderExists(strFullPath As String) As Boolean
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
EarlyExit:
On Error GoTo 0
End Function
Sub read_excel()
Dim ExcelApp As Object
Dim mybook As Object
Dim mysheet As Object
Dim excel_rows
Application.ScreenUpdating = False
If Tasks.Exists("Microsoft Excel") = True Then
Tasks("Microsoft Excel").Close
End If
Set ExcelApp = CreateObject("Excel.Application")
Set mybook = ExcelApp.Workbooks.Open(current_path & "首次签合同人员.xlsx")
Set mysheet = mybook.worksheets("首次简易合同人员")
With mysheet
exit_row_for = False
For Row = 2 To 1000
If exit_row_for = True Then
Exit For
End If
For col = 1 To 8
If .Cells(Row, col) = "" Then
exit_row_for = True
Exit For
End If
excel_content(Row - 1, col) = .Cells(Row, col)
Next
Next
End With
Application.ScreenUpdating = True
End Sub
Sub replace(old_text, new_text)
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = old_text
.Replacement.Text = new_text
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchByte = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute replace:=wdReplaceAll
End Sub
Sub make_contract()
current_path = ActiveDocument.Path
If FileFolderExists(current_path & "结果") Then
MsgBox "请先删除【" & current_path & "结果】文件夹"
Exit Sub
End If
MkDir current_path & "结果"
Dim nian As Integer
nian = InputBox("请问合同是几年的?" & Chr(13) & "请输入数字:123....")
Application.ScreenUpdating = False
Call read_excel
Dim cuurent_doc As Object
For i = 1 To UBound(excel_content, 1)
If excel_content(i, 1) = "" Then
Exit For
End If
Set currnet_doc = Documents.Open(FileName:=current_path & "模板:简易劳动合同.docx")
If excel_content(i, 1) < 10 Then
Num = "0" & excel_content(i, 1)
Else
Num = excel_content(i, 1)
End If
currnet_doc.SaveAs (current_path & "结果" & Num & excel_content(i, 2) & excel_content(i, 5) & ".docx")
With current_doc
date1 = Format(excel_content(i, 8), "yyyy/mm/dd")
date2 = Format(DateAdd("yyyy", nian, date1), "yyyy/mm/dd")
date1_arr = Split(date1, "/")
date2_arr = Split(date2, "/")
Call replace("{{name}}", excel_content(i, 2))
Call replace("{{idcard}}", excel_content(i, 5))
Call replace("{{work}}", excel_content(i, 7))
Call replace("{{y1}}", date1_arr(0))
Call replace("{{y2}}", date2_arr(0))
Call replace("{{m1}}", date1_arr(1))
Call replace("{{m2}}", date2_arr(1))
Call replace("{{d1}}", date1_arr(2))
Call replace("{{d2}}", date2_arr(2))
End With
currnet_doc.Save
currnet_doc.Close
Set currnet_doc = Nothing
Next
Application.ScreenUpdating = True
MsgBox "完成:" & Chr(13) & "生成的文件在【" & current_path & "结果】文件夹下"
End Sub
2) 批量打印文件
Sub print_doc()
Application.ScreenUpdating = False
msg = MsgBox("当前活动打印机是:" & Chr(13) & ActivePrinter & Chr(13), vbInformation + vbOKCancel)
If msg = 2 Then
MsgBox "请稍后再来"
Exit Sub
Else
End If
C = 0
On Error Resume Next
current_path = ActiveDocument.Path
Set fso = CreateObject("Scripting.filesystemobject")
Set myf = fso.getfolder(current_path & "结果")
n = InputBox("请问每个文件要打印几份" & Chr(13) & "请输入数字:123.....")
For Each i In myf.Files
C = C + 1
Application.PrintOut FileName:=i.Path, Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentWithMarkup, Copies:=n, Pages:="", PageType:= _
wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
Next
MsgBox "完成:" & Chr(13) & "共" & C & "个文件"
Application.ScreenUpdating = True
End Sub
4、总结:
语言 |
批量生成word文档 |
批量打印 |
总评 |
python |
很好,可以在后台生成 |
差,要控制速度,要不然可能会漏打或文字消失 |
单用【批量生成word文档】功能就好,还是别打印了 |
word vba |
不能在后台生成,速度比python略慢,还是很快的 |
very very 快!赞! |
就冲这打印速度,赞! |
|