openpyxl读写excel

本文主要是记录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)