openpyxl 学习笔记

一、简介

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

 安装方法

?

1	pip/pip3 install openpyxl

二、Excel表格术语

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

 

三、表格读取操作

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

?

1 2 3 4 5

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表大小

?

1 2 3

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.获取指定格子的内容

?

1 2 3

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

4. 获取多个格子的内容

?

1 2 3 4 5 6

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

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

?

1 2 3 4

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

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

?

1 2 3

# 起始行数 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.指定行和列的范围，按列获取

?

1 2 3

# 起始行数 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.获取所有的行

?

1 2	for row in sheet.rows:     print(row)

9.获取所有的列

?

1 2	for col in sheet.columns:     print(col)

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

初始化

?

1 2 3 4 5

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

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

?

1 2 3 4 5 6 7 8

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

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

?

1 2 3 4 5 6 7 8 9 10

data = [     ['a',1],     ['b',2],     ['c',3],     ['d',4] ]   for row in data:     sheet.append(row) work_book.save(filename=filename)

3.直接赋值公式字符串

?

1 2 3 4 5

from openpyxl.utils import FORMULAE print(FORMULAE)  # 打印所有支持直接赋值的公式   sheet['F1002'] = '=AVERAGE(F2:F1001)' #对F列进行评价计算 work_book.save(filename=filename)

4.插入一列和插入多列　　

?

1 2 3 4 5 6 7

# 插入一列 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.插入一行和插入多行　　

?

1 2 3 4 5 6 7

# 插入一行 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.删除一列和删除多列

?

1 2 3 4 5 6 7 8

# 删除一列 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.删除一行和删除多行　　

?

1 2 3 4 5 6 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.移动格子

?

1 2 3

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

9.创建新sheet

?

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

10.删除sheet

?

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

11.复制一个sheet

?

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

12.修改sheet名称

?

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

13.冻结窗格　　

?

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

14.添加筛选

?

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

五、修改表格样式

1.修改字体样式

?

1 2 3 4 5 6

# 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.获取表格中字体的样式

?

1 2 3 4 5

#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.设置对齐样式

?

1 2 3 4 5 6 7 8

# 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.设置边框样式

?

1 2 3 4 5 6 7 8 9

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.设置填充样式

?

1 2 3 4 5 6 7 8 9 10 11 12

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.合并单元格

?

1 2 3 4 5 6 7

# .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.取消合并单元格

?

1 2 3 4 5 6 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.插入图片

?

1 2 3 4 5 6 7 8 9

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.插入柱状图

?

1 2 3 4 5 6 7 8 9 10 11

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.插入折线图　　

?

1 2 3 4 5 6 7 8 9 10 11

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