openpyxl 学习笔记

一、简介

openpyxl 是一个用于处理 xlsx 格式Excel表格文件的第三方python库，其支持Excel表格绝大多数基本操作。

 安装方法

pip/pip3 install openpyxl

二、Excel表格术语

列	column，以大写字母表示，如A,B
行	row,以数字表示，从1开始
格式(方块)	cell
工作表	sheet

 

三、表格读取操作

 1.打开一个已存在的表格，获取表格文件内所有sheet名称

import openpyxl
from openpyxl import load_workbook  #load_workbook 只能打开已经存在的表格，不能创建新表格
filename = "阿里云天池电商婴儿用户数据.xlsx"
work_book = load_workbook(filename=filename)
print(work_book.sheetnames)  #打印表格中所有的sheet名称

2.打开一个sheet表并获取sheet表大小

sheet = work_book['(sample)sam_tianchi_mum_baby_tr']   # 方式一  指定sheet名称
sheet = work_book.active                               # 方式二  使用active属性，表示当前活跃的或者唯一的sheet
print(sheet.dimensions)  # 打印sheet尺寸大小  A1:G1002  从A1到G1，共1002行 

3.获取指定格子的内容

cell = sheet['A1']                  # 方式一 通过cell坐标，A1表示格子的坐标
cell = sheet.cell(row=1,column=1)   # 方式二 通过指定行和列
print(cell.value, cell.row, cell.column, cell.coordinate) # 打印格子 内容 行数 列数 坐标

4. 获取多个格子的内容

cells = sheet['A1:A5'] 
print(cells)
for cell in cells:
	print(cell[0].value)
	print(cell[0].row)
	print(cell[0].column)

5.获取整列、整行、多列、多行格子

cells = sheet['A']      # 获取指定列所有格子，如A列所有格子 
cells = sheet['A:C']    # 获取指A列到G列所有格子
cells = sheet[1]        # 获取第一行所有格子
cells = sheet[1:5]     # 获取第一行到第五行所有格子

6.指定行和列的范围，按行获取

# 起始行数 2 结束行数 3 起始列 1(A) 结束列 2(B)
for row in sheet.iter_rows(min_row=2,max_row=3,min_col=1,max_col=2):
    print(row) # A2,B2  A3,B3

7.指定行和列的范围，按列获取

# 起始行数 2 结束行数 3 起始列 1(A) 结束列 2(B)
for col in sheet.iter_cols(min_row=2,max_row=3,min_col=1,max_col=2):
    print(col) # A2,A3  B2,B3

8.获取所有的行

for row in sheet.rows:
    print(row)

9.获取所有的列

for col in sheet.columns:
    print(col)　

四、表格写入、修改、删除操作　　

初始化

import openpyxl
from openpyxl import load_workbook  #load_workbook 只能打开已经存在的表格，不能创建新表格
filename = "阿里云天池电商婴儿用户数据.xlsx"
work_book = load_workbook(filename=filename)
sheet = work_book.active  

1.向某个格子(cell)写入内容并保存　　

# 通过sheet实例写入
sheet['A1'] = '这是测试写入'
work_book.save(filename=filename)  #也可以是其他表格

# 通过cell实例写入
cell = sheet['A1']
cell.value = '这是测试写入'
work_book.save(filename=filename)

2.使用python列表批量插入数据

data = [
	['a',1],
	['b',2],
	['c',3],
	['d',4]
]

for row in data:
	sheet.append(row)
work_book.save(filename=filename)

3.直接赋值公式字符串

from openpyxl.utils import FORMULAE
print(FORMULAE)  # 打印所有支持直接赋值的公式

sheet['F1002'] = '=AVERAGE(F2:F1001)' #对F列进行评价计算
work_book.save(filename=filename)

4.插入一列和插入多列　　

# 插入一列
sheet.insert_cols(idx=2)  #在第2列的左边插入1个空列
work_book.save(filename=filename)

# 插入多列
sheet.insert_cols(idx=2,amount=3)  #在第2列的左边插入3个空列
work_book.save(filename=filename)

5.插入一行和插入多行　　

# 插入一行
sheet.insert_rows(idx=2)  #在第2行的上面插入1行
work_book.save(filename=filename)

# 插入多行
sheet.insert_rows(idx=2,amount=3)  #在第2行的上面插入3行
work_book.save(filename=filename)

6.删除一列和删除多列

# 删除一列
sheet.delete_cols(idx=2) #删除第2列
work_book.save(filename=filename)


# 删除多列
sheet.delete_cols(idx=2,amount=3) #从第2列开始删除3列 即 2 3 4 列
work_book.save(filename=filename)　

7.删除一行和删除多行　　

# 删除一行
sheet.delete_rows(idx=2) #删除第2行
work_book.save(filename=filename)

# 删除多行
sheet.delete_rows(idx=2,amount=3) #从第2行开始删除3行 即 2 3 4 行
work_book.save(filename=filename)

8.移动格子

# 将 C1和D4直接的格子 向下 2行 向左2行   正整数时 row下 col为左，反之 row为上 col为右
sheet.move_range("C1:D4",rows=2,cols=-2)
work_book.save(filename=filename)

9.创建新sheet

work_book.create_sheet("表格2")
work_book.save(filename=filename)

10.删除sheet

work_book.remove("表格2")
work_book.save(filename=filename

11.复制一个sheet

work_book.copy_worksheet(sheet)
work_book.save(filename=filename) #新sheet名称为 原sheet名称+ ' Copy' 注意有空格

12.修改sheet名称

sheet.title = "表格3"
work_book.save(filename=filename)

13.冻结窗格　　

sheet.freeze_panes= "G2" #冻结G列第2行  左右移动时 G列之前冻结  上下滚动第1行冻结，不包括第2行
work_book.save(filename=filename)

14.添加筛选

sheet.auto_filter.ref = sheet.dimensions  # 对整张表格都筛选，执行结果第一行所有列都有筛选下拉选项
work_book.save(filename=filename)　

五、修改表格样式

1.修改字体样式

# Font(name=字体名称, size=字体大小, bold=是否加粗, italic=是否斜体,color=字体颜色)
from openpyxl.styles import Font
cell = sheet['A1']
font = Font(name = "宋体 Regular",zize=12, bold=True, italic=True,color='FF000000')   
cell.font = font
work_book.save(filename=filename)

2.获取表格中字体的样式

#cell.font.属性 
workbook = load_workbook( filename '这是一个表格.xlsx ')sheet = workbook.active
cell = sheet[ 'A2']
font = cell.font
print(font.name，font.size，font.bold, font.italic)

3.设置对齐样式

# Alignment(horizontal=水平对齐模式, vertical=垂直对齐模式, text_rotation=旋转角度, wrap_text=是否自动换行)
# 水平对齐样式  distributed justify  center left fill centerContinuous right general
# 垂直对齐样式  bottom distributed justify  center top
cell.alignment = alignment
from openpyxl.styles import Alignment
cell = sheet['A1']
alignment = Alignment(horizontal='center',vertical='center',text_rotation=45,warp_text=True)  
work_book.save(filename=filename)

4.设置边框样式

from  openpyxl.styles import Side,Border
cell = sheet['A1']
side1 = Side(style='thin',color='FF000000')
side2 = Side(style='dotted',color='FFFF0000')
border = Border(left=side1,right=side1,top=side2,bottom=side2)


cell.border = border
work_book.save(filename=filename)

5.设置填充样式

from openpyxl.styles import PatternFill，GradientFill
from openpyxl import load_workbook   
 
workbook = load_workbook(filename='这是一个表格.xlsx ')
sheet = workbook.active    
cell_a3 = sheet[ 'A3']    
pattern_fill = PatternFill( fill_type='solid', fgColor='99ccff')
cell_a3.fill = pattern_fil1
cell_a4 = sheet[ 'A4']   
gradient_fill = GradientFill(stop=( 'FFFFFF' , '99ccff' , '000000 ')) #渐变色
cell_a4.fill = gradient_fill    
workbook.save( filename='测试.xlsx ')   

6.合并单元格

# .merge_cells(待合并的格子编号)
# .merge_cells(start_row=起始行号, start_column=起始列号, end_row=结束行号，end_column=结束列号)    
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=4)
workbook.save( filename='测试.xlsx ')

7.取消合并单元格

# .unmerge_cells(待合并的格子编号)
# .unmerge_cells(start_row=起始行号, start_column=起始列号, end_row=结束行号, end_column=结束列号)   
workbook = load_workbook( filename='测试.xlsx')
sheet = workbook.active
sheet.unmerge_cells ( 'C1:D2')
sheet.unmerge_cells (start_row=7, start_column=1, end_row=8,end_column=4)
workbook.save( filename='测试.xlsx ')

8.插入图片

from openpyxl import load_workbook
from openpyxl.drawing.image import Image
workbook = load_workbook(filename="测试.xlsx")
sheet = workbook.active
logo = Image("刘亦菲.jpg")  # 图片路径
logo.height = 100
logo.width = 100
sheet.add_image( logo,"C1")  # 插入位置
workbook.save( filename="测试.xlsx")   

9.插入柱状图

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
workbook = load_workbook(filename="测试.xlsx")
sheet = workbook.active
chart = BarChart()
data = Reference(worksheet=sheet,min_row=1,max_row=7, min_col=2,max_col=3)  #设定数据引用范围
categories = Reference(sheet,min_col=1, min_row=2, max_row=7) # 分类名称
chart.add_data(data,titles_from_data=True) #titles_from_data 使用数据源中的标题，如果存在的话
chart.set_categories(categories)
sheet.add_chart(chart,"E2")
workbook.save("测试.xlsx")

10.插入折线图　　

from openpyxl import load_workbook
from openpyxl.chart import LineChart,Reference
workbook = load_workbook(filename="测试.xlsx" )
sheet = workbook.active
chart = LineChart()
data = Reference(worksheet=sheet, min_row=20,max_row=21,min_col=1,max_col=13)
categories = Reference(sheet,min_row=19,min_col=2, max_col=13)
chart.add_data(data,from_rows=True,titles_from_data=True)  # from_rows=True表示数据是横向的
chart.set_categories(categories)
sheet.add_chart(chart,"B23")
workbook.save("测试.xlsx")