使用openpyxl 对Excel(.xlsx格式)文件进行增删改查:
封装的代码如下:
import openpyxl
from openpyxl import Workbook
def create_excel(file_path,sheet_name,data):
"""创建excel并写入数据"""
workbook = Workbook()
sheet = workbook.active # 获取当前sheet
sheet.title = sheet_name # 设置当前sheet名称
# sheet['A1'] = 1 # 在单元格A1写入1
for row in data:
sheet.append(row)
workbook.save(file_path)
def read_excel_by_sheet_name(file_path,sheet_name):
"""根据表名读取excel中的数据"""
workbook = openpyxl.load_workbook(file_path) # 加载文件
sheet_names = workbook.get_sheet_names()
if sheet_name not in sheet_names:
raise Exception("sheet名称不存在")
else:
sheet = workbook[sheet_name] # 获取sheet
data = []
for row in sheet.rows:
row_data = []
for cell in row:
row_data.append(cell.value)
data.append(row_data)
return data
def read_excel_by_sheet_index(file_path,sheet_index):
"""根据表索引读取数据"""
workbook = openpyxl.load_workbook(file_path) # 加载文件
sheet = workbook.worksheets[sheet_index]
data = []
for row in sheet.rows:
row_data = []
for cell in row:
row_data.append(cell.value)
data.append(row_data)
return data
def append_data_to_new_sheet(file_path,new_sheet,data):
"""将数据写入在新创建新的sheet中"""
workbook = openpyxl.load_workbook(file_path) # 加载文件
sheet = workbook.create_sheet(new_sheet) #创建新的sheet
# 按行写入数据
for row in data:
sheet.append(row)
workbook.save(file_path)
def append_data_to_sheet(file_path,sheet_name,data):
"""将数据追加到指定sheet中"""
workbook = openpyxl.load_workbook(file_path) # 加载文件
sheet = workbook[sheet_name]
# 按行写入数据
for row in data:
sheet.append(row)
workbook.save(file_path)
def update_excel_by_cell(file_path,sheet_name,row,column,value):
"""
更新指定单元格的值
:param file_path: 文件路径
:param sheet_name: sheet名称
:param row: 更新的行
:param column: 更新的列
:param value: 更新后的值
:return: None
"""
workbook = openpyxl.load_workbook(file_path) # 加载文件
sheet = workbook[sheet_name] # 获取sheet
if row > sheet.max_row or column > sheet.max_column: # 入参行列超出最大维度抛出异常
raise Exception("参数有误,最大行%s,最大列%s"%(sheet.max_row,sheet.max_column))
else:
sheet.cell(row=row, column=column).value = value # 设置单元格的值
workbook.save(file_path) #
def update_excel_by_row(file_path,sheet_name,row,data):
"""更新指定行数据:100%"""
workbook = openpyxl.load_workbook(file_path) # 加载文件
sheet = workbook[sheet_name] # 获取sheet
# 由于openpyxl的特性,需要创建一个新行来覆盖原有第二行
sheet.insert_rows(row) # 在更换行之前插入一行并写入数据
for col in range(1,len(data)+1):
sheet.cell(row=row, column=col).value = data[col-1]
# 删除原始行
sheet.delete_rows(row+1) #因为插入一行,因此原数据的行数是row+1
workbook.save(file_path) # 保存文件
def update_excel_by_col(file_path,sheet_name,col,data):
"""更新指定列的数据:100%"""
workbook = openpyxl.load_workbook(file_path) # 加载文件
sheet = workbook[sheet_name] # 获取sheet
# 由于openpyxl的特性,需要创建一个新行来覆盖原有第二行
sheet.insert_cols(col) # 在更换行之前插入一行并写入数据
for row in range(1,len(data)+1):
sheet.cell(row=row, column=col).value = data[row-1]
# 删除原始行
sheet.delete_rows(col+1) #因为插入一行,因此原数据的行数是row+1
workbook.save(file_path) # 保存文件
def remove_sheet(file_path, sheet_tag):
"""
删除表格
:param file_path: xlsx文件路径
:param sheet_tag: sheet索引或者sheet名称
:return: None
"""
workbook = openpyxl.load_workbook(file_path) # 加载文件
try:
if isinstance(sheet_tag, int):
# 按索引删除
if sheet_tag >= len(workbook.worksheets):
raise Exception("sheet索引超出最大维度")
sheet = workbook.worksheets[sheet_tag]
elif isinstance(sheet_tag, str):
# 按名称删除
if sheet_tag not in workbook.sheetnames:
raise Exception("sheet名称不存在")
sheet = workbook[sheet_tag]
else:
raise TypeError("sheet_tag应为sheet名称或索引")
except Exception as e:
print(f"发生错误:{e}")
return
workbook.remove(sheet) # 删除sheet
workbook.save(file_path)
print("sheet删除成功")
if __name__ == '__main__':
data = [['月份', '数值'],
[1, 66],
[2, 67],
[3, 68],
[4, 46],
]
data2 = [['月份a', '数值a'],
[2, 5],
[2, 5],
[2, 5],
[2, 5],
]
# create_excel('./data/create.xlsx',"s1",data)
# append_data_to_new_sheet('./data/test2.xlsx','new_sheet2',data)
# update_excel_by_cell('./data/test2.xlsx','new_sheet2',100,1,'哈哈哈')
# update_excel_by_row('./data/test2.xlsx', 'new_sheet2', 3, [1,2,3,4,5,6,7,8,9,10])
# update_excel_by_col('./data/test2.xlsx', 'new_sheet2', 5, [1,2,3,4,5,6,7,8,9,10])
# s = read_excel('./data/test2.xlsx','new_sheet2')
# s = read_excel_by_sheet_index('./data/test2.xlsx',1)
# print(s)
remove_sheet('./data/test2.xlsx', 1)