Share 20 practical Python Excel automation scripts

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!")

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!")

# 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!")

# 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!")

# 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!")

# 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!")

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!")

# 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!")

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')

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!")

import pandas as pd
import os

def merge_excel_files(folder_path, output_file):
    all_data = ()
    for filename in (folder_path):
        if ('.xlsx'):
            file_path = (folder_path, filename)
            df = pd.read_excel(file_path)
            all_data = ([all_data, df], ignore_index=True)
    all_data.to_excel(output_file, index=False)

merge_excel_files('your_folder_path', 'merged_file.xlsx')
print("Multiple Excel files merge successfully!")

import pandas as pd

def split_excel_file(file_path, column_name, output_folder):
    df = pd.read_excel(file_path)
    unique_values = df[column_name].unique()
    for value in unique_values:
        sub_df = df[df[column_name] == value]
        output_file = (output_folder, f'{value}.xlsx')
        sub_df.to_excel(output_file, index=False)

split_excel_file('', 'department', 'output_folder')
print("Excel file split successfully!")

import pandas as pd

def replace_cell_content(file_path, column_name, old_value, new_value):
    df = pd.read_excel(file_path)
    df[column_name] = df[column_name].replace(old_value, new_value)
    df.to_excel(file_path, index=False)

replace_cell_content('', 'Product Name', 'Old products', 'New Products')
print("Cell content replacement was successful!")

import pandas as pd

def sort_excel_data(file_path, column_name, ascending=True):
    df = pd.read_excel(file_path)
    df = df.sort_values(by=column_name, ascending=ascending)
    df.to_excel(file_path, index=False)

sort_excel_data('', 'Sales', ascending=False)
print("The data sorted successfully!")

import pandas as pd

def count_unique_values(file_path, column_name):
    df = pd.read_excel(file_path)
    unique_count = df[column_name].nunique()
    print(f"{column_name}The number of unique values ​​of the column is: {unique_count}")

count_unique_values('', 'Customer number')

import pandas as pd

def extract_columns(file_path, columns, output_file):
    df = pd.read_excel(file_path)
    new_df = df[columns]
    new_df.to_excel(output_file, index=False)

extract_columns('', ['Name', 'age'], 'extracted_columns.xlsx')
print("The specified column is extracted successfully!")

from openpyxl import load_workbook
from  import Border, Side

def add_border_to_excel(file_path):
    wb = load_workbook(file_path)
    ws = 
    thin_border = Border(left=Side(style='thin'), 
                         right=Side(style='thin'), 
                         top=Side(style='thin'), 
                         bottom=Side(style='thin'))
    for row in ws.iter_rows():
        for cell in row:
             = thin_border
    (file_path)

add_border_to_excel('')
print("The table border was added successfully!")

import pandas as pd

def remove_empty_rows(file_path):
    df = pd.read_excel(file_path)
    df = (how='all')
    df.to_excel(file_path, index=False)

remove_empty_rows('')
print("Blank line deleted successfully!")

import pandas as pd

def filter_data_by_multiple_conditions(file_path, conditions, output_file):
    df = pd.read_excel(file_path)
    query_str = ' & '.join([f'{col} {op} {val}' for col, op, val in conditions])
    filtered_df = (query_str)
    filtered_df.to_excel(output_file, index=False)

# Example Conditions: Age older than 25 and gender is femaleconditions = [('age', '>', 25), ('gender', '==', "'female'")]
filter_data_by_multiple_conditions('', conditions, 'filtered_data.xlsx')
print("Multiple-condition filtering data was successful!")

import pandas as pd

def format_date_column(file_path, column_name, date_format):
    df = pd.read_excel(file_path)
    df[column_name] = pd.to_datetime(df[column_name]).(date_format)
    df.to_excel(file_path, index=False)

format_date_column('', 'date', '%Y-%m-%d')
print("Date column formatting successfully!")