openpyxl

# pip install openpyxl

def open():
	from openpyxl import load_workbook
	wb = load_workbook('./data/excel.xlsx')
	
	# 获取工作簿
	sh1 = wb.active
	sh2 = wb['Sheet1']
	sh3 = wb.get_sheet_by_name('Sheet1')
	
	print(sh1 is sh2 is sh3)

# 获取Sheets(表)名
def show_sheets():
	from openpyxl import load_workbook
	wb = load_workbook('./data/excel.xlsx')
	
	print(wb.sheetnames)
	
	for sh in wb:
		print(sh.title)

# 获取一个单元格
def get_one_value():
	from openpyxl import load_workbook
	wb = load_workbook('./data/excel.xlsx')
	sh1 = wb.active
	
	value1 = sh1.cell(3,3).value
	value2 = sh1['C2'].value
	
	print(value1,value2)

# 获取多个单元格
def get_many_value():
	from openpyxl import load_workbook
	wb = load_workbook('./data/excel.xlsx')
	sh1 = wb['Sheet1']
	
	# 切片
	cells1 = sh1['C2':'D3']
	cells2 = sh1['A2:D3']
	print('切片1')
	print(cells1)
	print('切片2')
	print(cells2)
	
	# 整行、整列
	cell_row3 = sh1[3]
	cell_col3 = sh1['C']
	print('整行、整列')
	print(cell_row3)
	print(cell_col3)
	
	# 3到5行
	cell_row3_5 = sh1[3:5]
	print('3到5行')
	print(cell_row3_5)
	
	# 3到5列
	cell_col3_5 = sh1['C:D']
	print('3到5列')
	print(cell_col3_5)
	
	# 通过迭代获取数据
	print('通过迭代获取数据')
	for row in sh1.iter_rows(min_row =2 , max_row =5 , max_col=3):
		for cell in row:
			print(cell.value)
	
	for row in sh1.iter_rows(min_row =2 , max_row =5 , min_col =2 , max_col =4):
		for cell in row:
			print(cell.value)

# 获取所以数据
def get_all_dat():
	from openpyxl import load_workbook
	wb = load_workbook('./data/excel.xlsx')
	sh1 = wb['Sheet1']
	
	# 按行获取
	for row in sh1.rows:
		for cell in row:
			print(cell.value)
			
	# 按列获取
	for column in sh1.columns:
		for cell in column:
			print(cell.value)

# 获取行数、列数
def get_num():
	from openpyxl import load_workbook
	wb = load_workbook('./data/excel.xlsx')
	sh1 = wb['Sheet1']
	
	print(sh1.max_row)
	print(sh1.max_column)


if __name__ == "__main__":
	#open()
	#show_sheets()
	#get_one_value()
	get_many_value()
	#get_all_dat()
	#get_num()