Are you still bothered by the repetitive tasks of each day and month? Are you still anxious about the information that can't take WeChat screenshots in advance, and copy and paste again and again? Python automated office artifact, one-click to solve your office problems, say goodbye to the sea of overtime, and easily counterattack the peak of the workplace!
Office needs
I have a good friend who works in a personnel position in a branch of a state-owned enterprise. Every month, the personnel of the head office will send her the salary forms of the branch personnel in the form of WeChat screenshots. She needs to open the pictures one by one, manually enter them into an Excel spreadsheet, and then make the data into a pay slip and send it to the relevant personnel by email. The whole process is extremely tedious, taking two days to complete each month. For the way the leader sends it in the form of screenshots, he also dares to be angry.
Pain Point Analysis:
Inefficiency: The process of manually entering and producing pay slips is time-consuming, labor-intensive, and inefficient.
Error-prone: Typing errors or omissions are inevitable due to manual operation.
Heavy workload: As the number of employees in the company increases, so does the number of pay slips, and the workload is getting heavier and heavier.
Solution:
In response to the above pain points, I used Python automated office technology to tailor a solution for my friend.
Step 1: The OCR algorithm automatically extracts the payroll form
With the OCR (Optical Character Recognition) algorithm, we can easily extract the salary form from WeChat screenshots into Excel automatically. This process only requires copying the pictures to the specified folder, and the program will automatically recognize and convert them.
python code
from PIL import Image
import pytesseract
from openpyxl import Workbook
def load_image(image_path):
image = Image.open(image_path)
return image
def convert_to_grayscale(image):
return image.convert("L")
def extract_text(image):
return pytesseract.image_to_string(image)
def extract_table_data(text):
rows = text.strip().split("\n")
table_data = [row.split("\t") for row in rows]
return table_data
def save_as_excel(table_data, output_path):
workbook = Workbook()
sheet = workbook.active
for row_index, row_data in enumerate(table_data, start=1):
for column_index, cell_data in enumerate(row_data, start=1):
sheet.cell(row=row_index, column=column_index, value=cell_data)
workbook.save(output_path)
# 调用示例
image_path = "table_image.jpg"
output_path = "table_data.xlsx"
image = load_image(image_path)
grayscale_image = convert_to_grayscale(image)
text = extract_text(grayscale_image)
table_data = extract_table_data(text)
save_as_excel(table_data, output_path) 
It can be seen that the information extracted in this way is not only error-free, but also perfectly maintains the structure of the table
In conclusion, the Python automation tool provides us with an efficient and accurate way to extract tabular data from images with one click and convert it into an editable Excel format. It can not only solve the tedious problem of manually entering the form data, but also guarantee the accuracy and completeness of the data.
Step 2: Automatically create pay slips
After successfully importing the payroll form into Excel, the program will automatically convert the form of payslip according to the preset format. As a result, what would have required manual copy-pasting one by one can now be done in just a second.
python code
import pandas as pd
# 导入Excel写入库
from openpyxl import Workbook
import os
df = pd.read_excel('employee_data.xlsx')
print(df)
os.makedirs("工资条",exist_ok=True)
for i,row in df.iterrows():
wb = Workbook()
ws = wb.active
ws.append(list(df.columns))
ws.append(list(row))
file_name = f"./工资条/{row['姓名']}_工资条.xlsx"
wb.save(filename=file_name)
print("工资条生成完毕") Step 3: Automatically send emails based on the person's name
Finally, the program will automatically send the pay slip to the corresponding person in the form of an email based on the employee's name and email address information in the payroll. This eliminates the tedious process of manually sending emails and ensures that pay slips are delivered to each employee's mailbox without errors.
import pandas as pd
import datetime
import smtplib
# 处理邮件内容的库，email.mine
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
import logging
import os
logging.basicConfig(filename='E:\code\Plan_m\log.log', level=logging.INFO,filemode = 'a', format = '【%(asctime)s】 【%(levelname)s】 >>> %(message)s', datefmt = '%Y-%m-%d %H:%M')
# 邮箱属性配置
def send_email(text_msg):
# 邮箱服务端
mailserver = 'smtp.163.com'
# 发件人-填写自己的邮箱
userName_SendMail = '[email protected]'
# 邮箱发件授权码-为发件人生成的授权码，详见下文
userName_AuthCode = 'BOEFGGZHRAHEXGRN'
# 定义邮件的接收者-我随便写的，若收件人较多，可用列表表示
per_email = email_dict.get(text_msg.get("姓名","None"),"None")
name = text_msg.get("姓名","None")
print(per_email)
received_mail = ['[email protected]'] # 加入祖总 刘总 张总邮箱
# final_received_mail = received_mail+per_email
# print(final_received_mail)
# 发送一封简单的邮件，处理邮件内容
content = f'本月工资条请查收'
print(content)
# 纯文本形式的邮件内容的定义，通过MIMEText进行操作，plain为默认的文本的展示形式
email = MIMEMultipart()
email['Subject'] = '工资条' # 定义邮件主题
email['From'] = "人事部" # 发件人
email['To'] = ','.join(received_mail) # 收件人（可以添加多个，若只有一个收件人，可直接写邮箱号）
filename = f'{name}_工资条.xlsx'
with open(filename, 'rb') as attachment:
part = MIMEApplication(
attachment.read(),
Name=filename
)
part['Content-Disposition'] = f'attachment; filename="{filename}"'
email.attach(part)
# 发送邮件
# QQ邮箱的端口号是465，其他邮箱的端口号可自行百度，非QQ邮箱，一般使用SMTP即可，不需要有SSL
smtp = smtplib.SMTP_SSL(mailserver, port=465)
smtp.login(userName_SendMail, userName_AuthCode)
smtp.sendmail(userName_SendMail, ','.join(received_mail), email.as_string())
smtp.quit()
logging.info('恭喜，邮件发送成功了')
# data = pd.read_excel("E:\Plan_m\批量样品跟进计划表.xlsx")
# # data['下单时间'] = pd.to_datetime(data['下单时间']) 163 mailbox security code setting [the password in the python code uses this instead of the email password]
Effects:
After implementing this solution, my friend's workload has been greatly reduced. Now, she only needs to spend one second a month copying the images to a specific folder, and the rest is left to the Python automation tool. And, thanks to the program's high degree of automation and accuracy, there is no need to worry about errors or omissions anymore.
Epilogue:
In this era of informatization and intelligence, Python automated office technology has become a powerful assistant for us to improve work efficiency and reduce work burden. If you are still worried about tedious office tasks, you might as well try Python office automation artifact! I believe it will definitely bring you unexpected surprises and convenience!