Python - openpyxl Excel 操作示例与实践

简介: Python openpyxl库常用操作与实例分析~

 一.引言

使用脚本处理得到数据后需要转化至 excel,由于手动粘贴以及换行符的问题非常不智能,所以改用 openpyxl 库处理脚本得到的数据并完成标注,顺便介绍下 openpyxl 的常用语法,先看下我们的原始数据:

A B C D
0.012 0.951 0.234 -0.137
0.038 0.999 -0.225 0.139

image.gif

其中 A,B,C,D 为表头,下两列为小数点比例,我们想要达到的效果:

(1) 小数转化为百分数且保留1位小数

(2) 大于0的百分数标记为红色,小于0的数标记为绿色

(3) 数字在表中对齐

image.gif编辑

下面来熟悉下 openpyxl 的常用操作并实现上述需求~

二.openpyxl 常规操作

1.创建 WorkBook

Workbook 类为 openpyxl 的基础类,初始化该类可以理解为打开一个全新的 excel 工作区,其中 create_sheet 可以视作新建一个 sheet,下述示例创建一个名为 Sheet1 的表格,一个 Workbook 可以初始化多个 sheet:

# 初始化
workbook = Workbook()
# 创建 sheet
sheet = workbook.create_sheet("Sheet")

image.gif

2.sheet 添加数据

A.单点添加

sheet 对应 Sheet 类,其中表格中的每一个单元格对应 Cell 类,可以通过 Sheet['position'] = xxx 设定单元格的值,随后通过 Cell.value 获取对应单元格的值,position 需要通过 excel 类似的索引进行设定,例如 'A1', 'B10','AA5',而获取 cell 则需要通过 (row, col) 索引,这里有一些不同。

image.gif编辑

例如我想在 D7 位置设置为 999,则可以执行 sheet['D7'] = 999。

sheet['D7'] = 999

image.gif

B.批量添加

sheet = workbook.create_sheet("Sheet", 0)
sheet.append([1, 2, 3, 4])
sheet.append([2, 3, 4, 5])
sheet.append([3, 4, 5, 6])

image.gif

该方法会逐行添加数组的数据:

image.gif编辑

数组不等长也可以:

sheet.append([1, 2, 3, 4])
sheet.append([2, 3, 4, 5, 6])
sheet.append([3, 4, 5, 6])

image.gif

image.gif编辑

3.获取 sheet 数据

上面提到每个单元格的数据为 Cell,需要通过 sheet.cell(row, col) 方法获得,这里需要注意 excel 的行列索引都是从1开始,从 0 开始遍历会报错。下面获取 D7 的值:

cell = sheet.cell(7, 4)
print(cell.value)

image.gif

999

image.gif

4.行列转换

上面添加值采用的坐标系是 A-col 5-row 这种字母+数字的组合,而获取 Cell 单元格采取的是 10-row 2-col 这样的坐标形式,因此二者关联涉及到 col 的转换问题。这里 openpyxl 库提供了 util 函数,只需调用即可得到 index- string 的转换:

from openpyxl.utils import get_column_letter, column_index_from_string
num = 10
# index 转换为 字符
index2string = get_column_letter(num)
# 字符转换为 index
string2index = column_index_from_string(index2string)
print(num, "->", index2string)
print(index2string, "->", string2index)

image.gif

10 -> J
J -> 10

image.gif

image.gif编辑

5.访问多行,多列

A.切片访问

for i in sheet['A1':'A10']:
    cell = i

image.gif

(<Cell 'Sheet1'.A1>,)
(<Cell 'Sheet1'.A2>,)
(<Cell 'Sheet1'.A3>,)
(<Cell 'Sheet1'.A4>,)
(<Cell 'Sheet1'.A5>,)
(<Cell 'Sheet1'.A6>,)
(<Cell 'Sheet1'.A7>,)
(<Cell 'Sheet1'.A8>,)
(<Cell 'Sheet1'.A9>,)
(<Cell 'Sheet1'.A10>,)

image.gif

上述代码访问 A列的 1-10 行,对应下图所示区域:

image.gif编辑

 同理也可以用切片访问同一行的数据:

for i in sheet['A1':'D1']:
    cell = i

image.gif

(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>)

image.gif

image.gif编辑

B.区域遍历

对指定表格区域进行遍历,选取 row 和 col 的大小区间即可遍历对应区域内元素:

for row in sheet.iter_rows(min_row=5, max_row=8, min_col=1, max_col=3):
    for cell in row:
        print(cell)

image.gif

<Cell 'Sheet1'.A5>
<Cell 'Sheet1'.B5>
<Cell 'Sheet1'.C5>
<Cell 'Sheet1'.A6>
<Cell 'Sheet1'.B6>
<Cell 'Sheet1'.C6>
<Cell 'Sheet1'.A7>
<Cell 'Sheet1'.B7>
<Cell 'Sheet1'.C7>
<Cell 'Sheet1'.A8>
<Cell 'Sheet1'.B8>
<Cell 'Sheet1'.C8>

image.gif

上述代码访问 1-3 列即 A-C 列,5-8 行,元素对应下述方框:

image.gif编辑

C.全局遍历

遍历 rows 或者 cols 其实都是遍历整个 sheet。

# 从 row 的角度遍历 sheet
for rows in sheet.rows:
    for row in rows:
        print(row.value)
# 从 col 的角度遍历 sheet
for cols in sheet.columns:
    for col in cols:
        print(col.value)

image.gif

image.gif编辑

6.获取行列数

# 获取行列数
rowNum = sheet.max_row
colNum = sheet.max_column
print("row: ", rowNum, "col: ", colNum)

image.gif

row: 8 col: 4

image.gif

7.使用公式

A.单点使用公式

下述命令定义 C15 处的值为 5 + 5 = 10

sheet['C15'] = '=sum({}, {})'.format(5, 5)

image.gif

image.gif编辑

B.批量使用公式

先创建一个新表格 sheet2,然后列名分别为 Name,Score 并添加对应值:

sheet2 = workbook.create_sheet("Sheet2", 0)
sheet2['A1'] = "Name"
sheet2['B1'] = "Score"
sheet2.append(['A', 1])
sheet2.append(['B', 2])
sheet2.append(['C', 3])
sheet2['C1'] = "Add_1"

image.gif

image.gif编辑

下面应用公式,其中 Add_1 列的数据为 B2-B4 的值+1:

for row in range(2, 5):
    colLetter = get_column_letter(3)
    sheet2[colLetter + str(row)] = '=sum({}, {})'.format(sheet2.cell(row, 2).value, 1)

image.gif

image.gif编辑

8.保存,读取,删除表格

其中 file_path 为对应表格的存储,删除位置:

# 保存表
workbook.save(file_path)
# 删表
workbook.remove(file_path)
# 读取sheet
wb = openpyxl.load_workbook(file_path)
sheet = wb.get_sheet_by_name(sheet_name)

image.gif

三.openpyxl 样式操作

1.简介

上面介绍了如何向表格添加数据,以及对表格的数据进行遍历,处理,下面针对表格样式进行分析,现在我们知道每一个单元格对应一个 Cell Class 即 from openpyxl.cell import Cell :

image.gif编辑

cell 除了调用 .value 方法获取其对应值外,还可以修改如下格式:

A. font - 字体相关

B. fill - 填充颜色相关

C.alignment - 对齐相关

D.border - 边界相关

E.comment - 注释相关

下述示例基于上一步 append 得到的基本 sheet:

image.gif编辑

2.font 字体

下面对 B2 位置的 cell 切换为 黑体简 11号 加粗 红色 字体:

cell = sheet.cell(column=2, row=2)
# 指定 font 字体名 name + 大小 size + 加粗 blod + 斜体 italic + 颜色 color
font = Font(name='黑体简', size=11, italic=False, color="FF0000", bold=True)
cell.font = font

image.gif

image.gif编辑

这里参数含义为:

name 字体
size 大小
italic 斜体
blod 加粗
color 颜色

字体样式等参数详情也可以直接去 excel 里看,可以提前看看效果:

image.gif编辑

3.fill 填充相关

fill 主要负责单元格填充颜色,下面分别使用完整填充和渐变填充对单元格进行填充:

A.(2-3)位置填充完灰色 PatternFill

fgColor 为对应颜色的编码

cell = sheet5.cell(row=2, column=3)
cell.fill = PatternFill("solid", fgColor="00778899")

image.gif

B.(2-4)位置填充为红绿渐变

stop 内也可以添加多种颜色,会实现多层次渐变

cell2 = sheet5.cell(row=2, column=4)
cell2.fill = GradientFill(stop=("007CFC00", "00FF4500"))

image.gif

image.gif编辑

4.alignment 对齐相关

alignment = Alignment(horizontal="center", vertical="center", 
                      text_rotation=0, wrap_text=False)
cell = sheet.cell(row=2, column=3)
cell.alignment = alignment

image.gif

对 (2,3)位置的元素进行居中处理,参数含义为:

horizontal 水平对齐模式
vertical 垂直对齐模式
text_rotation 旋转角度
wrap_text 是否自动换行

image.gif编辑

5.border 边界相关

对 (2,4)位置元素加边界,上下分别为绿色和黑色细线,左右分别为红绿双线:

cell = sheet.cell(row=2, column=4)
cell.border = Border(left=Side(style='double', color="FF0000"),
                     right=Side(style='double', color="0000FF"),
                     top=Side(style='thin', color="00FF00"),
                     bottom=Side(style='thin'))

image.gif

image.gif编辑

Border 内共包含 left,right,top,bottom 即上下左右四个方位的边界线

SIde 为对应边界线属性,style 比较多,填写 style 随便填一个代码就会报错,随后异常栈就会打出当前版本支持的 style 属性,根据需求选取即可:

ValueError: Value must be one of {'dotted', 'dashed', 'mediumDashDotDot', 
'double', 'thick', 'dashDot', 'medium', 'slantDashDot', 'mediumDashDot', 'thin', 
'dashDotDot', 'hair', 'mediumDashed'}

image.gif

6.comment 注释相关

对 (1,1)位置元素进行注释,第一个参数为注释内容,第二个参数为对应 Author:

# 注释
cell = sheet.cell(row=1, column=1)
comment = Comment("This is a comment!", "DDD")
cell.comment = comment

image.gif

image.gif编辑

7.height-width 相关

sheet5.row_dimensions[1].height = 50
sheet5.column_dimensions['B'].width = 20

image.gif

调整 'B'列 和 '1'行 单元格的行宽和列高:

image.gif编辑

8.合并与取消合并单元格

A.合并单元格

sheet.merge_cells("C1:D2")

image.gif

以 C1 为边框左上角,D2 为边框右下角进行单元格合并,数据会保留为 C1 对应的 Value:

image.gif编辑

B.取消合并单元格

sheet.unmerge_cells("C1:D2")

image.gif

单元格解除合并,但是其余单元格的数据不会回补

image.gif编辑

四.openpyxl 实践

1.案例需求

经过上面的讲解分析,已经熟悉了 openpyxl 的基本操作,下面回归到引言中提到的问题,我们需要将下述数据:

A B C D
0.012 0.951 0.234 -0.137
0.038 0.999 -0.225 0.139

image.gif

转换为:

image.gif编辑

这里浅分析一下转化的过程:

A.初始化 Workbook 与 Sheet

B.通过 row,col 将对应元素添加至 Sheet 对应位置

C.大于 0 的值对应位置 Cell 颜色更改为红色,小于 0 更改为绿色

D.全部单元格居中对齐

E.保留1位小数

2.案例实现

def colorFulData(_sheet, _head, _values):
    # 定义字体
    _font = None
    # 获取 values 的长款
    _colNum = len(_head)
    _rowNum = len(_values)
    # 定义对齐方式
    _alignment = Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=False)
    for _col in range(1, _colNum + 1):
        # col 转换并添加元素到 Cell
        _sheet[get_column_letter(_col) + str(1)] = head[_col - 1]
        _cell = _sheet.cell(1, _col)
        _cell.alignment = _alignment
        for _row in range(1, _rowNum + 1):
            # 保留一位小数
            _sheet[get_column_letter(_col) + str(_row + 1)] = format(values[_row - 1][_col - 1], '.1%')
            _cell = _sheet.cell(_row + 1, _col)
            numValue = float(values[_row - 1][_col - 1])
            # 根据值改变字体颜色
            if numValue > float(0):
                _font = Font(name='黑体简', size=11, italic=False, color="00FF0000", bold=True)
            elif numValue < float(0):
                _font = Font(name='黑体简', size=11, italic=False, color="0000FF00", bold=True)
            _cell.font = _font
            _cell.alignment = _alignment
head = ["A", "B", "C", "D"]
values = [[0.012, 0.951, 0.234, -0.137], [0.038, 0.999, -0.225, 0.139]]
colorFulData(sheet, head, values)
workbook.save(file_path)

image.gif

只需要传入表头和对应的 values 数据即可,这里没有 try catch,因此输入的 value 应该是合法的形式,否则会报错,更多的字体样式,单元格合并,单元格填充等也可以添加到上面对应 Cell 中,下面是基础形式的实现得到的效果:

image.gif编辑

五.openpyxl 拓展

上面单元格的 color 或者是填充的 color 都用到了颜色代码,下面给出一些常用的颜色编码,有需要的可以应用到自己的程序内:

'00F0F8FF', '00FAEBD7', '0000FFFF', '007FFFD4', '00F0FFFF', '00F5F5DC', 
    '00FFE4C4', '00000000', '00FFEBCD', '000000FF', '008A2BE2', '00A52A2A', 
    '00DEB887', '005F9EA0', '007FFF00', '00D2691E', '00FF7F50', '006495ED',
    '00FFF8DC', '00DC143C', '0000FFFF', '0000008B', '00008B8B', '00B8860B', 
    '00A9A9A9', '00006400', '00BDB76B', '008B008B', '00556B2F', '00FF8C00',
    '009932CC', '008B0000', '00E9967A', '008FBC8F', '00483D8B', '002F4F4F',
    '0000CED1', '009400D3', '00FF1493', '0000BFFF', '00696969', '001E90FF', 
    '00B22222', '00FFFAF0', '00228B22', '00FF00FF', '00DCDCDC', '00F8F8FF', 
    '00FFD700', '00DAA520', '00808080', '00008000', '00ADFF2F', '00F0FFF0', 
    '00FF69B4', '00CD5C5C', '004B0082', '00FFFFF0', '00F0E68C', '00E6E6FA', 
    '00FFF0F5', '007CFC00', '00FFFACD', '00ADD8E6', '00F08080', '00E0FFFF', 
    '00FAFAD2', '0090EE90', '00D3D3D3', '00FFB6C1', '00FFA07A', '0020B2AA', 
    '0087CEFA', '00778899', '00B0C4DE', '00FFFFE0', '0000FF00', '0032CD32', 
    '00FAF0E6', '00FF00FF', '00800000','0066CDAA', '000000CD', '00BA55D3', 
    '009370DB', '003CB371', '007B68EE', '0000FA9A', '0048D1CC', '00C71585',
    '00191970', '00F5FFFA', '00FFE4E1', '00FFE4B5', '00FFDEAD', '00000080', 
    '00FDF5E6', '00808000', '006B8E23', '00FFA500', '00FF4500', '00DA70D6', 
    '00EEE8AA', '0098FB98', '00AFEEEE', '00DB7093', '00FFEFD5', '00FFDAB9',
    '00CD853F', '00FFC0CB', '00DDA0DD', '00B0E0E6', '00800080', '00FF0000', 
    '00BC8F8F', '004169E1', '008B4513', '00FA8072', '00FAA460', '002E8B57', 
    '00FFF5EE', '00A0522D', '00C0C0C0', '0087CEEB', '006A5ACD', '00708090',
    '00FFFAFA', '0000FF7F', '004682B4', '00D2B48C', '00008080', '00D8BFD8', 
    '00FF6347', '0040E0D0', '00EE82EE', '00F5DEB3', '00FFFFFF', '00F5F5F5', 
    '00FFFF00', '009ACD32'

image.gif

这里简单修改一下程序就可以得到上述代码对应的真实颜色,还是有很多好看的颜色可以选择:

image.gif编辑

目录
相关文章
|
5天前
|
Python
Python 自动化操作 Excel - 02 - xlwt
Python 自动化操作 Excel - 02 - xlwt
31 14
|
5天前
|
Python
Python 自动化操作 Excel - 03 - xlutils
Python 自动化操作 Excel - 03 - xlutils
23 13
|
5天前
|
Python
Python 自动化操作 Excel - 01 - xlrd
Python 自动化操作 Excel - 01 - xlrd
21 9
|
4天前
|
IDE 开发工具 数据安全/隐私保护
Python编程--实现用户注册信息写入excel文件
Python编程--实现用户注册信息写入excel文件
|
1天前
|
开发者 Python
Python类和子类的小示例:建模农场
Python类和子类的小示例:建模农场
|
3天前
|
存储 Python
Python实战项目Excel拆分与合并——合并篇
Python实战项目Excel拆分与合并——合并篇
|
3天前
|
存储 Python 容器
Python实战项目:Excel拆分与合并
Python实战项目:Excel拆分与合并
|
4天前
|
自然语言处理 搜索推荐 程序员
【Python】如何使用pip,安装第三方库和生成二维码、操作Excel
【Python】如何使用pip,安装第三方库和生成二维码、操作Excel
20 0
|
2月前
|
关系型数据库 MySQL Shell
不通过navicat工具怎么把查询数据导出到excel表中
不通过navicat工具怎么把查询数据导出到excel表中
35 0
|
1月前
|
数据采集 存储 数据挖掘
使用Python读取Excel数据
本文介绍了如何使用Python的`pandas`库读取和操作Excel文件。首先,需要安装`pandas`和`openpyxl`库。接着,通过`read_excel`函数读取Excel数据,并展示了读取特定工作表、查看数据以及计算平均值等操作。此外,还介绍了选择特定列、筛选数据和数据清洗等常用操作。`pandas`是一个强大且易用的工具,适用于日常数据处理工作。