In the process of data processing and analysis, Excel files are a common format in our daily work. Through Python, we can implement various automated operations on Excel files and improve work efficiency.
This article will share 10 practical Excel automation scripts again to help novice novice master these skills more easily.
1. Excel cell batch fill
import pandas as pd
# Batch fills the cells of the specified columndef fill_column(file_path, column_name, value):
df = pd.read_excel(file_path)
df[column_name] = value # Fill all cells of the specified column with value df.to_excel(file_path, index=False)
fill_column('', 'Remark', 'Processed')
print("The comment column has been populated successfully!")
explain
This script populates all the Notes columns in it as Processed. For ordinary users, when processing large amounts of data, a certain column is often required to be uniformly marked, which is particularly important.
2. Set row height and column width
from openpyxl import load_workbook
# Set the row height and column width of Exceldef set_row_column_size(file_path):
wb = load_workbook(file_path)
ws =
# Set the first row height and the first column width ws.row_dimensions[1].height = 30 # Set the row height ws.column_dimensions['A'].width = 20 # Set column width
(file_path)
set_row_column_size('')
print("The row height and column width are set successfully!")
explain
This script sets the row height of the first row and the column width of the first column for the Excel file. Appropriate adjustment of row height and column width can improve the readability of the table, especially when there are more or more complex content. Using this feature can make the report more beautiful and easy to read.
3. Delete the row according to the conditions
# Delete rows in Excel according to conditionsdef delete_rows_based_on_condition(file_path, column_name, condition):
df = pd.read_excel(file_path)
df = df[df[column_name] != condition] # Delete rows that meet the criteria df.to_excel(file_path, index=False)
delete_rows_based_on_condition('', 'state', 'invalid')
print("The row that meets the criteria has been deleted!")
explain
This script removes rows with the value "Invalid" in the Status column from Excel. This operation is very common during data cleaning and helps reduce noise in the data set and improves the accuracy of data analysis.
4. Create a new Excel worksheet
# Create a new worksheet in an existing Excel filedef create_new_sheet(file_path, sheet_name):
wb = load_workbook(file_path)
wb.create_sheet(title=sheet_name) # Create a new worksheet (file_path)
create_new_sheet('', 'New worksheet')
print("New worksheet was created successfully!")
explain
This script creates a new worksheet in an existing Excel file. This is very useful for organizing data, separating data from different tasks or projects, keeping the file structure clear.
5. Import CSV files to Excel
# Import CSV files into Excel worksheetsdef import_csv_to_excel(csv_file, excel_file):
df = pd.read_csv(csv_file)
df.to_excel(excel_file, index=False)
import_csv_to_excel('', 'imported_data.xlsx')
print("The CSV file was successfully imported into Excel!")
explain
This script imports CSV files into Excel. Many times, data is provided in CSV format, and the script can be easily converted to Excel format for subsequent analysis and processing.
6. Pivot table generation
# Generate pivot table and save to a new Excel filedef generate_pivot_table(file_path, index_column, values_column, output_file):
df = pd.read_excel(file_path)
pivot_table = df.pivot_table(index=index_column, values=values_column, aggfunc='sum') # Summary pivot_table.to_excel(output_file)
generate_pivot_table('sales_data.xlsx', 'area', 'Sales', 'pivot_output.xlsx')
print("Pivot table generation successfully!")
explain
The script generates a summary pivot table based on the given Region and Sales columns and saves it to a new file. When conducting business analysis, the pivot table can quickly display data summary in different dimensions.
7. Format Excel
from import Font, Color
# Set Excel cell font styledef format_cells(file_path):
wb = load_workbook(file_path)
ws =
for cell in ws['A']: # traverse column A = Font(bold=True, color="FF0000") # Set font bold and red
(file_path)
format_cells('')
print("Cell formatting successfully!")
explain
This script sets the column A font in it to bold and red. This formatting is often used to emphasize specific data, making the report more visually appealing.
8. Analyze and output descriptive statistics
# Output descriptive statistics to Exceldef descriptive_statistics(file_path, output_file):
df = pd.read_excel(file_path)
stats = () # Calculate descriptive statistics stats.to_excel(output_file)
descriptive_statistics('', 'statistics_output.xlsx')
print("Descriptive statistics output succeeded!")
explain
This script calculates descriptive statistics (such as mean, standard deviation, etc.) of Excel file and saves the results to a new Excel file. This is very important for understanding the basic characteristics of data, especially in the early stages of data analysis.
9. Bulkly modify the Excel file name
import os
# Batch rename Excel files in the specified directorydef rename_excel_files(directory, prefix):
for filename in (directory):
if ('.xlsx'):
new_name = f"{prefix}_{filename}"
((directory, filename), (directory, new_name))
print(f"Already {filename} Rename to {new_name}")
rename_excel_files('/path/to/excel/files', '2024')
explain
This script batch renames all Excel files in the specified directory, prefixing each file name. This batch operation is very convenient for users who need to process large numbers of Excel files, such as naming files based on year or project for easy management and archiving.
10. Automatically send emails containing Excel data
import smtplib
from import MIMEMultipart
from import MIMEApplication
from import MIMEText
# Automatically send emails with Excel attachmentsdef send_email(to_address, subject, body, excel_file):
from_address = "your_email@"
password = "your_password"
msg = MIMEMultipart()
msg['From'] = from_address
msg['To'] = to_address
msg['Subject'] = subject
# Add text (MIMEText(body, 'plain'))
# Add Excel attachments with open(excel_file, "rb") as attachment:
part = MIMEApplication((), Name=(excel_file))
part['Content-Disposition'] = f'attachment; filename="{(excel_file)}"'
(part)
# Send email with ('', 587) as server:
()
(from_address, password)
server.send_message(msg)
send_email('recipient@', 'Monthly Report', 'Please find attached the monthly report.', '')
print("The email was sent successfully!")
explain
This script uses the SMTP protocol to automatically send an email with an Excel file attached. This feature is especially useful in work, such as sending financial statements or performance reports to relevant personnel regularly every month. Automated mailing can save time and reduce human errors.
The above are the detailed content shared by 10 Python Excel automation scripts. For more information about Python Excel automation, please follow my other related articles!