Hello, everyone. I'm J.
In our work, we face a lot of repetitive work, through manual processing is often time-consuming and labor-intensive and prone to error. Python has a great advantage in office automation, can solve many repetitive problems we encountered in our work, office needs in minutes.
I. Background
In our economic dealings, sometimes it involves the batch production of sales contracts. For example, we need to batch generate sales contracts (Word) based on the following contract data (Excel).
II. Preparation
We start by preparing a contract template (Word) with the contract data to be replaced represented by {{}} as follows:
III. Actual combat
1. Installation of related libraries
openpyxl is a very good library to operate Excel, the function is more complete compared to xlrd, xlwt, we first install it:
pip install openpyxl
docxtpl is a very good operation Word library, which is mainly through the docx document template loading, so as to modify it, we also install this library.
pip install docxtpl
2. Reading contract data
We can open the contract data (Excel sheet) by load_workbook method, then read each contract data and store it into data dictionary, then put each dictionary into the list datas. ps: Since the read contract date is a timestamp, it needs to be converted to standard year, month and day format by strftime method.
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 = ("%Y-%m-%d")
data = {"Party A": name1,
"Party B": name2,
"Contract price": price,
"Product name": product,
"Number of products": count,
"Term of payment": deadline,
"Signing Time": time}
(data)
datas
Of course, we can also read the contract data through the pandas big method, the main use of the dataframe_to_rows method, the pandas format data into a line by line data. index = False means that do not need an index, header = False means that do not need a table header.
import pandas as pd
from import dataframe_to_rows
df = pd.read_excel("Contract data.xlsx")
df["Date of signing"] = df["Date of signing"].apply(lambda x:("%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],
"Product name": row[3],
"Number of products": row[4],
"Term of payment": row[5],
"Signing Time": row[6]}
(data)
datas
We can print datas with the following results:
[{'Party A': 'Brother J',
'Party B': 'Old King',
'Contract price': 1000000,
'Product Name': 'Vegetable J learns Python',
'Number of products': 1,
'Payment term': 30,
'Signing time': '2022-05-20'},
{'Party A': 'Brother K',
'Party B': 'Zhang San',
'Contract price': 20000,
'Product Name': 'Refrigerator',
'Number of products': 2,
'Payment term': 40,
'Signing time': '2022-05-21'},
{'Party A': 'Brother C',
'Party B': 'Li Si',
'Contract price': 30000,
'Product Name': 'Computer',
'Number of products': 3,
'Payment term': 50,
'Signing time': '2022-05-22'},
{'Party A': 'Brother B',
'Party B': 'Wang Wu',
'Contract price': 40000,
'Product Name': 'Laundry',
'Number of products': 4,
'Payment term': 60,
'Signing time': '2022-05-23'},
{'Party A': 'Brother P',
'Party B': 'Zhao Liu',
'Contract price': 50000,
'Product Name': 'Microwave Oven',
'Number of products': 5,
'Payment term': 70,
'Signing time': '2022-05-24'}]
3. Batch contract generation
Here the for statement is applied to iterate through each contract data data (dictionary format), open the contract template and replace the data with the data in the contract template and save it as a new sales contract.
for data in datas:
tpl = DocxTemplate('Contract template.docx')
(data)
(f'Contract generation/{data["Party A"]}sales contract{data["Signing Time"]}.docx')
print(f'{data["Party A"]}sales contract已生成')
After the code is run, the effect is as follows:
Open one of the sales contracts with the following effect:
To this article on Python read Excel data to achieve batch contract generation article is introduced to this, more relevant Python to generate the content of the contract, please search for my previous articles or continue to browse the following related articles I hope that you will support me in the future more!