Automatically generate data daily reports with Python!

In fact, I think it is quite simple. The core is that you assemble the content template of the daily report, and then give the changed amount to python to fill in. What you need is basically python to process excel and word Libraries related to ppt and so on. Use them skillfully and you can automate a whole process.

Daily newspapers are a problem that most migrant workers cannot avoid.

For managers, daily reports are the best way to manage beforehand and understand the atmosphere and status of the team. But for employees, there is nothing to talk about. For repetitive work, I highly recommend everyone to use Python to modularize and automate it, helping us achieve efficient office work.

Let’s demonstrate the advantages of Python’s automated office through a case of supplementing a sales daily report. This article simplifies the process of the case, and the complete code is attached at the end of the article.

Detailed explanation of requirements

My friend’s needs are as follows. Their usual sales data are recorded in Excel, and after summary, statistics will be made by department. But at the beginning of this year, the leader suddenly asked us to write a daily report. After writing for a month, we found that we did not check it and stopped writing.

Now I am suddenly required to submit all the daily reports before this month tomorrow. This is equivalent to making up nearly 120 days of daily reports from February to May. If you copy and paste with both hands, then Probably vomiting blood. A friend sent over all the relevant documents for writing his daily report and found that the final effect of the daily report is as follows.

So the requirements are relatively simple. You only need to read the daily data from the Excel table, use Python to process it, and then write it into the Word document. , you can generate daily reports in batches.

Data processing

Before performing data processing, you must first understand what data is ultimately needed. As shown in the figure below, the target daily report in Word is mainly divided into two categories: the values ​​marked in red are mainly composed of the data of the day, or the data obtained after their calculation; the table marked in green is simpler, that is, the past seven days data (sales quantity, sales amount, sales target, degree of completion).

First we import the Pandas module for data processing.

import pandas as pd
df = pd.read_excel("日报数据.xlsx")
df

Output result:

After importing the data, we can then perform data operations according to our needs. Data operations are mainly divided into two types, one is to use addition, subtraction -, multiplication *, and division / to perform data operations, and the other is to use statistical methods to perform data operations.

Enter the following command in the interactive environment:

df["日期"] = df["日期"].apply(lambda x:x.strftime("%Y-%m-%d"))
df["当日完成度"] = (df["销售金额"]/df["销售目标"]*100).round(1)
df["累计销售金额"] = df["销售金额"].cumsum()
df["当年完成度"] = (df["累计销售金额"]/2200000*100).round(1)
df["累计销售金额"] = (df["累计销售金额"]/10000).round(2)
df

Output result:

As you can see, the final result is marked in red in the screenshot The data contents have all been calculated. The table marked in green is even simpler, just use the data selection in the Pandas module.

Enter the following command in the interactive environment:

num = 10
df.iloc[num-7:num, :5]

Output result:

You can easily get a certain A collection of daily data within the past 7 days of the date.

Automatically generate daily reports

Using Python to automatically operate Word usually uses the python-docx module, and there are generally two methods for batch generating Word documents: using add_ paragraph(), add_table() and other methods Add various content to Word documents. The other one is what we are going to use this time, which is to replace text and table data in the original Word document according to position.

Enter the following command in the interactive environment:

for index, rows in df.iterrows():
 if index > 30:
 doc.paragraphs[0].runs[1].text = rows[0]
 doc.paragraphs[4].runs[4].text = rows[0]
 doc.paragraphs[4].runs[6].text = str(rows[1])
 doc.paragraphs[4].runs[8].text = str(rows[2])
 doc.paragraphs[5].runs[1].text = str(rows[3])
 doc.paragraphs[5].runs[3].text = str(rows[4])
 doc.paragraphs[9].runs[2].text = str(rows[5])
 doc.paragraphs[9].runs[7].text = str(rows[6])
 table = doc.tables[0]
 data_table = df.iloc[index-6:index+1,:5]
 for i in range(7):
 for j in range(5):
 table.cell(i+1,j).text = str(df.iloc[i,j])

 doc.save(f"销售日报-{rows[0]}.docx")

Execute the code and output the result:

As shown in the figure above, 120 copies The recorded sales daily report is ready. Python automated office is so magical.

How to obtain the complete code:

Link:​​//m.sbmmt.com/link/0d5a4a5a748611231b945d28436b8ece​​​

Extraction code: p9iw

Because of its simple syntax and easy to use, Python is called the "most suitable programming language for beginners to learn". For various repetitive computer tasks at work, you can consider using Python to transform them into automated programs.

If you are a Python beginner, you will find that the logic of this article is very simple, and you can even improve it. For example, the python-docx module has advantages in reading Word documents, but when writing text to templates, you can consider using the docxtpl module (learn a little Jinja2 syntax).

The above is the detailed content of Automatically generate data daily reports with Python!. For more information, please follow other related articles on the PHP Chinese website!