前言
1. 学习python自动化办公目的
- 学会使用python自动化处理文件,减少重复劳动
- 科学管理文件
- 能够不再使用别的软件合并、分割PDF等
2. 未来持续打卡内容
- Task03 Python与word和PDF
- Task04 简单的Python爬虫
- Task05 Python操作钉钉自动化
- Task06 其它推荐软件和网页
3. 资料来源与思维导图
本文不详细介绍函数,简单介绍方法,函数可自行百度,也可看课程GitHub链接:https://github.com/datawhalechina/office-automation。
openpyxl文档:https://openpyxl.readthedocs.io/en/stable/index.html
一、openpyxl简介
OpenPyXL 是读写 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 库,简单易用,功能广泛,单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有,图表功能是其一大亮点。优点:
- 几乎可以实现所有的 Excel 功能
- 接口清晰,文档丰富,学习成本相对较低
2. openpyxl对象介绍
- Workbook 工作簿
-
Workbook.get_sheet_by_name
方法,通过指定 sheetname 的方式来获取读取的工作簿中指定的 sheet(工作表) 对象 - 提过索引的方式获取读取的工作簿中指定的 sheet(工作表) 对象,如
wb['Sheet3']
- Worksheet工作表
-
Worksheet.title
获取 sheet 名称 -
Worksheet.dimensions
获取 sheet 中值的范围。
- Cell 单元格
- Cell 对象有一个 value 属性,包含这个单元格中保存的值。
- Cell 对象也有 row 、column 和 coordinate 属性,提供该单元格的位置信息。
- Excel 用字母指定列,在Z列之后,列开始使用两个字母:AA、AB等,所以在调用的 cell() 方法时,可传入整数作为 row 和 column 关键字参数,也可以得到一个单元格。
二、创建表格,写入数据
# 1) 导入 openpyxl 中的 Workbook 类
from openpyxl import Workbook
import os
root_path = os.os.getcwd()
# 2) 初始化一个 Workbook 对象
wb = Workbook()
print(f'默认sheet:{wb.sheetnames}')
# 3) 通过 Workbook 对象的 create_sheet 函数创建一个 sheet
# title sheet 名称
# index sheet 位置,默认从0开始
sheet = wb.create_sheet(title='mysheet', index=0)
print(f'添加后sheet:{wb.sheetnames}')
# 4) 在新建的 sheet 中写入数据
# 比如 在 A1 单元格中写入 'this is test'
sheet['A1'].value = 'this is test'
print(f"sheet['A1'].value = {sheet['A1'].value}")
# 保存
wb.save(root_path+'creat_sheet_test.xlsx')
几个注释:
- 创建Workbook时会自动创建一个worksheet,可以通过Workbook.active获得
- sheet创建后如果不传递名字,会自动命名,(Sheet, Sheet1, Sheet2, …),可以通过Worksheet.title改变
三 、与Pandas 和 NumPy
openpyxl 支持与Pandas 和 NumPy共同工作
支持numpy
OpenPyXl对Numpy类型浮点,整数和布尔值具有内置支持。使用Pandas的时间戳类型可以支持DateTimes。
Working with Pandas Dataframes
openpyxl.utils.dataframe.dataframe_to_rows()
函数提供了简单的与Pandas Dataframes共同工作的方法:
from openpyxl.utils.dataframe import
from openpyxl import Workbook
dataframe_to_rows
wb = Workbook()
ws = wb.active
#创建一个df
df = pd.DataFrame({
'money':np.random.randint(45, 50, size = [1, 20])[0],
},
index=pd.date_range('2021-02-01', '2021-02-20'), # 行索引和时间相关
)
df.index.name = '消费日期' # 设置索引名字
#将df写入ws中
for r in dataframe_to_rows(df, index=True, header=True):
ws.append(r)
#将数据框架转换为工作表,突出显示标题和索引:
for cell in ws['A'] + ws[1]:
cell.style = 'Pandas'
wb.save(root_path+'creat_sheet_df.xlsx')
四、绘图
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
rows = [
('月份', '苹果', '香蕉'),
(1, 43, 25),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "销量柱状图"
chart1.y_axis.title = '销量'
chart1.x_axis.title = '月份'
data = Reference(ws, min_col=2, min_row=1, max_row=8, max_col=3)
series = Reference(ws, min_col=1, min_row=2, max_row=8)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(series)
ws.add_chart(chart1, "A10")
绘图步骤:
使用柱状图类 BarChart
和 数据应用类 Reference
- 创建 Workbook,并为活动 Sheet 添加数据
- 创建柱状图对象,设置图表属性,
type
为 col 为列状图,bar 为水平图 - 创建数据引用对象,指定从那个 sheet 以及数据范围
- 创建系列数据引用对象
- 将数据和系列加入到图表对象中
- 最后将图表对象用 add_chart 添加到 sheet 里