本文主要是记录Python openpyxl方式读写excel
openpyxl用于创建xlsx后缀的excel,读写操作需要分开,如果在写过程中使用读方法,会有报错.
[openpyxl] 创建一个excel文件,并写入表头和内容
--[wins]安装openpyxl包: pip3 installer openpyxl
import openpyxl
import os
# 创建一个excel,传递参数:excel路径
def create_excel(file_path_and_name):
try:
if os.path.exists(file_path_and_name):
print("文件已存在")
# 删除文件
# os.remove(file_path_and_name )
# 创建一个excel对象,指定字符集utf-8
workbook = openpyxl.Workbook("utf-8")
# excel对象里添加一个Sheet1
sheet = workbook.create_sheet("Sheet1")
# 保存excel至文件中
workbook.save(file_path_and_name)
print("创建excel文件成功")
except Exception as e:
raise e
# 为excel添加表头,传递参数:excel路径
def add_excel_title(file_path_and_name):
try:
if os.path.exists(file_path_and_name):
# 向表中添加表头
# 表头内容
title = ["序号", "日期", "等级"]
# 打开excel
workbook = openpyxl.load_workbook(file_path_and_name)
# 根据sheet名称选取
sheet = workbook["Sheet1"]
# sheet = workbook .get_sheet_by_name('Sheet1')
# 选择当前显示,活动的sheet
# sheet = workbook .active
# sheet = workbook .get_active_sheet()
# 指定当前显示(活动)的sheet对象
# sheet = workbook .active
for i in range(len(title)):
# 在第一行写入表头(第一个值是行,第二个值是列,第三个是内容)
sheet.cell(row=1, column=i + 1, value=title[i])
# 保存表头
workbook.save(file_path_and_name)
print("写入excel表头成功")
else:
# 文件不存在,则提示
print("文件不存在")
return ""
except Exception as e:
raise e
# 将数据追加至一个已存在的excel,传递参数:excel路径
def add_data_to_excel(file_path_and_name):
try:
if os.path.exists(file_path_and_name):
data_list = [["1", "2021-01-01", "A"], ["2", "2021-01-01", "B"]]
# 打开excel
workbook = openpyxl.load_workbook(file_path_and_name)
# 复制excel
sheet = workbook["Sheet1"]
# 获取文件的最大行,在最大行后添加数据
row_num = sheet.max_row
row_cur = 0
for i in range(len(data_list)):
# 当前行
row_cur = row_cur + 1
for j in range(len(data_list[i])):
# (j+1)当前列,从1开始
sheet.cell(row=row_num + row_cur, column=j+1, value=data_list[i][j])
workbook.save(file_path_and_name)
print("写入excel成功")
else:
print("文件不存在")
except Exception as e:
raise e
if __name__ == '__main__':
file_path_and_name = r"d:\demo.xlsx"
create_excel(file_path_and_name)
add_excel_title(file_path_and_name)
add_data_to_excel(file_path_and_name)