最全总结 | 聊聊 Python 办公自动化之 Excel(中)

AirPython 2020-11-11 13:52:29
总结 Python 聊聊 最全 全总


image

1. 前言

上一篇文章中,我们聊到使用 xlrd、xlwt、xlutils 这一组合操作 Excel 的方法

最全总结 | 聊聊 Python 办公自动化之 Excel(上)

​本篇文章将继续聊另外一种方式,即:openpyxl

不得不说,openpyxl 更强大!

它支持 xlsx 格式的表格文件,并且支持 Numpy、Pandas 等包,可用于绘制图表

2. 准备

首先,我们需要安装依赖包

# 安装依赖包
pip3 install openpyxl

3. 读取数据

使用 openpyxl 中的 load_workbook(filepath) 加载本地一个 Excel 文件,返回结果是一个工作簿对象

import openpyxl
# 加载本地的Excel文件
wb = openpyxl.load_workbook(file_path)

利用工作簿对象,可以获取所有的 Sheet 名称及 Sheet 列表

def get_all_sheet_names(wb):
"""
获取所有sheet的名称
:param wb:
:return:
"""
# sheet名称列表
sheet_names = wb.sheetnames
return sheet_names
def get_all_sheet(wb):
"""
获取所有的sheet
:param wb:
:return:
"""
# sheet名称列表
sheet_names = get_all_sheet_names(wb)
# 所有sheet
sheets = []
for sheet_name in sheet_names:
sheet = wb[sheet_name]
sheets.append(sheet)
return sheets

工作簿对象提供了 active 属性,用于快速获取当前选择的 Sheet

def get_current_sheet(wb):
"""
获取当前选择的sheet,默认是最后一个sheet
:param wb:
:return:
"""
# 当前选中的sheet
current_sheet = wb.active
return current_sheet

另外,也可以通过 Sheet 名称去获取某一个特定的 Sheet 对象

def get_sheet_by_name(wb, sheet_name):
"""
通过sheetname去查找某一个sheet
:param wb:
​ :param sheet_name:
:return:
"""
sheet_names = get_all_sheet_names(wb)
if sheet_name in sheet_names:
result = wb[sheet_name]
else:
result = None
return result

使用 sheet.max_row 和 sheet.max_column 可以获取当前 Sheet 中的数据行数和列数

def get_row_and_column_num(sheet):
"""
获取sheet的行数和列数
:param sheet:
:return:
"""
# 行数
row_count = sheet.max_row
# 列数
column_count = sheet.max_column
return row_count, column_count
# 行数和列数
row_count, column_count = get_row_and_column_num(sheet)
print('行数和列数分别为:', row_count, column_count)

openpyxl 提供 2 种方式来定位一个单元格,分别是:

  • 数字索引,从 1 开始

    数字索引:行数字索引、列数字索引

    比如:row_index=1,column_index=1

  • 行和列组成的字符串索引

    字符串索引:列由字母组成 + 行索引

    比如:A1 对应第一行、第一列的单元格

并且,openpyxl.utils 提供了方法,便于 列索引 在两者之间进行转换

from openpyxl.utils import get_column_letter, column_index_from_string
def column_num_to_str(num):
"""
Excel索引列从数字转为字母
:param num:
:return:
"""
return get_column_letter(num)
def column_str_to_num(str):
"""
Excel索引列,从字母转为数字
:param str:
:return:
"""
return column_index_from_string(str)

单元格的获取,同样可以通过上面 2 种索引方式来获取

def get_cell(sheet, row_index, column_index):
"""
获取单元格
:param sheet:
:param row_index:
:param column_index:
:return:
"""
# openpyxl索引都是从1开始计数,这与xlrd有所不同
# 获取某一个单元格(二选一)
# 比如:获取A1单元格的数据,即第一个行、第一列的数据
# cell_one = sheet['A1']
cell_one = sheet.cell(row=row_index, column=column_index)
return cell_one

在日常处理 Excel 数据过程中,可能需要判断单元格数据类型,而 openpyxl 并没有提供现成的方法

这里,我们可以通过单元格对象的 value 属性拿到值,接着使用 isinstance 方法判断数据类型

def get_cell_value_and_type(cell):
"""
获取某一个cell的内容及数据类型
:param cell:
:return:
"""
# 单元格的值
cell_value = cell.value
# 单元格的类型
cell_type = get_cell_value_type(cell_value)
return cell_value, cell_type
def get_cell_value_type(cell_value):
"""
获取数据类型
:param cell_value:
:return:
"""
# 其中
# 0:空
# 1:数字
# 2:字符串
# 3:日期
# 4:其他
if not cell_value:
cell_type = 0
elif isinstance(cell_value, int) or isinstance(cell_value, float):
cell_type = 1
elif isinstance(cell_value, str):
cell_type = 2
elif isinstance(cell_value, datetime.datetime):
cell_type = 3
else:
cell_type = 4
return cell_type

单独获取某一行[列]的数据,可以使用下面的方式:

def get_row_cells_by_index(sheet, row_index):
"""
通过行索引,获取某一行的单元格
:param row_index:
:return:
"""
# 注意:第一列从1开始
row_cells = sheet[row_index]
return row_cells
def get_column_cells_by_index(sheet, column_index):
"""
通过列索引,获取某一列的单元格
"""
# 数字转为字母
column_index_str = column_num_to_str(column_index)
# 获取某一列的数据
column_cells = sheet[column_index_str]
return column_cells

需要注意的是,获取某一行的数据需要传入数字索引;而对于列数据的获取,必须传入字符串索引

和 Python 列表范围取值类似,openpyxl 同样支持使用 : 符号拿到某个范围内的数据行[列]

def get_rows_by_range(sheet, row_index_start, row_index_end):
"""
通过范围去选择行范围
比如:选择第2行到第4行的所有数据,返回值为元组
:param sheet:
:param row_index_start:
:param row_index_end:
:return:
"""
rows_range = sheet[row_index_start:row_index_end]
return rows_range
def get_columns_by_range(sheet, column_index_start, column_index_end):
"""
通过范围去选择列范围
比如:选择第2列到第4列的所有数据,返回值为元组
:param sheet:
:param column_index_start:
:param column_index_end:
:return:
"""
columns_range = sheet[column_num_to_str(column_index_start):column_num_to_str(column_index_end)]
return columns_range

4. 写入数据

要写入数据到 Excel 表格

首先,使用 openpyxl.Workbook() 创建一个 Excel 工作簿对象

接着,使用工作簿对象的 create_sheet() 新建一个 Sheet

# 创建一个Excel工作簿
# 注意:每次新建一个Excel文件,都会默认生成一个名称为【Sheet】的工作表Sheet
wb = openpyxl.Workbook()
# 创建一个新的sheet,默认被插到尾部
# new_sheet = wb.create_sheet('新的Sheet')
# 也可以通过第二个参数:index来指定插入的位置
# 比如:插入到开头
new_sheet = wb.create_sheet('新的Sheet', 0)

默认创建的 Sheet 被插入到最后一个位置,第 2 个参数可以指定 Sheet 插入的位置

Sheet 标签的背景色同样支持修改,使用 sheet_properties.tabColor 指定 RGB 颜色值

比如,要设置某一个 Sheet 的背景色为红色,只需要先查询到对应的 Sheet,然后指定颜色值为 FF0000 即可

def set_sheet_bg_color(sheet, rgb_value):
"""
设置Sheet标签的颜色
:param rgb_value:
:return:
"""
# 设置Sheet底部按钮的颜色(RRGGBB)
sheet.sheet_properties.tabColor = rgb_value
# 设置Sheet的背景色(红色)
set_sheet_bg_color(new_sheet, 'FF0000')

openpyxl 支持行列数字索引、字符串索引以这 2 种方式写入数据到单元格中

def write_value_to_cell_with_num(sheet, row_index, column_index, value):
"""
按行索引、列索引写入数据
:param shell:
:param row_index: 行索引
:param column_index: 列索引
:param value:
:return:
"""
# 二选一
sheet.cell(row=row_index, column=column_index, value=value)
# shell.cell(row=row_index, column=column_index).value = value
def write_value_to_cell_with_index_str(sheet, index_str, value):
"""
按字母位置,写入数据到对应单元格
:param shell:
:param index_str: 字母对应的单元格位置
:param value:
:return:
"""
sheet[index_str] = value

在单元格中插入图片也很简单,openpyxl 提供的 add_image() 方法

参数有 2 个,分别是:图片对象、单元格字符串索引

为了便于使用,我们可以将列索引进行转换,然后封装成两个插入图片的方法

from openpyxl.drawing.image import Image
def insert_img_to_cell_with_num(sheet, image_path, row_index, column_index):
"""
往单元格中插入图片
:param sheet:
:param image_path:
:param row_index:
:param column_index:
:return:
"""
# 通过行索引、列索引,获取到字母索引
index_str = column_num_to_str(column_index) + str(row_index)
insert_img_to_cell_with_str(sheet, image_path, index_str)
def insert_img_to_cell_with_str(sheet, image_path, index_str):
"""
往单元格中插入图片
:param sheet:
:param image_path:
:param index_str:
:return:
"""
sheet.add_image((image_path), index_str)

最后,调用工作簿对象的 save() 方法,将数据真实写入到 Excel 文件中

# 注意:必须要写入,才能真实的保存到文件中
wb.template = False
wb.save('new.xlsx')

5. 修改数据

修改数据包含:单元格数据的修改、单元格样式的修改

对于单元格数据的修改,只需要先读取工作簿对象,查询到要操作的 Sheet 对象,然后调用上面的方法修改单元格数据,最后调用 save() 函数保存覆盖即可

def modify_excel(self, file_path):
"""
修改本地Excel文件中数据
:param file_path:
:return:
"""
# 读取本地Excel文件
wb = openpyxl.load_workbook(file_path)
# 读取某一个sheet
sheet = wb['第一个Sheet']
print(sheet)
# 直接修改某一个单元格的数据
write_value_to_cell_with_num(sheet, 1, 1, '姓名1')
# 保存并覆盖
wb.save(file_path)

单元格样式包含:字体样式、单元格背景样式、边框样式、对齐方式等

以常见的字体样式、对齐方式为例

首先,使用 openpyxl 中的 Font 类创建一个对象,指定字体名称、字体大小、是否加粗、是否斜体、颜色、下划线等

from openpyxl.styles import Font
# 字体格式
# 指定字体类型、大小、是否加粗、颜色等
font0 = Font(name='Calibri',
size=20,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF00FF00')

接着,构建一个 Alignment 对象,指定单元格的对齐方式

from openpyxl.styles import Font,Alignment
# 单元格对齐方式
alignment0 = Alignment(horizontal='center',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)

最后,使用单元格对象的 font/alignment 属性,将字体样式和对齐方式设置进去即可

# 设置属性样式(字体、对齐方式)
sheet['A1'].font = font0
sheet['A1'].alignment = alignment0

6. 进阶用法

接下来,聊聊几个常用的进阶用法

1、获取可见及隐藏的 Sheet

通过判断 Sheet 对象的 sheet_state 属性值,可以判断当前 Sheet 是显示还是隐藏

当值为 visible 时,代表 Sheet 是显示的

当值是 hidden 时,代表这个 Sheet 被隐藏了

def get_all_visiable_sheets(wb):
"""
获取工作簿中所有可见的sheet
:param wb:
:return:
"""
return [sheet for sheet in get_all_sheet(wb) if sheet.sheet_state == 'visible']
def get_all_hidden_sheets(wb):
"""
获取工作簿中所有隐藏的sheet
:param wb:
:return:
"""
return [sheet for sheet in get_all_sheet(wb) if sheet.sheet_state == 'hidden']

2、获取隐藏/显示的行索引列表、列索引列表

受限于篇幅,这里以获取所有显示/隐藏的行索引列表为例

遍历 Sheet 对象的 row_dimensions 属性值,通过判断行属性的 hidden 值,判断当前行是否隐藏或显示

def get_all_rows_index(sheet, hidden_or_visiable):
"""
获取所有隐藏/显示的行
:param hidden_or_visiable: True:隐藏;False:显示
:param sheet:
:return:
"""
# 遍历行
# 隐藏的索引
hidden_indexs = []
# 所有隐藏的行索引
for row_index, rowDimension in sheet.row_dimensions.items():
if rowDimension.hidden:
hidden_indexs.append(row_index)
# 所有显示的行索引
visiable_indexs = [index + 1 for index in range(get_row_and_column_num(sheet)[0]) if index + 1 not in hidden_indexs]
# 隐藏或者显示的行索引列表
return hidden_indexs if hidden_or_visiable else visiable_indexs

3、获取单元格字体颜色及单元格背景颜色

单元格对象的 font.color.rgb、fill.fgColor.rgb 属性值分别代表字体颜色值、单元格背景颜色

def get_cell_font_color(sheet, row_index, column_index):
"""
获取单元格字体的颜色
:param sheet:
:param row_index:行索引
:param column_index:列索引
:return:
"""
cell_color = sheet.cell(row_index, column_index).font.color
if cell_color:
return sheet.cell(row_index, column_index).font.color.rgb
else:
# 颜色不存在,可能单元格没有数据
return None
def get_cell_bg_color(sheet, row_index, column_index):
"""
获取单元格背景的颜色
:param sheet:
:param row_index:行索引
:param column_index:列索引
:return:
"""
return sheet.cell(row_index, column_index).fill.fgColor.rgb

7. 最后

可以发现,openpyxl 相比 xlrd/xlwt,提供了大量实用的 API,功能更强大,并且完美支持 xlsx!

受限于篇幅,文中只展示了部分功能和代码,更加复杂的功能,比如:单元格合并、单元格完整样式操作,我已经封装成方法上传到后台

要获取全部源码,关注公众号「 AirPython 」,后台回复「 excel 」即可获得全部源码

如果你觉得文章还不错,请大家 点赞、分享、留言下,因为这将是我持续输出更多优质文章的最强动力!

推荐阅读

最全总结 | 聊聊 Python 办公自动化之 Excel(上)

最全总结 | 聊聊 Python 数据处理全家桶(配置篇)

我用几行 Python 自动化脚本完美解决掉了小姐姐的微信焦虑感

版权声明
本文为[AirPython]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/xingag/p/13958253.html

  1. 利用Python爬虫获取招聘网站职位信息
  2. Using Python crawler to obtain job information of recruitment website
  3. Several highly rated Python libraries arrow, jsonpath, psutil and tenacity are recommended
  4. Python装饰器
  5. Python实现LDAP认证
  6. Python decorator
  7. Implementing LDAP authentication with Python
  8. Vscode configures Python development environment!
  9. In Python, how dare you say you can't log module? ️
  10. 我收藏的有关Python的电子书和资料
  11. python 中 lambda的一些tips
  12. python中字典的一些tips
  13. python 用生成器生成斐波那契数列
  14. python脚本转pyc踩了个坑。。。
  15. My collection of e-books and materials about Python
  16. Some tips of lambda in Python
  17. Some tips of dictionary in Python
  18. Using Python generator to generate Fibonacci sequence
  19. The conversion of Python script to PyC stepped on a pit...
  20. Python游戏开发,pygame模块,Python实现扫雷小游戏
  21. Python game development, pyGame module, python implementation of minesweeping games
  22. Python实用工具,email模块,Python实现邮件远程控制自己电脑
  23. Python utility, email module, python realizes mail remote control of its own computer
  24. 毫无头绪的自学Python,你可能连门槛都摸不到!【最佳学习路线】
  25. Python读取二进制文件代码方法解析
  26. Python字典的实现原理
  27. Without a clue, you may not even touch the threshold【 Best learning route]
  28. Parsing method of Python reading binary file code
  29. Implementation principle of Python dictionary
  30. You must know the function of pandas to parse JSON data - JSON_ normalize()
  31. Python实用案例,私人定制,Python自动化生成爱豆专属2021日历
  32. Python practical case, private customization, python automatic generation of Adu exclusive 2021 calendar
  33. 《Python实例》震惊了,用Python这么简单实现了聊天系统的脏话,广告检测
  34. "Python instance" was shocked and realized the dirty words and advertisement detection of the chat system in Python
  35. Convolutional neural network processing sequence for Python deep learning
  36. Python data structure and algorithm (1) -- enum type enum
  37. 超全大厂算法岗百问百答(推荐系统/机器学习/深度学习/C++/Spark/python)
  38. 【Python进阶】你真的明白NumPy中的ndarray吗?
  39. All questions and answers for algorithm posts of super large factories (recommended system / machine learning / deep learning / C + + / spark / Python)
  40. [advanced Python] do you really understand ndarray in numpy?
  41. 【Python进阶】Python进阶专栏栏主自述:不忘初心,砥砺前行
  42. [advanced Python] Python advanced column main readme: never forget the original intention and forge ahead
  43. python垃圾回收和缓存管理
  44. java调用Python程序
  45. java调用Python程序
  46. Python常用函数有哪些?Python基础入门课程
  47. Python garbage collection and cache management
  48. Java calling Python program
  49. Java calling Python program
  50. What functions are commonly used in Python? Introduction to Python Basics
  51. Python basic knowledge
  52. Anaconda5.2 安装 Python 库(MySQLdb)的方法
  53. Python实现对脑电数据情绪分析
  54. Anaconda 5.2 method of installing Python Library (mysqldb)
  55. Python implements emotion analysis of EEG data
  56. Master some advanced usage of Python in 30 seconds, which makes others envy it
  57. python爬取百度图片并对图片做一系列处理
  58. Python crawls Baidu pictures and does a series of processing on them
  59. python链接mysql数据库
  60. Python link MySQL database