introduction
1、Python Handle Excel data
2、Python Handle PDF Text
3、Python Handle Email
4、Python Dealing with databases
5、Python Processing batch files
6、Python Control mouse
7、Python Control the keyboard
8、Python Compressed files
9、Python Crawling network data
10、Python Working with pictures and charts
Summary
introductionYou know, there's a hot question ,Python Will it become a common programming tool for public office in the future ？
In the world of programming ,Python It has become a veritable online celebrity . Once I was a graduate student of Chinese language , Ask me how to learn Python, Because they need text analysis in their course papers , use Python Let's run the data . I told him , Look at the grammar for two days , You can get started , No, check the information again . Later, the student used it for half a month Python Do a good job of the thesis data .
therefore Python The biggest advantage is that it is easy to learn , Threshold ratio Java、C++ Very low , It gives the non programmer community the possibility to work with code . Of course Python Can become a popular programming tool , Not only is it easy to learn , But also because Python There are thousands of toolkits , All walks of life .
for 10 Several common examples of office automation ,Python Can be handled efficiently .
1、Python Handle Excel datahave access to pandas、xlwings、openpyxl How much Excel Add, delete, modify, etc 、 Format adjustment and other operations , You can even use Python Function to excel Data analysis .
Read excel form
import xlwings as xwwb = xw.Book() # this will create a new workbookwb = xw.Book('FileName.xlsx') # connect to a file that is open or in the current working directorywb = xw.Book(r'C:\path\to\file.xlsx') # on Windows: use raw strings to escape backslashestake matplotlib Plot write excel form
import matplotlib.pyplot as pltimport xlwings as xwfig = plt.figure()plt.plot([1, 2, 3])sheet = xw.Book().sheets[0]sheet.pictures.add(fig, name='MyPlot', update=True)
PDF Almost the most common text format , Many people have all kinds of treatments PDF The needs of , Like making PDF、 Get text 、 Get photo 、 Get forms, etc .Python There is PyPDF、pdfplumber、ReportLab、PyMuPDF Such packages can easily implement these requirements .
extract PDF written words
import PyPDF2pdfFile = open('example.pdf','rb')pdfReader = PyPDF2.PdfFileReader(pdfFile)print(pdfReader.numPages)page = pdfReader.getPage(0)print(page.extractText())pdfFile.close()extract PDF form
# extract pdf form import pdfplumberwith pdfplumber.open("example.pdf") as pdf: page01 = pdf.pages[0] # Specify the page number table1 = page01.extract_table()# Extract a single table # table2 = page01.extract_tables()# Extract multiple tables print(table1)3、Python Handle Emailstay Python Can be used in smtplib coordination email library , To realize the automatic transmission of mail , Very convenient .
import smtplibimport email# Responsible for assembling multiple objects from email.mime.multipart import MIMEMultipartfrom email.header import Header# SMTP The server , Use here 163 mailbox mail_host = "smtp.163.com"# Sender email mail_sender = "******@163.com"# Email authorization code , Note that this is not the email password , How to get email authorization code , Take a look at the final tutorial of this article mail_license = "********"# Recipient email , It can be for multiple recipients mail_receivers = ["******@qq.com","******@outlook.com"]mm = MIMEMultipart('related')# The body of the email body_content = """ Hello , This is a test email ！"""# Construct text , Parameters 1： Text content , Parameters 2： Text format , Parameters 3： Encoding mode message_text = MIMEText(body_content,"plain","utf-8")# towards MIMEMultipart Add text object to object mm.attach(message_text)# establish SMTP object stp = smtplib.SMTP()# Set the domain name and port of the sender's mailbox , The port address is 25stp.connect(mail_host, 25) # set_debuglevel(1) It can be printed out and SMTP All the information that the server interacts with stp.set_debuglevel(1)# Login mailbox , Pass parameters 1： Email address , Parameters 2： Email authorization code stp.login(mail_sender,mail_license)# Send E-mail , Pass parameters 1： Sender's email address , Parameters 2： Email address of the addressee , Parameters 3： Change the email content format to strstp.sendmail(mail_sender, mail_receivers, mm.as_string())print(" Mail sent successfully ")# close SMTP object stp.quit()4、Python Dealing with databases Database is our common office application ,Python There are various database driver interface packages in , Support the addition, deletion, modification and query of the database 、 Operation and maintenance management . for instance pymysql Package correspondence MySQL、psycopg2 Package correspondence PostgreSQL、pymssql Package correspondence sqlserver、cxoracle Package correspondence Oracle、PyMongo Package correspondence MongoDB wait .
Yes MySQL Connection query for
import pymysql# Open database connection db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Use cursor() Method to create a cursor object cursorcursor = db.cursor()# Use execute() Method execution SQL Inquire about cursor.execute("SELECT VERSION()")# Use fetchone() Method to get a single piece of data .data = cursor.fetchone()print ("Database version : %s " % data)# Close database connection db.close()5、Python Processing batch files For many office scenarios , Batch processing files has always been a dirty job ,Python Can help you out of the sea of suffering .Python There are many packages for handling system files in , such as sys、os、shutil、glob、path.py wait .
Delete folders with the same name under different folders in batch
import os,shutilimport sysimport numpy as npdef arrange_file(dir_path0): for dirpath,dirnames,filenames in os.walk(dir_path0): if 'my_result' in dirpath: # print(dirpath) shutil.rmtree(dirpath)Batch modify file suffix
import osdef file_rename(): path = input(" Please enter the directory you need to modify ( The format is as follows 'F:\\test')：") old_suffix = input(' Please input the suffix you need to modify （ Need to add some .）：') new_suffix = input(' Please enter the suffix you want to change to （ Need to add some .）：') file_list = os.listdir(path) for file in file_list: old_dir = os.path.join(path, file) print(' Current file ：', file) if os.path.isdir(old_dir): continue if old_suffix != os.path.splitext(file)[1]: continue filename = os.path.splitext(file)[0] new_dir = os.path.join(path, filename + new_suffix) os.rename(old_dir, new_dir)if __name__ == '__main__': file_rename()6、Python Control mouse This is the demand of many people , Realize the automatic control of the mouse , To do some assembly line work , Such as software testing .
Python There is one pyautogui The library can control your mouse at will .
Control the left mouse click / Right click / Double click the function and test the source code
# Get mouse position import pyautogui as pgtry: while True: x, y = pg.position() print(str(x) + " " + str(y)) # Output mouse position if 1746 < x < 1800 and 2 < y < 33: pg.click()# Left click if 1200 < x < 1270 and 600 < y < 620: pg.click(button='right')# Right click if 1646 < x < 1700 and 2 < y < 33: pg.doubleClick()# Next, double-left click on except KeyboardInterrupt: print("\n")7、Python Control the keyboard alike ,Python It can also be done through pyautogui Control the keyboard .
Keyboard write
import pyautogui#typewrite() Unable to enter Chinese content , For mixed Chinese and English, you can only enter English #interval Set the text input speed , The default value is 0pyautogui.typewrite(' Hello ,world!',interval=0.5)8、Python Compressed files Compressed file is a common operation in office , Generally, compression software will be used for compression , Manual operation required .
Python There are many packages in support of file compression , It allows you to automatically compress or decompress local files , Or package the analysis results in memory . such as zipfile、zlib、tarfile And so on .zip、.rar、.7z And other compressed file formats .
Compressed files
import zipfiletry: with zipfile.ZipFile("c://test.zip",mode="w") as f: f.write("c://test.txt") # Write compressed file , Will overwrite the original in the compressed file except Exception as e: print(" The type of the exception object is :%s"%type(e)) print(" The contents of the exception object are :%s"%e)finally: f.close()Unzip the file
import zipfiletry: with zipfile.ZipFile("c://test.zip",mode="a") as f: f.extractall("c://",pwd=b"root") ## Unzip the file to the specified directory , The decompression password is rootexcept Exception as e: print(" The type of the exception object is :%s"%type(e)) print(" The contents of the exception object are :%s"%e)finally: f.close()9、Python Crawling network data python Crawlers should be the most popular feature , It's also the vast Python The main reason why enthusiasts enter the pit .
Python There are many packages in support of crawlers , And the crawler bag is divided into grabbing 、 Analysis of two kinds .
for instance requests、urllib This is a network data request tool , That is, grab the bag ;xpath、re、bs4 This will parse the captured web content , It is called parsing package .
Crawl Baidu home page pictures , And save to local
# Import urlopenfrom urllib.request import urlopen# Import BeautifulSoupfrom bs4 import BeautifulSoup as bf# Import urlretrieve function , For downloading pictures from urllib.request import urlretrieve# The request for HTMLhtml = urlopen("http://www.baidu.com/")# use BeautifulSoup analysis htmlobj = bf(html.read(),'html.parser')# From the label head、title Take the title title = obj.head.title# Extract only logo Picture information logo_pic_info = obj.find_all('img',class_="index-logo-src")# extract logo Links to pictures logo_url = "https:"+logo_pic_info[0]['src']# Use urlretrieve Download the pictures urlretrieve(logo_url, 'logo.png')10、Python Working with pictures and charts The image processing 、 Graph visualization involves image processing , This is also Python The strengths of , Now such as image recognition 、 Computer vision and other frontier fields will also be used Python.
stay Python The packages that process images in include scikit Image、PIL、OpenCV etc. , The packages that handle charts are matplotlib、plotly、seaborn etc. .
Black and white the picture
from PIL import Imagefrom PIL import ImageEnhanceimg_main = Image.open(u'E:/login1.png')img_main = img_main.convert('L')threshold1 = 138table1 = []for i in range(256): if i < threshold1: table1.append(0) else: table1.append(1)img_main = img_main.point(table1, "1")img_main.save(u'E:/login3.png')Generate statistical charts
import numpy as npimport matplotlib.pyplot as pltN = 5menMeans = (20, 35, 30, 35, 27)womenMeans = (25, 32, 34, 20, 25)menStd = (2, 3, 4, 1, 2)womenStd = (3, 5, 2, 3, 3)ind = np.arange(N) # the x locations for the groupswidth = 0.35 # the width of the bars: can also be len(x) sequencep1 = plt.bar(ind, menMeans, width, yerr=menStd)p2 = plt.bar(ind, womenMeans, width, bottom=menMeans, yerr=womenStd)plt.ylabel('Scores')plt.title('Scores by group and gender')plt.xticks(ind, ('G1', 'G2', 'G3', 'G4', 'G5'))plt.yticks(np.arange(0, 81, 10))plt.legend((p1[0], p2[0]), ('Men', 'Women'))plt.show() Summary All in all Python Will become a popular programming language , Help more people in need , More about Python For information on office automation processing, please pay attention to other relevant articles on the software development network ！
