Preface
As a data analyst, financial staff or office automation enthusiast, Excel form operations are an integral part of our daily work. With its powerful data processing capabilities, Python can greatly improve the efficiency of our operation of Excel. This article will introduce 10 must-learn Python scripts to help you automate Excel table processing and save a lot of repetitive work time.
1. Read Excel file
Excel files can be read easily using openpyxl or pandas library:
import pandas as pd
# Read Excel filedf = pd.read_excel('', sheet_name='Sheet1')
# Show the first 5 rows of dataprint(())
2. Write to Excel file
Save the processed data back to Excel:
# Create a DataFramedata = {'Name': ['John', 'Anna', 'Peter'],
'Age': [28, 24, 35]}
df = (data)
# Write to Excel filedf.to_excel('', index=False)
3. Merge multiple Excel files
Merging is a very common requirement when batching multiple Excel files:
import os
# Get all xlsx files in the current directoryfiles = [f for f in ('.') if ('.xlsx')]
# Merge all filescombined_df = ()
for file in files:
df = pd.read_excel(file)
combined_df = ([combined_df, df], ignore_index=True)
# Save the merged filecombined_df.to_excel('', index=False)
4. Filter and sort data
Python can easily implement complex data filtering and sorting:
# Filter records older than 25 years oldfiltered_df = df[df['Age'] > 25] # Arrange ascending order by namesorted_df = df.sort_values(by='Name') # Multiple sorting: first descending order by age, then ascending order by namemulti_sorted = df.sort_values(by=['Age', 'Name'], ascending=[False, True])
5. Pivot Table
Create a pivot table similar to Excel:
# Assume that df contains columns such as 'Salesperson', 'Region', 'Sales'pivot_table = pd.pivot_table(df,
values='Sales',
index='Salesperson',
columns='Region',
aggfunc='sum',
fill_value=0)
pivot_table.to_excel('pivot_table.xlsx')
6. Conditional formatting
Use openpyxl to implement a conditional format similar to Excel:
from openpyxl import load_workbook
from import PatternFill
# Load the workbookwb = load_workbook('')
ws =
# Create a red fillred_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
# Apply a red background to cells with a median value of less than 50 in column Bfor row in ws.iter_rows(min_col=2, max_col=2):
for cell in row:
if isinstance(, (int, float)) and < 50:
= red_fill
# Save and modify('')
7. Batch renaming worksheet
from openpyxl import load_workbook
wb = load_workbook('')
# Rename all worksheetsfor i, sheet in enumerate(, start=1):
ws = wb[sheet]
= f'Sheet_{i}'
('')
8. Extract specific columns to new file
# Extract 'Name' and 'Email' columns to new fileextracted_df = df[['Name', 'Email']]
extracted_df.to_excel('extracted_data.xlsx', index=False)
9. Automatic filling formula
from openpyxl import load_workbook
wb = load_workbook('')
ws =
# Add SUM formula in column Cfor row in range(2, ws.max_row + 1):
ws[f'C{row}'] = f'=SUM(A{row}:B{row})'
('with_formulas.xlsx')
10. Send emails with Excel attachments
import smtplib
from import MIMEMultipart
from import MIMEText
from import MIMEBase
from email import encoders
# Set the email contentmsg = MIMEMultipart()
msg['From'] = 'your_email@'
msg['To'] = 'recipient@'
msg['Subject'] = 'Automatically sent Excel report'
# Add textbody = "Please check the Excel report in the attachment."
(MIMEText(body, 'plain'))
# Add attachmentfilename = ""
attachment = open(filename, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(())
encoders.encode_base64(part)
part.add_header('Content-Disposition', f"attachment; filename= {filename}")
(part)
# Send emailserver = ('', 587)
()
('your_email@', 'your_password')
text = msg.as_string()
('your_email@', 'recipient@', text)
()
This is the article about 10 must-learn scripts for Python operating Excel. For more related content on Python operating Excel, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!