【python自动化办公】Python自动化之Excel——openpyxl 模块（入门）

前言

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 里