[Python知识库]Python库openpyxl对Excel文件的常用处理操作

openpyxl的优劣势

优势：

• 1、openpyxl提供对pandas的dataframe对象完美支持；

• 2、openpyxl支持后台静默打开excel文件；

• 3、它支持excel的一些sort、filter筛选、排序功能，支持丰富的单元格style（样式）设计；

• 4、它同时支持读取现成的excel文件&创建全新的excel文件；

• 5、它支持最新的xlsx格式文件，且更新频繁，操作较为简练。

劣势

• 1、运算效率相对不高，当表格行项目过多时，运算相对pandas等速度较慢；

• 2、部分针对行或列的样式设计有一定bug，语法可能失效；

• 3、对sort和filter的功能虽然支持，但是需要用户手工打开excel后刷新数据方能使搜索条件生效，属于半自动；

• 4、不支持excel原生的自动列宽功能，实现同样效果略复杂。

正文

简单案例（使用openpyxl的一般流程为：创建/读取excel文件–>选择sheet对象–>对表单/cell进行操作–>保存excel）

from openpyxl import Workbook

#创建一个工作薄对象,也就是创建一个excel文档
wb = Workbook()

#指定当前显示（活动）的sheet对象
ws = wb.active

# 给A1单元格赋值
ws['A1'] = 42

# 一行添加多列数据
ws.append([1, 2, 3])

# 保存excel
wb.save("sample.xlsx")
wb.save(r'D:\example.xlsx')

一，创建/读取/保存 Excel文件

创建空白excel

from openpyxl import Workbook
wb = Workbook()

读取excel

from openpyxl import load_workbook
wb = load_workbook('1.xlsx')

保存excel

wb.save('filename.xlsx')
wb.save(r'D:\example.xlsx')

二，sheet表单操作

创建新的sheet

#默认插入到最后
ws = wb.create_sheet("newsheet") 
#插入到最开始的位置(从0开始计算)
ws = wb.create_sheet("newsheet", 0)

获取现有所有sheet的名称列表

#以list方式返回excel文件所有sheet名称（->list[str,str..]）
wb.sheetnames
wb.get_sheet_names()

选择活动sheet对象

#根据sheet名称选取
ws = wb['sheet1']
ws = wb.get_sheet_by_name('sheet1')
#选择当前显示，活动的sheet
ws = wb.active
ws = wb.get_active_sheet()

复制一个sheet对象

source = wb.active
target = wb.copy_worksheet(source)

遍历表单

#按行完整遍历表
for row in sheet:
    for cell in row:
        print(cell.value,end=",")#默认end是换行

    print()

#按行指定区域遍历表
for row in sheet.iter_rows(min_row=3,max_row=4,min_col=3,max_col=20):
    for cell in col:
        print(cell.value, end=",")
    print()


#按列遍历表
for col in sheet.columns:
    for cell in col:
        print(cell.value, end=",")
    print()

#按列指定区域遍历表
for col in sheet.iter_cols(min_col=3,max_col=4,min_row=3,max_row=20):
    for cell in col:
        print(cell.value, end=",")
    print()

Openpyxl 冻结窗格

from openpyxl import Workbook
from openpyxl.styles import Alignment

book = Workbook()
sheet = book.active

sheet.freeze_panes = 'B2'

book.save('freezing.xlsx')

sheet常见属性

#sheet名称
sheet.title
#最大行和最大列
sheet.max_row
sheet.max_column
#行列生成器
sheet.rows #为行生成器, 里面是每一行的cell对象，由一个tuple包裹。
sheet.columns #为列生成器, 里面是每一列的cell对象，由一个tuple包裹。

可以使用list(sheet.rows)[0].value 类似方法来获取数据，或
for row in sheet.rows:
    for cell in row:
        print(cell.value)
来遍历值,或值生成器 sheet.values 仅遍历值

删除sheet

wb.remove(sheetobject) 
del wb['sheet'] #sheetname

删除指定的行列数据

#从第六列开始删除之后的三列
ws.delete_cols(6,3)
#删除第三行
wx.delete_rows(3)

sheet的其它操作

#插入行，在第7行之前插入
ws.insert_rows(7)
#插入列，在第7列之前插入
ws.insert_cols(7)
#删除行列
ws.delete_rows(7)
ws.delete_cols(7)
#可以删除多个
ws.delete_cols(6, 3)

根据数字得到字母，根据字母得到数字（对应位置转换使用）

from openpyxl.utils import get_column_letter, column_index_from_string

# 根据列的数字返回字母
print(get_column_letter(2))  # B
# 根据字母返回列的数字
print(column_index_from_string('D'))  # 4

矩阵置换（行 → 列）

rows = [
    ['Number', 'data1', 'data2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10]]

list(zip(*rows))

# out
[('Number', 2, 3, 4, 5, 6, 7),
 ('data1', 40, 40, 50, 30, 25, 50),
 ('data2', 30, 25, 30, 10, 5, 10)]

# 注意 方法会舍弃缺少数据的列(行)
rows = [
    ['Number', 'data1', 'data2'],
    [2, 40      ],    # 这里少一个数据
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]
# out
[('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50)]

三，单元格对象操作

添加单元格数据

• 使用append()方法，我们可以在当前工作表的底部附加一组值。
• 单行数据可以直接使用append函数，但当插入多行数据时，不能给append函数复合数据，只能循环进行单行插入操作。

from openpyxl import Workbook

book = Workbook()
sheet = book.active

rows = (
    (88, 46, 57),
    (89, 38, 12),
    (23, 59, 78),
    (56, 21, 98),
    (24, 18, 43),
    (34, 15, 67)
)

for row in rows:
    sheet.append(row)

book.save('appending.xlsx')

选择cell单元格对象

#根据名称访问
a1 = ws['A1'] #A列1行的单元对象
a2 = ws['a2'] #也可以小写
#cell方法访问
b2 = ws.cell(row=2, column=2)
b3 = ws.cell(3,2)
#从cell列表中返回
b3 = list(ws.rows)[2][1]
b3 = list(ws.columns)[1][2]

切片选择多个单元格

#切片访问
a2_b3 = ws['a2':'b3']
以行组成tuple返回tuple
((<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>))
#单独字母与数字返回列与行的所有数据
b = ws['b'] #返回b列的所有cell对象
row1 = ws['1'] #返回第1行的所有cell
#当然也能范围选择
a_e = ws['a:e'] #a-e列的cell对象

更改单元格值

#直接赋值
ws['a2'] = 222 
ws['a2'] = 'aaa'
ws['b2'] = '=SUM(A1:A17)' #使用公式

#value属性赋值
cell.value = 222
或
ws.cell(1,2,value = 222)

单元格属性

#返回列
cell.column
#返回行
cell.row
#返回值
cell.value
注意:如果单元格是使用的公式，则值是公式而不是计算后的值
#返回单元格格式属性
cell.number_format
默认为General格式
#单元格样式
cell.font

移动单元格

ws.move_range("D4:F10", rows=-1, cols=2)
表示单元格D4:F10向上移动一行，右移两列。单元格将覆盖任何现有单元格。(最新版本的才会这个方法，使用pip list查看版本是否为最新)
ws.move_range("G4:H10", rows=1, cols=1, translate=True)
移动中包含公式的自动转换

合并与拆分单元格

#合并单元格,以最左上角写入数据或读取数据
ws.merge_cells('A2:D2')
#拆分单元格
ws.unmerge_cells('A2:D2')

四，格式样式设置

导入类

from openpyxl.styles import Font, colors, Alignment

Font类常见参数

font = Font(name='Calibri',  　　　　　　#字体名字
                 size=11,            　#字体大小
                 bold=False,        　　#是否加粗
                 italic=False,        #斜体
                 underline='none',　　#下划线
                 color='FF000000')　　#颜色,可以用colors中的颜色

设置字体

t_font = Font(name='Calibri', size=24, italic=True, color=colors.RED, bold=True)
#给font属性赋值font对象即可
sheet['A1'].font = t_font

对齐方式

# 设置B1中的数据垂直居中和水平居中，除了center，还可以使用right、left等等参数
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')

设置单元格行高，列宽

# 第2行行高
sheet.row_dimensions[2].height = 40
# C列列宽
sheet.column_dimensions['C'].width = 30

最后再举个例子温习一下

import datetime
from random import choice
from time import time
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# 设置文件 mingc
addr = "openpyxl.xlsx"
# 打开文件
wb = load_workbook(addr)
# 创建一张新表
ws = wb.create_sheet()
# 第一行输入
ws.append(['TIME', 'TITLE', 'A-Z'])

# 输入内容（500行数据）
for i in range(500):
    TIME = datetime.datetime.now().strftime("%H:%M:%S")
    TITLE = str(time())
    A_Z = get_column_letter(choice(range(1, 50)))
    ws.append([TIME, TITLE, A_Z])

# 获取最大行
row_max = ws.max_row
# 获取最大列
con_max = ws.max_column
# 把上面写入内容打印在控制台
for j in ws.rows:    # we.rows 获取每一行数据
    for n in j:
        print(n.value, end="\t")   # n.value 获取单元格的值
    print()
# 保存，save（必须要写文件名（绝对地址）默认 py 同级目录下，只支持 xlsx 格式）
wb.save(addr)