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

This article mainly explains "how to fill out hundreds of forms with Python code". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to fill out hundreds of forms with Python code.

The thing is, when I got off work yesterday, I accidentally found that the secretary was in a bad mood. In line with the principle of being willing to help others, I took the initiative to come forward to offer warmth.

After a complaint from my little sister, it turned out that I was about to get off work, but the boss suddenly sent me a form, as shown in the following picture:

There are about 300 personnel information, the boss asked the little sister to fill in the information according to the following template, each one form, finished today:

Even if one form takes half a minute, it will take two and a half hours for 300 forms to be finished.

How could we let our little sister suffer like this, so we took it down very freely? at that time, the way my little sister looked at me was different.

Finally, I used 30 lines of python code, and then spent 5 minutes to fill out these forms automatically, the details are as follows: 1. Open the workbook import xlwings as xw with xlwings

App=xw.App (visible=True,add_book=False)

Workbook=app.books.open (ringing D:\ data analysis\ 27. I used python to help my little sister fill out hundreds of forms\ personnel information. Xlsx')

Sheet=workbook.sheets [0] # Select the first table 2. Loop the data for each row info = sheet.used_range

For i in info.raw_value [1:]:

Print (I)

('Zhang San', 'male', 28.0, 177.0, 150.0, 'undergraduate','No', 'Han', 'Beijing', 8.0)

('Li Si', 'male', 31. 0, 165.0, 130.0, 'undergraduate', 'Yes', 'Han nationality', 'Shanghai', 130.0)

('Wang er', 'male', 40.0, 182.0, 162.0, 'graduate student', 'yes', 'Han nationality', 'Guangzhou, Guangdong', 2.0)

('Li Jie', 'female', 25.0, 163.0, 110.0, 'postgraduate','No', 'Han nationality', 'Shenzhen, Guangdong', 8.0)

('Zhang Ru', 'female', 36, 168.0, 120.0, 'Graduate students', 'Shi', 'Han nationality', 'Nanjing, Jiangsu', 4.0)

('Zhang Wu', 'male', 35.0, 165.0, 120.0, 'undergraduate','No', 'Han nationality', 'Zhengzhou, Henan', 2.0)

('Li Jie', 'male', 36. 0, 163.0, 142.0, 'undergraduate', 'Yes', 'Han nationality', 'Shijiazhuang, Hebei', 1.0)

('Wang Shuai', 'male', 37.0,94.4, 'postgraduate', 'Shi', 'Han nationality', 'Shenyang, Liaoning', 161.0)

('Li Yi', 'female', 38.0,86.4, 'postgraduate','No', 'Han nationality', 'Jinan, Shandong', 159.0)

('Zhang Xia', 'female', 39.0, 157.0, 78.4, 'postgraduate', 'Shi', 'Han nationality', 'Changsha, Hunan', 4.0)

('Wang Wanli', 'male', 40.0,70.4, 'undergraduate','No', 'Han nationality', 'Harbin, Heilongjiang', 155.0)

('Li Qing', 'male', 41.0,62.4, 'undergraduate', 'Yes', 'Han nationality', 'Jilin Changchun', 153.0)

('Wang Hou', 'male', 42.0,54.4, 'postgraduate', 'Yes', 'Han nationality', 'Wuhan, Hubei', 151.0)

('Zeng Mei', 'female', 43.0,46.4, 'postgraduate','No', 'Han nationality', 'Hainan Haikou', 149.0)

We can find that the data is output in tuples, and it is very convenient to use it in the next step.

3. Open the personal information template and enter the information into for i in info.raw_value [1:]:

App=xw.App (visible=True,add_book=False)

Workbook=app.books.open (ringing D:\ data analysis\ 27. I used python to help my little sister fill out hundreds of forms\ personal information template .xlsx')

Sheet=workbook.sheets [0]

Sheet ['B1'] .value = I [0]

Sheet ['D1'] .value = I [1]

Sheet ['F1'] .value = I [8]

Sheet ['H1'] .value = I [2]

Sheet ['B2'] .value = I [9]

Sheet ['D2'] .value = I [5]

Sheet ['F2'] .value = I [6]

Sheet ['H2'] .value = I [7]

This step is also easy to understand, that is, the personal information in the tuple is extracted and put into the corresponding cell location of the personal information template, but the format is not very good-looking and needs to be improved.

4. Set the cell format list_cell= ['B1','D1','F1','H1','H1','B2','D2', 'F2', H2'] # cell location

For j in list_cell:

Sheet [j] .api.Font.Name = 'italics' # set font

Sheet [j] .api.Font.Size = 14 # set font size

# set text horizontal alignment to center

Sheet [j]. Api.HorizontalAlignment=xw.constants.HAlign.xlHAlignCenter ('table').

# set text horizontal alignment to center

Sheet [j]. Api.VerticalAlignment=xw.constants.VAlign.xlVAlignCenter ('table').

Set the font, font size, and cell up and down, left and right, respectively.

5. Save the table and rename and close workbook.save (rusted D:\ data Analysis\ 27. I used python to fill out hundreds of forms for my little sister\ {} .xlsx '.format (I [0]) # named after her.

Workbook.close ()

App.quit () so far, I believe you have a deeper understanding of "how to fill out hundreds of forms with Python code". 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.