Python自动化之Excel

安装openpyxl模块pip install openpyxl

Excel读取

读取对应表格

# 模块导入
from openpyxl import load_workbook

# 读取Excel文件
exl = load_workbook(filename = 'F:\\Datawhale\\POA\\xlsx\\test.xlsx')
print(exl.sheetnames)

# ['data']


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

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

# A1:M4000

读取单元格

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

# 北京市

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

# /secondtipdm/index.jhtml

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

# 6 3 F5

读取多个格子的值

# 指定坐标范围  A1到C8区域的值
cells = sheet['A1:F6']
cells[3][4].value

# '125.208.4.78'

# 指定行的值  第1行的值
Row = sheet[1] 
print(Row[0].value)

# id

# 第1到2行的值
Rows = sheet[1:2] 
print(Rows[1][1].value)
# 736

# 指定列的值  第A列
Column = sheet['A']

# 第A到C列
Columns = sheet['A:B']

# 指定范围的值  行获取
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)

 练习题

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

from openpyxl import load_workbook

exl = load_workbook('F:\\Datawhale\\POA\\xlsx\\test.xlsx')
sheet = exl.active
for row in sheet.iter_rows(min_row = 1, max_row = 4000,
                           min_col = 1, max_col = 10):

#具体查看对应表格的行列数
    for cell in row:
        if not cell.value:
            print(cell.coordinate)

"""
G408
G1316
G1645
G1838
G1847
G1848
G1852
G1853
G1861
G1862
G1917
G3514
G3612
"""

 Excel写入

写入单元格并保存

from openpyxl import load_workbook

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

cell = sheet['A1']
print(cell.value)

# hello word

 写入行数据并保存

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

# 写入多行数据并保存
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('F:\\Datawhale\\POA\\xlsx\\new_test2.xls')

将公式写入单元格保存

from openpyxl import load_workbook

exl = load_workbook(filename = 'F:\\Datawhale\\POA\\xlsx\\test.xlsx')
sheet = exl.active
sheet['A2'] = '=SUM(B15:B21)'
exl.save(filename='F:\\Datawhale\\POA\\xlsx\\new_test3.xlsx')

 插入列数据

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

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

插入行数据

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

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

 删除

# 删除多列
sheet.delete_cols(idx=5, amount=2) #第5列前删除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 = 'F:\\Datawhale\\POA\\xlsx\\test.xlsx')

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

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

# 修改sheet表名
sheet = exl['new_sheet']
sheet.title = 'data_copy'

创建新的Excel表

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active
workbook.save(filename = 'F:\\Datawhale\\POA\\xlsx\\new_test4.xlsx')

Excel 样式

设置字体样式

# 设置字体样式
# Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)

from openpyxl import Workbook
from openpyxl.styles import Font

workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
font = Font(name='字体', size=10, bold=True, italic=True, color='FF0000')
cell.font = font
workbook.save(filename='F:\\Datawhale\\POA\\xlsx\\new_test4.xlsx')

# 设置多个格子的字体样式

from openpyxl import Workbook
from openpyxl.styles import Font

workbook = Workbook()
sheet = workbook.active
cells = sheet[2]
font = Font(name='字体', size=10, bold=True, italic=True, color='FF000000')
for cell in cells:
    cell.font = font
workbook.save(filename='F:\\Datawhale\\POA\\xlsx\\new_test4.xlsx')