Task02：Python与Excel

---

首先，安装openpyxl和xlwt模块pip install openpyxl、pip install xlwt

Excel读取

test.xlsx表如下：

读取对应表格

# 导包
from openpyxl import load_workbook
# 1.打开已经存在的Excel表格
exl = load_workbook(filename = 'D:/组团学习/test.xlsx')
print(exl.sheetnames)
# ['Work', 'Sheet2', 'Sheet3']

# 2.根据名称或去表格
sheet = exl['work']
#若只有一张表则：
#sheet = exl_1.active

# 3.获取Excel 内容占据的大小
print(sheet.dimensions)
# A1:L14

读取单元格

# 1.获取某个单元格的具体内容
cell = sheet.cell(row=5,column=5) #指定行列数
print(cell.value)
# 5
cell_1 = sheet['C3'] #指定坐标
print(cell_1.value)
# 3

# 2.获取单元格对应的行、列和坐标
print(cell_1.row, cell_1.column, cell.coordinate)
# 3 3 E5

读取多个格子的值

# 1.指定坐标范围
cells = sheet['A1:B3'] #A1到B3区域的值

# 2.指定行的值
Row = sheet[1] #第1行的值
Rows = sheet[1:2] #第1到2行的值

# 3.指定列的值
Column = sheet['A'] #第A列
Columns = sheet['A:C'] #第A到C列

# 4.指定范围的值
# 行获取
for row in sheet.iter_rows(min_row = 1, max_row = 5,
						   min_col = 2, max_col = 6):
	print(row)
	# 一列由多个单元格组成，若需要获取每个单元格的值则循环获取即可
	for cell in row:
		print(cell.value)
		
# 列获取
for col in sheet.iter_cols(min_row = 1, max_row = 5,
						   min_col = 2, max_col = 6):
	print(col)
	
	for cell in col:
		print(cell.value)

练习题

找出test_1.xlsx中sheet1表中空着的格子，并输出这些格子的坐标

from openpyxl import load_workbook

exl = load_workbook('D:/组团学习/test.xlsx')
sheet = exl.active
for row in sheet.iter_rows(min_row = 1, max_row = 15,
						   min_col = 1, max_col = 16):
	#具体查看对应表格的行列数
	for cell in row:
		if not cell.value:
			print(cell.coordinate)
"""
输出：
D5
I6
L7
G8
K11
E12
"""

Excel写入

写入单元格并保存

from openpyxl import load_workbook

exl = load_workbook(filename = 'D:/组团学习/test.xlsx')
sheet = exl.active
sheet['A1'] = 'hello word'       
#或者cell = sheet['A1'] 
#cell.value = 'hello word'
exl.save(filename = 'D:/组团学习/test1.xlsx') 

写入行数据并保存

# 1.写入一行数据并保存
import xlwt
workbook = xlwt.Workbook(encoding = 'utf-8')
# 创建一个sheet
sheet = workbook.add_sheet('My Worksheet')

# 写入excel
# 参数对应 行, 列, 值
sheet.write(1,0,label = 'this is test')

# 保存
workbook.save('D:/组团学习/new_test1.xls')

# 2.写入多行数据并保存
import xlwt
workbook = xlwt.Workbook(encoding = 'utf-8')
# 创建一个sheet
sheet = workbook.add_sheet('My Worksheet')

data = [['hello',22,'hi'],
        ['hell',23,'h'],
        ['he',25,'him']]
for i in range(len(data)):
    for j in range(len(data[i])):
        sheet.write(i,j,data[i][j])
workbook.save('D:/组团学习/new_test2.xls')

将公式写入单元格保存

from openpyxl import load_workbook

exl = load_workbook(filename = 'D:/组团学习/test.xlsx')
sheet = exl.active
sheet['A2'] = '=SUM(A1:I1)'
exl.save(filename='D:/组团学习/test3.xlsx')

插入列数据

# 1.插入一列
sheet.insert_cols(idx=2)  # 第2列前插入一列

# 2.插入多列
sheet.insert_cols(idx=2, amount=5)  # 第2列前插入5列

插入行数据

# 1.插入一行
sheet.insert_cols(idx=2)  # 第2行前插入一行
# 2.插入多行
sheet.insert_cols(idx=2, amount=5)  # 第2行前插入5行

删除

# 1. 删除多列
sheet.delete_cols(idx=5, amount=2) #第5列前删除2列
# 2. 删除多行
sheet.delete_rows(idx=5, amount=2) #第5前行删除2行

移动

# 当数字为正即向下或向右，为负即为向上或向左
sheet.move_range('C5:F10', row=2, cols=-3)  # 向下移动2行，向左移动3列

Sheet表操作

from openpyxl import load_workbook
exl = load_workbook(filename = 'D:/组团学习/test.xlsx')

# 1.创建新的sheet
exl.create_sheet('new_sheet')

# 2.复制已有的sheet
exl.copy_worksheet(sheet)

# 3.修改sheet表名
sheet = exl['Sheet2 ']
sheet.title = 'newSheet2'

创建新的Excel表

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active
workbook.save(filename = 'D:/组团学习/new_test3.xlsx')

练习题

打开test文件，找出文件中购买数量buy_mount超过5的行，并对其标红、加粗、附上边框。

from openpyxl import load_workbook
from openpyxl.styles import Font, Side, Border 

workbook = load_workbook('D:/组团学习/test.xlsx') 
sheet = workbook.active
buy_mount = sheet['F'] 
row_lst = []

for cell in buy_mount:
	if isinstance(cell.value, int) and cell.value > 5: 
		# print(cell.row)
		row_lst.append(cell.row)
		
side = Side(style='thin', color='FF000000')
border = Border(left=side, right=side, top=side, bottom=side) 
font = Font(bold=True, color='FF0000')
for row in row_lst:
	for cell in sheet[row]: 
		cell.font = font 
		cell.border = border
workbook.save('D:/组团学习/new_test4.xlsx')