In our work , Faced with a lot of repetitive work , Manual processing is often time-consuming, labor-intensive and error prone . and Python It has great advantages in automatic office
potential , It can solve many repetitive problems encountered in our work , Get the office needs done in minutes . Go home from work quickly to accompany your wife …
In our economic exchanges , Sometimes it involves mass production of sales contracts . For example, we need the following contract data （Excel）, Generate sales contracts in batch
（Word）.
We must first prepare a contract template （Word）, Use the contract data to be replaced with { {}} Express , as follows ：
openpyxl It's an operation Excel Very useful library , Function is relative to xlrd、xlwt More complete for , We first install it ：
pip install openpyxl
docxtpl It's an operation Word Very useful library , It is mainly through docx Document template loading , So as to modify it , We also install this library .
pip install docxtpl
We can go through load_workbook Method to open contract data （Excel surface ）, Then read each contract data and store it in data Dictionaries , Then put each dictionary into
list datas in .PS： Since the signing date read is a timestamp , Need to pass through strftime The method is converted to the standard format .
python Exchange of learning Q Group ：906715085####
from docxtpl import DocxTemplate
from openpyxl import load_workbook
wb = load_workbook(" Contract data .xlsx")
ws = wb['Sheet1']
datas = []
for row in range(2, ws.max_row):
name1 = ws[f"A{
row}"].value
name2 = ws[f"B{
row}"].value
price = ws[f"C{
row}"].value
product = ws[f"D{
row}"].value
count = ws[f"E{
row}"].value
deadline = ws[f"F{
row}"].value
time = ws[f"G{
row}"].value
time = time.strftime("%Y-%m-%d")
data = {
" Party a ": name1,
" Party B ": name2,
" Contract price ": price,
" The product name ": product,
" Product quantity ": count,
" Payment term ": deadline,
" Signing time ": time}
datas.append(data)
datas
Of course , We can also pass pandas To read the contract data , Mainly applied to dataframe_to_rows Method , take pandas Format data into row by row
data .index=False Indicates that no index is required ,header=False Indicates that no header is required .
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
df = pd.read_excel(" Contract data .xlsx")
df[" Signing date "] = df[" Signing date "].apply(lambda x:x.strftime("%Y-%m-%d"))
datas = []
for row in dataframe_to_rows(df,index=False,header=False):
data = {
" Party a ": row[0],
" Party B ": row[1],
" Contract price ": row[2],
" The product name ": row[3],
" Product quantity ": row[4],
" Payment term ": row[5],
" Signing time ": row[6]}
datas.append(data)
datas
We can print datas, The effect is as follows ：
[{
' Party a ': 'J Brother ',
' Party B ': ' Lao Wang ',
' Contract price ': 1000000,
' The product name ': ' food J learn Python',
' Product quantity ': 1,
' Payment term ': 30,
' Signing time ': '2022-05-20'},
{
' Party a ': 'K Brother ',
' Party B ': ' Zhang San ',
' Contract price ': 20000,
' The product name ': ' The refrigerator ',
' Product quantity ': 2,
' Payment term ': 40,
' Signing time ': '2022-05-21'},
{
' Party a ': 'C Brother ',
' Party B ': ' Li Si ',
' Contract price ': 30000,
' The product name ': ' The computer ',
' Product quantity ': 3,
' Payment term ': 50,
' Signing time ': '2022-05-22'},
{
' Party a ': 'B Brother ',
' Party B ': ' Wang Wu ',
' Contract price ': 40000,
' The product name ': ' Washing machine ',
' Product quantity ': 4,
' Payment term ': 60,
' Signing time ': '2022-05-23'},
{
' Party a ': 'P Brother ',
' Party B ': ' Zhao Liu ',
' Contract price ': 50000,
' The product name ': ' The microwave oven ',
' Product quantity ': 5,
' Payment term ': 70,
' Signing time ': '2022-05-24'}]
Here we use for Statement to traverse each contract data data（ Dictionary format ）, Open the contract template , And will data Replace the data in the contract template , Then save it as a new sales contract .
for data in datas:
tpl = DocxTemplate(' Contract template .docx')
tpl.render(data)
tpl.save(f' Contract generation /{
data[" Party a "]} Sales contract {
data[" Signing time "]}.docx')
print(f'{
data[" Party a "]} The sales contract for has been generated ')
After the code runs , The effect is as follows ：
Open one of the sales contracts , The effect is as follows ：
Have you learned ！！ Today's case is still very simple , It is suitable for Xiaobai to practice . You want to strike while the iron is hot , Now hurry to a yard
wave . Do not understand or have doubts about this article , You can tell me in the comments section …
