必学的20个Excel表格操作Python脚本

引言

本文将介绍使用 Python 处理 Excel 文件的多种方法，涵盖从基本的读写操作到高级的数据分析与可视化。通过这些示例，你可以学习如何高效地管理和分析 Excel 数据。

创作不易，还请各位同学三连点赞！！收藏！！转发！！！

对于刚入门学习Python还找不到方向的小伙伴可以试试我的这份学习方法和籽料，免费自取！！

1. 安装必要的库

在开始之前，确保安装了 pandas 和 openpyxl 这两个库。这两个库是处理 Excel 文件的基础。

pip install pandas openpyxl  

2. 读取 Excel 文件

首先，让我们看看如何读取一个 Excel 文件。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx', engine='openpyxl')  # 显示前五行数据  
print(df.head())  

输出：

   A    B     C  
0  1  100  1000  
1  2  200  2000  
2  3  300  3000  
3  4  400  4000  
4  5  500  5000

3. 写入 Excel 文件

接下来，我们将创建一个新的 DataFrame 并将其写入新的 Excel 文件。

import pandas as pd  # 创建一个 DataFrame  
data = {  'A': [1, 2, 3, 4, 5],  'B': [100, 200, 300, 400, 500],  'C': [1000, 2000, 3000, 4000, 5000]  
}  
df = pd.DataFrame(data)  # 将 DataFrame 写入 Excel 文件  
df.to_excel('output.xlsx', index=False)  

4. 选择特定列

有时候我们只需要 Excel 文件中的某些列。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx', usecols=['A', 'C'])  # 显示前五行数据  
print(df.head())  

输出：

   A     C  
0  1  1000  
1  2  2000  
2  3  3000  
3  4  4000  
4  5  5000

5. 过滤数据

过滤数据可以帮助我们找到特定条件下的记录。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 过滤出 A 列大于 3 的行  
filtered_df = df[df['A'] > 3]  # 显示过滤后的数据  
print(filtered_df)  

输出：

   A    B     C  
3  4  400  4000  
4  5  500  5000

6. 数据排序

排序数据可以让我们更容易地查看数据的趋势。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 按照 A 列降序排列  
sorted_df = df.sort_values(by='A', ascending=False)  # 显示排序后的数据  
print(sorted_df)  

输出：

   A    B     C  
4  5  500  5000  
3  4  400  4000  
2  3  300  3000  
1  2  200  2000  
0  1  100  1000

7. 数据分组

数据分组可以帮助我们分析不同类别的数据。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 按照 B 列分组并计算平均值  
grouped_df = df.groupby('B').mean()  # 显示分组后的数据  
print(grouped_df)  

输出：

          A        C  
B                      
100  1.000000  1000.0  
200  2.000000  2000.0  
300  3.000000  3000.0  
400  4.000000  4000.0  
500  5.000000  5000.0

8. 添加新列

我们可以根据现有列的数据轻松添加新列。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 添加新列 D  
df['D'] = df['A'] * df['B']  # 显示更新后的数据  
print(df)  

输出：

   A    B     C     D  
0  1  100  1000   100  
1  2  200  2000   400  
2  3  300  3000   900  
3  4  400  4000  1600  
4  5  500  5000  2500

9. 更新单元格

有时候我们需要更新特定单元格的值。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 更新 A 列第 2 行的值  
df.at[1, 'A'] = 10  # 显示更新后的数据  
print(df)  

输出：

   A    B     C  
0  1  100  1000  
1 10  200  2000  
2  3  300  3000  
3  4  400  4000  
4  5  500  5000

10. 删除列

删除不需要的列可以简化数据集。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 删除 C 列  
del df['C']  # 显示更新后的数据  
print(df)  

输出：

   A    B  
0  1  100  
1  2  200  
2  3  300  
3  4  400  
4  5  500

11. 合并多个 Excel 文件

合并多个 Excel 文件可以方便地将数据集中到一起。

import pandas as pd  # 读取多个 Excel 文件  
df1 = pd.read_excel('example1.xlsx')  
df2 = pd.read_excel('example2.xlsx')  # 合并两个 DataFrame  
merged_df = pd.concat([df1, df2], ignore_index=True)  # 显示合并后的数据  
print(merged_df)  

输出：

   A    B     C  
0  1  100  1000  
1  2  200  2000  
2  3  300  3000  
3  4  400  4000  
4  5  500  5000

12. 数据透视表

数据透视表是一种强大的工具，可以快速汇总和分析数据。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 创建数据透视表  
pivot_table = pd.pivot_table(df, values='C', index=['A'], columns=['B'], aggfunc=sum)  # 显示数据透视表  
print(pivot_table)  

输出：

B       100   200   300   400   500  
A                          
1       1000  NaN   NaN   NaN   NaN  
2       NaN   2000  NaN   NaN   NaN  
3       NaN   NaN   3000  NaN   NaN  
4       NaN   NaN   NaN   4000  NaN  
5       NaN   NaN   NaN   NaN   5000  

13. 数据合并

合并多个数据集可以让你更好地分析数据之间的关系。

import pandas as pd  # 读取两个 Excel 文件  
df1 = pd.read_excel('example1.xlsx')  
df2 = pd.read_excel('example2.xlsx')  # 使用内连接合并两个数据集  
merged_df = pd.merge(df1, df2, on='A', how='inner')  # 显示合并后的数据  
print(merged_df)  

输出：

   A    B_x    C_x    B_y    C_y  
0  1   100  1000    10    100  
1  2   200  2000    20    200  
2  3   300  3000    30    300  
3  4   400  4000    40    400  
4  5   500  5000    50    500

14. 数据清洗

数据清洗是数据分析的重要步骤，可以去除无效或错误的数据。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 去除空值  
df.dropna(inplace=True)  # 去除重复行  
df.drop_duplicates(inplace=True)  # 显示清洗后的数据  
print(df)  

输出：

   A    B     C  
0  1  100  1000  
1  2  200  2000  
2  3  300  3000  
3  4  400  4000  
4  5  500  5000

15. 数据类型转换

正确设置数据类型有助于节省内存并提高性能。

import pandas as pd  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 将 A 列转换为整型  
df['A'] = df['A'].astype(int)  # 将 B 列转换为浮点型  
df['B'] = df['B'].astype(float)  # 显示转换后的数据  
print(df.dtypes)  

输出：

A         int64  
B        float64  
C        float64  
dtype: object  

16. 数据可视化

使用 matplotlib 库可以方便地绘制图表。

import pandas as pd  
import matplotlib.pyplot as plt  # 读取 Excel 文件  
df = pd.read_excel('example.xlsx')  # 绘制柱状图  
plt.bar(df['A'], df['B'])  
plt.xlabel('A')  
plt.ylabel('B')  
plt.title('A vs B')  
plt.show()  

17. 多页 Excel 文件操作

处理多页 Excel 文件时，可以使用 openpyxl 库。

from openpyxl import load_workbook  # 加载 Excel 文件  
wb = load_workbook('example.xlsx')  # 获取所有工作表名称  
sheet_names = wb.sheetnames  
print(sheet_names)  # 选择特定工作表  
sheet = wb['Sheet1']  # 读取特定单元格的值  
cell_value = sheet.cell(row=1, column=1).value  
print(cell_value)  

输出：

['Sheet1', 'Sheet2', 'Sheet3']  
1  

18. 条件格式化

条件格式化可以帮助你突出显示特定数据。

import pandas as pd  
from openpyxl import Workbook  
from openpyxl.styles import PatternFill  # 创建一个新的 DataFrame  
data = {  'A': [1, 2, 3, 4, 5],  'B': [100, 200, 300, 400, 500],  'C': [1000, 2000, 3000, 4000, 5000]  
}  
df = pd.DataFrame(data)  # 将 DataFrame 写入 Excel 文件  
wb = Workbook()  
ws = wb.active  
df.to_excel(ws, index=False)  # 设置条件格式化  
for row in ws.iter_rows(min_row=2, max_row=6, min_col=2, max_col=2):  for cell in row:  if cell.value > 300:  cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")  # 保存 Excel 文件  
wb.save('condition.xlsx')  

19. 自定义样式

自定义样式可以让你的 Excel 文件更加美观。

import pandas as pd  
from openpyxl import Workbook  
from openpyxl.styles import Font, Border, Side, Alignment  # 创建一个新的 DataFrame  
data = {  'A': [1, 2, 3, 4, 5],  'B': [100, 200, 300, 400, 500],  'C': [1000, 2000, 3000, 4000, 5000]  
}  
df = pd.DataFrame(data)  # 将 DataFrame 写入 Excel 文件  
wb = Workbook()  
ws = wb.active  
df.to_excel(ws, index=False)  # 设置字体样式  
for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=3):  for cell in row:  cell.font = Font(bold=True, color="FFFFFF")  # 设置边框样式  
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(min_row=1, max_row=6, min_col=1, max_col=3):  for cell in row:  cell.border = thin_border  # 设置居中对齐  
for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=3):  for cell in row:  cell.alignment = Alignment(horizontal='center', vertical='center')  # 保存 Excel 文件  
wb.save('styled.xlsx')  

20. 批量处理文件

批量处理多个 Excel 文件可以大大提高效率。

import os  
import pandas as pd  # 获取目录中的所有 Excel 文件  
files = [f for f in os.listdir('.') if f.endswith('.xlsx')]  # 循环处理每个文件  
for file in files:  # 读取 Excel 文件  df = pd.read_excel(file)  # 进行数据处理  df['D'] = df['A'] * df['B']  # 保存处理后的文件  df.to_excel(f'processed_{file}', index=False)  

21. 实战案例：员工绩效分析

假设你有一个包含员工绩效数据的 Excel 文件，需要分析每位员工的绩效。

import pandas as pd  # 读取 Excel 文件  
performance_data = pd.read_excel('employee_performance.xlsx')  # 计算每位员工的总销售额  
performance_data['Total Sales'] = performance_data['Quantity'] * performance_data['Price']  # 分析每位员工的平均销售额  
average_sales = performance_data.groupby('Employee')['Total Sales'].mean()  # 显示平均销售额  
print(average_sales)  

输出：

Employee  
Alice    5000.0  
Bob      6000.0  
Charlie  7000.0  
Dave     8000.0  
Eve      9000.0  
dtype: float64  

总结

本文介绍了使用 Python 处理 Excel 文件的各种方法，包括读取、写入、筛选、排序、分组、添加新列、更新单元格、删除列、合并多个文件、数据透视表、数据合并、数据清洗、数据类型转换、数据可视化、多页文件操作、条件格式化、自定义样式以及批量处理等。通过这些示例，你可以掌握如何高效地管理和分析 Excel 数据。

好了，今天的分享就到这里了，我们下期见。如果本文对你有帮助，请动动你可爱的小手指点赞、收藏吧！