python-excel驱动之openpyxl

安装

openpyxl是一个非标准库，因此需要自行安装，安装过程并不困难，Windows/Mac用户均可以在命令行(CMD)/终端(Terminal)中使用pip安装

pip install openpyxl

前置知识

一个Excel工作簿workbook由一个或者多个工作表sheet组成，一个sheet可以看作是多个行row组成，也可以看作是多个列column组成，而每一行每一列都由多个单元格cell组成！

读取Excel

注意load_workbook只能打开已经存在的Excel，不能创建新的工作簿。Workbook用于新建工作簿
示范表格：

获取单个单元格内容

from openpyxl import load_workbook #导入load_workbook模块
workbook = load_workbook(filename='yxy.xlsx') 	#打开名为'yxy.xlsx'的工作簿
print(workbook.sheetnames)	#打印所有工作表名
sheet = workbook['测试项目1']	#打开名为测试项目1的工作表。如果只有一张工作表也可以用sheet = workbook.active
print(sheet.dimensions)		#获取工作表内容所在范围（左上单元格:右下单元格）
cell_1 = sheet['A1']		#指定坐标获取单元格内容
cell_2 = sheet.cell(row=2,column=2)		#指定行列数获取单元格内容
print('A1=',cell_1)			#输出A1单元格信息
print('row2column2.value=',cell_2.value)		#输出第二行第二列单元格内容
print(cell_2.row, cell_2.column, cell_2.coordinate)		#输出目标单元格的行，列，坐标

以上代码执行结果如下

获取多个单元格内容

Excel中每一列由字母确定，是字符型；每一行由一个数字确定，是整型。当然，下面的1-3三种方法都是获取一堆表格，现在要输出每一个表格的值就需要遍历：

• 方法1-3：指定坐标范围、指定列、指定行

from openpyxl import load_workbook #导入load_workbook模块
workbook = load_workbook(filename='yxy.xlsx') 	#打开名为'yxy.xlsx'的工作簿
sheet = workbook['测试项目1']	#打开名为测试项目1的工作表。如果只有一张工作表也可以用sheet = workbook.active）
cells_1 = sheet['A1:B3']	#单元格A1:B3
cells_2 = sheet['A:C']		#列A-列C
cells_3 = sheet['4:6']		#行4-行6

print('cells1逐行排列为')
for row in cells_1:			#逐行读取
  for cell_1 in row:		#行内逐个单元格读取
     print(cell_1.value)	#打印单元格内值

print('cells2逐行排列为')
for row in cells_2:			#逐行读取
  for cell_2 in row:		#行内逐个单元格读取
     print(cell_2.value)	#打印单元格内值

print('cells3逐行排列为')
for row in cells_3:			#逐行读取
  for cell_3 in row:		#行内逐个单元格读取
     print(cell_3.value)	#打印单元格内值

以上代码执行结果如下

三种方法依然有自己的局限性，如果我需要特定范围的值，且懒得换算成字母数字坐标。例如我想要获取第2行至第5行、第1列至第3列的全部单元格。因此必须掌握第4种方法：

• 方法4：指定范围的值

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
for row in sheet.iter_rows(
  min_row=2,max_row=4,
  min_col=2,max_col=3
):				#取2-4行，2-3列的值
  for cell in row:
    print(cell.value)

获取所有值

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
for row in sheet:			#逐行读取
  for cell in row:		#行内逐个单元格读取
     print(cell.value)	#打印单元格内值

写入Excel

保存Excel

如果读取和写入Excel的路径相同则为对原文件进行修改，
如果读取和写入Excel的路径不同则为保存成新的文件

workbook.save(filename='ChangeName.xlsx')

创建新的Excel

from openpyxl import Workbook
workbook = Workbook()
workbook.save(filename='New.xlsx')

写入单元格

cell = sheet['A1']
cell.value = '姓名'

• 写入公式
  示例：

sheet['K11'] = '=AVERAGE(K1:K10)'

• 写入超链接
  示例：

sheet["A1"].hyperlink = "https://www.example.com" 
sheet["A1"].style = hyperlink_style

写入一行或多行数据

会在已有数据后面追加写入

• 一行一行写入

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
data1=['Cavia',30,'山东烟台']
sheet.append(data1)
data2=['Kaer',35,'浙江湖州']
sheet.append(data2)
workbook.save(filename='yxy.xlsx')

• 一次写入多行

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
data=[['Cavia',30,'山东烟台'],
      ['Kaer',35,'浙江湖州']]
for i in data:
  sheet.append(i)
workbook.save(filename='yxy.xlsx')

插入一列/多列

idx列为索引，在idx列左边插入

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
sheet.insert_cols(idx=2,amount=3)		#再第二列前面插入3列。其中',amount=3'省略即为插入一列
workbook.save(filename='yxy.xlsx')

删除多行/多列

idx为索引，从idx行/列开始（包含）删除多列

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
sheet.delete_cols(idx=2,amount=2)
sheet.delete_rows(idx=4,amount=3)
workbook.save(filename='yxy.xlsx')

移动范围数据

数字正为向下或向右，负为向左或向上

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
sheet.move_range('A7:A8',rows=-4,cols=1)
workbook.save(filename='yxy.xlsx')

样式调整

需要导入openpyxl.styles

设置字体样式

name=‘微软雅黑’——————字体名称
size=12——————字体大小
bold=True——————粗体
italic=True——————斜体
color=‘7035BC’——————颜色

from openpyxl import load_workbook
from openpyxl.styles import Font
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
cell =sheet['A1']
font=Font(name='微软雅黑',size=12,bold=True,italic=True,color='7035BC')
cell.font = font
workbook.save(filename='yxy.xlsx')

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

设置对齐样式

horizontal=‘center’——————水平对齐
vertical=‘center’———————垂直对齐
text_rotation=45——————字体倾斜度
wrap_text=True——————自动换行

水平对齐选项和它们的含义如下：

distributed：将文本均匀分布在单元格中，以填充整个宽度。
justify：将文本两端对齐，但不进行额外的间距调整。
center：将文本居中对齐。
left：将文本靠左对齐。
fill：将文本填充到单元格的宽度，在文本之间添加额外的空格以填充空白部分。
centerContinuous：将文本在单元格中连续居中对齐，但如果存在合并的单元格，则只对第一个单元格应用居中对齐。
right：将文本靠右对齐。
general：表达一般性格式，不进行特定的对齐设置。
垂直对齐选项和它们的含义如下：

bottom：将文本底部与单元格底部对齐。
distributed：将文本均匀分布在单元格中，以填充整个高度。
justify：将文本顶部和底部对齐，但不进行额外的间距调整。
center：将文本垂直居中对齐。
top：将文本顶部与单元格顶部对齐。

from openpyxl import load_workbook
from openpyxl.styles import Alignment
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
cell =sheet['A1']
alignment=Alignment(horizontal='center',vertical='center',text_rotation=45,wrap_text=True)
cell.alignment = alignment
workbook.save(filename='yxy.xlsx')

设置边框样式

style=‘thin’——————边框样式
color=‘89284D’——————边框颜色

边线样式选项和它们的含义如下：
double：双边线。
mediumDashDotDot：中等虚线点虚线。
slantDashDot：斜线虚线点线。
dashDotDot：虚线点点线。
dotted：点线。
hair：细边线。
mediumDashed：中等虚线。
dashed：虚线。
dashDot：虚线点线。
thin：细线。
mediumDashDot：中等虚线点。
medium：中等线。
thick：粗线。
Border（左 右 上 下边线）

from openpyxl import load_workbook
from openpyxl.styles import Side,Border
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
cell =sheet['B2']
side = Side(style='thin',color='89284D')
border = Border(left=side,right=side,top=side,bottom=side)
cell.border =border
workbook.save(filename='yxy.xlsx')

设置单元格填充样式

from openpyxl import load_workbook
from openpyxl.styles import PatternFill,GradientFill
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
#单色填充
cell1 =sheet['A3']
pattern_fill = PatternFill(fill_type='solid',fgColor='99ccff')
cell1.fill =pattern_fill
#渐变色填充
cell2 =sheet['B4']
gradient_fill = GradientFill(stop=('FFFFFF','99CCFF','000000'))
cell2.fill =gradient_fill
workbook.save(filename='yxy.xlsx')

设置单元格行高/列宽

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx') 
sheet = workbook['测试项目1']
sheet.row_dimensions[6].height = 50 
sheet.column_dimensions['B'].width = 20 
workbook.save(filename='yxy.xlsx')

单元格合并/取消合并

• 合并

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['测试项目1']
sheet.merge_cells('A1:B2')		#指定范围
sheet.merge_cells(start_row=4, start_column=1,
                  end_row=6, end_column=2)		#指定行列范围
workbook.save(filename='yxy.xlsx')

• 取消合并

from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['测试项目1']
sheet.unmerge_cells('A1:B2')		#指定范围
sheet.unmerge_cells(start_row=4, start_column=1,
                  end_row=6, end_column=2)		#指定行列范围
workbook.save(filename='yxy.xlsx')