Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to make 100 contracts with Python+Excel+Word". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use Python+Excel+Word to make 100 contracts.

Preface

Hello, everyone, it's time for Python office automation series again.

Today we continue to share a real office automation requirement: how to enable Python+Excel+Word to batch generate contracts with content in a specified format.

The main knowledge points involved are: the comprehensive application of openpyxl module and the two kinds of traversal logic of Word documents.

Requirement description

You are the construction company of Party B, and you have a blank contract template Word document, as shown below:

There is also an Excel contract information form, which is what all Party A (developer) needs to fill in in the contract.

It can be seen that one line is all the information of a company. Now you need to fill in the information of each company in Excel into the blank Word contract template to generate the contracts of each company. The final result is as follows

Step analysis

Originally, we need to fill in the information of each row in the Excel summary table into the word template to generate the corresponding contract.

Now that we need to give it to Python to implement, it leads to a question: how does the program know which underscore to fill in certain information? In order to solve this problem, we need to modify the template.

Change the underscore to some kind of logo, so that the program can see the logo and understand what information should be put here. The strategy here is to change the underscore to be filled in to the column name in the summary table, as shown in the following figure.

This way the program can identify what needs to be filled in. The so-called recognition here can be replaced with a very simple word, that is, text substitution. As long as you retrieve # xxxx# (the column name in excel), replace this with specific information.

For this strategy, the column name needs to be in the format of # xxxx#, otherwise the information in the normal irrelevant text will be replaced, which will destroy the original requirement, and finally the template will be modified as follows:

Through the Excel table, we can see that a row is the information of a company, and the column names of each column exist in the template, replacing the column names in the template with the actual information of each company (the basis for program identification and text replacement).

This requirement can be accomplished in this way. The implementation of the entire large requirement can be done as follows:

Steps after analysis:

Adjust the blank contract to the contract template, change the underscore you need to fill in to the exclusive column name, open the Excel table, cycle by row, and then cycle through each information one by one, each information will find the corresponding column name that exists in the template and replace it (if you don't understand the following explanation) save the contract after each row of cells, and survive each company's separate contract.

After a clear analysis, the logic is very simple, but there is a hidden knowledge point not mentioned, let's write the code and say it!

Code implementation

First, import the module, set the path, and set up the folder. In this case, it involves the opening of the Excel table and the creation of the Word, so you need to import load_workbook from openpyxl, while Word can be opened or created with the Document of the docx module.

From docx import Document

From openpyxl import load_workbook

# create a folder using the os module to store the generated contract

Import os

# given the folder path where the contract template and summary table are located, which is convenient for reuse

Path = contract C:\ Users\ chenx\ Desktop\ contract'

# generate a folder combined with path judgment to avoid the risk of termination when the program reports an error

If not os.path.exists (path +'/'+ all contracts'):

Os.mkdir (path +'/'+ all contracts')

Then open the Excel file

Workbook = load_workbook (path +'/'+ 'contract information table .xlsx')

Sheet = workbook.active

Now iterate through the Excel to generate the contract. As mentioned repeatedly earlier, each line of Excel is information about a specific contract, so the instantiation and preservation of docx for Word files must be in the loop body, unlike Excel instantiation outside the loop body.

# the valid information line starts from the second line, which is the header, including the column name, and is also the basis for text replacement

For table_row in range (2, sheet.max_row + 1):

# instantiate a new word file per loop

Wordfile = Document (path +'/'+ 'contract template .docx')

# cells need to be traversed one by one, each containing useful information

For table_col in range (1, sheet.max_column + 1):

# the old text, that is, the column name, has been filled out in the template for text replacement, limiting the row to the first line is the column name

Old_text = str (sheet.cell (row=1, column=table_col) .value)

# the new text is the actual information, and when table_col loops to a certain value, the actual cell and column names are determined

New_text = str (sheet.cell (row=table_row, column=table_col) .value)

# add this judgment because the date information reader is in "date time" format. If you want to retain the date information, you can use the string method or use the time/datetime module to deal with it.

If''in new_text:

New_text = new_text.split () [0]

Learn more about this replacement through the following figure:

For example, if the program has entered the third loop (cycle to the third company) and entered the fourth loop for the cell loop, then the actual value obtained at this time is the construction of C park, and the corresponding column name is # Project content #.

At this point, it is clear what needs to be replaced, as long as find # project content # in the template and replace it with the construction of C Park! After learning about this replacement, the next step is to traverse the Word template and find the corresponding column name replacement!

As we mentioned earlier in the docx module, Word text has a three-level structure of document Document- paragraph Paragraph- text block Run. You can use the following code to traverse the text:

All_paragraphs = wordfile.paragraphs

For paragraph in all_paragraphs:

Print (paragraph.text)

For run in paragraph.runs:

Print (run.text)

Text information is available for both paragraphs and text blocks. The trap implied in this requirement is here. Pay attention to what you need to fill in at the end of the contract:

This part of the content can not be traversed with the above code. Why? Because this is a table in an Word document!

Traversing the table requires special traversal logic: document Document- table Table- row Row/ column Column- cell Cell. The code for traversing the text in the table is as follows:

All_tables = wordfile.tables

For table in all_tables:

# you can also traverse by column

For row in table.rows:

For cell in row.cells:

Print (cell.text)

With this additional knowledge, the core code in this case can be written like this.

For table_row in range (2, sheet.max_row + 1):

Wordfile = Document (path +'/'+ 'contract template .docx')

For table_col in range (1, sheet.max_column + 1):

Old_text = str (sheet.cell (row=1, column=table_col) .value)

New_text = str (sheet.cell (row=table_row, column=table_col) .value)

If''in new_text:

New_text = new_text.split () [0]

# document Document-paragraph Paragraph-text block Run

All_paragraphs = wordfile.paragraphs

For paragraph in all_paragraphs:

For run in paragraph.runs:

Run.text = run.text.replace (old_text, new_text)

# document Document-Table Table-Row Row/ column Column-Cell Cell

All_tables = wordfile.tables

For table in all_tables:

For row in table.rows:

For cell in row.cells:

Cell.text = cell.text.replace (old_text, new_text)

# get the name of the company to generate the contract

Company = str (sheet.cell (row=table_row, column=1) .value)

Wordfile.save (path +'/'+ f' all contracts / {company} contracts .docx') is written at the end

This case has strong practicability, and the requirements can be extended as follows: fill in each individual information in an information summary table Excel (each line or each individual, company or other information) into the designated template Eord to generate a separate document, but before writing the automation script, you should first split the task and make clear your mind before proceeding!

At this point, I believe you have a deeper understanding of "how to use Python+Excel+Word to make 100 contracts". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.