Python办公自动化 | Task 02 Python与Excel

1. Excel读取

1.1 读取表格

• 打开表格

from openpyxl import load_workbook

exl = load_workbook(filename = 'test.xlsx')
print(exl.sheetnames)         #输出工作表的名字列表

• 指定表格，如果只有一个工作表可以用 exl.active

from openpyxl import load_workbook

exl = load_workbook(filename = 'test.xlsx')
sheet = exl['test1']     # 工作表test1

• 获取表格大小

sheet.dimensions      # A1:B3

1.2 读取单元格

• 单元格内容

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

cell = sheet['A1']                #指定坐标
cell.value

• 单元格行、列和坐标

cell.row           # 行
cell.column        # 列
cell.coordinate    # 坐标

1.3 读取多个单元格

• 指定坐标

cells = sheet['A1:C10'] #A1到C10区域的值

• 指定行的值

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

• 指定列的值

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

• 指定范围的值

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

 

2. Excel写入

2.1 写入单元格

• 写入

sheet = exl['test1']
sheet['A1'] = 'hello world'  
     
#或者cell = sheet['A1'] 
#cell.value = 'hello world'

• 保存

exl.save(filename = 'test.xlsx') 
#存入原Excel表中

exl.save(filename = 'new.xlsx') 
#另存为

2.2 写入行数据

• 创建工作表

import xlwt
workbook = xlwt.Workbook(encoding = 'utf-8')
sheet = workbook.add_sheet('new')
workbook.save('new_test.xls')

• 写入一行

sheet.write(1,0,label = 'hello') 
# 行、列、值

• 写入多行

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

2.3 写入公式

sheet['A2'] = '=SUM(A1:D1)'

2.4 插入行/列数据

• 插入一行/列

sheet.insert_rows(idx=2)
sheet.insert_cols(idx=2)
# 在第2行/列前插入

• 插入多行/列

sheet.insert_rows(idx=2, amount=5)
sheet.insert_cols(idx=2, amount=5)
# 插入5行/列

2.5 删除

• 删除多行/列

sheet.delete_rows(idx=2, amount=5)
sheet.delete_cols(idx=2, amount=5)

2.6 移动

• 正：向下或向右，负：向上或向左

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

2.7 工作表操作

• 创建新的工作表

from openpyxl import Workbook

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

workbook.create_sheet('new_sheet')
# 添加新的sheet

• 复制已有的工作表

workbook.copy_worksheet(sheet)
# 名字sheet_copy
workbook.save(filename='new_test.xlsx')

• 修改工作表表名

sheet.title = 'newname'
workbook.save(filename='new_test.xlsx')

2.8 创建新的Excel表

from openpyxl import load_workbook

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

 

3. Excel样式

3.1 字体样式

• 单个格子

from openpyxl import Workbook
from openpyxl.styles import Font

workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
font = Font(name='字体', sizee=10, bold=True, italic=True, color='FF0000')
# Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)
cell.font = font
workbook.save(filename='new_test')

• 多个格子

from openpyxl import Workbook
from openpyxl.styles import Font

workbook = Workbook()
sheet = workbook.active
cells = sheet[2]
font = Font(name='字体', sizee=10, bold=True, italic=True, color='FF000000')
for cell in cells:
	cell.font = font
workbook.save(filename='new_test')

3.2 对齐样式

水平对齐：distributed, justify, center, left, fill, centerContinuous, right, general

垂直对齐：bottom, distributed, justify, center, top

• 边框

from openpyxl import Workbook
from openpyxl.styles import Font

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

• 填充

from openpyxl import Workbook
from openpyxl.styles import Font

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

3.3 行高与列宽

from openpyxl import Workbook 

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

3.4 合并与拆分单元格

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)