【python处理Excel之openpyxl】

安装软件

pip install openpyxl ###python2 安装库
pip3 install openpyxl ###python3 安装库

pip install openpyxl==版本号 || pip install openpyxl==3.0.9 ##安装指定版本

pycharm安装openpyxl库
选择 File > Settings打开设置面板。
在设置面板中，选择 Project: 你的项目名 > Project Interpreter。
点击右侧的加号按钮，这通常标有 “+” 或 “Install”。
在搜索框中输入 openpyxl。
选择 openpyxl 并点击 Install Package 或相应的安装按钮进行安装。

或使用快捷键 Ctrl+Alt+S

导入库使用

import openpyxl   #方式一
from openpyxl import load_workbook  ##方式二

一个Excel表格文档称为一个工作簿:excel_obj = load_workbook(excel_path)
一个工作簿可以包含多个sheet表,打印所有表格名称:excel_obj.sheetnames
用户当前查看的表（或关闭Excel前最后查看的表）称为活动表sheet = excel_obj.active

读取Excel数据

1. 打开某个excel表

import os
from openpyxl import load_workbook

excel_path_Dir = r"C:\Users\user_name\Desktop"

Dir_path = os.path.dirname(os.path.abspath(__file__))
####当前py脚本所在的路径
print("Current_py_path: ",Dir_path)
excel_path = os.path.join(Dir_path, 'test1_1111.xlsx')
excel_path = os.path.join(excel_path_Dir, 'test1_1111.xlsx') ###excel在桌面存放
####当前py脚本所在的路径和excel文件拼接（excel和py在同一级目录）
print("Current_excel_path: ",excel_path)


excel_obj = load_workbook(excel_path) # 返回一个workbook数据类型的值
print('all sheet name:',excel_obj.sheetnames) ##打印所有sheetname 返回列表
# 打印所有sheet的名称
for id,sheetname in enumerate(excel_obj.sheetnames):
    print("index :",id,"SheetName: ",sheetname)

#############结果
Current_excel_path:  C:\RCS_Files\11111\compare1\test\python\test1_1111.xlsx
all sheet name: ['English', 'Chinese', 'Math']
index : 0 SheetName:  English
index : 1 SheetName:  Chinese
index : 2 SheetName:  Math

2. 获取某个sheet对象

sheet_name = excel_obj["Math"]  ###获取sheet为Math的对象
print(sheet_name)

sheet_name = excel_obj.active  # 获取活动表，当前正在看的sheet表，或者上次关闭之前看的的sheet
print(sheet_name )

#######结果
<Worksheet "Math">
<Worksheet "Chinese">

修改某个sheet名字
excel_obj["Math"].title = new_sheet_name
workbook.save('1.xlsx')

3. 获取某行某列

print("row_max: ",sheet_name.max_row) ##打印目标sheet一共多少行
print("colume_max: ",sheet_name.max_column)##打印目标sheet一共多少列
print("sheet_dimensioning: ",sheet_name.dimensions) # 获取表格的尺寸大小，也就是哪个范围有数据
#######结果
row_max:  62
colume_max:  6
sheet_dimensioning:  A1:F62

3.1 获取某单元格数据

cell2 = sheet['B1'].value		# 方式二，常用方式一
cell = sheet_name.cell(row=1,column=3)  ##方式一
print(cell)
print("result :",cell.value)  ###加value才是结果
cell1.value获取单元格中的值
#######结果
<Cell 'Math'.C1>
result : 98

print(cell.value, cell.row, cell.column, cell.coordinate) ##获取cell的结果，所在行的，所在的列，以及坐标
#########结果
98 1 3 C1

3.2 获取某区间单元格数据

cell = sheet_name['A1:A5']  # 获取A1到A5的数据
print(cell)
# 打印A1到A5的数据
for i in cell:
    for j in i:
        print(j.value)

#########结果是
((<Cell 'Chinese'.A1>,), (<Cell 'Chinese'.A2>,), (<Cell 'Chinese'.A3>,), (<Cell 'Chinese'.A4>,), (<Cell 'Chinese'.A5>,))
1
2
3
4
5

3.3 获取某行单元格数据

cell = sheet_name['2']
for i in cell:
    print(i.value)

#########结果
2
22
22
33
33
None

3.4 获取某列单元格数据
用字母当key，是获取列数据
sheet[“A”] — 获取A列的数据
sheet[“A:C”] — 获取A,B,C三列的数据

# 获取D列的所有单元格
column = sheet_name['D']

# 遍历列中的所有单元格，并打印出单元格的值,包含空格
for cell in column:
    print(cell.value)

# 如果你只想获取D列中非空的单元格，可以使用以下代码
for cell in column:
    if cell.value is not None:
        print(cell.value)

##########结果
4
33
33dd
None
f
None
None
None
None
如果你只想获取D列中非空的单元格
4
33
33dd
f

3.5 按行、列获取值

按行取值，就是取完一行，再取下一行数据，以此类推。

print('按行获取值')
for i in sheet_name.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2):
    for j in i:
        print(j.value)

###########结果
按行获取值
2
22
3
None
4
f
5
x

按列取值，就是取完一列，再取下一列数据，以此类推。

# 按列获取值
print('按列获取值')
for i in sheet_name.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2):
    for j in i:
        print(j.value)

##########结果
按列获取值
2
3
4
5
22
None
f
x

4 插入行或者列
操作时，不要桌面打开excel表格
insert_rows(idx=数字编号, amount=要插入的行数)
insert_cols(idx=数字编号, amount=要插入的列数)

sheet_name.insert_rows(idx=3, amount=2) ###在第三行开始插入两行空行
sheet_name.insert_cols(idx=2, amount=1) ###在第二列开始插入一列空行
excel_obj.save('test1_1111.xlsx')

4.1 删除行或者列
操作时，不要桌面打开excel表格
delete_rows(idx=数字编号, amount=要删除的行数)
delete_cols(idx=数字编号, amount=要删除的列数)

Dir_path = os.path.dirname(os.path.abspath(__file__))
print("Current_py_path: ",Dir_path)
excel_path = os.path.join(Dir_path, 'test1_1111.xlsx')
print("Current_excel_path: ",excel_path)
excel_obj = load_workbook(excel_path)
sheet_name = excel_obj['Chinese']
sheet_name.delete_rows(idx=6)           # 删除第6行
sheet_name.delete_cols(idx=3, amount=2)  # 删除第3列，及往右共2列

excel_obj.save('test1_1111.xlsx')

4.2 移动单元格
move_range(“数据区域”,rows=,cols=)：正整数为向下或向右、负整数为向左或向上

row决定了上下移动；cols决定了左右移动；
row为正，向下移动
cols为正，向右移动

sheet_name.move_range('B3:C4',rows=0,cols=5)  # 移动B3到C4构成的矩形格子;向右移动五个单元格
excel_obj.save('test1_1111.xlsx')

5 插入数据
使用append()方法，在excel表中指定sheet中，原来数据的后面，按行插入数据；最后一行数据，往下插入

Dir_path = os.path.dirname(os.path.abspath(__file__))
print("Current_py_path: ",Dir_path)
excel_path = os.path.join(Dir_path, 'test1_1111.xlsx')
print("Current_excel_path: ",excel_path)
excel_obj = load_workbook(excel_path)
sheet_name = excel_obj['Chinese']
########插入三列数据=============
data = [
    ['liming',23,11],
    ['zheng',24,111],
    ['wang',2,1111]
]
for row in data:
    sheet_name.append(row)   # 使用append插入数据
excel_obj.save('test1_1111.xlsx')

excel_obj.copy_worksheet(sheet1111111) # 复制sheet表
excel_obj.remove(sheet) # 删除指定sheet表

创建Excel数据

import os
from openpyxl import load_workbook
import openpyxl

Dir_path = os.path.dirname(os.path.abspath(__file__))
print("Current_py_path: ",Dir_path)

# 创建workbook对象进行保存，就得到一个新的Excel文件
file_path = os.path.join(Dir_path, 'test1_1122.xlsx')
workbook = openpyxl.Workbook()
sheet = workbook.active  ##创建完excel表之后，会默认创建sheet表，选中这个表
sheet.title = '1号sheet'  ##修改sheet----->1号sheet

# 创建新的sheet页
new_sheet = workbook.create_sheet("newSheet")  # 默认插入到工作簿的最后一个sheet页后
new_sheet2 = workbook.create_sheet("newSheet2", 1)  # 插入到第1个工作簿后面
workbook.save(file_path)

# 获取当前工作簿所有的sheet页名称
print(workbook.sheetnames)

使用openpyxl.Workbook()创建的工作簿会有一个默认的sheet.
默认生成的sheet页名字为“sheet”，可以对workbook.active.title属性赋值修改名字。
创建新的sheet页可以用workbook.create_sheet(sheetname)函数

格式

1 读取格式

sheet_name = excel_obj['Chinese']

cell = sheet_name['B4']
font = cell.font
print('当前单元格的字体样式是')
print(font.name, font.size, font.bold, font.italic, font.color)

############结果是
当前单元格的字体样式是
Calibri 11.0 True False <openpyxl.styles.colors.Color object>
Parameters:
rgb='FFFFFF00', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'

Process finished with exit code 0

2 修改字体格式
openpyxl.styles.Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
其中，字体颜色中的color是RGB的16进制表示

excel_obj = load_workbook(excel_path)

sheet_name = excel_obj['Chinese']

cell = sheet_name['A']
for i in cell:
    i.font = openpyxl.styles.Font(name="微软雅黑", size=20, bold=True, italic=True, color="FF0000")

excel_obj.save('test1_1111.xlsx')

3 对齐格式
Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
水平对齐：‘distributed’，‘justify’，‘center’，‘left’， ‘centerContinuous’，'right，‘general’
垂直对齐：‘bottom’，‘distributed’，‘justify’，‘center’，‘top’

sheet_name = excel_obj['Chinese']

cell = sheet_name['A1']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
cell.alignment = alignment

excel_obj.save('test1_1111.xlsx')


or:###针对A列进行修改
cell = sheet_name['A']  
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
for i in cell:
    i.alignment = alignment
    
excel_obj.save('test.xlsx')

4 设置行、列宽度
row_dimensions[行编号].height = 行高
column_dimensions[列编号].width = 列宽

excel_obj = load_workbook(excel_path)
sheet_name = excel_obj['Chinese']
# 设置第1行的高度
sheet_name.row_dimensions[1].height = 20
# 设置B列的卷度
sheet_name.column_dimensions['B'].width = 15
excel_obj.save('test1_1111.xlsx')