Python办公自动化_ 2.Python与Excel

Datawhale组队学习自学笔记，开源内容：链接

Excel读取

• 读取表格

#打开已有的Excel表格
from openpyxl import load_workbook
exl = load_workbook(filename = 'test.xlsx')
print(exl.sheetnames)

sheet = exl_1['work'] #根据名称获取表格
sheet = exl_1.active  #若只有一张表

#获取Excel内容大小
print(sheet.dimensions)

• 读取单元格

cell = sheet.cell(row=1,column=2) #指定行列数
print(cell.value)

cell_1 = sheet['A1'] #指定坐标
print(cell_1.value)

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

• 读取多个单元格的值

cells = sheet['A1:C8'] #指定坐标的范围 
Rows = sheet[1:2] #指定行第1到2行的值 
columns = sheet[A:C] #第A到C列

指定范围的值

# 行获取
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)

Excel写入

• 写入单元格并保存

from openpyxl import load_workbook

exl = load_workbook(filename = 'test.xlsx')
sheet = exl.active
sheet['A1'] = 'hello word'       
#或者cell = sheet['A1'] 
#cell.value = 'hello word'
exl.save(filename = 'test.xlsx') #存入原Excel表中，若创建新文件则可命名为不同名称

• 写入行数据并保存

#写入一行数据并保存
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('new_test.xls')

#写入多行并保存
data = [['hello',22,'hi'],
		['hell',23,'h'],
		['he',25,'him']]
for i in range(len(data)):
    for j in range(len(data[i])):
        worksheet.write(i,j,data[i][j])
exl.save(filename = 'test.xlsx')

• 插入列、行数据

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

sheet.insert_cols(idx=2, amount=5) #第2列前插入5列作为举例

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

• 删除

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

sheet.delete_rows(idx=2, amount=5) #删除多行

• 移动
  当数字为正即向下或向右，为负即为向上或向左

sheet.move_range('C5:F10', row=2, cols=-3)

sheet表操作

• 创建新的sheet

from openpyxl import load_workbook

exl = load_workbook(filename = 'test.xlsx')
print(exl.sheetnames)

exl.create_sheet('new_sheet')

• 复制已有的sheet

exl.copy_worksheet(sheet)

• 修改sheet表名

sheet = exl.active
sheet.title = 'newname'

• 创建新的Excel表

from openpyxl import load_workbook

workbook = Workbook()
sheet = workbook.active
workbook.save(filename = 'new_test.xlsx')