Python分享20个Excel自动化脚本

在数据处理和分析的过程中，Excel文件是我们一样寻常工作中常见的格式。通过Python，我们可以实现对Excel文件的各种自动化操作，进步工作服从。

本文将分享20个实用的Excel自动化脚本，以资助新手小白更轻松地掌握这些技能。



1. Excel单元格批量添补

1. import pandas as pd  
3. # 批量填充指定列的单元格  
4. def fill_column(file_path, column_name, value):  
5.     df = pd.read_excel(file_path)  
6.     df[column_name] = value  # 将指定列的所有单元格填充为value  
7.     df.to_excel(file_path, index=False)  
9. fill_column('example.xlsx', '备注', '已处理')  
10. print("备注列已成功填充！")

复制代码

解释

此脚本将example.xlsx中的“备注”列全部添补为“已处理”。对于普通用户来说，处理大量数据时常必要对某一列进行同一标志，这个功能就显得尤为重要。


2. 设置行高与列宽

1. from openpyxl import load_workbook  
3. # 设置Excel的行高与列宽  
4. def set_row_column_size(file_path):  
5.     wb = load_workbook(file_path)  
6.     ws = wb.active  
8.     # 设置第一行行高、第一列列宽  
9.     ws.row_dimensions[1].height = 30  # 设置行高  
10.     ws.column_dimensions['A'].width = 20  # 设置列宽  
12.     wb.save(file_path)  
14. set_row_column_size('example.xlsx')  
15. print("行高和列宽设置成功！")

复制代码

解释

这个脚本为Excel文件设置了第一行的行高和第一列的列宽。适当调解行高和列宽可以进步表格的可读性，尤其是在内容较多或较复杂时，使用此功能可以使报告更加雅观易读。


3. 根据条件删除行

1. # 根据条件删除Excel中的行  
2. def delete_rows_based_on_condition(file_path, column_name, condition):  
3.     df = pd.read_excel(file_path)  
4.     df = df[df[column_name] != condition]  # 删除满足条件的行  
5.     df.to_excel(file_path, index=False)  
7. delete_rows_based_on_condition('example.xlsx', '状态', '无效')  
8. print("符合条件的行已删除！")

复制代码

解释

该脚本从Excel中删除“状态”列中值为“无效”的行。这种操作在数据清理过程中非常常见，有助于减少数据集中的噪声，进步数据分析的正确性。


4. 创建新的Excel工作表

1. # 在现有Excel文件中创建新的工作表  
2. def create_new_sheet(file_path, sheet_name):  
3.     wb = load_workbook(file_path)  
4.     wb.create_sheet(title=sheet_name)  # 创建新的工作表  
5.     wb.save(file_path)  
7. create_new_sheet('example.xlsx', '新工作表')  
8. print("新工作表创建成功！")

复制代码

解释

该脚本在已有的Excel文件中创建一个新的工作表。这对于组织数据，分开不同任务或项目的数据非常有用，保持文件布局的清楚。


5. 导入CSV文件到Excel

1. # 将CSV文件导入到Excel工作表  
2. def import_csv_to_excel(csv_file, excel_file):  
3.     df = pd.read_csv(csv_file)  
4.     df.to_excel(excel_file, index=False)  
6. import_csv_to_excel('data.csv', 'imported_data.xlsx')  
7. print("CSV文件成功导入到Excel！")

复制代码

解释

这个脚本将CSV文件导入到Excel中。很多时候，数据是以CSV格式提供的，通过该脚本可以方便地将其转换为Excel格式，便于后续分析和处理。


6. 数据透视表生成

1. # 生成数据透视表并保存到新的Excel文件  
2. def generate_pivot_table(file_path, index_column, values_column, output_file):  
3.     df = pd.read_excel(file_path)  
4.     pivot_table = df.pivot_table(index=index_column, values=values_column, aggfunc='sum')  # 汇总  
5.     pivot_table.to_excel(output_file)  
7. generate_pivot_table('sales_data.xlsx', '地区', '销售额', 'pivot_output.xlsx')  
8. print("透视表生成成功！")

复制代码

解释

该脚本根据给定的“地区”和“销售额”列生成汇总透视表，并保存到新文件中。在进行业务分析时，透视表能快速展示不同维度下的数据总结。


7. 格式化Excel

1. from openpyxl.styles import Font, Color  
3. # 设置Excel单元格字体样式  
4. def format_cells(file_path):  
5.     wb = load_workbook(file_path)  
6.     ws = wb.active  
8.     for cell in ws['A']:  # 遍历A列  
9.         cell.font = Font(bold=True, color="FF0000")  # 设置字体加粗和红色  
11.     wb.save(file_path)  
13. format_cells('example.xlsx')  
14. print("单元格格式化成功！")

复制代码

解释

该脚本将example.xlsx中的A列字体设置为加粗和赤色。这种格式化通常用于强调特定数据，使报告更具视觉吸引力。


8. 分析并输出形貌性统计

1. # 输出描述性统计到Excel  
2. def descriptive_statistics(file_path, output_file):  
3.     df = pd.read_excel(file_path)  
4.     stats = df.describe()  # 计算描述性统计  
5.     stats.to_excel(output_file)  
7. descriptive_statistics('example.xlsx', 'statistics_output.xlsx')  
8. print("描述性统计输出成功！")

复制代码

解释

该脚本盘算Excel文件的形貌性统计信息（如均值、尺度差等），并将结果保存到新的Excel文件中。这对于了解数据的基本特性非常重要，尤其在数据分析前期阶段。
9. 批量修改Excel文件名称

1. import os  
3. # 批量重命名指定目录下的Excel文件  
4. def rename_excel_files(directory, prefix):  
5.     for filename in os.listdir(directory):  
6.         if filename.endswith('.xlsx'):  
7.             new_name = f"{prefix}_{filename}"  
8.             os.rename(os.path.join(directory, filename), os.path.join(directory, new_name))  
9.             print(f"已将 {filename} 重命名为 {new_name}")  
11. rename_excel_files('/path/to/excel/files', '2024')

复制代码

解释

该脚本批量重命名指定目录中的所有Excel文件，在每个文件名前面添加一个前缀。对于必要处理大量Excel文件的用户来说，这种批量操作非常便利，比如根据年份或项目为文件命名，以便于管理和归档。
10. 自动发送包含Excel数据的电子邮件

1. import smtplib  
2. from email.mime.multipart import MIMEMultipart  
3. from email.mime.application import MIMEApplication  
4. from email.mime.text import MIMEText  
6. # 自动发送带有Excel附件的电子邮件  
7. def send_email(to_address, subject, body, excel_file):  
8.     from_address = "your_email@example.com"  
9.     password = "your_password"  
11.     msg = MIMEMultipart()  
12.     msg['From'] = from_address  
13.     msg['To'] = to_address  
14.     msg['Subject'] = subject  
16.     # 添加正文  
17.     msg.attach(MIMEText(body, 'plain'))  
19.     # 添加Excel附件  
20.     with open(excel_file, "rb") as attachment:  
21.         part = MIMEApplication(attachment.read(), Name=os.path.basename(excel_file))  
22.         part['Content-Disposition'] = f'attachment; filename="{os.path.basename(excel_file)}"'  
23.         msg.attach(part)  
25.     # 发送邮件  
26.     with smtplib.SMTP('smtp.example.com', 587) as server:  
27.         server.starttls()  
28.         server.login(from_address, password)  
29.         server.send_message(msg)  
31. send_email('recipient@example.com', 'Monthly Report', 'Please find attached the monthly report.', 'report.xlsx')  
32. print("邮件发送成功！")

复制代码

解释

此脚本使用SMTP协议自动发送一封电子邮件，其中附带了一个Excel文件。这个功能在工作中尤其有用，比如每月定期发送财务报表或业绩报告给相关职员。通过自动化邮件发送，可以节省时间并减少人为错误。
11. 归并多个Excel文件

1. import pandas as pd
2. import os
4. def merge_excel_files(folder_path, output_file):
5.     all_data = pd.DataFrame()
6.     for filename in os.listdir(folder_path):
7.         if filename.endswith('.xlsx'):
8.             file_path = os.path.join(folder_path, filename)
9.             df = pd.read_excel(file_path)
10.             all_data = pd.concat([all_data, df], ignore_index=True)
11.     all_data.to_excel(output_file, index=False)
13. merge_excel_files('your_folder_path', 'merged_file.xlsx')
14. print("多个Excel文件合并成功！")

复制代码

解释

该脚本将指定文件夹下的所有Excel文件归并成一个文件。在处理分散在多个文件中的数据时，这个功能可以将数据整合在一起，方便后续的同一分析。
12. 拆分Excel文件

1. import pandas as pd
3. def split_excel_file(file_path, column_name, output_folder):
4.     df = pd.read_excel(file_path)
5.     unique_values = df[column_name].unique()
6.     for value in unique_values:
7.         sub_df = df[df[column_name] == value]
8.         output_file = os.path.join(output_folder, f'{value}.xlsx')
9.         sub_df.to_excel(output_file, index=False)
11. split_excel_file('example.xlsx', '部门', 'output_folder')
12. print("Excel文件拆分成功！")

复制代码

解释

此脚本根据指定列的唯一值将Excel文件拆分成多个文件。例如，按照“部门”列将数据拆分成不同部门对应的文件，便于各部门独立查看和处理自己的数据。
13. 更换单元格内容

1. import pandas as pd
3. def replace_cell_content(file_path, column_name, old_value, new_value):
4.     df = pd.read_excel(file_path)
5.     df[column_name] = df[column_name].replace(old_value, new_value)
6.     df.to_excel(file_path, index=False)
8. replace_cell_content('example.xlsx', '产品名称', '旧产品', '新产品')
9. print("单元格内容替换成功！")

复制代码

解释

该脚本将指定列中的特定内容更换为新的内容。在数据修正或更新时，这个功能可以快速修改数据中的错误或过时信息。
14. 对数据进行排序

1. import pandas as pd
3. def sort_excel_data(file_path, column_name, ascending=True):
4.     df = pd.read_excel(file_path)
5.     df = df.sort_values(by=column_name, ascending=ascending)
6.     df.to_excel(file_path, index=False)
8. sort_excel_data('example.xlsx', '销售额', ascending=False)
9. print("数据排序成功！")

复制代码

解释

这个脚本的重要功能是对 Excel 文件中的数据根据指定列进行排序操作，而且可以选择升序或降序排列，最后将排序后的数据保存回原 Excel 文件。排序操作在数据处理和分析中非常常见，例如按照销售额对销售数据进行降序排序，能快速找出销售额高的记载。
15. 统计特定列的唯一值数量

1. import pandas as pd
3. def count_unique_values(file_path, column_name):
4.     df = pd.read_excel(file_path)
5.     unique_count = df[column_name].nunique()
6.     print(f"{column_name}列的唯一值数量为: {unique_count}")
8. count_unique_values('example.xlsx', '客户编号')

复制代码

解释

该脚本用于统计Excel文件中指定列的唯一值数量。在数据分析中，了解某列有多少不同的值可以资助我们快速掌握数据的分布情况，例如统计客户编号的唯一值数量可以知道有多少不同的客户。
16. 提取指定列到新的Excel文件

1. import pandas as pd
3. def extract_columns(file_path, columns, output_file):
4.     df = pd.read_excel(file_path)
5.     new_df = df[columns]
6.     new_df.to_excel(output_file, index=False)
8. extract_columns('example.xlsx', ['姓名', '年龄'], 'extracted_columns.xlsx')
9. print("指定列提取成功！")

复制代码

解释

此脚本可以从一个Excel文件中提取指定的列，并保存到一个新的Excel文件中。当我们只必要数据中的部门信息时，使用这个脚本可以快速筛选出所需的数据，制止处理大量无关信息。
17. 为Excel表格添加边框

1. from openpyxl import load_workbook
2. from openpyxl.styles import Border, Side
4. def add_border_to_excel(file_path):
5.     wb = load_workbook(file_path)
6.     ws = wb.active
7.     thin_border = Border(left=Side(style='thin'),
8.                          right=Side(style='thin'),
9.                          top=Side(style='thin'),
10.                          bottom=Side(style='thin'))
11.     for row in ws.iter_rows():
12.         for cell in row:
13.             cell.border = thin_border
14.     wb.save(file_path)
16. add_border_to_excel('example.xlsx')
17. print("表格边框添加成功！")

复制代码

解释

该脚本为Excel表格中的每个单元格添加了细边框。添加边框可以使表格更加清楚易读，特别是在打印或展示数据时，能够提升表格的雅观度和专业性。
18. 查抄Excel文件中是否存在空行并删除

1. import pandas as pd
3. def remove_empty_rows(file_path):
4.     df = pd.read_excel(file_path)
5.     df = df.dropna(how='all')
6.     df.to_excel(file_path, index=False)
8. remove_empty_rows('example.xlsx')
9. print("空行删除成功！")

复制代码

解释

此脚本用于查抄Excel文件中是否存在所有列都为空的行，并将这些空行删除。空行大概会影响数据处理和分析的结果，通过删除空行可以保证数据的完备性和正确性。
19. 根据多列条件筛选数据

1. import pandas as pd
3. def filter_data_by_multiple_conditions(file_path, conditions, output_file):
4.     df = pd.read_excel(file_path)
5.     query_str = ' & '.join([f'{col} {op} {val}' for col, op, val in conditions])
6.     filtered_df = df.query(query_str)
7.     filtered_df.to_excel(output_file, index=False)
9. # 示例条件：年龄大于25且性别为女
10. conditions = [('年龄', '>', 25), ('性别', '==', "'女'")]
11. filter_data_by_multiple_conditions('example.xlsx', conditions, 'filtered_data.xlsx')
12. print("多条件筛选数据成功！")

复制代码

解释

该脚本可以根据多个列的条件对Excel数据进行筛选，并将筛选结果保存到新的文件中。在实际数据分析中，我们常常必要根据多个条件来筛选出符合要求的数据，使用这个脚本可以方便地实现多条件筛选。
20. 对Excel中的日期列进行格式化

1. import pandas as pd
3. def format_date_column(file_path, column_name, date_format):
4.     df = pd.read_excel(file_path)
5.     df[column_name] = pd.to_datetime(df[column_name]).dt.strftime(date_format)
6.     df.to_excel(file_path, index=False)
8. format_date_column('example.xlsx', '日期', '%Y-%m-%d')
9. print("日期列格式化成功！")

复制代码

解释

此脚本用于对Excel文件中指定的日期列进行格式化。在处理日期数据时，不同的业务需求大概必要不同的日期格式，通过这个脚本可以将日期列转换为我们必要的格式，方便后续的数据分析和展示。
希望这些Excel自动化脚本能够进一步资助你进步工作服从，更好地掌握Python在Excel数据处理方面的应用！假如你在实践过程中有任何疑问，欢迎随时交换。