输
作者介绍:
大家可以叫我黄同学(博客名:Huang Supreme),一个应用统计硕士,爱好写一些技术博客,志在用通俗易懂的写作风格,帮助大家学到知识,学好知识!
本章目录
章节一
python使用openpyxl操作excel
1、openpyxl库介绍
3、python如何向excel中写入某些内容?
① 向某个格子中写入内容并保存
② .append():向表格中插入行数据
③ 在python中使用excel函数公式(很有用)
④ .insert_cols()和.insert_rows():插入空行和空列
⑤ .delete_rows()和.delete_cols():删除行和列
⑥ .move_range():移动格子
⑦ .create_sheet():创建新的sheet表格
⑧ .remove():删除某个sheet表
⑨ .copy_worksheet():复制一个sheet表到另外一张excel表格
⑩ sheet.title:修改sheet表的名称
⑪ 创建新的excel表格文件
⑫ sheet.freeze_panes:冻结窗口
⑬ sheet.auto_filter.ref:给表格添加“筛选器”
4、批量调整字体和样式
2)获取表格中格子的字体样式
3)设置对齐样式
4)设置边框样式
5)设置填充样式
6)设置行高和列宽
7)合并单元格
上下滚动查看更多
章节一:python使用openpyxl操作excel
这里需要大家仔细查看图中的每一项内容,知道什么是“行(row)、列(column)”?什么是“格子(cell)”?什么是“sheet表”?
from openpyxl import load_workbook
workbook = load_workbook(filename = 'test.xlsx')
workbook.sheetnames
结果如下:
from openpyxl import load_workbook
workbook = load_workbook(filename = 'test.xlsx')
workbook.sheetnames
sheet = workbook['Sheet1']
print(sheet)
结果如下:
sheet.dimensions
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
cell1 = sheet['A1']
cell2 = sheet['C11']
print(cell1.value, cell2.value)
'''
workbook.active 打开激活的表格;
sheet['A1'] 获取A1格子的数据;
cell.value 获取格子中的值;
'''
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
cell1 = sheet.cell(row = 1,column = 1)
cell2 = sheet.cell(row = 11,column = 3)
print(cell1.value, cell2.value)
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
cell1 = sheet['A1']
cell2 = sheet['C11']
print(cell1.value, cell1.row, cell1.column, cell1.coordinate)
print(cell2.value, cell2.row, cell2.column, cell2.coordinate)
'''
.row 获取某个格子的行数;
.columns 获取某个格子的列数;
.corordinate 获取某个格子的坐标;
'''
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
# 获取A1:C2区域的值
cell = sheet['A1:C2']
print(cell)
for i in cell:
for j in i:
print(j.value)
结果如下:
sheet['A'] --- 获取A列的数据
sheet['A:C'] --- 获取A,B,C三列的数据
sheet[5] --- 只获取第5行的数据
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
# 按行获取值
for i in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2):
for j in i:
print(j.value)
# 按列获取值
for i in sheet.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2):
for j in i:
print(j.value)
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
for i in sheet.rows:
print(i)
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
sheet['A1'] = '哈喽' # 这句代码也可以改为cell = sheet['A1'] cell.value = '哈喽'
workbook.save(filename = '哈喽.xlsx')
'''
注意:我们将“A1”单元格的数据改为了“哈喽”,并另存为了“哈喽.xlsx”文件。
如果我们保存的时候,不修改表名,相当于直接修改源文件;
'''
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
data = [
['唐僧','男','180cm'],
['孙悟空','男','188cm'],
['猪八戒','男','175cm'],
['沙僧','男','176cm'],
]
for row in data:
sheet.append(row)
workbook.save(filename = 'test.xlsx')
结果如下:
# 这是我们在excel中输入的公式
=IF(RIGHT(C2,2)='cm',C2,SUBSTITUTE(C2,'m','')*100&'cm')
# 那么,在python中怎么插入excel公式呢?
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
sheet['D1'] = '标准身高'
for i in range(2,16):
sheet['D{}'.format(i)] = '=IF(RIGHT(C{},2)='cm',C{},SUBSTITUTE(C{},'m','')*100&'cm')'.format(i,i,i)
workbook.save(filename = 'test.xlsx')
结果如下:
import openpyxl
from openpyxl.utils import FORMULAE
print(FORMULAE)
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
sheet.insert_cols(idx=4,amount=2)
sheet.insert_rows(idx=5,amount=4)
workbook.save(filename = 'test.xlsx')
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
# 删除第一列,第一行
sheet.delete_cols(idx=1)
sheet.delete_rows(idx=1)
workbook.save(filename = 'test.xlsx')
结果如下:
# 向左移动两列,向下移动两行
sheet.move_range('C1:D4',rows=2,cols=-1)
演示效果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(sheet)
workbook.create_sheet('我是一个新的sheet')
print(workbook.sheetnames)
workbook.save(filename = 'test.xlsx')
结果如下:
workbook = load_workbook(filename = 'test.xlsx')
sheet = workbook.active
print(workbook.sheetnames)
# 这个相当于激活的这个sheet表,激活状态下,才可以操作;
sheet = workbook['我是一个新的sheet']
print(sheet)
workbook.remove(sheet)
print(workbook.sheetnames)
workbook.save(filename = 'test.xlsx')
结果如下:
workbook = load_workbook(filename = 'a.xlsx')
sheet = workbook.active
print('a.xlsx中有这几个sheet表',workbook.sheetnames)
sheet = workbook['姓名']
workbook.copy_worksheet(sheet)
workbook.save(filename = 'test.xlsx')
结果如下:
workbook = load_workbook(filename = 'a.xlsx')
sheet = workbook.active
print(sheet)
sheet.title = '我是修改后的sheet名'
print(sheet)
结果如下:
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = '表格1'
workbook.save(filename = '新建的excel表格')
结果如下:
workbook = load_workbook(filename = '花园.xlsx')
sheet = workbook.active
print(sheet)
sheet.freeze_panes = 'C3'
workbook.save(filename = '花园.xlsx')
'''
冻结窗口以后,你可以打开源文件,进行检验;
'''
结果如下:
workbook = load_workbook(filename = '花园.xlsx')
sheet = workbook.active
print(sheet)
sheet.auto_filter.ref = sheet['A1']
workbook.save(filename = '花园.xlsx')
结果如下:
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename='花园.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = Font(name='微软雅黑',size=20,bold=True,italic=True,color='FF0000')
cell.font = font
workbook.save(filename = '花园.xlsx')
'''
这个color是RGB的16进制表示,自己下去百度学习;
'''
结果如下:
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename='花园.xlsx')
sheet = workbook.active
cell = sheet['A2']
font = cell.font
print(font.name, font.size, font.bold, font.italic, font.color)
结果如下:
from openpyxl.styles import Alignment
from openpyxl import load_workbook
workbook = load_workbook(filename='花园.xlsx')
sheet = workbook.active
cell = sheet['A1']
alignment = Alignment(horizontal='center',vertical='center',text_rotation=45,wrap_text=True)
cell.alignment = alignment
workbook.save(filename = '花园.xlsx')
结果如下:
openpyxl import load_workbook
workbook = load_workbook(filename='花园.xlsx')
sheet = workbook.active
cell = sheet['D6']
side1 = Side(style='thin',color='FF0000')
side2 = Side(style='thick',color='FFFF0000')
border = Border(left=side1,right=side1,top=side2,bottom=side2)
cell.border = border
workbook.save(filename = '花园.xlsx')
结果如下:
from openpyxl.styles import PatternFill,GradientFill
from openpyxl import load_workbook
workbook = load_workbook(filename='花园.xlsx')
sheet = workbook.active
cell_b9 = sheet['B9']
pattern_fill = PatternFill(fill_type='solid',fgColor='99ccff')
cell_b9.fill = pattern_fill
cell_b10 = sheet['B10']
gradient_fill = GradientFill(stop=('FFFFFF','99ccff','000000'))
cell_b10.fill = gradient_fill
workbook.save(filename = '花园.xlsx')
结果如下:
workbook = load_workbook(filename='花园.xlsx')
sheet = workbook.active
# 设置第1行的高度
sheet.row_dimensions[1].height = 50
# 设置B列的宽度
sheet.column_dimensions['B'].width = 20
workbook.save(filename = '花园.xlsx')
'''
sheet.row_dimensions.height = 50
sheet.column_dimensions.width = 30
这两句代码,是将整个表的行高设置为50,列宽设置为30;
'''
结果如下:
workbook = load_workbook(filename='花园.xlsx')
sheet = workbook.active
sheet.merge_cells('C1:D2')
sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=3)
workbook.save(filename = '花园.xlsx')
结果如下:
全文PDF获取方式:
联系客服