Excel 是 Windows 环境下流行的、强大的电子表格应用。openpyxl 模块让 Python 程序能读取和修改 Excel 电子表格文件。例如，可能有一个无聊的任务，需要从一个电子表格拷
贝一些数据，粘贴到另一个电子表格中。或者可能需要从几千行中挑选几行，根据某种条件稍作修改。或者需要查看几百份部门预算电子表格，寻找其中的赤字。正是这种无聊无脑的电子表格任务，可以通过 Python 来完成。
1. 读取电子表格
import openpyxl # 第三方模块
wb = openpyxl.load_workbook('D:\学习成长\宁夏服务台采集监控接口.xlsx') #openpyxl.load_workbook()函数接受文件名，返回一个 workbook 数据类型的值。
print(type(wb)) # 打印wb的类型，应为openpyxl.workbook.workbook.Workbook
print(wb.sheetnames) # 打印工作簿中所有工作表的名称
sheet = wb['表模型配置接口（日全量）'] # 获取名为'Sheet1'的工作表，并赋值给变量sheet
print(type(sheet)) # 打印sheet的类型，应为openpyxl.worksheet.worksheet.Worksheet
print(sheet.title) # 打印工作表的标题
2.从工作簿中读取工作表
import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active # 获取活动工作表
print(sheet.title) # 获取工作表名称
从工作表中取得单元格
import openpyxl
wb = openpyxl.load_workbook('D:\学习成长\宁夏服务台采集监控接口.xlsx')
sheet = wb.active
cell = sheet['A1'] # 获取单元格A1
print(cell.value) # 打印单元格A1的值
cell = sheet.cell(row=2, column=1) # 获取单元格A1
print(cell.value) # 打印单元格A1的值
3.工作簿、工作表、单元格
openpyxl 模块使用工作簿、工作表和单元格这三个术语来表示 Excel 文件中的不同部分。工作簿（workbook）是电子表格文件，工作表（worksheet）是工作簿中的单个表，单元格（cell）是工作表中的单个方格。工作簿、工作表和单元格这三个术语来自电子表格的术语，在 Python 中使用它们来表示 Excel 文件中的相应部分。
3. 遍历工作表中的单元格
import openpyxl
wb = openpyxl.load_workbook('D:\学习成长\宁夏服务台采集监控接口.xlsx')
sheet = wb.active
for row in sheet['A1':'C3']:
for cell in row:
print(cell.coordinate, cell.value)
• 比较一个电子表格中多行的数据。
import pandas as pd
# 读取Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
df = pd.read_excel(file_path)
# 显示数据框的前几行
print("Excel 数据:")
print(df.head())
# 比较多行数据
rows_to_compare = [0, 1, 2] # 替换为你想比较的行索引
comparison_result = df.iloc[rows_to_compare]
print("比较的行数据:")
print(comparison_result)
# 检查所有列是否相同
all_columns_same = comparison_result.apply(lambda x: x.nunique() == 1, axis=0)
print("所有列是否相同:")
print(all_columns_same)
# 检查每一列是否相同
each_column_same = comparison_result.apply(lambda x: x.nunique() == 1, axis=1)
print("每一列是否相同:")
print(each_column_same)
• 打开多个 Excel 文件，跨电子表格比较数据。
import pandas as pd
# 定义要打开的Excel文件列表
file_paths = ['file1.xlsx', 'file2.xlsx', 'file3.xlsx'] # 替换为你的Excel文件路径列表
# 读取所有Excel文件并存储在字典中
dataframes = {file: pd.read_excel(file) for file in file_paths}
# 显示每个数据框的前几行
for file, df in dataframes.items():
print(f"{file} 的前几行数据:")
print(df.head())
print()
# 比较特定列的数据
column_to_compare = '列名' # 替换为你想比较的列名
comparison_data = [df[column_to_compare] for df in dataframes.values()]
# 使用pandas.concat将所有数据合并到一个DataFrame中
comparison_df = pd.concat(comparison_data, keys=file_paths, names=['文件', '行索引'])
# 显示合并后的数据
print("合并后的比较数据:")
print(comparison_df)
# 检查特定列是否相同
all_same = comparison_df.xs(column_to_compare, level=1, axis=1).apply(lambda x: x.nunique() == 1)
print("特定列是否相同:")
print(all_same)
• 检查电子表格是否有空行或无效的数据，如果有就警告。
import pandas as pd
# 读取Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
df = pd.read_excel(file_path)
# 显示数据框的前几行
print("Excel 数据:")
print(df.head())
# 检查是否有空行
empty_rows = df.isnull().all(axis=1)
if empty_rows.any():
print("警告: 存在空行！")
else:
print("没有空行。")
# 检查是否有无效的数据（例如，特定列中的非数字数据）
# 假设我们检查 'Age' 列是否包含非数字数据
invalid_data = pd.to_numeric(df['Age'], errors='coerce').isnull()
if invalid_data.any():
print("警告: 'Age' 列包含无效数据！")
else:
print("'Age' 列没有无效数据。")
# 检查整个数据框是否有任何无效数据
invalid_data_anywhere = df.isnull().values.any()
if invalid_data_anywhere:
print("警告: 数据框中存在无效数据！")
else:
print("数据框中没有无效数据。")
• 从电子表格中读取数据，将它作为 Python 程序的输入。
import pandas as pd
# 读取Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
df = pd.read_excel(file_path)
# 显示数据框的前几行
print("Excel 数据:")
print(df.head())
# 假设我们要将 '列名' 列的数据作为程序的输入
# 替换 '列名' 为你实际的列名
input_data = df['列名'].tolist()
# 现在 input_data 是一个包含 '列名' 列所有数据的列表
# 你可以将其作为程序的输入进行进一步处理
print("程序输入数据:")
print(input_data)
# 示例：计算输入数据的平均值
if input_data: # 确保列表不为空
average_value = sum(input_data) / len(input_data)
print(f"输入数据的平均值是: {average_value}")
else:
print("输入数据为空，无法计算平均值。")
4.创建并保存 Excel 文档
import openpyxl
wb = openpyxl.Workbook() # 创建一个新的工作簿 这只是在内存中创建了一个新的工作簿，还没有保存到文件中
ws1 = wb.active # 获取工作簿的活动工作表
ws1.title = 'test1' # 设置工作表的标题
ws2 = wb.create_sheet(title='test2') # 创建一个新的工作表，并设置标题为'test2'
ws3 = wb.create_sheet(title='test3') # 创建一个新的工作表，并设置标题为'test3'
ws4 = wb.create_sheet(title='test4') # 创建一个新的工作表，并设置标题为'test4'
wb.save('D:\学习成长\example_create.xlsx') # 保存工作簿到文件example.xlsx
# 这种创建它不会自动保存，需要调用save()方法保存到文件中
# 给sheet1添加一些数据
ws1['A1'] = 'Talleres'
ws1['B1'] = 'México'
ws1['A2'] = 'Fórmula E'
ws1['B2'] = 'España'
# 给sheet2添加一些数据
# 往工作表中添加数据有很多种方法
ws2.append(['Talleres', 'México'])
ws2.append(['Fórmula E', 'España'])
# 往sheet3添加一些数据
data = [
['Talleres', 'Data1', 'Data2'],
['Workshop', 'Data3', 'Data4']
]
for row in data:
ws3.append(row)
wb.save('D:\学习成长\example_create.xlsx') # 保存工作簿到文件example.xlsx
5.更新一个电子表格
• 循环遍历所有行。
• 如果该行是 Garlic、Celery 或 Lemons，更新价格。
这意味着代码需要做下面的事情：
• 打开电子表格文件。
• 针对每一行，检查列 A 的值是不是 Celery、Garlic 或 Lemon。
• 如果是，更新列 B 中的价格。
• 将该电子表格保存为一个新文件（这样就不会丢失原来的电子表格，以防万一）
import openpyxl
# 加载Excel文件
file_path = 'D:\学习成长\example_create.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择工作表
ws = wb.active # 获取活动工作表
# 或者通过名称选择工作表
# ws = wb['SheetName']
# 创建一个字典来存储更新后的价格
price_updates = {
'Celery': 1.99,
'Garlic': 2.49,
'Lemon': 0.99
}
# 遍历工作表中的所有行
for row in ws.iter_rows(min_row=2, values_only=False): # 从第二行开始，跳过标题行
cell_a = row[0] # 获取列A的单元格
cell_b = row[1] # 获取列B的单元格
# 检查列A的值是否在字典的键中
if cell_a.value in price_updates:
# 更新列B中的价格为字典中对应的值
cell_b.value = price_updates[cell_a.value]
if cell_a.value is None or cell_a.value == '':
ws.delete_rows(cell_a.row)
# 指定新文件的保存路径
new_file_path = 'D:\学习成长\example_create1.xlsx' # 替换为你想要保存的新文件路径
# 保存更改到新文件
wb.save(new_file_path)
6.从电子表格中删除数据
import openpyxl
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 删除A1单元格
ws.delete_rows(1) # 删除第一行
ws.delete_cols(1) # 删除第一列
# 保存更改
wb.save(file_path)
7.从电子表格中提取数据
• 从电子表格中提取数据，将其作为 Python 程序的输入。
import pandas as pd
# 读取Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
df = pd.read_excel(file_path)
# 显示数据框的前几行
print("Excel 数据:")
print(df.head())
# 假设我们要将 '列名' 列的数据作为程序的输入
# 替换 '列名' 为你实际的列名
input_data = df['列名'].tolist()
# 现在 input_data 是一个包含 '列名' 列所有数据的列表
# 你可以将其作为程序的输入进行进一步处理
print("程序输入数据:")
print(input_data)
# 示例：计算输入数据的平均值
if input_data: # 确保列表不为空
average_value = sum(input_data) / len(input_data)
print(f"输入数据的平均值是: {average_value}")
else:
print("输入数据为空，无法计算平均值。")
8.将数据写入电子表格
• 将数据写入电子表格，作为程序输出的一部分。
import pandas as pd
# 创建一个数据框
data = {'Name': ['Tom', 'Nick', 'John'],
'Age': [28, 32, 25],
'City': ['New York', 'Paris', 'London']}
df = pd.DataFrame(data)
# 将数据框写入Excel文件
output_file_path = 'output.xlsx' # 替换为你想要保存的文件路径
df.to_excel(output_file_path, index=False)
9.处理电子表格中的公式
• 在电子表格中创建和更新公式。
• 计算电子表格中的公式。
import openpyxl
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 在单元格中创建公式
ws['A1'] = 10
ws['B1'] = 20
ws['C1'] = '=A1+B1' # 在C1单元格中创建一个公式，计算A1和B1的和
# 保存更改
wb.save(file_path)
10.处理电子表格中的图表
• 在电子表格中创建和更新图表。
• 更新图表的数据源。
import openpyxl
from openpyxl.chart import BarChart, Reference
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 创建一个柱状图
chart = BarChart()
chart.title = "Sample Bar Chart"
chart.x_axis.title = "X Axis"
chart.y_axis.title = "Y Axis"
# 指定图表的数据源
data = Reference(ws, min_col=1, min_row=1, max_col=2, max_row=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
# 将图表添加到工作表中
ws.add_chart(chart, "E5")
# 保存更改
wb.save(file_path)
11.处理电子表格中的图片
• 在电子表格中插入和删除图片。
• 更新图片的位置和大小。
import openpyxl
from openpyxl.drawing.image import Image
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 插入图片
image_path = 'your_image_file.png' # 替换为你的图片文件路径
img = Image(image_path)
ws.add_image(img, 'A1')
# 保存更改
wb.save(file_path)
12.处理电子表格中的宏
• 在电子表格中运行宏。
• 创建和编辑宏。
import openpyxl
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 运行宏
# 假设你的宏名称为 "MyMacro"
ws['A1'].value = "Hello, World!"
ws['A2'].value = "This is a macro example."
# 保存更改
wb.save(file_path)
13.处理电子表格中的条件格式
• 在电子表格中应用条件格式。
• 更新条件格式规则。
import openpyxl
from openpyxl.styles import PatternFill
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 应用条件格式
red_fill = PatternFill(start_color="FFFF0000",
end_color="FFFF0000",
fill_type="solid")
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
for cell in row:
if cell.value > 50: # 假设我们只对大于50的值应用条件格式
cell.fill = red_fill
# 保存更改
wb.save(file_path)
14.处理电子表格中的数据验证
• 在电子表格中设置数据验证规则。
• 更新数据验证规则。
import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 设置数据验证规则
dv = DataValidation(type="list", formula1='"Option1,Option2,Option3"', showDropDown=True)
ws.add_data_validation(dv)
# 应用数据验证规则到指定单元格范围
dv.add(ws['A1:A10'])
# 保存更改
wb.save(file_path)
15.处理电子表格中的超链接
• 在电子表格中插入和删除超链接。
• 更新超链接的目标。
import openpyxl
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 插入超链接
ws['A1'].hyperlink = 'https://www.example.com'
# 保存更改
wb.save(file_path)
16.处理电子表格中的批注
• 在电子表格中插入和删除批注。
• 更新批注的内容。
import openpyxl
# 打开现有的Excel文件
file_path = 'your_excel_file.xlsx' # 替换为你的Excel文件路径
wb = openpyxl.load_workbook(file_path)
# 选择要操作的工作表
sheet_name = 'Sheet1' # 替换为你的工作表名称
ws = wb[sheet_name]
# 插入批注
comment = "This is a comment."
ws['A1'].comment = openpyxl.comments.Comment(comment, "Author")
# 保存更改
wb.save(file_path)
浙公网安备 33010602011771号