第三章 python在office中的应用(Excel篇)

本章需要安装的一些模块，可以在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)