Task 02 关于openpyxl库操作excel表格

1、读取对应的Excel表格

1.1 打开已经存在的Excel表格

#####’’'感觉没有pandas + xlwings好用，调色部分可以借鉴‘’‘####
“”“学习路径均来源于DataWhale”"

from openpyxl import load_workbook

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

['work']

1.1.1 根据名称获取表格

from openpyxl import load_workbook

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

sheet = exl_1['work']

'可改为如果表中只有一个sheet可以直接用active:'
sheet = exl_1.active

['work']

1.1.2 获取表格内容占据的大小

print(sheet.dimensions)

A1:C51104

1.2 读取单元格

1.2.1 获取某个单元格的具体内容

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

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

人流数
时间点

1.2.2 获取单元格对应的行、列和坐标

print(cell_1.row, cell_1.column, cell.coordinate)

1 1 B1

1.3 读取多个格子的值

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

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

cells

((<Cell 'work'.A1>, <Cell 'work'.B1>, <Cell 'work'.C1>),
 (<Cell 'work'.A2>, <Cell 'work'.B2>, <Cell 'work'.C2>),
 (<Cell 'work'.A3>, <Cell 'work'.B3>, <Cell 'work'.C3>),
 (<Cell 'work'.A4>, <Cell 'work'.B4>, <Cell 'work'.C4>),
 (<Cell 'work'.A5>, <Cell 'work'.B5>, <Cell 'work'.C5>),
 (<Cell 'work'.A6>, <Cell 'work'.B6>, <Cell 'work'.C6>),
 (<Cell 'work'.A7>, <Cell 'work'.B7>, <Cell 'work'.C7>),
 (<Cell 'work'.A8>, <Cell 'work'.B8>, <Cell 'work'.C8>))

Row

(<Cell 'work'.A1>, <Cell 'work'.B1>, <Cell 'work'.C1>)

Rows

((<Cell 'work'.A1>, <Cell 'work'.B1>, <Cell 'work'.C1>),
 (<Cell 'work'.A2>, <Cell 'work'.B2>, <Cell 'work'.C2>))

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

4.指定范围的值

sheet.iter_rows(min_row = 1, max_row = 5,min_col = 2, max_col = 6)

<generator object Worksheet._cells_by_row at 0x0000023BD39F56C8>

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

(<Cell 'work'.B1>, <Cell 'work'.C1>, <Cell 'work'.D1>, <Cell 'work'.E1>, <Cell 'work'.F1>)
人流数
None
None
None
None
(<Cell 'work'.B2>, <Cell 'work'.C2>, <Cell 'work'.D2>, <Cell 'work'.E2>, <Cell 'work'.F2>)
63306.166
=SUM(B2:B3)
None
None
None
(<Cell 'work'.B3>, <Cell 'work'.C3>, <Cell 'work'.D3>, <Cell 'work'.E3>, <Cell 'work'.F3>)
40535.964
None
None
None
None
(<Cell 'work'.B4>, <Cell 'work'.C4>, <Cell 'work'.D4>, <Cell 'work'.E4>, <Cell 'work'.F4>)
30026.64
None
None
None
None
(<Cell 'work'.B5>, <Cell 'work'.C5>, <Cell 'work'.D5>, <Cell 'work'.E5>, <Cell 'work'.F5>)
46291.07
None
None
None
None

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

(<Cell 'work'.B1>, <Cell 'work'.B2>, <Cell 'work'.B3>, <Cell 'work'.B4>, <Cell 'work'.B5>)
人流数
63306.166
40535.964
30026.64
46291.07
(<Cell 'work'.C1>, <Cell 'work'.C2>, <Cell 'work'.C3>, <Cell 'work'.C4>, <Cell 'work'.C5>)
None
=SUM(B2:B3)
None
None
None
(<Cell 'work'.D1>, <Cell 'work'.D2>, <Cell 'work'.D3>, <Cell 'work'.D4>, <Cell 'work'.D5>)
None
None
None
None
None
(<Cell 'work'.E1>, <Cell 'work'.E2>, <Cell 'work'.E3>, <Cell 'work'.E4>, <Cell 'work'.E5>)
None
None
None
None
None
(<Cell 'work'.F1>, <Cell 'work'.F2>, <Cell 'work'.F3>, <Cell 'work'.F4>, <Cell 'work'.F5>)
None
None
None
None
None

1.4 练习题

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

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

['Sheet1']

Sheet = exl['Sheet1']

Sheet[1]

(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>)

for row in Sheet.iter_rows(min_row = 1, max_row = 50,
						   min_col = 1, max_col = 2):
						   #具体查看对应表格的行列数
	for cell in row:
		if not cell.value:
			print(cell.coordinate)

B32
B33
B34
B35

2 Excel写入

2.1写入单元格并保存

Sheet['C1'] = 'hello world'  

exl.save(filename = 'test_2.xlsx')  # 保存

2.2 写入一行数据并保存

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')

exl=xlwt.Workbook(encoding='utf-8')
worksheet=exl.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])):
        worksheet.write(i,j,data[i][j])
        print(i)
        print(j)
        print("*"*4)
exl.save('test1.xls')

0
0
****
0
1
****
0
2
****
1
0
****
1
1
****
1
2
****
2
0
****
2
1
****
2
2
****

data[2][2]

'him'

2.3将公式写入单元格保存

exl = load_workbook(filename = 'test.xlsx')
sheet = exl['work']

sheet['C2'] = '=SUM(B2:B3)'
exl.save(filename='test.xlsx')

2.4插入一列

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

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

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

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

2.5 创建新的表

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

exl.create_sheet('new_sheet')

<Worksheet "new_sheet">

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

from openpyxl import load_workbook

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

3. 设置字体样式

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='new_test')

workbook = Workbook()
sheet = workbook.active

cells = sheet[2]

sheet[2]

(<Cell 'Sheet'.A2>,)

font = Font(name='字体', size=10, bold=True, italic=True, color='FF000000')
for cell in cells:
    cell.font = font
workbook.save(filename='new_test')

cell.font

<openpyxl.styles.fonts.Font object>
Parameters:
name='字体', charset=None, family=None, b=True, i=True, strike=None, outline=None, shadow=None, condense=None, color=<openpyxl.styles.colors.Color object>
Parameters:
rgb='FF000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb', extend=None, sz=10.0, u=None, vertAlign=None, scheme=None

水平对齐：distributed, justify, center, left, fill, centerContinuous, right, general
垂直对齐：bottom, distributed, justify, center, top

3.1 设置单元格边框样式

Side(style变现样式， color边线颜色)

Border(左右上下边线)

from openpyxl.styles import Side
from openpyxl.styles import Border

workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
side = Side(style='thin', color='FF000000')
#先定好side的格式
border = Border(left=side, right=side, top=side, bottom=side)
#代入边线中
cell.border = border
workbook.save(filename='new_test')

3.2 设置单元格边框样式

变现样式：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick

from openpyxl import Workbook
from openpyxl.styles import PatternFill

from openpyxl.styles import GradientFill

workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
pattern_fill = PatternFill(fill_type='solid')
cell.fill = pattern_fill
#单色填充
cell2 = sheet['A3']
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff','000000'))
cell2.fill = gradient_fill
#渐变填充
workbook.save(filename='new_test')

3.3设置行高与列宽

workbook = Workbook()
sheet = workbook.active
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['C'].width = 20 
workbook.save(filename='new_test')

sheet.merge_cells('A1:B2')
sheet.merge_cells(start_row=1, start_column=3,
				  end_row=2, end_column=4)

sheet.unmerge_cells('A1:B2')
sheet.unmerge_cells(start_row=1, start_column=3,
				    end_row=2, end_column=4)