本章需要安装的一些模块，可以在CSDN上搜索一下如何安装，其实非常简单，这里我大概举一个例子。
openpyxl 模块
这个模块主要是用来使用python操作Excel。详细请点击
安装openpyxl
只需要在Anaconda Prompt中输入下述命令
(C:\Anaconda3) C:\Users\Phato> pip install openpyxl
#安装第三方Python模块包大概都是这样的方法
pip install 模块名称 或pip3 install 模块名称
进入cmd命令行，检测是否安装成功
![]()
没有报错就是安装成功了
没有报错就是安装成功了
load_workbook(filename='path')
打开Excel表格并获取表格名称，也就是能够获取一个excel文件中的sheet名称。
from openpyxl import load_workbook
workbook = load_workbook(filename='C:/Users/cate.xlsx')
#workbook.sheetnames 获取表格文件内的sheet名称
print(workbook.sheetnames)
>>>['Sheet1', 'Sheet2', 'Sheet3']
sheet = workbook['Sheet1']
#sheet.dimensions 获取表格的尺寸大小
print(sheet.dimensions)
>>> A1:C172
#表示从A1到C172的格子中都有数据
此时分两种情况：
当excel中只有一张sheet时：
onlysheet = workbook.active #打开唯一的表格
cell = onlysheet['A1'] #将A1的内容放入cell中
print(cell.value)#获取格子cell中的内容
--------------------------------------------------
当excel中有多张sheet时：
sheet = workbook['sheetname'] #打开对应名称的表格
cell = sheet['A1'] #将A1的内容放入cell中
print(cell.value) #获取格子cell中的内容
--------------------------------------------------
具体单个cell的用法：
sheet = workbook.active
cell = sheet['B1']
print(cell.row,cell.column,cell.coordinate)
>>> 1 B B1```
cell相当于是对于单个格子或者一部分格子的托管，其中包括几个值。
# cell.value 表示格子的值
# cell.row 表示格子的行数
# cell.column 表示格子的列数
# cell.coordinate 表示格子的坐标
cell的定位方法有两种：
cell = sheet.cell(row=1,column=2)
cell = sheet['B1']
#两种写法都是表示第一行第二列
具体多个cell的用法：
cells = sheet['B1:C5'] #从B1到C5的数据
cells = sheet['A'] #A列的所有数据
cells = sheet[5] #第五行的所有数据
cells = sheet[5:6] #第五行到第六行的数据
通过指定行、列读取cell
.iter_rows(min_row=最低行数，max_row=最高行数，min_col=最低列数，max_col=最高列数)
for row in sheet.iter_rows(min_row=2,max_row=5,min_col=1,max_col=4):
for cell in row:
print(cell)
print(row)
#获取了从第2行第1列到第5行第4列的格子
这是按行读取，也就是从第2行开始到第5行结束，读取了4次。
-------------------------------------------------------
.iter_cols(min_row=最低行数，max_row=最高行数，min_col=最低列数，max_col=最高列数)
for col in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=4):
for cell in col:
print(cell)
#获取了从第1列第2行到第4列第5行的格子
这是按列读取，也就是从第1列开始到第四列结束，读取了4次。
两种获取方式的差别是一种是按行返回值，一种是按列返回值。
迭代整个表格的所有行、列
#迭代整个列表的所有行 .rows
for row in sheet.rows:
print(row)
#迭代整个列表的所有行 .columns
for col in sheet.columns:
print(col)
向表格写入数据内容并保存
from openpyxl import load_workbook
workbook = load_workbook(filename='C:/Users/Doc/cc.xlsx')
sheet = workbook.active #打开唯一的sheet
cell = sheet['A1'] #在sheet的A1表格位置
cell.value = 'phato' #写入字段：phato
workbook.save(filename='C:/Users/Doc/cc.xlsx') #保存表格文件
--------------------------------------------------
两种写入内容的方式
方法一：
cell = sheet['A1']
cell.value = '你好啊'
方法二：
sheet['A1']='hello' '''
向表格追加数据内容
from openpyxl import load_workbook
workbook = load_workbook(filename='C:/Users/Phato/Documents/cc.xlsx')
sheet = workbook.active
data = [
['张三',1,12321],
['李四',2,213213],
['王五',3,123213],
['赵六',4,2132131]
]
for row in data:
sheet.append(row) #从没有记录的行与列来追加数据内容
workbook.save(filename='C:/Users/Doc/cc.xlsx')
插入公式
workbook = load_workbook(filename='C:/Users/Doc/cc.xlsx')
sheet = workbook.active
sheet['B6'] = '=AVERAGE(B2:B5)' #这里使用Execl公式
workbook.save(filename='C:/Users/Doc/cc.xlsx')
--------------------------------
#查看openpyxl支持的公式
from openpyxl.utils import FORMULLLAE
print(FORMULAE)
插入一列或多列
idx=数字编号， amount=要插入的列数
workbook = load_workbook(filename='cc.xlsx')
sheet = workbook.active
sheet.insert_cols(idx=2,amount=1) #第二列前插入一列
workbook.save(filename='cc.xlsx')
插入一行或多行
workbook = load_workbook(filename='cate1.xlsx')
sheet = workbook.active
sheet.insert_rows(idx=2,amount=1) #第二行前插入一行
workbook.save(filename='cate1.xlsx')
删除一列或多列
orkbook = load_workbook(filename='cate1.xlsx')
sheet = workbook.active
sheet.delete_cols(idx=2,amount=1) #删除第二列前的一列
workbook.save(filename='cate1.xlsx')
删除一行或多行
orkbook = load_workbook(filename='cate1.xlsx')
sheet = workbook.active
sheet.delete_rows(idx=2,amount=1) #删除第二行前的一行
workbook.save(filename='cate1.xlsx')
移动格子
row=2,cols=-2中正整数为向下向右、负整数为向上向左
orkbook = load_workbook(filename='cate1.xlsx')
sheet = workbook.active
sheet.move_range("C1:D4",rows=2,cols=-2)
workbook.save(filename='cate1.xlsx')
创建新的sheet
workbook = load_workbook(filename = 'C:/Users/Doc/cc.xlsx')
print(workbook.sheetnames)
workbook.create_sheet('newsheet2')
print(workbook.sheetnames)
workbook.save(filename = 'C:/Users/Doc/cc.xlsx')
复制一个sheet
workbook = load_workbook(filename = 'C:/Users/Doc/cc.xlsx')
print(workbook.sheetnames)
sheet = workbook['Sheet1']
workbook.copy_worksheet(sheet)
print(workbook.sheetnames)
workbook.save(filename = 'C:/Users/Doc/cc.xlsx')
#修改表格名称 sheet.title = 'newnamesheet'
删除一个sheet
workbook = load_workbook(filename = 'C:/Users/Doc/cc.xlsx')
print(workbook.sheetnames)
sheet = workbook['Sheet2']
workbook.remove(sheet)
print(workbook.sheetnames)
workbook.save(filename = 'C:/Users/Doc/cc.xlsx')
创建新的Excel表格文件
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = 'newsheet'
workbook.save(filename = 'C:/Users/Doc/new.xlsx')
冻结窗格
workbook = load_workbook(filename= 'C:/Users/Doc/new.xlsx')
sheet = workbook.active
sheet.freeze_panes = "B2"
workbook.save(filename = 'C:/Users/Doc/new.xlsx')
添加筛选
workbook = load_workbook(filename= 'C:/Users/Doc/new.xlsx')
sheet = workbook['(sample)rowsname'] #rowsname名称
sheet.auto_filter.ref = sheet.dimensions
workbook.save(filename = 'C:/Users/Doc/new.xlsx')
修改字体样式
Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename='C:/Users/Doc/cc.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = Font(name='微软雅黑',size=12,bold=True,italic=True,color='FF0000')
cell.font = font
workbook.save(filename='C:/Users/Doc/cc.xlsx')
获取表格内字体的样式
workbook = load_workbook(filename='C:/Users/Phato/Documents/cc.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = cell.font
print(font.name,font.size,font.bold,font.italic)
>>>微软雅黑 12.0 True True
设置对齐样式
Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
from openpyxl.styles import Alignment
from openpyxl import load_workbook
workbook = load_workbook(filename='C:/Users/Phato/Documents/cc.xlsx')
sheet = workbook.active
cell = sheet['A1']
alignment = Alignment(harizontal='center',vertical='center',text_rotation=45)
cell.alignment = alignment
workbook.save(filename='C:/Users/Phato/Documents/cc.xlsx')
设置边框样式
![]()
from openpyxl.styles import Side,Border
from openpyxl import load_workbook
filepath = 'C:/Users/Doc/cc.xlsx'
workbook = load_workbook(filename=filepath)
sheet = workbook.active
cell = sheet['A3']
side = Side(style='thin',color='FF0000')
border = Border(left=side,right=side,top=side,bottom=side)
cell.border = border
workbook.save(filename=filepath)
设置填充样式
![]()
from openpyxl.styles import PatternFill,GradientFill
from openpyxl import load_workbook
filepath = 'C:/Users/Doc/cc.xlsx'
workbook = load_workbook(filename=filepath)
sheet = workbook.active
cell_a3 = sheet['A3']
pattern_fill = PatternFill(fill_type='solid',fgColor='99ccff')
cell_a3.fill = pattern_fill
cell_a4 = sheet['A4']
gradient_fill = GradientFill(stop=('FFFFFF','99ccff','000000'))
cell_a4.fill = gradient_fill
workbook.save(filename=filepath)
设置行高和列宽
.row_dimensions[行编号].height =行高
.column_dimensions[列编号].width =列宽
filepath = 'C:/Users/Doc/cc.xlsx'
workbook = load_workbook(filename=filepath)
sheet = workbook.active
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['B'].width =20
workbook.save(filename=filepath)
合并单元格
![]()
filepath = 'C:/Users/Doc/cc.xlsx'
workbook = load_workbook(filename=filepath)
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=filepath)
